preface

The purpose of this article is to systematically explain how different statements in MySQL are locked under various conditions. It is not to explain what various locks are (or the essence of locking).

Create a hero table to store heroes of The Three Kingdoms:

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;
Copy the code

Then insert a few entries into the table:

INSERT INTO hero VALUES
    (1.'l liu bei'.'shu'),
    (3.'Zhuge Liang'.'shu'),
    (8.'cao cao c'.'魏'),
    (15.'x xun yu'.'魏'),
    (20.'s sun quan'.'wu');
Copy the code

Now the hero table has two indexes (a secondary index and a clustered index), as shown below:

Statement locking analysis

In fact, “XXX statement should add what lock” itself is a false proposition, a statement needs to add a lock subject to many conditions, for example:

  • The isolation level of the transaction
  • Indexes used for statement execution (such as clustered indexes, unique secondary indexes, plain secondary indexes)
  • Query conditions (e.g. =, =<, >=, etc.)
  • The type of statement to execute

Before we go into the details of statement locking, it is important to keep in mind the big picture: Lock just solve the execution of concurrent transactions caused by dirty, dirty read, written not repeatable read, read a solution of these problems (MVCC is a dirty read and not repeatable read, read a solution of these problems), must be realized that the starting point of locking is in order to solve these problems, to solve the problem of different under different scenarios, Lead to add the lock is not the same, do not lock to lock and lock, easy to put yourself around. Of course, sometimes because of the specific implementation of MySQL and lead to some scenarios of locking some not very easy to understand, which we have to memorize ~

We’ve divided statements into three broad categories: plain SELECT statements, lock read statements, and INSERT statements. Let’s look at each.

A normal SELECT statement

A normal SELECT statement is found in:

  • At the READ UNCOMMITTED isolation level, the latest version of a record is READ without locking. Dirty reads, unrepeatable reads, and magic reads may occur.
  • At the READ COMMITTED isolation level, a ReadView is generated every time a normal SELECT statement is executed without locking. This solves dirty reads, but not unrepeatable and phantom reads.
  • REPEATABLE READ isolation level: no lock, only a ReadView is generated on the first execution of a normal SELECT statement. This eliminates dirty READ, unrepeatable READ and phantom READ problems. However, there is an interlude: # transaction T1, REPEATABLE READ isolation level

mysql> BEGIN; Query OK, 0 rows affected (0.00 SEC)mysql> SELECT * FROM hero WHERE number = 30; Empty set (0.01sec)# INSERT INTO hero VALUES(30, ‘g ‘, ‘w ‘); Mysql > UPDATE hero SET country = ’10’ WHERE number = 30; Query OK, 1 row affected (0.01sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT * FROM hero WHERE number = 30; + + — — — — — — — — — — — — — — — — – + — — — — — — — — — + | number | name | country | + — — — — — — — — + + — — — — — — — — — — — — — — — — — — + | | 30 g guan yu | shu | + — — — — — — — — + + — — — — — — — — — — — — — — — — — — + 1 row in the set (0.01 SEC) under the REPEATABLE READ isolation level, T1 first execution when the common SELECT statement generates a ReadView, T2 then inserts a new record into the Hero table and commits it. ReadView does not prevent T1 from performing an UPDATE or DELETE statement to alter the newly inserted record (since T2 has already committed and changing the record will not block). However, the trx_ID hidden column of the new record becomes the transaction ID of T1, which can then be seen in T1 using the normal SELECT statement to query the record and return it to the client. Because of this special phenomenon, you can also assume that InnoDB’s MVCC does not completely prohibit magic reading.

  • Under the SERIALIZABLE isolation level, two cases need to be discussed:
    • When the system variable autoCOMMIT =0, that is, when automatic commit is disabled, normal SELECT statements are converted to SELECT… LOCK IN SHARE MODE, that is, the record S LOCK must be obtained before reading the record. The specific locking situation is the same as that IN REPEATABLE READ isolation level, which will be analyzed later.
    • When the system variable AutoCOMMIT =1, that is, when automatic commit is enabled, the normal SELECT statement does not lock, but uses MVCC to generate a ReadView to read the record. Why don’t you lock it? Since enabling auto-commit means that a transaction contains only one statement, there is no problem with unrepeatable and phantom reads.

