MySQL/InnoDB lock analysis has always been a difficult topic. During my work, I often ask questions from my colleagues. At the same time, Weibo often receives MySQL lock-related private messages, asking me to help solve some deadlocks. This article provides in-depth analysis and discussion on the locking problem of MySQL/InnoDB. it mainly introduces an idea to use this idea to obtain any SQL statement, all of them can completely analyze what locks will be applied to this statement? What are the risks? It even analyzes an online deadlock scenario to understand the cause of the deadlock.
Note:MySQL is a database system that supports plug-in storage engines. All the introductions below are based on the InnoDB storage engine. The performance of other engines varies greatly.
MySQL InnoDB storage engine implements Multi-Version Concurrency Control Protocol-MVCC (Multi-Version Concurrency Control) (Note: compared with MVCC, it implements lock-based Concurrency Control, lock-Based Concurrency Control ). The biggest benefit of MVCC is that it is also familiar: read is not locked, and read/write is not conflicted. In OLTP applications with multiple reads and writes, read/write conflicts are very important, greatly increasing the system's concurrency performance. This is why almost all RDBMS support MVCC at this stage.
In MVCC concurrency control, read operations can be divided into two types: snapshot read and current read ). Snapshot reading reads the visible version of the record (which may be a previous version) without locking. The current read reads 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 this record concurrently.
In a system that supports MVCC concurrency control, which read operations are Snapshot reads? What operations are currently read? Take MySQL InnoDB as an example:
Snapshot read:A simple select operation is a snapshot read without locking. (Of course, there are exceptions, which will be analyzed below)
Select * from table where ?;
Current read:Special read operations, insert, update, and delete operations, which belong to the current read and need 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 preceding statements belong to the latest version of the Read record. In addition, after reading, you must ensure that other concurrent transactions cannot modify the current record and lock the READ record. In addition to the first statement, the S Lock (shared lock) is applied to read Records. in other operations, the X lock (exclusive lock) is applied ).
Why are all insert/update/delete operations classified as current read? Let's take a look at the following update operation and the execution process in the database:
, You can see the specific process of an Update operation. After the Update SQL statement is sent to MySQL, MySQL Server reads the first record that meets the condition according to the where condition. then, the InnoDB engine returns the first record and locks the record (current read ). After the MySQL Server receives the lock record, it will initiate an Update request to Update the record. After a record operation is completed, read the next record until no matching record exists. Therefore, the Update operation contains the current read. The same applies to Delete operations. The Insert operation is slightly different. Simply put, the Insert operation may trigger the Unique Key conflict check and perform the current read.
Note: Based on the interaction, InnoDB interacts with MySQL Server one by one for a currently read SQL statement. Therefore, locking is also performed one by one. Lock a record that meets the conditions, return it to the MySQL Server, and perform some DML operations. then, lock the next record until the read is complete.
The data organization method of the InnoDB storage engine is a clustered index table: complete records are stored in the primary key index. through the primary key index, all columns of the record can be obtained. For more information about how to organize Clustered index tables, see the official MySQL documents Clustered and Secondary Indexes. This article assumes that the reader has a certain understanding of this and will not introduce it in detail. In the following section, the names of the primary key index and clustered index may be mixed.
A traditional RDBMS Locking principle is 2PL (Two-Phase lock): Two-Phase Locking. 2PL is relatively easy to understand. the lock operation is divided into two phases: the lock and unlock phases, and the lock and unlock phases are not at each other. Next, we will take MySQL as an example to briefly look at the implementation of 2PL in MySQL.
2PL divides lock/unlock into two completely different stages. Lock phase: only lock, no lock. Unlock phase: only lock, no lock.
Isolation Level: Isolation Level, which is also a key feature of RDBMS. I believe that my friends who have some knowledge about databases have a deep understanding of four isolation levels: Read Uncommited, Read Committed, Repeatable Read, and Serializable. This article will not discuss how to define the meaning of the four isolation levels in the database theory, but will introduce to you how MySQL/InnoDB defines these four isolation levels.
MySQL/InnoDB defines four isolation levels:
Read Uncommited
Uncommitted records can be read. This isolation level is not used and ignored.
Read Committed (RC)
Snapshot read is ignored.
For the current read,RC isolation level ensures locking (record lock) of read Records).
Repeatable Read (RR)
Snapshot read is ignored.
For the current read, the RR isolation level ensures that the read records are locked (record lock) and the read range is locked, new records that meet the query conditions cannot be inserted (gap locks). phantom reading does not exist.
From MVCC concurrency control to lock-based concurrency control. There is no difference between the snapshot read and the current read. all the read operations are the current read, the read lock (S Lock), and the write lock (X lock ).
In the Serializable isolation level, the read/write conflicts, so the concurrency decreases sharply, which is not recommended in MySQL/InnoDB.
After introducing some background knowledge, this article will select several representative examples to analyze MySQL lock processing in detail. Of course, let's start with the simplest example. Some friends often send me an SQL statement and then ask me, what locks does this SQL statement apply? Just like the following two simple SQL statements, what locks do they apply?
SQL1:Select * from t1 where id = 10;
SQL2:Delete from t1 where id = 10;
How can I answer this question? One answer I can imagine is:
SQL1:No lock. Because MySQL uses multi-Version Concurrency control, read is not locked.
SQL2:Add a write lock to the record with id = 10 (using the primary key index ).
Is this correct? I can't say that. That is, it may be correct, or it may be wrong. if the conditions are not known, there is no answer to this question. If I want to answer this question, I must know the following prerequisites. the answers I can give vary depending on the prerequisites. What are the prerequisites for answering this question?
Prerequisite 1:Is the id column a primary key?
Prerequisite 2:What is the isolation level of the current system?
Prerequisite 3:If the id column is not a primary key, is there an index on the id column?
Prerequisite 4:If there is a secondary index on the id column, is this index unique?
Premise 5:What are the execution plans of two SQL statements? Index scan? Full table scan?
Without these prerequisites, it is a very amateur performance to directly give an SQL statement and then ask what locks will be applied to this SQL statement. When there is a clear answer to these questions, what locks will be applied to the given SQL statements, which will be clear at a glance. Next, I will combine the answers to these questions, and then analyze the corresponding SQL locks for each combination in the order of ease to difficulty?
Note:In the following combination, I made a premise assumption that when there is an index, the execution plan will definitely choose to use the index for filtering (index scanning ). However, the actual situation is much more complicated. the actual execution plan should be based on the MySQL output.
Combination 1:The id column is the primary key at the RC isolation level.
Combination 2:The id column is a secondary unique index, at the RC isolation level
Combination 3:The id column is a secondary non-unique index and is at the RC isolation level.
Combination 4:No index on the id column, RC isolation level
Combination 5:The id column is the primary key and the RR isolation level
Combination 6:The id column is a unique secondary index with the RR isolation level.
Combination 7:The id column is a secondary non-unique index with the RR isolation level.
Combination 8:No index on the id column, RR isolation level
Combination 9:Serializable isolation level
The permutation and combination are not listed completely yet, but it seems that there are already many. Is it really necessary to be so complex? In fact, the analysis and locking requires such complexity. But from another perspective, as long as you select a combination, what locks do SQL need. Next, let's analyze the SQL lock policies under these nine combinations one by one.
Note: In the first eight combinations, that is, at the RC and RR isolation levels, SQL1: select operations are not locked and snapshot reads are used, therefore, it is ignored in the following discussion, mainly discussing the locking of SQL2: delete operations.
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; you only need to add the X lock to the record with id = 10 on the primary key. As shown in:
Conclusion:When id is the primary key, you only need to add the X lock to the record id = 10.
In this combination, id is not a primary key, but a Unique secondary index key value. In the RC isolation level, delete from t1 where id = 10; what locks do I need to apply? See:
In this combination, the id is the unique index, and the primary key is the name column. In this case, the combination of locks is different. Because id is a unique index, the delete statement filters the where condition based on the index of the id column. after the record with id = 10 is found, first, the id = 10 index records on the unique index will be added with the X lock. at the same time, the primary key index (clustered index) will be returned based on the read name column ), then add the X lock to the primary key index corresponding to name = 'd on the clustered index. Why should records on clustered indexes be locked? Imagine if a concurrent SQL statement is updated through the primary key index: update t1 set id = 100 where name = 'd; at this time, if the delete statement does not lock the record on the primary key index, the concurrent update will not be aware of the existence of the delete statement, violating the constraints that require serial execution to update/delete the same record.
Conclusion: If the id column is a unique column, it has a unique index. Therefore, SQL requires two X locks, one corresponding to the id = 10 record on the id unique index, and the other corresponding to the [name = 'D' on the clustered index ', id = 10.
Compared with the combination 1 and 2, the combination 3 changes, and the isolation level remains the same as RC, but the constraints on the id column are reduced. the id column is no longer unique and has only one common index. Assume that the delete from t1 where id = 10 statement still selects the index in the id column to filter the where condition. what locks will be held at this time? See also:
According to this figure, we can see that, first, the records that meet the id = 10 query conditions on the id column index have been locked. At the same time, the records on the primary key indexes corresponding to these records are also locked. The only difference from composite 2 is that Composite 2 has at most one record that meets the equivalent query, and composite 3 locks all the records that meet the query conditions.
Conclusion: If the id column has a non-unique index, all corresponding records that meet the SQL query conditions will be locked. At the same time, these records on the primary key index will also be locked.
Compared with the preceding three combinations, this is a special case. There is no index on the id column, where id = 10; this filtering condition cannot be filtered by the index, so you can only perform full table scan for filtering. What locks does SQL apply to this combination? In other words, what locks will be applied during full table scan? There are also many answers: Some people say that the X lock will be applied to the table; some say that the X lock will be applied to the records with the id = 10; selected from the clustered index. What is the actual situation? See:
Because the id column does not have an index, you can only perform a clustered index for all scans. As you can see, there are two records that meet the deletion condition. However, the X lock is applied to all records on the clustered index. The X lock is applied to all records regardless of whether the conditions are met. Neither adding a table lock nor adding a row lock to a record that meets the conditions.
Someone may ask? Why not apply a lock only to records that meet the conditions? This is determined by the implementation of MySQL. If a condition cannot be quickly filtered by the index, the storage engine locks all records and returns the results, which are then filtered by the MySQL Server layer. Therefore, all records are locked.
Note: in actual implementation, MySQL has some improvements. when the filtering conditions of MySQL Server are not met, the unlock_row method will be called, lock records that do not meet the conditions (violation of the 2PL constraint ). This ensures that only the locks on the matching records are held at the end, but the locking operation of each record cannot be omitted.
Conclusion:If no index exists in the id column, the SQL statement performs a full scan of the clustered index for filtering, because the filtering is performed at the MySQL Server level. Therefore, the X lock is applied to each record, whether or not the conditions are met. However, for the sake of efficiency, MySQL has been optimized. for records that do not meet the conditions, the locks will be placed after judgment, and the final hold will be the locks on the records that meet the conditions, however, the locking/unlocking actions on records that do not meet the conditions will not be omitted. Optimization also violates the 2PL constraint.
The above four combinations are all lock actions at the Read Committed isolation level. the next four combinations are Lock actions at the Repeatable Read isolation level.
Combination 5: The id column is the primary key column, and the Repeatable Read isolation level. for delete from t1 where id = 10; this SQL statement, locking is consistent with combination 1: [id primary key, Read Committed.
Similar to the combination five, the combination six locks are consistent with the combination two: [unique id index, Read Committed. There are two X locks. the unique id index is the one that meets the condition, and the record corresponding to the clustered index is one.
Do you still remember the difference between the four isolation levels of MySQL mentioned above? The RC isolation level allows Phantom read, while the RR isolation level does not allow Phantom read. However, in combination 5 and 6, the locking behavior is exactly the same as that under RC. How can we prevent phantom reads at the RR isolation level? The answer to the question is displayed in combination 7.
Combination 7: Repeatable Read isolation level. the id has a non-unique index and executes delete from t1 where id = 10. assume that the index on the id column is selected for conditional filtering and the final locking behavior is performed, what is it like? Take a look at the figure below:
In this figure, compared to composite 3: [non-unique lock on the id column, Read Committed] seems the same, but there is a big difference. The biggest difference is that there is a GAP lock in this figure, and the GAP lock does not seem to be added to the record. the reverse is the position between loading two records. what is the use of the GAP lock?
In fact, this extra GAP lock is the RR isolation level. compared with the RC isolation level, there will be no phantom read key. Indeed, the location of the GAP lock is not the record itself, but the GAP between two records. The so-called phantom read refers to the same transaction, which performs the current read twice in a row (for example, select * from t1 where id = 10 for update ;), the two current reads return identical records (the number of records is the same, and the records are also the same), and the second current read, no more Records (Phantom) will be returned for the first time ).
To ensure that the record is consistent between the first read and the second read, other transactions do not insert new records that meet the conditions and commit them. To achieve this function, the GAP lock came into being.
As shown in, the locations where new items that meet the conditions can be inserted (id = 10). Considering the order of B + tree indexes, items that meet the conditions must be stored continuously. Records with id = 10 are not inserted before [6, c;
Insert operations, such as insert [10, aa], are first located between [6, c] and [10, B], and before insertion, checks whether the GAP is locked. if it is locked, Insert cannot Insert records. Therefore, the record that meets the condition is locked (X lock) through the first read, similar to the combination of three. At the same time, three GAP locks are added, and three gaps that may meet the condition record are inserted to the lock to ensure that new records with id = 10 cannot be inserted in subsequent Insert operations, this eliminates the second current read of the same transaction and creates an illusion.
If you are interested in seeing this, you can ask: Since Phantom read prevention requires the protection of GAP locks, why is combination 5 and combination 6 also at the RR isolation level, but does not require a GAP lock?
First, this is a good question. Secondly, it is very easy to answer this question. The purpose of the GAP lock is to prevent two current reads of the same transaction from phantom reads. In combination 5, id is the primary key. in combination 6, id is the unique key, which guarantees uniqueness. A maximum of one record can be returned for an equi-type query. records with the same new values are not inserted in the query. Therefore, the use of the GAP lock is avoided. In fact, there is a more in-depth problem to address this problem: if combination 5 and 6 are used, for SQL: select * from t1 where id = 10 for update; the first query, if no record meeting the query conditions is found, can the GAP lock be omitted? This question is for everyone to think about.
Conclusion:At the Repeatable Read isolation level, the id column has a non-unique index corresponding to SQL: delete from t1 where id = 10. first, locate the first record that meets the query conditions using the id index, add the X lock on the record, add the GAP lock on the GAP, add the X lock on the record on the primary key clustered index, and then return the lock; then read the next one and repeat it. Until the first record that does not meet the conditions [11, f] is performed, the record X lock is not required, but the GAP lock is still required, and the return ends.
In the last case at the Repeatable Read isolation level, the id column has no index. In this case, SQL: delete from t1 where id = 10; no other paths can be selected, and only full table scans can be performed. The final locking status is shown in:
This is a terrible phenomenon. First, the X lock is applied to all records on the clustered index. Second, the GAP between each record of the clustered index is also added with the GAP lock. In this example table, there are only six records. a total of six record locks and seven GAP locks are required. Imagine if there are 10 million records in the table?
In this case, except for the fast illumination without locking, no other concurrent SQL statements with locking can be executed, updated, deleted, inserted, and the entire table is locked.
Of course, similar to combination 4: [no id index, Read Committed], MySQL has also made some optimizations in this case, which is the so-called semi-consistent read. When semi-consistent read is enabled, MySQL locks records that do not meet the query conditions in advance. In the preceding example, all record locks except [d, 10], [g, 10] are released without the GAP lock. How to trigger semi-consistent read: either the read committed isolation level or the Repeatable Read isolation level, and the innodb_locks_unsafe_for_binlog parameter is set. For more details about semi-consistent read, refer to my previous blog: MySQL + InnoDB semi-consitent read principles and implementation analysis.
Conclusion:At the Repeatable Read isolation level, if the current Read of the full table scan is performed, all records in the table above will be locked and all gaps in the clustered index will be locked, eliminate all concurrent update, delete, and insert operations. Of course, you can also trigger semi-consistent read to alleviate the impact of lock overhead and concurrency. However, semi-consistent read itself will also cause other problems and is not recommended.
For the simple SQL mentioned above, 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.
Serializable isolation level, which affects SQL1: select * from t1 where id = 10; this SQL statement, at the RC and RR isolation level, is read by snapshots without locking. However, at the Serializable isolation level, SQL1 will apply a read Lock, that is to say, snapshot reading no longer exists, and MVCC concurrency control will be downgraded to Lock-Based CC.
Conclusion:In MySQL/InnoDB, the so-called read lock does not apply to all situations, but is related to the isolation level. Serializable isolation level, read is no longer valid without locking, all read operations are the current read.
Here, MySQL locks have also been introduced. As long as you analyze the above ideas and most of the SQL statements, you can analyze the locks they will apply. Here, let's look at a slightly more complex SQL statement to illustrate another logic of MySQL locking. The SQL example is as follows:
What locks will be applied to SQL statements in? Assume that the locks at the Read Committed isolation level are left for analysis by the reader .), At the same time, assume that the SQL statement adopts the idx_t1_pu index.
Before analyzing the locking of this SQL statement in detail, you still need to have a knowledge reserve, that is, how to split the where condition in an SQL statement? For more information, see my previous article: SQL where condition extraction and application analysis in the database. Here, I will give the analysis result directly:
Index key:Pubtime> 1 and puptime <20. This condition is used to determine the SQL query range 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 the Index Key.
Table Filter:Comment is not NULL. This condition cannot be filtered on the idx_t1_pu index, and can only be filtered on the clustered index.
After analyzing the composition of the SQL where condition, let's take a look at the SQL locking (RR isolation level), as shown in:
It can be seen that, at the Repeatable Read isolation level, the range determined by the Index Key is added with the GAP lock; the condition (userid = 'hdc ') given by the Index Filter lock when to Filter, according to MySQL version, Index Condition Pushdown (ICP) is not supported before MySQL 5.6. Therefore, Index Filter is used at the MySQL Server layer and Index Condition Pushdown is supported after MySQL 5.6, then filter on the index. If you do not support the ICP record and do not meet the Index Filter record, you also need to add the record X lock. if you support the ICP record, you do not need to add the record X lock (in the figure, the X lock marked by the Red Arrow, whether to add, depends on whether the ICP is supported); and the Filter condition corresponding to Table Filter, after reading in the clustered index, mySQL Server-level filtering, so the clustering index also requires the X lock. Finally, a record meeting the conditions [8, hdc, d, 5, good] is selected, but the number of locks is far greater than the number of records meeting the conditions.
Conclusion:At the Repeatable Read isolation level, the where condition must be extracted for a complex SQL statement. The GAP lock must be applied to the range determined by the Index Key. The Index Filter condition depends on whether the MySQL version supports ICP. if the Index Filter record is not supported, the X lock is not applied, otherwise, the X lock is required. The X lock is required no matter whether the Filter conditions are met or not.
The previous sections in this article basically cover all the locking rules of MySQL/InnoDB. Understanding how to lock MySQL has two important roles:
You can write SQL statements that do not encounter deadlocks based on MySQL locking rules;
You can locate the cause of the deadlock on the egress according to the MySQL locking rules;
Next, let's take a look at two deadlocks (one is two SQL statements of two sessions generate deadlocks; the other is one SQL statement of two sessions, resulting in deadlocks ):
The two deadlock cases above. The first one is very easy to understand and is also the most common deadlock. each transaction executes two SQL statements and holds a lock respectively, and then adds another lock to generate a deadlock.
In the second case, although each Session has only one statement, a deadlock still occurs. To analyze the deadlock, you must first use the MySQL locking rules mentioned earlier in this article. For Session 1, the [hdc, 1], [hdc, 6] read starting from the name index meet the conditions, not only will the record X lock on the name index be added, in addition, the record X lock on the clustered index will be added. The lock sequence is [1, hdc, 100], and then [6, hdc, 10]. Session 2, starting from the pubtime Index, [] and [, 1] all meet the filtering conditions. The record X lock on the clustered index will also be added, and the lock order will be [6, hdc, 10], and later [1, hdc, 100]. No. the lock sequence of Session 1 is the opposite. if both sessions hold the first lock and the second lock is requested, the deadlock will occur.
Conclusion:Whether a deadlock occurs is not caused by the number of SQL statements in the transaction,The key to deadlock lies in: Two (or more) sessionsLock orderInconsistent. Using the locking rules of each SQL statement in MySQL, we can analyze the locking sequence of each statement, then, check whether there are locks in the opposite order between multiple concurrent SQL statements to analyze various potential deadlocks and the causes of deadlocks on the egress.
This article also comes to an end. let's make a simple conclusion to fully master the locking rules of MySQL/InnoDB, and even the locking rules of any other databases, you must have the following knowledge points:
Understand some basic theoretical knowledge about the database: data storage format (heap organization table vs cluster index table); concurrency control protocol (MVCC vs Lock-Based CC); Two-Phase Locking; database Isolation Level definition (Isolation Level );
Understand the SQL execution plan (primary key scan vs unique key scan vs range scan vs full table scan );
Understand the implementation details of the database (filtering Condition extraction; Index Condition Pushdown; Semi-Consistent Read );
Understand the cause of the deadlock and the analysis method (the locking sequence is inconsistent; analyze the locking sequence of each SQL statement)
With these knowledge points, coupled with appropriate practical experience, we have full control over MySQL/InnoDB locking rules.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
