Shulou(Shulou.com)06/01 Report--

Locking analysis of MySQL/InnoDB has always been a difficult topic. In the course of my work, I often have colleagues to consult on this issue. At the same time, I often receive private messages about MySQL locks on Weibo, asking me to help solve some deadlock problems. In this paper, we are going to carry out a more in-depth analysis and discussion on the locking problem of MySQL/InnoDB, mainly to introduce a train of thought, using this idea to get any SQL statement, we can completely analyze what locks will be added to this statement. What kind of use risk will there be? Even analyze a deadlock scenario online to understand the cause of the deadlock.

Note: MySQL is a database system that supports plug-in storage engine. All of the following descriptions in this article are based on the InnoDB storage engine, and the performance of other engines will be quite different.

MVCC:Snapshot Read vs Current Read

The MySQL InnoDB storage engine implements a multi-version concurrency control protocol-MVCC (Multi-Version Concurrency Control). (note: as opposed to MVCC, it is lock-based concurrency control, Lock-Based Concurrency Control). The biggest advantage of MVCC, I believe, is also familiar: read without lock, read and write do not conflict. In OLTP applications with more read and write less, read-write conflict is very important, which greatly increases the concurrency performance of the system, which is why almost all RDBMS supports MVCC at this stage.

In MVCC concurrency control, read operations can be divided into two categories: snapshot read (snapshot read) and current read (current read). Snapshot reading, read the visible version of the record (it may be the historical version), without locking. The current read is the latest version of the record, and the record returned by the current read will be locked to ensure that other transactions will not modify the record concurrently.

Which reads are snapshot reads in a system that supports MVCC concurrency control? Which operations are currently read? Take MySQL InnoDB as an example:

Snapshot read: simple select operation, belongs to snapshot read, unlocked. (of course, there are exceptions, which will be analyzed below)

Select * from table where?

Current read: special read operation, insert / update / delete operation, belongs to the current read and needs to be locked.

Select * from table where? Lock in share mode

Select * from table where? For update

Insert into table values (…)

Update table set? Where?

Delete from table where?

All the above statements belong to the latest version of the current read and read record. Moreover, after reading, you also need to ensure that other concurrent transactions cannot modify the current record and lock the read record. Among them, except for the first statement, S lock (shared lock) is added to the read record, and X lock (exclusive lock) is added to all other operations.

Why are insert / update / delete operations classified as current reads? You can take a look at the following update operation, the execution process in the database:

From the figure, you can see the specific flow of a Update operation. When Update SQL is sent to MySQL, MySQL Server reads the first record that meets the condition according to the where condition, and then the InnoDB engine returns the first record and current read it. After MySQL Server receives the locked record, it initiates another Update request to update the record. A record operation is completed, and the next record is read until there is no record that meets the criteria. Therefore, inside the Update operation, there is a current read. In the same way, the same goes for Delete operations. The Insert operation is slightly different, simply put, the Insert operation may trigger a conflict check for Unique Key, as well as a current read.

Note: according to the interaction above, for a currently read SQL statement, the interaction between InnoDB and MySQL Server is carried out one by one, so locking is also done one by one. First lock a record that meets the condition, return it to MySQL Server, do some DML operations, and then lock the next one until it is finished reading.

Cluster Index: clustered index

The data organization of the InnoDB storage engine is clustered index tables: complete records are stored in the primary key index, and all the columns of the records can be obtained through the primary key index. For more information about how clustered index tables are organized, you can refer to MySQL's official document: Clustered and Secondary Indexes. This article assumes that the reader already has a certain understanding of this, so he will no longer make a specific introduction. In the next part, the two names of primary key index / clustered index will be mixed, which I hope the reader will know.

2PL:Two-Phase Locking

A principle of traditional RDBMS locking is 2PL (two-stage lock): Two-Phase Locking. Relatively speaking, 2PL is relatively easy to understand, saying that the locking operation is divided into two stages: the locking phase and the unlocking phase, and ensures that the locking phase and the unlocking phase do not intersect. Next, let's take a brief look at the implementation of 2PL in MySQL, taking MySQL as an example.