Lock read statements

Let’s put the following four statements together:

  • SELECT… LOCK IN SHARE MODE;
  • Statement 2: SELECT… FOR UPDATE;
  • Statement 3: UPDATE…
  • Statement 4: DELETE…

Statement 1 and statement 2 are the two types of lock read syntax specified in MySQL, while statement 3 and statement 4 are also considered lock reads because of the need to locate the changed record and lock the record during execution.

READ UNCOMMITTED or READ COMMITTED Isolation level

READ UNCOMMITTED and READ COMMITTED isolation levels lock statements in almost the same way, so let’s put them together. It is important to note that dirty reads and unrepeatable reads do not occur at any isolation level (because read-write operations need to be queued) when using locks to solve the problem of concurrent transactions.

In the case of equivalent queries using primary keys

  • Use the SELECT… SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE; SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE; This statement only needs to access the cluster index whose number is 8, so it only needs to add an S-type serious record lock to it, as shown in the following figure:
  • Use the SELECT… SELECT * FROM hero WHERE number = 8 FOR UPDATE; This statement is executed only need to visit the clustering index number value is 8 records, so you just need to give it a proper record locks X, as shown: tip: in order to distinguish between S and X lock lock, we later in the sketch is locked with S record of dyed blue, dye with X lock record purple.
  • Use the UPDATE… UPDATE hero SET country = ‘han’ WHERE number = 8; The UPDATE statement does not UPDATE the secondary index column, lock mode, or SELECT… The FOR UPDATE statement is consistent. UPDATE hero SET name = ‘cao cao ‘WHERE number = 8; Mysql > update cluster index (number = 8); update secondary index (number = 8);
  • Add type X canonical record lock (corresponding to the record) to the cluster index record with number 8.
  • Select idx_name (name = ‘c cao ‘, number = 8) from idx_name (number = 8);
  • Here’s how to draw a graph: Tip: We use a number with a circle to indicate the order in which each record is locked.
  • Use the DELETE… DELETE FROM hero WHERE number = 8; DELETE (idx_name, idx_name, idx_name, idx_name); DELETE (idx_name, idx_name, idx_name, idx_name); SQL > UPDATE column with secondary index;

For range queries using primary keys

  • Use the SELECT… SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE; SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE; This statement looks pretty simple, but it’s a bit complicated to execute:
  • Locate the first record whose number <= 8 in the cluster index, that is, the record whose number value is 1, and lock it.
  • Determine if the record meets the conditions in the index condition push. We introduced a feature called Index Condition Pushdown (ICP), which pushes queries related to the Index being used to the storage engine rather than back to the server layer. However, it is important to note that the index condition is pushed down only to reduce the number of back tables, that is, the number of times the full clustered index record is read, thus reducing IO operations. For clustered indexes, there is no need to return to the table. It contains all columns and does not reduce I/O operations, so InnoDB’s designers specify that this push-down feature only applies to secondary indexes. That is to say, in this case, the condition related to the index being used is: number <= 8, and the number column is a clustered index column, so in this case, there is no query condition for pushing under the index condition, so naturally, there is no need to judge whether the record meets the condition for pushing under the index condition.
  • Because in this case, the primary key number is used for range queries, InnoDB’s design requires that every time a record is fetched from the cluster index, it should determine whether the record meets the range query boundary conditions, i.e. number <= 8. If so, it is returned to the Server layer for further processing, otherwise the lock on the record needs to be released and a message is returned to the Server layer indicating that the query is complete. Records with a value of 1 are eligible for this condition, so they are returned to the Server layer for further processing.
  • Return the record to the Server layer for further judgment. The server layer terminates the query if it receives a message from the storage engine layer indicating that the query is complete. Otherwise, the server layer continues to determine the condition that the index condition is not pushed, which in this case is number <= 8. Alas, there is not a judgment has been made in step 3, how to judge again in this time? Yes, InnoDB design uncle adopted such a simple and crude strategy, all the conditions that are not indexed need to be judged in the server layer again. If the record meets the remaining criteria (push without indexing), it is sent to the client, otherwise the lock placed on the record needs to be released.
  • Then, the one-way linked list of the record (that is, the record with number value 1) continues to search backwards and obtains the record with number value 3. Then, steps 2,3,4 and 5 are repeated.
  • Tip: The above step is verified in MySQL 5.7.21, not guaranteed for other versions. The problem with this process is that when you find the record with number 8, you have to go back to the record (number 15). When the storage engine reads the record, in step 1 above, it locks the record, and then at step 3, Select * from transaction T1 where number <= 8; select * from transaction T1 where number <= 8; select * from transaction T1 where number <= 8

