Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the use of MySQL locks, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Lock is a mechanism by which a computer coordinates multiple processes or threads to access a resource concurrently. In the database, in addition to the contention of traditional computing resources (such as CPU, RAM, Imax O, etc.), data is also a kind of resource shared by many users. How to ensure the consistency and effectiveness of data concurrent access is a problem that must be solved in all databases, and lock conflicts are also an important factor affecting the performance of database concurrent access. From this point of view, locks are particularly important and more complex for databases. In this chapter, we focus on the characteristics of MySQL locking mechanism, common locking problems, and some methods or suggestions to solve MySQL locking problems.

Overview of MySQL Lock

Compared with other databases, the locking mechanism of MySQL is relatively simple, and its most prominent feature is that different storage engines support different locking mechanisms. For example, MyISAM and MEMORY storage engines use table-level locks (table-level locking); BDB storage engines use page locks (page-level locking), but also support table-level locks; and InnoDB storage engines support both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.

The characteristics of the three locks of MySQL can be roughly summarized as follows.

Overhead, locking speed, deadlock, granularity, concurrency performance

Table-level lock: low overhead, fast locking; no deadlock; large lock granularity, the highest probability of lock conflict and the lowest concurrency.

Row-level locks: expensive and slow to add locks; deadlocks occur; locking granularity is the smallest, the probability of lock conflicts is the lowest, and the degree of concurrency is the highest.

L page lock: the overhead and locking time are between table lock and row lock; deadlocks occur; lock granularity is between table lock and row lock, and the concurrency is general.

As can be seen from the above characteristics, it is difficult to say in general which kind of lock is better, only in terms of the characteristics of the specific application, which kind of lock is more appropriate! Only from the perspective of locks: table-level locks are more suitable for applications that focus on queries, with only a small amount of data updated according to index conditions, such as Web applications, while row-level locks are more suitable for applications with a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries, such as some online transaction processing (OLTP) systems. This is also mentioned in the "Development" of this book when it introduces the choice of table types. The following sections focus on MySQL table locks and InnoDB row locks. Since BDB has been replaced by InnoDB and is about to become a thing of the past, we will not discuss it further here.

MyISAM table lock

The MyISAM storage engine only supports table locks, which is the only lock type supported in the first few versions of MySQL. With the increasing requirements of applications for transaction integrity and concurrency, MySQL began to develop a transaction-based storage engine, and then slowly emerged a BDB storage engine that supports page locks and an InnoDB storage engine that supports row locks (actually InnoDB is a separate company that has now been acquired by Oracle). But MyISAM's table lock is still the most widely used lock type. This section details the use of MyISAM table locks.

Query table-level lock contention

Table locking contention on the system can be analyzed by examining the table_locks_waited and table_locks_immediate state variables:

Mysql > show status like 'table%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 2979 | |

| | Table_locks_waited | 0 | |

+-+ +

2 rows in set (0.00 sec)

If the value of Table_locks_waited is high, there is a serious table-level lock contention condition.

Lock mode of MySQL table-level lock

MySQL has two modes of table-level locking: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write Lock). The compatibility of lock mode is shown in Table 20-1.

Table lock compatibility in table 20-1 MySQL

Request lock mode

Is it compatible?

Current lock mode

None read lock, write lock, read lock, write lock, no lock

It can be seen that the read operation to the MyISAM table will not block other users' read requests to the same table, but will block the write requests to the same table; the write operation to the MyISAM table will block other users' read and write operations to the same table; the read operation and write operation of the MyISAM table are serial! According to the example shown in Table 20-2, when a thread acquires a write lock on a table, only the thread that holds the lock can update the table. The read and write operations of other threads wait until the lock is released.

Table 20-2 write blocking read examples for the MyISAM storage engine

Session_1session_2

Get the WRITE lock of table film_text

Mysql > lock table film_text write

Query OK, 0 rows affected (0.00 sec)