As you can see from the figure above, 2PL divides locking / unlocking into two completely disjoint phases. Lock stage: only lock, no lock. Unlock stage: only lock, no lock.

Isolation Level

Isolation level: Isolation Level is also a key feature of RDBMS. I believe that friends who know something about the database have an in-depth understanding of the four isolation levels: Read Uncommited,Read Committed,Repeatable Read,Serializable. This article is not going to discuss how these four isolation levels are defined in database theory, but to introduce how MySQL/InnoDB defines these four isolation levels.

Four isolation levels defined by MySQL/InnoDB:

Read Uncommited

Uncommitted records can be read. This isolation level will not be used and ignored.

Read Committed (RC)

Snapshot read is ignored, which is not considered in this article. For the current read, the RC isolation level ensures that the read record is locked (record lock), and there is a phenomenon of phantom reading.

Repeatable Read (RR)

Snapshot read is ignored, which is not considered in this article. For the current read, the RR isolation level ensures that the read record is locked (record lock), and that the read range is locked, and that new records that meet the query conditions can not be inserted (gap lock), and there is no phantom reading.

Serializable

From MVCC concurrency control to lock-based concurrency control. No difference between snapshot read and current read, all read operations are current read, read plus read lock (S lock), write plus write lock (X lock). Under the Serializable isolation level, there are read-write conflicts, so the degree of concurrency decreases sharply, so it is not recommended under MySQL/InnoDB.

Analysis of locking implementation of a simple SQL

After introducing some background knowledge, this paper will select several representative examples to analyze the locking processing of MySQL in detail. Of course, let's start with the simplest example. Often a friend sends me a SQL and asks me, what lock does this SQL add? Just like the following two simple SQL, what locks do they add?

SQL1:select * from T1 where id = 10

SQL2:delete from T1 where id = 10

How to answer this question? One answer I can imagine is:

SQL1: no lock. Because MySQL uses multi-version concurrency control, reads are unlocked.

SQL2: add a write lock to the record with id = 10 (primary key index).

Is this the right answer? I don't know. That is, it may be right or wrong. given that the conditions are insufficient, there is no answer to this question. If I were to answer this question, I must also know some of the following premises. If the premise is different, the answer I can give is different. What other prerequisites are missing to answer this question?

Premise one: is the id column the primary key?

Premise 2: what is the isolation level of the current system?

Premise 3: if the id column is not a primary key, is there an index on the id column?

Premise 4: if there is a secondary index on the id column, is this index the only index?

Premise 5: what is the implementation plan of the two SQL? Index scan? Full table scan?

Without these premises, it is amateur to just give a SQL and then ask what lock the SQL will add. When these questions are clearly answered, it is clear at a glance what locks will be added to a given SQL. Next, I combine the answers to these questions, and then analyze each combination in order from easy to difficult, which locks will be added to the corresponding SQL?

Note: in the following combinations, I made the premise that when there is an index, the execution plan must choose to use the index for filtering (index scanning). But the actual situation will be much more complicated, and the real implementation plan still needs to be based on the output of MySQL.

Combination 1: id column is the primary key, RC isolation level

Combination 2: id column is the second-level unique index, RC isolation level

Combination 3: id column is a secondary non-unique index, RC isolation level

Group 4: no index on id column, RC isolation level

Combination five: id column is the primary key, RR isolation level

Combination 6: id column is the second-level unique index, RR isolation level

Combination 7: id column is a secondary non-unique index, RR isolation level

Group 8: no index on id column, RR isolation level

Combination 9: Serializable isolation level

The permutations and combinations have not been enumerated completely, but it seems that there are already many. Is it really necessary to be so complicated? In fact, to analyze locking, it needs to be so complicated. But from another point of view, as long as you choose a combination, SQL needs to add which locks, in fact, also determined. Next, let's analyze the SQL locking strategies under these nine combinations one by one.