BEGIN; SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE; # transaction T2BEGIN; SELECT * FROM hero WHERE number = 15 FOR UPDATE; There is no problem, because when T2 is executed, transaction T1 has already released the lock on the record with number 15. However, if you execute T2 first and then T1, since T2 already holds the lock on the record with number 15, transaction T1 will wait because it cannot acquire the lock. SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE; SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE; The execution of this statement is similar to the previous example. It also locates the first record satisfying the condition of number >= 8 in the cluster index, that is, the record with the value of number 8. Then it can look back all the way along the one-way linked list composed of records. Every time it finds a record, it will lock it and judge whether the record conforms to the boundary condition of range query. If the number of records is not less than 8, the boundary condition is the same as if the number of records is not less than 8. Finally, this record is returned to the server layer, and the server layer determines whether the condition number >= 8 is true, if it is true, it sends the query to the client, otherwise the query ends. However, once the InnoDB storage engine finds the last record in the index, the Supremum pseudorecord, it is immediately determined within the storage engine that it is a pseudorecord and does not need to be returned to the server layer for processing, nor does it need to be locked (that is, it was not locked at all in step 1). Select * from ‘number’ where ‘number’ = 8, ‘number’ = 15, ‘number’ = 20, ‘number’ = 20

  • Use the SELECT… FOR UPDATE statement to lock records: and SELECT… The FOR UPDATE statement is similar, but with a serious x-lock.
  • Use the UPDATE… UPDATE hero SET country = ‘han’ WHERE number >= 8; The UPDATE statement does not UPDATE the secondary index column, lock mode, or SELECT… The FOR UPDATE statement is consistent. UPDATE hero SET name = ‘cao cao ‘WHERE number >= 8; UPDATE hero SET name = ‘cao Cao’ WHERE number = 8; In this case, the cluster index record will be updated first, and then the corresponding secondary index record will be updated, so the lock step is:
  • Add type X canonical record lock to cluster index record with number 8.
  • Then add type X canonical record lock to the idX_NAME secondary index record corresponding to the record index record in the previous step.
  • Add an X-type canonical record lock to the cluster index record for number 15.
  • Then add type X canonical record lock to the idX_NAME secondary index record corresponding to the record index record in the previous step.
  • Add an X type canonical record lock to the clustered index record whose number is 20.
  • Then add type X canonical record lock to the idX_NAME secondary index record corresponding to the record index record in the previous step.
  • UPDATE hero SET namey = ‘han’ WHERE number <= 8; Will the number value of 1, 3, 8 clustering index record and their corresponding secondary index record to add X serious record locks, lock order similar sequence and lock in the statement above, is the first after a clustering index record locking, and give the corresponding secondary index record locking. The InnoDB storage engine then releases the lock on the cluster index record (note that the secondary index record corresponding to the cluster index record with number 15 is not locked) when it decides that the cluster index record does not meet the boundary conditions. I’m not going to draw the schematic.
  • Use the DELETE… DELETE FROM hero WHERE number >= 8; DELETE FROM hero WHERE number <= 8; The lock condition of the two statements is the same as that of the UPDATE statement with a secondary index column.