Currently, session can perform query, update, and insert operations on locked tables:

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | Update Test |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into film_text (film_id,title) values (1003 recorder Test')

Query OK, 1 row affected (0.00 sec)

Mysql > update film_text set title = 'Test' where film_id = 1001

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Other session queries on the locked table are blocked and need to wait for the lock to be released:

Mysql > select film_id,title from film_text where film_id = 1001

wait for

Release the lock:

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

wait for

Session2 gets the lock, and the query returns:

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | Test |

+-+ +

1 row in set (57.59 sec)

How to add a table lock

MyISAM automatically locks all tables involved before executing the query statement (SELECT), and automatically adds write locks to the tables before performing update operations (UPDATE, DELETE, INSERT, etc.). This process does not require user intervention, so users generally do not need to lock the MyISAM table directly with the LOCK TABLE command. In the examples in this book, explicit locking is basically for convenience, not necessarily.

The purpose of locking the MyISAM table display is to simulate the transaction operation to a certain extent and realize the consistent reading of multiple tables at a certain point in time. For example, there is an order table orders, which records the total amount total of each order, and an order schedule order_detail, which records the subtotal subtotal of each product for each order. Suppose we need to check whether the sum of the two tables match, we may need to execute the following two SQL:

Select sum (total) from orders

Select sum (subtotal) from order_detail

At this point, if you do not lock the two tables first, you may have the wrong result, because the order_ detail table may have changed during the execution of the first statement. Therefore, the correct way should be:

Lock tables orders read local, order_detail read local

Select sum (total) from orders

Select sum (subtotal) from order_detail

Unlock tables

The following two points should be specifically stated.

? The above example adds the "local" option to LOCK TABLES, and its function is to allow other users to insert records concurrently at the end of the table when the condition of concurrent insertion of MyISAM table is met. The problem of concurrent insertion of MyISAM table will be further introduced in later chapters.

? When explicitly locking a table with LOCK TABLES, you must acquire all locks involving the table at the same time, and MySQL does not support lock escalation. That is, after LOCK TABLES, you can only access these explicitly locked tables, not unlocked tables; at the same time, if you add a read lock, you can only perform query operations, not update operations. In fact, this is basically the case in the case of automatic locking, where MyISAM always acquires all the locks needed by the SQL statement at once. This is why there is no Deadlock Free in the MyISAM table.

In the example shown in Table 20-3, a session uses the LOCK TABLE command to add a read lock to the table film_text. This session can query the records in the locked table, but updating or accessing other tables will prompt an error; at the same time, another session can query the records in the table, but the update will cause a lock wait.

Table 20-3 write examples of read blocking for MyISAM storage engine

Session_1session_2

Get the READ lock of table film_text

Mysql > lock table film_text read

Query OK, 0 rows affected (0.00 sec)

The current session can query the table record

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | ACADEMY DINOSAUR |

+-+ +

1 row in set (0.00 sec)

Other session can also query the records of this table

Mysql > select film_id,title from film_text where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | ACADEMY DINOSAUR |

+-+ +

1 row in set (0.00 sec)

The current session cannot query tables that are not locked

Mysql > select film_id,title from film where film_id = 1001

ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES

Other session can query or update unlocked tables

Mysql > select film_id,title from film where film_id = 1001

+-+ +

| | film_id | title |

+-+ +

| | 1001 | update record |

+-+ +

1 row in set (0.00 sec)

Mysql > update film set title = 'Test' where film_id = 1001

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Inserting or updating locked tables in the current session will prompt an error:

Mysql > insert into film_text (film_id,title) values (1002 recorder Test')

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Mysql > update film_text set title = 'Test' where film_id = 1001

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Other session update lock tables wait for the lock to be acquired:

Mysql > update film_text set title = 'Test' where film_id = 1001

wait for

Release lock

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

wait for

Session acquires the lock, and the update operation is completed:

Mysql > update film_text set title = 'Test' where film_id = 1001

Query OK, 1 row affected (1 min 0.71 sec)

Rows matched: 1 Changed: 1 Warnings: 0

When using LOCK TABLES, you not only need to lock all the tables used at once, but also lock the same table with the same alias as in the SQL statement how many times it appears in the SQL statement, otherwise you will also make an error! Examples are given below.

(1) obtain a read lock on the actor table:

Mysql > lock table actor read

Query OK, 0 rows affected (0.00 sec)

(2) however, accessing through an alias will prompt an error:

Mysql > select a.firstrunname.lastroomnamereb.firstroomnamerewritingb.lastroomname from actor an actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name =' Tom' and a.last_name b.last_name

ERROR 1100 (HY000): Table 'a'was not locked with LOCK TABLES

(3) aliases need to be locked separately:

Mysql > lock table actor as a read,actor as b read

Query OK, 0 rows affected (0.00 sec)

(4) the query based on the alias can be executed correctly:

Mysql > select a.firstrunname.lastroomnamereb.firstroomnamerewritingb.lastroomname from actor an actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name =' Tom' and a.last_name b.last_name

+-+

| | first_name | last_name | first_name | last_name | |

+-+

| | Lisa | Tom | LISA | MONROE | |

+-+

1 row in set (0.00 sec)

Concurrent insertion (Concurrent Inserts)

As mentioned above, the reading and writing of the MyISAM table are serial, but this is overall. Under certain conditions, MyISAM tables also support the concurrency of query and insert operations.

The MyISAM storage engine has a system variable, concurrent_insert, specifically designed to control its concurrent insertion behavior, which can be 0, 1, or 2, respectively.

When concurrent_insert is set to 0, concurrent insertion is not allowed.

When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts records from the footer. This is also the default setting for MySQL.

When concurrent_insert is set to 2, records are allowed to be inserted concurrently at the end of the table, regardless of whether there are holes in the MyISAM table.

In the example shown in Table 20-4, session_1 acquires an READ LOCAL lock on a table that can query the table but cannot update the table; other threads (session_2), although they cannot delete and update the table, can insert the table concurrently, assuming that there are no holes in the middle of the table.

Table 20-4 read-write (INSERT) concurrency examples for MyISAM storage engine

Session_1session_2

Get the READ LOCAL lock of table film_text

Mysql > lock table film_text read local

Query OK, 0 rows affected (0.00 sec)

Currently, session cannot update or insert locking tables:

Mysql > insert into film_text (film_id,title) values (1002 recorder Test')

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Mysql > update film_text set title = 'Test' where film_id = 1001

ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated

Other session can insert, but the update waits:

Mysql > insert into film_text (film_id,title) values (1002 recorder Test')

Query OK, 1 row affected (0.00 sec)

Mysql > update film_text set title = 'Update Test' where film_id = 1001

wait for

The current session cannot access records inserted by other session:

Mysql > select film_id,title from film_text where film_id = 1002

Empty set (0.00 sec)

Release the lock:

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

wait for

Other session inserted records can be obtained after the current session is unlocked:

Mysql > select film_id,title from film_text where film_id = 1002

+-+ +

| | film_id | title |

+-+ +

| | 1002 | Test |

+-+ +

1 row in set (0.00 sec)

Session2 acquires the lock, and the update operation is completed:

Mysql > update film_text set title = 'Update Test' where film_id = 1001

Query OK, 1 row affected (1 min 17.75 sec)

Rows matched: 1 Changed: 1 Warnings: 0

The concurrent insert feature of the MyISAM storage engine can be used to solve the lock contention for the same table query and insert in the application. For example, setting the concurrent_insert system variable to 2 always allows concurrent insertions; at the same time, defragment the space by executing OPTIMIZE TABLE statements periodically during the idle period of the system to recover the middle hole caused by deleting the record. For a detailed description of the OPTIMIZE TABLE statement, you can see the section "two simple and practical optimization methods" in Chapter 18.

Lock scheduling of MyISAM

As mentioned earlier, the read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So how does MySQL handle that one process requests a read lock for a MyISAM table while another process requests a write lock for the same table? The answer is that the writing process acquires the lock first. Not only that, even if the read request goes to the lock waiting queue first, and after the write request, the write lock will be inserted before the read lock request! This is because MySQL believes that write requests are generally more important than read requests. This is why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks and may be blocked forever. This situation can sometimes get very bad! Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

? By specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to read requests by default.

? Lower the priority of update requests issued by the connection by executing the command SET LOW_PRIORITY_UPDATES=1.

? Lower the priority of the statement by specifying the LOW_PRIORITY property of the INSERT, UPDATE, and DELETE statement.

Although the above three methods are either update priority or query priority, they can still be used to solve the serious problem of reading lock waiting in relatively important applications (such as user login system).

In addition, MySQL also provides a compromise method to adjust the read-write conflict, that is, set an appropriate value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request, giving the read process a chance to acquire the lock.

The problems and solutions caused by the write-first scheduling mechanism have been discussed above. I would also like to emphasize one point here: some query operations that need to run for a long time will also make the writing process starve to death! Therefore, in the application, we should try to avoid long-running query operations, and do not always want to use a SELECT statement to solve the problem, because this seemingly ingenious SQL statement is often more complex and takes a long time to execute. If possible, we can use intermediate tables and other measures to do certain "decomposition" of SQL statements, so that each step of the query can be completed in a short time, so as to reduce lock conflicts. If complex queries are inevitable, try to schedule execution during database idle periods, such as some periodic statistics that can be scheduled to be performed at night.

InnoDB lock problem

There are two biggest differences between InnoDB and MyISAM: one is to support transactions (TRANSACTION); the other is to use row-level locks. There are many differences between row-level locks and table-level locks. In addition, the introduction of transactions also brings some new problems. Let's first introduce a little background, and then discuss the locking problem of InnoDB in detail.

Background knowledge 1. Transaction and its ACID attributes

A transaction is a logical processing unit composed of a set of SQL statements. A transaction has the following four attributes, which are usually referred to as the ACID attributes of a transaction.

Atomicity: a transaction is an atomic operation unit that either performs all or none of the changes to the data.

Consistency (Consistent): data must be in a consistent state at the beginning and completion of a transaction. This means that all relevant data rules must be applied to the modification of the transaction to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or two-way linked lists) must also be correct.

Isolation: the database system provides an isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that the intermediate state during the transaction is not visible to the outside, and vice versa.

L Durable: after a transaction is completed, its modification of the data is permanent and can be maintained even in the event of a system failure.

A bank transfer is a typical example of a transaction.

2. Problems caused by concurrent transaction processing

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources and improve the transaction throughput of the database system, thus supporting more users. However, concurrent transaction processing can also bring some problems, including the following situations.

Lost update (Lost Update): when two or more transactions select the same row and then update the row based on the initially selected value, the problem of missing updates occurs because each transaction is unaware of the existence of other transactions-the last update overwrites updates made by other transactions. For example, two editors made an electronic copy of the same document. Each editor changes its copy independently, and then saves the changed copy, thus overwriting the original document. The editor who finally saved a copy of his changes overrides the changes made by another editor. This problem can be avoided if another editor cannot access the same file before one editor completes and commits the transaction.

Dirty reading (Dirty Reads): a transaction is modifying a record, and the data of this record is in an inconsistent state before the transaction is completed and committed; at this time, another transaction also reads the same record. If uncontrolled, the second transaction reads these "dirty" data and makes further processing accordingly, resulting in uncommitted data dependencies. This phenomenon is vividly called "dirty reading".

Unrepeatable Non-Repeatable Reads: a transaction reads previously read data again at some time after reading some data, only to find that the data it reads has changed, or some records have been deleted! This phenomenon is called "unrepeatable reading".

L Phantom Reads: a transaction re-reads previously retrieved data according to the same query conditions, only to find that other transactions insert new data that meets its query criteria. This phenomenon is called "phantom reading".

3. Transaction isolation level

Among the problems caused by concurrent transactions mentioned above, "update loss" should usually be avoided completely. However, preventing the loss of updates can not be solved by the database transaction controller alone, and the application needs to add the necessary locks to the data to be updated. Therefore, it should be the responsibility of the application to prevent the loss of updates.

In fact, "dirty reading", "unrepeatable reading" and "phantom reading" are all problems of database read consistency, which must be solved by the database to provide a certain transaction isolation mechanism. Database transaction isolation can be basically divided into the following two ways.

One is to lock the data before reading it to prevent other transactions from modifying the data.

The other is to generate a consistent data snapshot (Snapshot) of a data request point in time without adding any locks, and use this snapshot to provide a certain level of consistent reading (statement level or transaction level). From the user's point of view, it seems that a database can provide multiple versions of the same data, so this technology is called data multi-version concurrency control (MultiVersion Concurrency Control, referred to as MVCC or MCC), and is often called multi-version database.

The stricter the transaction isolation of the database, the less the side effects, but the greater the cost, because transaction isolation is essentially to make transactions "serialized" to a certain extent, which is obviously contradictory to "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to "unrepeatable reading" and "phantom reading" and may be more concerned about the ability to access data concurrently.

In order to solve the contradiction between "isolation" and "concurrency", ISO/ANSI SQL92 defines four transaction isolation levels. The isolation degree of each level is different and the allowed side effects are also different. Applications can balance the contradiction between "isolation" and "concurrency" by selecting different isolation levels according to their own business logic requirements. Table 20-5 provides a good overview of the characteristics of these four isolation levels.

Table 20-5 comparison of four isolation levels

Read data consistency and allowed concurrent side effects

Isolation level

Read data consistency dirty reading can not repeat fantasy reading

Uncommitted read (Read uncommitted)

At the lowest level, it is only guaranteed that physically corrupted data will not be read

Degree of submission (Read committed)

Whether the statement level is yes or not

Repeatable read (Repeatable read)

Whether the transaction level is

Serializer (Serializable)

Highest level, transaction level No No

Finally, it is important to note that each specific database does not necessarily fully implement the above four isolation levels. For example, Oracle only provides two standard isolation levels, Read committed and Serializable, as well as self-defined Read only isolation levels; SQL Server supports an isolation level called "snapshot" in addition to the four isolation levels defined by ISO/ANSI SQL92 above, but strictly speaking, it is an Serializable isolation level implemented with MVCC. MySQL supports all four isolation levels, but there are some features in the implementation, such as using MVCC consistent reading under some isolation levels, but not in some cases, which will be further described in later chapters.

Get InnoDB row lock contention

You can analyze row lock contention on the system by checking the InnoDB_row_lock state variable:

Mysql > show status like 'innodb_row_lock%'

+-+ +

| | Variable_name | Value |

+-+ +

| | InnoDB_row_lock_current_waits | 0 | |

| | InnoDB_row_lock_time | 0 | |

| | InnoDB_row_lock_time_avg | 0 | |

| | InnoDB_row_lock_time_max | 0 | |

| | InnoDB_row_lock_waits | 0 | |

+-+ +

5 rows in set (0.01 sec)

If you find that lock contention is serious, such as high values of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg, you can further observe the tables and data rows that have lock conflicts by setting InnoDB Monitors, and analyze the causes of lock contention.

The specific methods are as follows:

Mysql > CREATE TABLE innodb_monitor (an INT) ENGINE=INNODB

Query OK, 0 rows affected (0.14 sec)

You can then view it with the following statement:

Mysql > Show innodb status\ G

* * 1. Row *

Type: InnoDB

Name:

Status:

...

...

-

TRANSACTIONS

-

Trx id counter 0 117472192

Purge done for trx's n:o

< 0 117472190 undo n:o < 0 0 History list length 17 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456 MySQL thread id 200610, query id 291197 localhost root ---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936 MySQL thread id 199285, query id 291199 localhost root Show innodb status … 监视器可以通过发出下列语句来停止查看： mysql>

DROP TABLE innodb_monitor

Query OK, 0 rows affected (0.05 sec)

After setting up the monitor, in the display content of SHOW INNODB STATUS, there will be detailed information about the current lock waiting, including table name, lock type, locking record and so on, which is convenient for further analysis and problem determination. After opening the monitor, by default, the monitoring content will be recorded in the log every 15 seconds, and if it is opened for a long time, the .err file will become very large, so users should remember to delete the monitoring table to close the monitor after confirming the cause of the problem, or by using the "--console" option to start the server to shut down the log file.

Row Lock Mode and locking method of InnoDB

InnoDB implements the following two types of row locks.

L shared lock (S): allows one transaction to read a row, preventing other transactions from acquiring exclusive locks for the same dataset.

L exclusive lock (X): allows transaction update data to be acquired with exclusive locks, preventing other transactions from acquiring shared read locks and exclusive write locks for the same dataset.

In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity locking mechanism, InnoDB also has two internally used Intention Locks locks, both of which are table locks.

Intention shared lock (IS): a transaction intends to add a row shared lock to a data row, and the transaction must acquire the IS lock of the table before adding a shared lock to the data row.

Intention exclusive lock (IX): a transaction intends to add an exclusive lock to a data row, and the transaction must acquire the IX lock of the table before adding an exclusive lock to the data row.

The compatibility of the above lock modes is shown in Table 20-6.

Table 20-6 InnoDB row lock mode compatibility list

Request lock mode

Is it compatible?

Current lock mode

XIXSISX conflict IX conflict compatibility conflict compatibility S conflict compatibility IS conflict compatibility

If the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction; conversely, if the two are not compatible, the transaction waits for the lock to be released.

The intention lock is automatically added by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds exclusive locks (X) to the dataset involved; for normal SELECT statements, InnoDB does not add any locks; transactions can be shown to add shared or exclusive locks to the recordset through the following statement.

? Shared lock (S): SELECT * FROM table_name WHERE... LOCK IN SHARE MODE .

? Exclusive lock (X): SELECT * FROM table_name WHERE. FOR UPDATE .

Use SELECT... IN SHARE MODE acquires a shared lock, which is mainly used to confirm the existence of a row record when data dependencies are needed, and to ensure that no one UPDATE or DELETE the record. However, if the current transaction also needs to update the record, it is likely to cause deadlock. For applications that need to update the row record after locking it, SELECT... should be used. The exclusive lock is obtained by FOR UPDATE.

In the example shown in Table 20-7, SELECT... Update the record after IN SHARE MODE is locked to see what happens, where the actor_id field of the actor table is the primary key.

Table 20-7 examples of shared locks for the InnoDB storage engine

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

The current session adds the shared lock of share mode to the record of actor_id=178:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.01 sec)

Other session can still query the record, and you can also add share mode's shared lock to the record:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.01 sec)

The current session updates the locked record and waits for the lock:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

wait for

Other session also updates the record, which will cause the deadlock to exit:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

After obtaining the lock, you can successfully update:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

Query OK, 1 row affected (17.67 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Update the record after locking with SELECT...FOR UPDATE, as shown in Table 20-8.

Table 20-8 examples of exclusive locks for the InnoDB storage engine

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

The current session records for actor_id=178 plus for update exclusive locks:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178for update

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Other session can query the record, but cannot add a shared lock to the record, and will wait for the lock to be acquired:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178,

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE |

+-+

1 row in set (0.00 sec)

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178for update

wait for

The current session can update the locked record and release the lock after the update:

Mysql > update actor set last_name = 'MONROE T' where actor_id = 178,

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Other session acquires locks and records submitted by other session:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 178for update

+-+

| | actor_id | first_name | last_name | |

+-+

| | 178 | LISA | MONROE T |

+-+

1 row in set (9.59 sec)

InnoDB Row Lock implementation

InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!

In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts, thus affecting concurrency performance. The following is illustrated by some practical examples.

(1) InnoDB does use table locks instead of row locks when querying without index conditions.

In the example shown in Table 20-9, the tab_no_index table does not have an index at first:

Mysql > create table tab_no_index (id int,name varchar (10)) engine=innodb

Query OK, 0 rows affected (0.15 sec)

Mysql > insert into tab_no_index values (1), (2) (2), (3), (4)

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Table 20-9 example of using table locks for InnoDB storage engine tables without indexes

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_no_index where id = 1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_no_index where id = 2

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_no_index where id = 1 for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_no_index where id = 2 for update

wait for

In the example shown in Table 20-9, it looks like session_1 only adds an exclusive lock to one row, but session_2 waits for a lock when it requests an exclusive lock for another row! The reason is that without an index, InnoDB can only use table locks. When we add an index to it, InnoDB locks only eligible rows, as shown in tables 20-10.

Create a tab_with_ index table. The id field has a normal index:

Mysql > create table tab_with_index (id int,name varchar (10)) engine=innodb

Query OK, 0 rows affected (0.15 sec)

Mysql > alter table tab_with_index add index id (id)

Query OK, 4 rows affected (0.24 sec)

Records: 4 Duplicates: 0 Warnings: 0

Table 20-10 examples of InnoDB storage engine tables using row locks when using indexes

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 2

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_with_index where id = 1 for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from tab_with_index where id = 2 for update

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

(2) because the row lock of MySQL is a lock for index, not a lock for records, although it accesses records of different rows, lock conflicts will occur if the same index key is used. You should pay attention to this when applying design.

In the example shown in tables 20-11, the id field of table tab_with_index has an index, and the name field has no index:

Mysql > alter table tab_with_index drop index name

Query OK, 4 rows affected (0.22 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > insert into tab_with_index values (1)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

| | 1 | 4 |

+-+ +

2 rows in set (0.00 sec)

Table 20-11 blocking examples of the InnoDB storage engine using the same index key

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1 and name ='1' for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

+-+ +

1 row in set (0.00 sec)

Although session_2 accesses a different record than session_1, because it uses the same index, you need to wait for the lock:

Mysql > select * from tab_with_index where id = 1 and name ='4' for update

wait for

(3) when the table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB will use row locks to lock the data, whether using primary key indexes, unique indexes, or normal indexes.

In the example shown in tables 20-12, the id field of table tab_with_index has a primary key index and the name field has a normal index:

Mysql > alter table tab_with_index add index name (name)

Query OK, 5 rows affected (0.23 sec)

Records: 5 Duplicates: 0 Warnings: 0

Table 20-12 blocking examples of InnoDB storage engine tables using different indexes

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from tab_with_index where id = 1 for update

+-+ +

| | id | name |

+-+ +

| | 1 | 1 |

| | 1 | 4 |

+-+ +

2 rows in set (0.00 sec)

Session_2 uses name's index to access the record, and because the record is not indexed, the lock can be obtained:

Mysql > select * from tab_with_index where name ='2' for update

+-+ +

| | id | name |

+-+ +

| | 2 | 2 |

+-+ +

1 row in set (0.00 sec)

Since the accessed record has been locked by session_1, wait for the lock to be acquired. :

Mysql > select * from tab_with_index where name ='4' for update

(4) even if the index field is used in the condition, whether or not to use the index to retrieve data is decided by MySQL by judging the cost of different execution plans. If MySQL thinks that a full table scan is more efficient, for example, for some very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the execution plan of SQL to confirm that indexes are actually used. For a detailed discussion of when MySQL does not use indexes, see the introduction to the "indexing issues" section of this chapter.

In the following example, the data type of the retrieval value is different from that of the index field, and although MySQL can convert the data type, it does not use the index, which causes InnoDB to use table locks. We can clearly see this by examining the execution plans of the two SQL with explain.

In the example, the name field of the tab_with_index table has an index, but the name field is of type varchar. If the where condition is not compared with the varchar type, the name will be converted and the full table scan will be performed.

Mysql > alter table tab_no_index add index name (name)

Query OK, 4 rows affected (8.06sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > explain select * from tab_with_index where name = 1\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: tab_with_index

Type: ALL

Possible_keys: name

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 4

Extra: Using where

1 row in set (0.00 sec)

Mysql > explain select * from tab_with_index where name ='1'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: tab_with_index

Type: ref

Possible_keys: name

Key: name

Key_len: 23

Ref: const

Rows: 1

Extra: Using where

1 row in set (0.00 sec)

Gap lock (Next-Key lock)

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the range of conditions but do not exist, it is called "GAP", and InnoDB will also lock this "gap", which is the so-called Next-Key lock.

For example, if there are only 101 records in the emp table, the values of their empid are 1, 2, and 100, respectively, and the following SQL:

Select * from emp where empid > 100 for update

Is a search of range conditions, where InnoDB locks not only records with an empid value of 101, but also "gaps" with an empid greater than 101 (these records do not exist).

The purpose of InnoDB using gap lock is, on the one hand, to prevent misreading in order to meet the requirements of the relevant isolation level. For the above example, if you do not use gap lock, if other transactions insert any records with an empid greater than 100, then if the transaction executes the above statement again, false reading will occur; on the other hand, it is to meet its recovery and replication needs. The impact of its recovery and replication on the locking mechanism, as well as the use of gap locks by InnoDB under different isolation levels, will be further described in subsequent chapters.

Obviously, when using range conditions to retrieve and lock records, the locking mechanism of InnoDB will block the concurrent insertion of key values within the conditional range, which often leads to serious lock waiting. Therefore, in the practical application development, especially the applications with more concurrent inserts, we should optimize the business logic as far as possible, use equal conditions to access updated data as far as possible, and avoid using scope conditions.

In particular, in addition to using a gap lock when locking through a range condition, InnoDB will also use a gap lock if an equal condition is used to request a lock on a record that does not exist!

In the example shown in Table 20-13, if there are only 101 records in the emp table, the empid values are 1, 2, and 100, respectively.

Table 20-13 examples of gap lock blocking for the InnoDB storage engine

Session_1session_2

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

The current session adds for update locks to records that do not exist:

Mysql > select * from emp where empid = 102for update

Empty set (0.00 sec)

At this point, if another session inserts a record with an empid of 102 (note: this record does not exist), a lock wait will also occur:

Mysql > insert into emp (empid,...) Values (102)

Blocking waiting

Session_1 executes rollback:

Mysql > rollback

Query OK, 0 rows affected (13.04 sec)

Because other session_1 backs back and releases the Next-Key lock, the current session can acquire the lock and successfully insert the record:

Mysql > insert into emp (empid,...) Values (102)

Query OK, 1 row affected (13.35 sec)

The need for recovery and replication, the impact on the InnoDB locking mechanism

Through BINLOG recording, MySQL executes successful SQL statements for updating data such as INSERT, UPDATE, DELETE, etc., and thus realizes the recovery of MySQL database and master-slave replication (see the introduction of the management section of this book). MySQL's recovery mechanism (replication is actually constantly doing BINLOG-based recovery in Slave Mysql) has the following characteristics.

First, the recovery of MySQL is at the SQL statement level, that is, re-executing the SQL statement in BINLOG. Unlike Oracle databases, Oracle is based on database file blocks.

Second, the Binlog of MySQL is recorded according to the order in which transactions are committed, and the recovery is also carried out in this order. This is also different from Oralce, which recovers data according to the system update number (System Change Number,SCN). At the beginning of each transaction, Oracle allocates a globally unique SCN,SCN in the same order as the transaction starts.

From the above two points, we can see that the recovery mechanism of MySQL requires that before a transaction is committed, other concurrent transactions cannot insert any records that meet its locking conditions, that is, no illusion is allowed, which has exceeded the requirement of the ISO/ANSI SQL92 "repeatable read" isolation level. In fact, it requires the transaction to be serialized. This is also the reason why InnoDB uses gap locks in many cases. For example, when updating records with range conditions, InnoDB uses gap locks both at Read Commited and Repeatable Read isolation levels, but this is not required by isolation levels. The differences between InnoDB locking under different isolation levels will be described in the next section.

In addition, for "insert into target_tab select * from source_tab where..." And the SQL statement "create table new_tab... select... From source_tab where... (CTAS)", the user does not do any updates to source_tab, but MySQL does special treatment for this kind of SQL statement. Let's first look at the examples shown in Table 20-14.

Table 20-14 example of locking the original table by CTAS operation

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from target_tab

Empty set (0.00 sec)

Mysql > select * from source_tab where name ='1'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 1 | 1 |

| | 5 | 1 | 1 |

| | 6 | 1 | 1 |

| | 7 | 1 | 1 |

| | 8 | 1 | 1 |

+-+

5 rows in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from target_tab

Empty set (0.00 sec)

Mysql > select * from source_tab where name ='1'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 1 | 1 |

| | 5 | 1 | 1 |

| | 6 | 1 | 1 |

| | 7 | 1 | 1 |

| | 8 | 1 | 1 |

+-+

5 rows in set (0.00 sec)

Mysql > insert into target_tab select D1 name from source_tab where name ='1'

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

Mysql > update source_tab set name ='1' where name ='8'

wait for

Commit

Return the result

Commit

In the above example, simply reading the data in the source_tab table is equivalent to executing a normal SELECT statement, which can be read with consistency. This is exactly what ORACLE does. It uses multiple versions of data implemented by MVCC technology to achieve consistent reading without any lock on the source_tab. We know that InnoDB also implements multi-version data, and there is no need to add any locks to ordinary SELECT consistent reading, but here InnoDB adds a shared lock to source_tab and does not use multi-version data consistent reading technology!

Why would MySQL do that? The reason is to ensure the correctness of recovery and replication. Because it is not locked, if other transactions update the source_tab during the execution of the above statement, it may result in an error in the result of data recovery. To demonstrate this, let's repeat the previous example, except that before session_1 executes the transaction, set the value of the system variable innodb_locks_unsafe_for_binlog to "on" (its default value is off), as shown in Table 20-15.

Table 20-15 examples of security problems caused by CTAS operations that do not lock the original table

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > set innodb_locks_unsafe_for_binlog='on'

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from target_tab

Empty set (0.00 sec)

Mysql > select * from source_tab where name ='1'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 1 | 1 |

| | 5 | 1 | 1 |

| | 6 | 1 | 1 |

| | 7 | 1 | 1 |

| | 8 | 1 | 1 |

+-+

5 rows in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from target_tab

Empty set (0.00 sec)

Mysql > select * from source_tab where name ='1'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 1 | 1 |

| | 5 | 1 | 1 |

| | 6 | 1 | 1 |

| | 7 | 1 | 1 |

| | 8 | 1 | 1 |

+-+

5 rows in set (0.00 sec)

Mysql > insert into target_tab select D1 name from source_tab where name ='1'

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

Session_1 has not been submitted, you can update the record of session_1 's select.

Mysql > update source_tab set name ='8' where name ='1'

Query OK, 5 rows affected (0.00 sec)

Rows matched: 5 Changed: 5 Warnings: 0

Mysql > select * from source_tab where name ='8'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 8 | 1 |

| | 5 | 8 | 1 |

| | 6 | 8 | 1 |

| | 7 | 8 | 1 |

| | 8 | 8 | 1 |

+-+

5 rows in set (0.00 sec)

Update operation is submitted first

Mysql > commit

Query OK, 0 rows affected (0.05 sec)

Submit after insert operation

Mysql > commit

Query OK, 0 rows affected (0.07 sec)

If you view the data at this point, you can insert the results before the source_tab update into the target_tab, which is in line with the application logic:

Mysql > select * from source_tab where name ='8'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 8 | 1 |

| | 5 | 8 | 1 |

| | 6 | 8 | 1 |

| | 7 | 8 | 1 |

| | 8 | 8 | 1 |

+-+

5 rows in set (0.00 sec)

Mysql > select * from target_tab

+-+ +

| | id | name |

+-+ +

| | 4 | 1.00 |

| | 5 | 1.00 |

| | 6 | 1.00 |

| | 7 | 1.00 |

| | 8 | 1.00 |

+-+ +

5 rows in set (0.00 sec)

Mysql > select * from tt1 where name ='1'

Empty set (0.00 sec)

Mysql > select * from source_tab where name ='8'

+-+

| | D1 | name | D2 |

+-+

| | 4 | 8 | 1 |

| | 5 | 8 | 1 |

| | 6 | 8 | 1 |

| | 7 | 8 | 1 |

| | 8 | 8 | 1 |

+-+

5 rows in set (0.00 sec)

Mysql > select * from target_tab

+-+ +

| | id | name |

+-+ +

| | 4 | 1.00 |

| | 5 | 1.00 |

| | 6 | 1.00 |

| | 7 | 1.00 |

| | 8 | 1.00 |

+-+ +

5 rows in set (0.00 sec)

As can be seen from above, after setting the value of the system variable innodb_locks_unsafe_for_binlog to "on", InnoDB no longer locks the source_tab, and the result is in line with the application logic, but if you analyze the contents of the BINLOG:

.

SET TIMESTAMP=1169175130

BEGIN

# at 274

# 070119 10:51:57 server id 1 end_log_pos 105 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1169175117

Update source_tab set name ='8' where name ='1'

# at 379

# 070119 10:52:10 server id 1 end_log_pos 406 Xid = 5

COMMIT

# at 406

# 070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1169175134

BEGIN

# at 474

# 070119 10:51:29 server id 1 end_log_pos 119 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1169175089

Insert into target_tab select D1 name from source_tab where name ='1'

# at 593

# 070119 10:52:14 server id 1 end_log_pos 620 Xid = 7

COMMIT

.

It can be found that in BINLOG, the location of the update operation is before INSERT...SELECT. If you use this BINLOG for database recovery, the recovery result is inconsistent with the actual application logic; if you replicate, it will lead to inconsistency between the master and slave database!

From the above example, it is not difficult to understand why MySQL is dealing with "Insert into target_tab select * from source_tab where." And "create table new_tab... select... From source_tab where..." Lock the source_tab instead of using multiple versions of data that have the least impact on concurrency for consistent reading. In particular, if the SELECT of the above statement is a scope condition, InnoDB also adds a gap lock (Next-Lock) to the source table.

Therefore, INSERT...SELECT... And CREATE TABLE...SELECT... Statement, which may prevent concurrent updates to the source table, resulting in waiting for the source table lock. If the query is more complex, it will cause serious performance problems, and we should try our best to avoid using it. In fact, MySQL calls this SQL the SQL of non-deterministic, and it is not recommended.

If you must use this SQL to implement business logic in your application, and you do not want to have an impact on the concurrent updates of the source table, you can take the following two measures:

? One is to do as in the example above, setting the value of innodb_locks_unsafe_for_binlog to "on" to force MySQL to use multi-version data consistent reading. However, the price is that data may not be recovered or copied correctly with binlog, so this approach is not recommended.

? Second, through the use of "select * from source_tab... Into outfile" and "load data infile." Statement combination to implement indirectly, in this way MySQL will not lock source_tab.

Consistency of InnoDB under different isolation levels and differences in reads and locks

As mentioned earlier, locks and multiversion data are InnoDB's means of achieving consistent reads and ISO/ANSI SQL92 isolation levels, so InnoDB uses different consistent read strategies and required locks when dealing with SQL at different isolation levels. At the same time, the characteristics of data recovery and replication mechanism also have a great impact on the consistent read strategy and locking strategy of some SQL. These features are summarized as shown in Table 20-16 for readers' reference.

Table 20-16 comparison of different SQL locks under different isolation levels in the InnoDB storage engine

Isolation level

Consistent read and lock

SQL

Read UncommitedRead CommitedRepeatable ReadSerializableSQL condition select equal None locksConsisten read/None lockConsisten read/None lockShare locks range None locksConsisten read/None lockConsisten read/None lockShare Next-Keyupdate equal exclusive locksexclusive locksexclusive locksExclusive locks range exclusive next-keyexclusive next-keyexclusive next-keyexclusive next-keyInsertN/Aexclusive locksexclusive locksexclusive locksexclusive locksreplace keyless conflict exclusive locksexclusive locksexclusive locksexclusive locks key conflict exclusive next-keyexclusive next-keyexclusive next-keyexclusive next-keydelete equal exclusive locksexclusive locksexclusive locksexclusive locks range exclusive next-keyexclusive next-keyexclusive next-keyexclusive next-keySelect... From... Lock in share mode equals Share locksShare locksShare locksShare locks range Share locksShare locksShare Next-KeyShare Next-KeySelect * from... For update equal exclusive locksexclusive locksexclusive locksexclusive locks range exclusive locksShare locksexclusive next-keyexclusive next-key

Insert into... Select...

(refers to source table lock)

Innodb_locks_unsafe_for_binlog=offShare Next-KeyShare Next-KeyShare Next-KeyShare Next-Keyinnodb_locks_unsafe_for_binlog=onNone locksConsisten read/None lockConsisten read/None lockShare Next-Key

Create table... Select...

(refers to source table lock)

Innodb_locks_unsafe_for_binlog=offShare Next-KeyShare Next-KeyShare Next-KeyShare Next-Keyinnodb_locks_unsafe_for_binlog=onNone locksConsisten read/None lockConsisten read/None lockShare Next-Key

As you can see from tables 20-16, for many SQL, the higher the isolation level, the stricter the InnoDB locks on the recordset (especially when using scope conditions), the higher the likelihood of lock conflicts, and the greater the impact on the performance of concurrent transactions. Therefore, in our application, we should try to use a lower isolation level to reduce the probability of lock contention. In fact, by optimizing transaction logic, it is sufficient for most applications to use the Read Commited isolation level. For transactions that do require a higher isolation level, you can meet the requirements by dynamically changing the isolation level by executing SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ or SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE in the program.

When to use a table lock

For InnoDB tables, row-level locks should be used in most cases, because transaction and row locks are often the reasons why we choose InnoDB tables. However, table-level locks can also be considered in individual special transactions.

? The first case is that the transaction needs to update most or all of the data, and the table is relatively large. If the default row lock is used, not only the transaction execution efficiency is inefficient, but also other transactions may cause long lock waiting and lock conflicts. In this case, you can consider using table locks to improve the execution speed of the transaction.

? The second situation is that the transaction involves multiple tables, which is more complex, which is likely to cause deadlock and cause a large number of transaction rollback. In this case, you can also consider locking the tables involved in the transaction at one time, so as to avoid deadlock and reduce the overhead of the database due to transaction rollback.

Of course, there should not be too many of these two kinds of transactions in the application, otherwise, you should consider using the MyISAM table.

Under InnoDB, you should pay attention to the following two points when using table locks.

(1) although you can add table-level locks to InnoDB by using LOCK TABLES, it must be stated that table locks are not managed by the InnoDB storage engine layer, but by the upper layer ── MySQL Server. Only when autocommit=0 and innodb_table_locks=1 (default setting) are used, the InnoDB layer can know the table locks added by MySQL, and MySQL Server can also perceive the row locks added by InnoDB. In this case, InnoDB can automatically identify deadlocks involving table-level locks. Otherwise, InnoDB will not be able to automatically detect and handle this deadlock. Deadlocks will be discussed in the next section.

(2) when locking the InnoDB table with LOCK TABLES, it should be noted that AUTOCOMMIT should be set to 0, otherwise MySQL will not lock the table; before the end of the transaction, do not use UNLOCK TABLES to release the table lock, because UNLOCK TABLES will implicitly commit the transaction; COMMIT or ROLLBACK can not release the table lock added by LOCK TABLES, you must release the table lock with UNLOCK TABLES. For the correct way, see the following statement:

For example, if you need to write table T1 and read from table t, you can do this as follows:

SET AUTOCOMMIT=0

LOCK TABLES t1 WRITE, t2 READ,...

[do something with tables t1 and t2 here]

COMMIT

UNLOCK TABLES

About deadlocks

As mentioned above, MyISAM table locks are deadlock free, because MyISAM always gets all the locks you need at once, either satisfying them or waiting, so there is no deadlock. However, in InnoDB, locks are acquired step by step except for transactions composed of a single SQL, which determines that it is possible to have a deadlock in InnoDB. An example of a deadlock occurs as shown in Table 20-17.

Table 20-17 examples of deadlocks in the InnoDB storage engine

Session_1session_2

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from table_1 where where id=1 for update

...

Do some other treatment.

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from table_2 where id=1 for update

...

Select * from table_2 where id = 1 for update

Because session_2 has acquired an exclusive lock, wait

Do some other treatment.

Mysql > select * from table_1 where where id=1 for update

Deadlock

In the above example, both transactions need to acquire an exclusive lock held by the other to continue to complete the transaction, and this kind of cyclic lock waiting is a typical deadlock.

After a deadlock occurs, InnoDB usually automatically detects it and causes one transaction to release the lock and roll back, while the other transaction acquires the lock and continues to complete the transaction. However, when external locks or table locks are involved, InnoDB does not automatically detect deadlocks, which needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve the deadlock problem. In the case of high concurrent access, if a large number of transactions hang because they cannot get the locks they need immediately, it will take up a lot of computer resources and cause serious performance problems. Even drag across the database. We can avoid this by setting the appropriate lock wait timeout threshold.

Generally speaking, deadlocks are a problem of application design, and most of them can be avoided by adjusting business processes, database object design, transaction size, and SQL statements to access the database. Here are several common ways to avoid deadlocks through examples.

(1) in the application, if different programs will access multiple tables concurrently, it should be agreed to access the tables in the same order as far as possible, which can greatly reduce the chance of deadlock. In the following example, because two session accesses two tables in a different order, the chance of a deadlock is very high! But if accessed in the same order, deadlocks can be avoided.

Table 20-18 examples of deadlocks caused by table order in the InnoDB storage engine

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select first_name,last_name from actor where actor_id = 1 for update

+-+ +

| | first_name | last_name |

+-+ +

| | PENELOPE | GUINESS |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into country (country_id,country) values (110djm)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into country (country_id,country) values (110djm)

wait for

Mysql > select first_name,last_name from actor where actor_id = 1 for update

+-+ +

| | first_name | last_name |

+-+ +

| | PENELOPE | GUINESS |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into country (country_id,country) values (110djm)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

(2) when the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can also be greatly reduced.

Table 20-19 examples of deadlocks caused by inconsistent order of table data operations in the InnoDB storage engine

Session_1session_2

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > select first_name,last_name from actor where actor_id = 1 for update

+-+ +

| | first_name | last_name |

+-+ +

| | PENELOPE | GUINESS |

+-+ +

1 row in set (0.00 sec)

Mysql > select first_name,last_name from actor where actor_id = 3 for update

+-+ +

| | first_name | last_name |

+-+ +

| | ED | CHASE |

+-+ +

1 row in set (0.00 sec)

Mysql > select first_name,last_name from actor where actor_id = 3 for update

wait for

Mysql > select first_name,last_name from actor where actor_id = 1 for update

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Mysql > select first_name,last_name from actor where actor_id = 3 for update

+-+ +

| | first_name | last_name |

+-+ +

| | ED | CHASE |

+-+ +

1 row in set (4.71 sec)

(3) in a transaction, if you want to update the record, you should directly apply for a lock of sufficient level, that is, an exclusive lock, instead of applying for a shared lock first, and then apply for an exclusive lock when updating, because when the user applies for an exclusive lock, other transactions may have acquired the shared lock of the same record, resulting in lock conflicts or even deadlocks. For a specific demonstration, see the examples in section 20.3.3.

(4) as mentioned earlier, under the REPEATABLE-READ isolation level, if two threads add an exclusive lock to the same condition record at the same time, both threads will lock successfully if the condition record is not met. The program finds that the record does not yet exist and attempts to insert a new record, and if both threads do so, a deadlock occurs. In this case, changing the isolation level to READ COMMITTED avoids the problem, as shown in Table 20-20.

Table 20-20 examples of deadlocks caused by isolation levels in the InnoDB storage engine 1

Session_1session_2

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | REPEATABLE-READ |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit = 0

Query OK, 0 rows affected (0.00 sec)

The current session adds for update locks to records that do not exist:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 201 for update

Empty set (0.00 sec)

Other session can also add for update locks to records that do not exist:

Mysql > select actor_id,first_name,last_name from actor where actor_id = 201 for update

Empty set (0.00 sec)

Because the record is also locked by other session, the current barge will wait:

Mysql > insert into actor (actor_id, first_name, last_name) values

wait for

Because other session has updated the record, inserting the record at this time prompts for a deadlock and exits:

Mysql > insert into actor (actor_id, first_name, last_name) values

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Because other session has exited, the current session can acquire the lock and successfully insert the record:

Mysql > insert into actor (actor_id, first_name, last_name) values

Query OK, 1 row affected (13.35 sec)

(5) when the isolation level is READ COMMITTED, if both threads execute SELECT...FOR UPDATE first, determine whether there is a qualified record, and if not, insert the record. At this point, only one thread can insert successfully, and the other thread will have a lock wait. when the first thread commits, the second thread will make an error because of the primary key, but although this thread has made an error, it will get an exclusive lock! At this point, if a third thread comes to apply for an exclusive lock, there will also be a deadlock.

In this case, you can do the insert operation directly and then catch the primary key weight exception, or always perform the exclusive lock obtained by ROLLBACK release in the event of a primary key weight error, as shown in Table 20-21.

Table 20-21 examples of deadlocks caused by isolation levels in the InnoDB storage engine 2

Session_1session_2session_3

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-COMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.01 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-COMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.01 sec)

Mysql > select @ @ tx_isolation

+-+

| | @ @ tx_isolation |

+-+

| | READ-COMMITTED |

+-+

1 row in set (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.01 sec)

Session_1 acquires for update's shared lock:

Mysql > select actor_id, first_name,last_name from actor where actor_id = 201 for update

Empty set (0.00 sec)

Because the record does not exist, session_2 can also acquire a shared lock for for update:

Mysql > select actor_id, first_name,last_name from actor where actor_id = 201 for update

Empty set (0.00 sec)

Session_1 can insert the record successfully:

Mysql > insert into actor (actor_id,first_name,last_name) values

Query OK, 1 row affected (0.00 sec)

Session_2 insert request waiting for lock:

Mysql > insert into actor (actor_id,first_name,last_name) values

wait for

Session_1 successfully submitted:

Mysql > commit

Query OK, 0 rows affected (0.04 sec)

Session_2 acquires the lock and finds that the primary key of the insert record is heavy, and an exception is thrown at this time, but the shared lock is not released:

Mysql > insert into actor (actor_id,first_name,last_name) values

ERROR 1062 (23000): Duplicate entry '201' for key' PRIMARY'

Session_3 applies for a shared lock, because session_2 has locked the record, so session_3 needs to wait:

Mysql > select actor_id, first_name,last_name from actor where actor_id = 201 for update

wait for

At this time, if session_2 updates the record directly, a deadlock exception will be thrown:

Mysql > update actor set last_name='Lan' where actor_id = 201

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

After Session_2 releases the lock, session_3 acquires the lock:

Mysql > select first_name, last_name from actor where actor_id = 201 for update

+-+ +

| | first_name | last_name |

+-+ +

| | Lisa | Tom |

+-+ +

1 row in set (31.12 sec)

Although deadlocks can be greatly reduced through measures such as the design and SQL optimization described above, deadlocks are difficult to avoid completely. Therefore, it is a good programming habit to always catch and handle deadlock exceptions in programming.

If a deadlock occurs, you can use the SHOW INNODB STATUS command to determine the cause of the last deadlock. The return results include details of the deadlock-related transactions, such as the SQL statement that caused the deadlock, the locks that the transaction has acquired, what locks it is waiting for, and the transactions that have been rolled back. Based on this, the causes of deadlock and improvement measures can be analyzed. Here is an example of a SHOW INNODB STATUS output:

Mysql > show innodb status\ G

…… .

-

LATEST DETECTED DEADLOCK

-

070710 14:05:16

* * (1) TRANSACTION:

TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 inserting

Mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct (s), heap size 1216

MySQL thread id 7521657, query id 673468054 localhost root update

Insert into country (country_id,country) values (110Japanese Test')

.

* * (2) TRANSACTION:

TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500

Mysql tables in use 1, locked 1

4 lock struct (s), heap size 1216, undo log entries 1

MySQL thread id 7521664, query id 673468058 localhost root statistics

Select first_name,last_name from actor where actor_id = 1 for update

(2) HOLDS THE LOCK (S):

.

* * (2) WAITING FOR THIS LOCK TO BE GRANTED:

.

* WE ROLL BACK TRANSACTION (1)

……

Summary

This chapter focuses on the implementation characteristics of MyISAM table-level lock and InnoDB row-level lock in MySQL, and discusses the lock problems and solutions often encountered by the two storage engines.

For MyISAM table locks, the following points are mainly discussed:

(1) the shared read lock (S) is compatible, but the shared read lock (S) and the exclusive write lock (X) and the exclusive write lock (X) are mutually exclusive, that is, read and write are serial.

(2) under certain conditions, MyISAM allows concurrent execution of queries and inserts, which can be used to solve the lock contention problem of queries and inserts for the same table in the application.

(3) the default lock scheduling mechanism of MyISAM is write first, which is not necessarily suitable for all applications. Users can adjust the contention of read-write locks by setting LOW_PRIORITY_UPDATES parameters or specifying LOW_PRIORITY options in INSERT, UPDATE, and DELETE statements.

(4) because the locking granularity of the table lock is large, and the reading and writing are serial, therefore, if there are more update operations, the MyISAM table may have serious lock waiting, so we can consider using the InnoDB table to reduce lock conflicts.

For the InnoDB table, this chapter mainly discusses the following.

L InnoDB's row locks are based on lock references, and InnoDB uses table locks if the data is not accessed through the index.

L introduces the mechanism of InnoDB gap lock (Next-key) and the reason why InnoDB uses gap lock.

Under different isolation levels, the locking mechanism and consistent read strategy of InnoDB are different.

The recovery and replication of MySQL also have a great influence on the InnoDB locking mechanism and consistent read strategy.

Lock conflicts and even deadlocks are difficult to avoid completely.

After understanding the characteristics of InnoDB locks, users can reduce lock conflicts and deadlocks through measures such as design and SQL adjustment, including:

L use a lower isolation level as much as possible

L carefully design the index and use the index to access the data as far as possible to make the locking more accurate, thus reducing the chance of lock conflict

L choose a reasonable transaction size, and small transactions are less likely to have lock conflicts.

When locking a recordset display, it is best to request a lock of sufficient level at one time. For example, if you want to modify the data, it is best to apply for an exclusive lock directly, rather than apply for a shared lock first, and then request an exclusive lock when you modify it, which can easily lead to a deadlock.

When different programs access a set of tables, they should try to agree to access the tables in the same order, and for a table, access the rows in the table in a fixed order as far as possible. This will greatly reduce the chances of deadlocks.

L access data with equal conditions as far as possible, so as to avoid the effect of gap lock on concurrent insertion.

L do not apply for more than the actual lock level; do not show locking when querying unless you have to

For some specific transactions, table locks can be used to improve processing speed or reduce the possibility of deadlocks.

The above is all the content of the article "what is the use of locks in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.