Note: under the previous eight combinations, that is, under the RC,RR isolation level, SQL1:select operations are unlocked and snapshot reads are used, so they are ignored in the following discussion, focusing on locking SQL2:delete operations.

Combination one: id primary key + RC

This combination is the simplest and easiest to analyze. Id is the primary key, Read Committed isolation level, given SQL:delete from T1 where id = 10; just add an X lock to the record on the primary key with id = 10. As shown in the following figure:

Conclusion: when id is the primary key, the SQL only needs to add an X lock to the record of id=10.

Combination two: id unique index + RC

In this combination, id is not the primary key, but the secondary index key value of a Unique. So what locks do you need to add under the RC isolation level, delete from T1 where id = 10;? See the following figure:

In this combination, id is the unique index and the primary key is the name column. At this point, the locking situation is different due to the combination one. Because id is a unique index, the delete statement will choose the index of id column to filter under where condition. After finding the record of id=10, it will first add an X lock to the id=10 index record on the unique index. At the same time, it will return to the primary key index (clustered index) according to the read name column, and then add an X lock to the primary key index entry corresponding to name ='d'on the clustered index. Why are records on a clustered index locked? Just imagine, if a concurrent SQL is updated through the primary key index: update T1 set id = 100where name = 'delete; at this point, if the delete statement does not lock the record on the primary key index, then the concurrent update will not be aware of the existence of the delete statement, violating the constraint that updates / deletions on the same record need to be executed serially.

Conclusion: if the id column is a unique column, there is an unique index on it. Then SQL needs to add two X locks, one corresponding to the record of id=10 on the id unique index and the other corresponding to the record of [name='d',id=10] on the clustered index.

Combination 3: id non-unique index + RC

Compared with combination one and two, combination three has changed again, and the isolation level remains the same as RC, but the constraint on the id column is reduced again, and the id column is no longer unique, only a common index. Suppose delete from T1 where id = 10; statement, and still select the index on the id column to filter the where condition, which locks will be held at this time? Also see the following figure:

According to this figure, you can see that, first of all, the records on the id column index that meet the query condition of id = 10 are locked. At the same time, the records on the corresponding primary key index of these records are also locked. The only difference from combination 2 is that combination 2 has at most one record that satisfies the equivalent query, while combination 3 locks all records that meet the query criteria.

Conclusion: if there is a non-unique index on the id column, then all records that meet the SQL query criteria will be locked. At the same time, these records on the primary key index will also be locked.

Combination 4: id No Index + RC

Compared with the first three combinations, this is a relatively special case. There is no index on the id column, where id = 10; this filter condition cannot be filtered by the index, so it can only be filtered by a full table scan. What locks will SQL add to this combination? Or in other words, what locks will be added when a full table is scanned? There are also many answers: some people say that they will add an X lock to the table; others say that they will add an X lock to the selected id = 10; records on the clustered index. What about the actual situation? Please take a look at the following picture:

Since there is no index on the id column, we can only go to the clustered index and scan it all. As you can see from the figure, there are two records that meet the deletion criteria, but all the records on the clustered index are X-locked. Regardless of whether the record meets the condition or not, it is all X-locked. Neither a table lock nor a row lock is added to a record that meets the condition.

Someone might ask? Why not just lock records that meet the conditions? This is due to the implementation of MySQL. If a condition cannot be quickly filtered by the index, the storage engine layer locks all records and returns them, which are then filtered by the MySQL Server layer. So all the records are locked.

Note: in the actual implementation, MySQL has some improvements. After the MySQL Server filter conditions are found to be not satisfied, the unlock_row method will be called to lock the records that do not meet the conditions (in violation of the constraints of 2PL). In doing so, it is guaranteed that only the locks on records that meet the conditions will be held in the end, but the locking operation for each record cannot be omitted.

Conclusion: if there is no index on the id column, SQL will take the full scan of the clustered index to filter, because the filtering is done at the MySQL Server level. So every record, whether it meets the condition or not, is locked with an X. However, for the sake of efficiency, MySQL optimizes that records that do not meet the conditions will be locked after judgment, and finally hold the locks on records that meet the conditions, but the locking / unlocking actions on records that do not meet the conditions will not be omitted. At the same time, optimization violates the constraints of 2PL.