In the case of equivalent queries using secondary indexes

Tip: At READ UNCOMMITTED and READ COMMITTED isolation levels, locking with a normal secondary index is the same as locking with a unique secondary index, so we won’t discuss it separately.

  • Use the SELECT… SELECT * FROM hero WHERE name = ‘c cao ‘LOCK IN SHARE MODE; Select * from idx_name where name = ‘c cao ‘; Select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index, select * from secondary index;
  • Select * from ‘c Cao ‘where name = ‘c Cao’;
  • Then lock the corresponding cluster index record
  • Note: idx_name is a common secondary index. If you find the first entry in the idx_name index that matches the condition name= ‘c cao ‘, you can follow this entry all the way back. As we can see from the above description, the next secondary index record is not locked, why is this? This is because the uncle who designed InnoDB has special treatment for the condition of equivalent matching. They stipulate that when InnoDB storage engine finds the next record of the current record, it will directly judge whether the record meets the condition of equivalent matching before locking it. If it does not meet the condition, it will return directly (that is, no lock). Otherwise, lock it and return it to the Server layer. So there is no need to lock the next secondary index record. UPDATE hero SET name = ‘cao’ WHERE number = 8; UPDATE hero SET name = ‘cao’ WHERE number = 8; Both statements lock the clustered index record (number 8) and the corresponding secondary index record (number 8) in different order. The UPDATE statement locks the clustered index record first and then the secondary index record. If the two statements are run in different transactions, a strange thing can happen
    • Transaction T2 holds the lock on the clustered index record, and transaction T1 holds the lock on the secondary index record.
    • Transaction T2 is waiting to acquire a lock on a secondary index record, and transaction T1 is waiting to acquire a lock on a clustered index record.
  • Both transactions hold a lock and are waiting for the lock already held by the other. This situation is called a deadlock. Both transactions cannot run and must be rolled back.
  • Use the SELECT… SELECT * FROM hero WHERE name = ‘c cao ‘FOR UPDATE; In this case with SELECT… The LOCK IN SHARE MODE statement locks the secondary index record and the corresponding cluster index record.
  • Use the UPDATE… To lock records, for example: SELECT with update secondary index records… The FOR UPDATE case is similar, but if there are other secondary index columns in the updated column, the corresponding secondary index record will also be locked.
  • Use the DELETE… To lock records, for example, with SELECT… FOR UPDATE is similarly locked, but secondary index records are also locked if there are other secondary index columns in the table.