Combination five: id primary key + RR

The above four combinations are all locking behaviors under the Read Committed isolation level, and the next four combinations are locking behaviors under the Repeatable Read isolation level.

Combination five, id column is the primary key column, Repeatable Read isolation level, for delete from T1 where id = 10; this SQL is locked with combination one: [id primary key, Read Committed].

Combination six: id unique index + RR

Similar to combination five, the locking of combination six is consistent with that of combination two: [id unique index, Read Committed]. Two X locks, one on the record that the id unique index meets the criteria, and one record on the corresponding clustered index.

Combination 7: id non-unique index + RR

Remember the difference between the four isolation levels of MySQL mentioned earlier? The RC isolation level allows phantom reading, while the RR isolation level does not allow phantom reading. But in combination 5 and combination 6, the locking behavior is completely consistent with the locking behavior under RC. So under the RR isolation level, how to prevent phantom reading? The answer to the question is revealed in combination 7.

Group 7, Repeatable Read isolation level, there is a non-unique index on id, execute delete from T1 where id = 10; suppose you select the index on the id column for conditional filtering, and what is the final locking behavior? Also look at the following picture:

This figure, compared to combination three: [non-unique lock on id column, Read Committed] looks the same, but it is actually very different. The biggest difference is that there is an extra GAP lock in this picture, and the GAP lock does not seem to be added to the record, but rather like loading the position between two records, what is the use of the GAP lock?

In fact, this extra GAP lock is the RR isolation level, which is the key to no phantom reading relative to the RC isolation level. Indeed, the location where the GAP lock is locked is not the record itself, but the GAP between the two records. The so-called illusory reading means that the same transaction does two current reads in a row (for example: select * from T1 where id = 10 for update;), then these two current reads return exactly the same records (the same number of records and the same records themselves), and the second current read will not return more records (illusion) than the first one.

How to ensure that the two current reads return consistent records, it is necessary that between the first current read and the second current read, other transactions will not insert new qualified records and commit. In order to achieve this function, the GAP lock came into being.

As shown in the figure, where new qualified items can be inserted (id = 10). Considering the order of the B+ tree index, the qualified items must be stored continuously. Before recording, id=10 records will not be inserted; [10, aa] can be inserted between [6] and [10]; between [10] and [10], new [10] BB], [10] [10] c] can be inserted; [10], [10] records can be inserted between [10] and [11], and records that meet the conditions can not be inserted after [11]. Therefore, in order to ensure that no new records satisfying the conditions will be inserted between [10Magnec] and [10Magneb], between [10Magneb] and [10Magnedd], MySQL chose to lock the three GAP with GAP lock.

Insert operations, such as insert [10Magna], will first navigate between [6recorder c] and [10recorder b], and then check whether the GAP is locked before insertion, and if so, Insert cannot insert the record. Therefore, through the current reading of the first pass, not only the records that meet the conditions are locked (X lock), similar to combination three. At the same time, three GAP locks are added to lock the three GAP that may be inserted into the records that meet the conditions, so as to ensure that the subsequent Insert cannot insert new id=10 records, which will put an end to the illusion of the second current read of the same transaction.

When interested friends see this, they may ask: since the protection of GAP locks is needed to prevent phantom reading, why does combination five and combination six, which is also the RR isolation level, do not need to add GAP locks?

First of all, this is a good question. Secondly, it is also very simple to answer this question. The purpose of the GAP lock is to prevent the phantom reading of two current reads of the same transaction. In combination five, id is the primary key; in combination six, id is the Unique key, which can guarantee uniqueness. An equivalent query can return at most one record, and new records with the same value must not be inserted, so the use of GAP locks is avoided. In fact, to solve this problem, there is a deeper question: if the combination of five or six, for the first query of SQL:select * from T1 where id = 10 for update;, there is no record that meets the query criteria, can the GAP lock be omitted? This question is left for everyone to think about.

Conclusion: under the Repeatable Read isolation level, there is a non-unique index on the id column, corresponding to SQL:delete from T1 where id = 10; first, locate the first record that meets the query conditions through the id index, add the X lock on the record, add the GAP lock on the GAP, then add the record X lock on the primary key cluster index, and then return; then read the next one and repeat. Until the first record that does not meet the condition [115F], there is no need to add record X lock, but still need to add GAP lock, and finally return to the end.

Combination eight: id No Index + RR

Combination eight, the last case under the Repeatable Read isolation level, there is no index on the id column. At this point, SQL:delete from T1 where id = 10; there is no other path to choose but a full table scan. The final locking situation is shown in the following figure:

As shown in the picture, this is a very terrible phenomenon. First, all records on the clustered index are X-locked. Secondly, the gap between each record (GAP) of the clustering index is also locked by GAP. This sample table has only six records and requires a total of six record locks and seven GAP locks. Just imagine, what if there are 10 million records on the table?

In this case, except for the unlocked snapshot, any locked concurrent SQL on this table cannot be executed, cannot be updated, cannot be deleted, cannot be inserted, and the whole table is locked.

Of course, similar to combination 4: [id No Index, Read Committed], in this case, MySQL also makes some optimizations, which is called semi-consistent read. When semi-consistent read is enabled, MySQL will release the lock in advance for records that do not meet the query criteria. For the above use case, all record locks are released without GAP locks, except for records [dGrain10] and [gmagin10]. How semi-consistent read triggers: either the read committed isolation level or the Repeatable Read isolation level, with the innodb_locks_unsafe_for_binlog parameter set. For a more detailed introduction to semi-consistent read, please refer to my previous blog: MySQL+InnoDB semi-consitent read principle and implementation Analysis.

Conclusion: under the Repeatable Read isolation level, if the current read of a full table scan is performed, all records in the table will be locked and all GAP in the clustered index will be locked, eliminating all concurrent update / delete / insert operations. Of course, you can also mitigate locking overhead and concurrency effects by triggering semi-consistent read, but semi-consistent read itself can cause other problems and is not recommended.

Combination 9: Serializable

For the simple SQL mentioned earlier, the last case is the Serializable isolation level. For SQL2:delete from T1 where id = 10;, the Serializable isolation level is exactly the same as the Repeatable Read isolation level, so it is not introduced.

The Serializable isolation level affects SQL1:select * from T1 where id = 10. This SQL, under the RC,RR isolation level, is snapshot read and unlocked. But at the Serializable isolation level, SQL1 adds read locks, that is, snapshot reads no longer exist, and MVCC concurrency control is degraded to Lock-Based CC.

Conclusion: in MySQL/InnoDB, the so-called read unlocking does not apply to all cases, but is related to the isolation level. Serializable isolation level, read without lock is no longer established, all read operations are the current read.

A complicated SQL

At this point, in fact, the locking implementation of MySQL has also been introduced in 8899. As long as the above analysis ideas in this article, most of the SQL, will be able to analyze which locks will be added. Here, let's take a look at a slightly more complex SQL, which illustrates another logic of MySQL locking. The SQL use case is as follows:

For example, what locks will be added to the SQL in the figure? Assume that under the Repeatable Read isolation level (locking under the Read Committed isolation level is left to the reader for analysis.) At the same time, assume that SQL takes the idx_t1_pu index.

Before analyzing the locking of this SQL in detail, we need to have a knowledge reserve, that is, how to split the where condition in a SQL? For a specific introduction, it is recommended to read my previous article: where conditions in SQL, extraction and application analysis in the database. Here, I directly give the results of the analysis:

Index key:pubtime > 1 and puptime < 20. This condition is used to determine the query scope of SQL on the idx_t1_pu index.

Index Filter:userid = 'hdc'. This condition can be filtered on the idx_t1_pu index, but does not belong to Index Key.

Table Filter:comment is not NULL . This condition cannot be filtered on the idx_t1_pu index, but only on the clustered index.