For range queries using secondary indexes

  • Use the SELECT… SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= ‘c cao ‘LOCK IN SHARE MODE; SELECT * FROM hero FORCE INDEX WHERE name >= ‘c Cao’ LOCK IN SHARE MODE; Tip: Since the optimizer calculates the cost of using the secondary INDEX to perform the query, it is possible to perform the query in full table scan mode if the cost is high, so we use FORCE INDEX(IDx_name) to FORCE the secondary INDEX to perform the query. Select * from idx_name where idx_name = ‘c cao ‘; select * from idx_name where idx_name = ‘c cao ‘and idx_name = ‘c cao’; All user records meet the condition name >= ‘c cao ‘, so all secondary index records will be locked, and their corresponding cluster index records will be locked. If a secondary index is locked, then the corresponding cluster index is locked, and then the next secondary index is locked. SELECT * FROM hero FORCE INDEX(idx_name) WHERE name <= ‘c cao ‘LOCK IN SHARE MODE; The locking of this statement is a little more interesting. If number <= 8, add a lock to the record whose number is 15. If number <= 8, add a lock to the record whose number is 15. For the query condition name <= ‘c cao ‘, the execution of the statement requires the use of the secondary index, and the condition associated with the secondary index can use the index condition to push this feature. InnoDB’s design states that if a record does not meet the criteria in the index condition push, it is skipped to the next record (this process does not return it to the server layer at all), and if it is the last record, it is reported to the Server layer that the query is complete. If the query does not meet the conditions of the index, you can directly jump to the next record or report to the server that the query is completed. This process does not release the lock on the locked record. . Is the name used in this example query condition < = ‘c cao cao, in the name value is’ c cao cao secondary index record and its corresponding clustering index after locking, then the secondary in the index will have a record, the name for’ l, liu bei is the secondary index record, as the record is not in conformity with the index under the condition of pushing, And it is the end of the range queries a record, the query will report directly to the server layer, the focus is on the process will not release the name value is’ l liu bei ‘on a secondary index record lock, also led to the time of completion of statement execution locking situation is as follows: SELECT * FROM hero WHERE name = ‘l ‘FOR UPDATE; SELECT * FROM hero WHERE name = ‘l’; The statement in T2 needs to acquire the X-type serious record lock on the secondary index record whose name value is L Liu Bei, while T1 still holds the S-type serious record lock on the secondary index record whose name value is L Liu Bei, so T2 cannot obtain the lock and enters the wait state. Tip: Why not release locks on secondary index records that do not meet the conditions in the index condition push? I do not understand this question, it is stipulated in this way, if you understand it, you can add my wechat xiaohaizi4919 to discuss it. Again, I use MySQL version 5.7.21, I do not guarantee that the locking situation in other versions is completely the same.
  • Use the SELECT… FOR UPDATE statement: and SELECT… The FOR UPDATE statement is similar, but with a serious x-lock.
  • Use the UPDATE… UPDATE hero SET country = ‘c ‘WHERE name >= ‘c ‘; Tip: FORCE INDEX only works for SELECT statements, UPDATE statements support it but not substantially, and DELETE statements do not support it at all. SELECT * from idx_name; SELECT * from idx_name; The FOR UPDATE statement is consistent. If there are other secondary index columns that are also updated, then the corresponding secondary index record is also locked, without further elaboration. UPDATE hero SET country = ‘han’ WHERE name <= ‘c cao ‘; SELECT (‘c cao ‘, ‘L Liu’, ‘C Cao’, ‘L Liu’, ‘c Cao ‘, ‘L Liu’, ‘C Cao’, ‘L Liu’, ‘C Cao’, ‘L Liu’) Then, when judging boundary conditions, it is found that the secondary index record whose name value is’ L Liu Bei ‘does not meet the condition of name <=’ C Cao ‘, and then the locks on the secondary index record and the corresponding cluster index record are released. This process is shown below:
  • Use the DELETE… DELETE FROM hero WHERE name >= ‘c cao ‘; DELETE FROM hero WHERE name <= ‘c cao ‘; If these two statements use the secondary index for lock reads, they are locked the same as the UPDATE statement with the secondary index column, and the graph is not drawn.

Full table scan

For example:

SELECT * FROM hero WHERE country  = '魏'LOCK IN SHARE MODE; Share a mysql200-page study notes and interview questions about MySQLCopy the code

Since there is no index on the country column, this query can only be executed in full table scan mode. The storage engine will lock an S-type normal record lock on each clustered index record, and then return it to the server layer. If the server layer determines whether country = ‘w’ is true, then the server layer will determine whether country = ‘w’. If true, send it to the client, otherwise the lock on the record will be released. Draw a graph like this:

Use the SELECT… FOR UPDATE, UPDATE, UPDATE, UPDATE, UPDATE, UPDATE

For the UPDATE… And DELETE… If the cluster index is traversed, the cluster index will be locked with an X type canonical record, and then:

  • If the cluster index record does not meet the criteria, the lock on the record is released.
  • If the clustered index record meets the criteria, an X-type canonical record lock is applied to the corresponding secondary index record (the DELETE statement locks all secondary index columns, and the UPDATE statement locks only the secondary index record corresponding to the updated secondary index column).