After analyzing the composition of the SQL where condition, let's take a look at the locking of this SQL (RR isolation level), as shown in the following figure:

As can be seen from the figure, under the Repeatable Read isolation level, the range determined by Index Key is added with a GAP lock; when the Index Filter lock is filtered under the given condition (userid = 'hdc') depends on the version of MySQL. Before MySQL version 5.6, Index Condition Pushdown (ICP) is not supported, so Index Filter filters in the MySQL Server layer and supports Index Condition Pushdown after 5.6, then it is filtered on index. If you do not support ICP and records that do not meet Index Filter, you also need to add a record X lock. If you support ICP, you do not need to add an X lock for Index Filter records. (in the figure, the X lock marked with the red arrow should be added, depending on whether or not ICP is supported. The filter condition corresponding to Table Filter is read in the cluster index and filtered at the MySQL Server level, so X lock is also needed on the cluster index. Finally, a record that meets the conditions is selected, but the number of locks is much larger than the number of records that meet the conditions.

Conclusion: under the Repeatable Read isolation level, it is necessary to extract the where conditions for a complex SQL. GAP lock is required for the range determined by Index Key; Index Filter filtering condition, depending on whether the MySQL version supports ICP; if ICP is supported, the record of Index Filter is not satisfied and X lock is not required, otherwise X lock is required; Table Filter filtering condition, no matter whether it is satisfied or not, X lock is required.

Principle and Analysis of deadlock

The previous part of this article has basically covered all the locking rules of MySQL/InnoDB. An in-depth understanding of how MySQL locks has two more important functions:

1 according to the locking rules of MySQL, you can write out the SQL that will not cause deadlock.

2 you can locate the cause of the online deadlock according to the locking rules of MySQL

Let's take a look at two examples of deadlocks (one is a deadlock caused by two SQL of two Session, and the other is a SQL of two Session, which produces a deadlock):

The two deadlock use cases above. The first is a very understandable and most common deadlock, in which each transaction executes two SQL, holding one lock, and then adding another lock, resulting in a deadlock.

The second use case, although there is only one statement per Session, still produces a deadlock. To analyze this deadlock, you must first use the MySQL locking rules mentioned earlier in this article. For Session 1, starting from the name index, the [hdc,1] and [hdc, 6] all meet the conditions. Not only the record X lock on the name index, but also the record X lock on the clustered index will be added. The locking order is first [1m HDC Magne100], then [6m HDC Magne10]. On the other hand, Session 2, starting from the pubtime index, [10jie 6] and [100jue 1] all meet the filtering conditions, and the record X lock on the clustered index is also added, and the locking order is [6] HDC 10], and then [1]. Found no, contrary to the locking order of Session 1, if both Session happen to hold the first lock and request to add a second lock, deadlock occurs.

Conclusion: the occurrence of deadlock does not depend on the number of SQL statements in the transaction. The key to deadlock is that the locking order of two (or more) Session is not the same. Using the above mentioned in this article, analyzing the locking rules of each SQL statement in MySQL, analyzing the locking order of each statement, and then checking whether there is locking in the opposite order among multiple concurrent SQL, we can analyze all kinds of potential deadlocks and the causes of online deadlocks.

Summary

At this point, this article comes to an end. To make a simple summary, to fully grasp the locking rules of MySQL/InnoDB or even any other database, you need to have the following knowledge points:

1 understand some basic theoretical knowledge of database: data storage format (heap organization table vs clustering index table); concurrency control protocol (MVCC vs Lock-Based CC); isolation level definition of Two-Phase Locking; database (Isolation Level)

2 understand the execution plan of SQL itself (primary key scan vs unique key scan vs range scan vs full table scan)

3 understand some implementation details of the database itself (filter condition extraction; Index Condition Pushdown;Semi-Consistent Read)

4 understand the cause of deadlock and the method of analysis (the locking sequence is inconsistent; analyze the locking sequence of each SQL)

With these knowledge points, coupled with the appropriate actual combat experience, fully control the locking rules of MySQL/InnoDB, it is no problem.

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.