Understanding the InnoDB Lock (Record,gap,next-key Lock)

Record LockA single index record is locked, the record lock is always indexed, not the records themselves, even if there is no index on the table, then InnoDB creates a hidden clustered primary key index in the background, so the hidden clustered primary key index is locked. So when an SQL does not take any indexes, it will add an X lock behind each clustered index, similar to a table lock, but the principle and table lock should be completely different.


Gap LockA lock in the gap between index records, or a lock before or after an index record, does not include the index record itself. The mechanism of gap lock is to solve the problem of phantom reading in the repeatable reading mode, and how to solve the illusion reading with the demo of Phantom Reading and Gap lock. For this piece, first give a few definitions


Snapshot read:

Simple select operation, no lock in share mode or for update, snapshot read does not add any locks, and because of MySQL's consistent unlocked read mechanism exists, any snapshot read will not be blocked. However, if the isolation level of the transaction is serializable, then the snapshot read is also added to the shared Next-key lock, and the Serializable isolation level is not described in this article.
Current read:

The official document term is locking read, or Insert,update,delete,select. In Share mode and select. For update, the current read locks all the scanned index records, regardless of where they are after the corresponding row records are hit. The current read may cause a deadlock. Intent Lock:

InnoDB's intent to lock the majority of users with multiple-granularity locks coexist. For example, transaction A to add S lock on a table, if a row in the table has been x locked by transaction B, then the application of the lock should also be blocked. If there is a lot of data in the table, the overhead of checking the lock flags line by row will be significant and the performance of the system will be affected. In order to solve this problem, a new lock type can be introduced at the table level to represent the lock of the row to which it belongs, which leads to the concept of "intent lock". For example, if the table records 100 million, transaction A has a few records on the row lock, then transaction B needs to add table-level lock on the table, if there is no intent to lock, it is necessary to go to the table to find out if the 100 million records are locked. If an intent lock exists, then if transaction A is to update a record, first add intent to lock, and then add x lock, transaction b first check whether there is an intent lock on the table, the intention of the existence of the lock and its own ready to add the lock conflict, if there is conflict, then wait until the transaction a release, without the need for a record to detect. Transaction B When you update the table, you don't need to know exactly which line is locked, it just knows that a line is locked anyway.
The main purpose of the intent lock is to handle the contradiction between the row lock and the table lock, to show that "a transaction is holding a lock on a line, or is ready to hold the lock"
non-repeatable reads:

Refers to the same transaction, several consecutive snapshots read, read the record should be the same

Non-repeatable demos are simpler and are not discussed in this article. Phantom reads:

Refers to the execution of a current read operation in a transaction a, while another transaction B inserts a record within the impact interval of transaction A, when transaction a then performs a current read operation, and a phantom line appears. The main difference between this and the non repeatable reading is that in relation to transaction a one is a snapshot read, a current read; and one of the transaction B is any DML operation, and one is just an insert. In a, for example, select * from Test where id<10 lock in share mode result set is (1,2,3), then insert a record 4 in B for the test table, then requery the result set in a (1,2,3,4), and transaction A in the first query out of the result set inconsistent, here 4 is the Phantom line. Demo conditions: Due to the isolation level can be stressed by default Next-key Locks, is the record lock and Gap lock combination, that is, in addition to locking records itself, but also to lock the gap between the index, so this gap lock mechanism opened by default, and will not produce magic line, So we're going to demonstrate the Phantom line, either by changing the isolation level to read-commited or disabling gap lock in Repeatable-read mode, where we use the second approach.

The Phantom-reading demo introduces the Innodb_locks_unsafe_for_binlog parameter before the demo, which disables Gap lock.

Innodb_locks_unsafe_for_binlog: Static parameter, the default is 0, to start gap lock, if set to 1, to disable Gap lock, then MySQL only record lock, but it is worth noting that even though the set of 1 , Gap Lock is still valid for duplicate checks on foreign keys and unique keys. It can be simply understood that the isolation level of the transaction degrades to repeatable reading, and then the two should be different. The suggestion is not casually set, we set up here just do a simple phantom reading demo, MySQL subsequent version may discard this parameter.

Session 1 first adds a myid>95 record to the current read
Mysql> Show CREATE TABLE Test_gap_lock\g
1. Row ***************************
Table:test_gap_lock
Create table:create Table ' Test_gap_lock ' (
' id ' int (one) not NULL,
' Name ' varchar (+) DEFAULT NULL,
' myID ' int (one) DEFAULT NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' uniq_name ' (' name '),
KEY ' Idex_myid ' (' myID ')
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)
Mysql> begin;
Mysql> SELECT * from Test_gap_lock where myid>95 for update;
+----+------------+------+
| ID | name | myID |
+----+------------+------+
| 1 |   Jiang | 99 |
| 2 |   Hubingmei | 99 |
| 5 |  Hubingmei4 | 100 |
+----+------------+------+
3 Rows in Set (0.00 sec)


Session 2 Then Session 2 inserts the MYID=98 record successfully.
INSERT into Test_gap_lock values (6, ' jiang2 ', 98);
Query OK, 1 row Affected (0.00 sec)


Session 1 when session 1 is viewed again, the record myid=98 is found, and this record is a phantom line.
Mysql> SELECT * from Test_gap_lock where myid>95 for update;
+----+------------+------+
| ID | name | myID |
+----+------------+------+
| 1 |   Jiang | 99 |
| 2 |   Hubingmei | 99 |
| 5 |  Hubingmei4 | 100 |
| 6 |   Jiang2 | 98 |
+----+------------+------+
4 rows in Set (0.00 sec)


The Gap lock mechanism solves the Phantom reading problem demonstrationCondition: Let's change the Innodb_locks_unsafe_for_binlog value back to the default value of 0, and tx_isolation to Repeatable-read, make sure that the SQL is out of the unique index when the demo is explain IDX_ myID (because if the test data is low, the optimizer may go to the full table scan directly, which leads to locking all records, can not simulate gap lock)


Demo Example 1 (non-unique index + range current read)Mysql> Show CREATE TABLE Test_gap_lock\g
1. Row ***************************
Table:test_gap_lock
Create table:create Table ' Test_gap_lock ' (
' id ' int (one) not NULL,
' Name ' varchar (+) DEFAULT NULL,
' myID ' int (one) DEFAULT NULL,
PRIMARY KEY (' id '),
UNIQUE KEY ' uniq_name ' (' name '),
KEY ' Idex_myid ' (' myID ')
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)


Session 1 First explain ensure that the session's current read SQL execution is indexed Idx_myid
Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Explain select * from Test_gap_lock where myid>100 for update;
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+--------------- --------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+--------------- --------+
| 1 | Simple | Test_gap_lock | Range | Idex_myid | Idex_myid | 5 |    NULL | 2 | Using Index Condition |
+----+-------------+---------------+-------+---------------+-----------+---------+------+------+--------------- --------+
1 row in Set (0.00 sec)
Mysql> SELECT * from Test_gap_lock where myid>100 for update;
+----+------------+------+
| ID | name | myID |
+----+------------+------+
| 5 |  Hubingmei4 | 101 |
| 98 |  Test | 105 |
+----+------------+------+
2 rows in Set (0.00 sec)


Session 2 Inserts the success of the myid=56 first, because the locked gap is myid>100,56 is not in the range, and then when inserting myid=109, it will be stuck until the sessions 1commit,rollback or the direct lock wait timeout. Executing the same SQL in session 1 before the lock wait times out, the result is still only id=5,98 records, which avoids the problem of phantom reading
mysql> INSERT into Test_gap_lock values (999, ' test2 ', 56);
Query OK, 1 row Affected (0.00 sec)
mysql> INSERT into Test_gap_lock values (123, ' test4 ', 109);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction




Demo Example 2 (non-unique index + equivalent current read)Mysql> select * from Test_gap_lock;
+-----+------------+------+
| ID | name | myID |
+-----+------------+------+
| 1 |   Jiang | 98 |
| 2 |   Hubingmei | 99 |
| 5 |  Hubingmei4 | 101 |
| 6 |  Jiang2 | 100 |
| 7 |   Jiang22 | 70 |
| 67 |   jiang222 | 80 |
| 98 |  Test | 105 |
| 123 |  test4 | 109 |
| 999 |   Test2 | 56 |
+-----+------------+------+
9 Rows in Set (0.00 sec)
Session 1
Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)
Mysql> explain delete from test_gap_lock where myid=100;
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
| 1 | Simple | Test_gap_lock | Range | Idex_myid | Idex_myid | 5 |    Const | 2 | Using where |
+----+-------------+---------------+-------+---------------+-----------+---------+-------+------+-------------+
1 row in Set (0.00 sec)
Mysql> Delete from Test_gap_lock where myid=100;
Query OK, 2 rows Affected (0.00 sec)


Session 2 Insert Myid=99 records are still blocked, gap lock exists; the record of inserting myid=97 is successful
mysql> INSERT into Test_gap_lock values (676, ' Gap recored Test ', 99);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT into Test_gap_lock values (675, ' Gap recored test1 ', 97);
Query OK, 1 row Affected (0.00 sec)


Example 3 (primary key index + range current read)

Mysql> select * from Test_gap_lock;
+-----+------------+------+
| ID | name | myID |
+-----+------------+------+
| 1 |   Jiang | 98 |
| 2 |   Hubingmei | 98 |
| 5 |  Hubingmei4 | 100 |
| 6 |  Jiang2 | 100 |
| 7 |   Jiang22 | 70 |
| 67 |   jiang222 | 80 |
| 98 |  Test | 105 |
| 123 |  test4 | 109 |
| 999 |   Test2 | 56 |
+-----+------------+------+
9 Rows in Set (0.00 sec)
Session 1
Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)
Mysql> Explain select * from Test_gap_lock where ID > a for update;
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | Simple | Test_gap_lock | Range | PRIMARY | PRIMARY | 4 |    NULL | 2 | Using where |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+-------------+
1 row in Set (0.00 sec)
Mysql> SELECT * from Test_gap_lock where ID > a for update;
+-----+-------+------+
| ID | name | myID |
+-----+-------+------+
| 123 |  test4 | 109 |
| 999 |   Test2 | 56 |
+-----+-------+------+
2 rows in Set (0.00 sec)


Session 2 (id=3 can be inserted; id=108 cannot be inserted, the Gap lock;id=123 record cannot be select. In share mode, because the record lock;id=125 can be select. In share mode and update, this is strange, it should be considered as current reading, but later check the official document that Gap lock will only block insert operation, because Gap gap is not any records, in addition to insert operation, Other operational results should be equivalent to null operations, MySQL will not block it.
mysql> INSERT into Test_gap_lock values (108, ' Gap lock Test3 ', 123);
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
mysql> INSERT into Test_gap_lock values (3, ' Gap lock Test3 ', 123);
Query OK, 1 row Affected (0.00 sec)
Mysql> SELECT * from Test_gap_lock where id=125 lock in share mode;
Empty Set (0.00 sec)
Mysql> Explain select * from Test_gap_lock where id=125 lock in share mode;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ------------------------+
| 1 | Simple | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------- ------------------------+
1 row in Set (0.00 sec)
mysql> Update test_gap_lock set myid=12345 where id=125;
Query OK, 0 rows Affected (0.00 sec)
Rows matched:0 changed:0 warnings:0




the internal locking principle of gap lock the precondition of Gap Lock:1 transaction ISOLATION level is Repeatable-read,innodb_locks_unsafe_for_binlog parameter 0, and SQL Walk index is not unique index

2 transaction ISOLATION level is Repeatable-read,innodb_locks_unsafe_for_binlog parameter 0, and SQL is a range of current read operations, even if not a unique index will add gap lock Gap Lock Step

For example 1 above (not unique index + range current read) and Example 3 (primary key index + range current read) better understand, so why does example 2 (non-primary key index + equivalent current read) cause Gap lock for what it is, as we all know from the principle of the Btree index, Btree index is arranged in order, and InnoDB exists the primary key aggregation index, my drawing ability is limited, already example 2 lock process Analysis example, handwritten lock process as follows figure

From the data organization order of the graph, it can be seen that there are two myid=100 records, if the gap lock will produce three gaps, respectively, GAP1 (98,100), GAP2 (100,100), GAP3 (100,105), In these three open ranges (if my high school math is correct) myID values cannot be inserted, apparently gap1 (myid=99,id=3) (myID

=99,id=4) and other records, GAP2 no actual clearance, GAP3 and (myid=101,id=7) and other records. In addition, record lock is added to the two records of myid=100, which means that the two data services cannot be read by other sessions (example three can be seen)

Next-key Locks

By default, the transaction isolation level of MySQL is repeatable and the Innodb_locks_unsafe_for_binlog parameter is 0, and Next-key locks is used by default. The so-called Next-key Locks, is the combination of record lock and Gap lock, that is, in addition to locking the records themselves, but also to lock the gap between the index.

Here's how we lock up most of the SQL type analysis, assuming the transaction isolation level is repeatable read . Select.. from

Without any type of lock Select...from lock in Share mode

Add shared Next-key lock on any index record that is scanned, plus a primary key clustered index to lock the select. from for update

The Next-key lock is added to any index record that is scanned, and the primary key clustered index is added to lock update. where delete from.. where

Add Next-key Lock to any index record that is scanned, plus a primary key clustered index that locks insert into.

A simple insert adds an exclusive lock to the corresponding index record on the insert row, which is a record lock and no gap, so it doesn't block other sessions inserting records in gap gaps. However, before the insert operation, there will be a lock, the official document called it insertion intention gap lock, which is the gap lock intention. The purpose of this gap lock is to indicate that when multiple transactions are inserted into the same gap gap, as long as the inserted record is not the same position in Gap Gap, you do not have to wait for the other session to complete, so that insert operation without adding real gap lock. Imagine that if a table has an index idx_test, a table with records 1 and 8, each transaction can insert any record between 2 and 7, only the record lock is added to the currently inserted records, and no other sessions are plugged into records that are different from their own. Because they didn't have any conflicts.

Assuming a unique key conflict error occurs, the lock will be read on the duplicate index record. When multiple sessions insert the same row record at the same time, a deadlock will result if another session has obtained an exclusive lock on the row. deadlock caused by Insert Demo 1

Mysql> Show CREATE TABLE T1\g
1. Row ***************************
Table:t1
Create table:create Table ' T1 ' (
' I ' int (one) not NULL DEFAULT ' 0 ',
PRIMARY KEY (' i ')
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.00 sec)

Session 1

Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)
mysql> INSERT into T1 VALUES (1);
Query OK, 1 row Affected (0.00 sec)

Session 2 Then Session2 has been stuck

Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)
mysql> INSERT into T1 VALUES (1);

Session 3 then Session3 was stuck.

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into T1 VALUES (1);

Session 1 When we roll back Session1

mysql> rollback;
Query OK, 0 rows Affected (0.00 sec)

Insert succeeded with session 2 found, and Session3 detected a deadlock rollback

Session 2 Query OK, 1 row affected (28.87 sec)

Session 3 ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction deadlock reason analysis:

First Session1 inserts a record, obtains the record the exclusive lock, then Session2 and Session3 both detected the primary key conflict error, but because Session1 did not submit, therefore session1 does not calculate inserts the success, therefore it does not have the direct error, So session2 and Session3 all applied for the shared lock of the record, and there was no shared lock in the waiting queue. At this time Session1 rollback, also released the row of the record of the exclusive lock, then Session2 and Session3 have acquired the shared lock on the line. and Session2 and Session3 want to insert records, must acquire the exclusive lock, but because they have a shared lock, so they can never get the exclusive lock, so the deadlock occurred. If Session1 is a commit instead of rollback, then both Session2 and Session3 directly complain about the primary key conflict error. Viewing the deadlock log is also a glance

The deadlock phenomenon caused by insert 2

Another similar deadlock is session1 deleted id=1 records were not submitted, when Session2 and Session3 were inserted into the id=1 records. At this time Session1 commit, session2 and session3 need to insert, you need to get the exclusive lock, then deadlock also occurred; Session1 rollback, then the Session2 and Session3 error primary key conflict. No more demo here.

INSERT ... On DUPLICATE KEY UPDATE

The difference between this SQL and insert lock is that if a key conflict is detected, it directly requests the lock instead of the shared lock.
If a key conflict is not detected by the replace Replace operation, its lock strategy is similar to insert; If a key conflict is detected, it is also a direct request to add the lock
INSERT into T SELECT ... From S WHERE ...
The lock strategy on the T table is consistent with the normal insert, plus the shared Next-key lock on the related records on the S table. (If it is repeatable read mode, it will not lock)
CREATE TABLE ... SELECT ... Add shared Next-key lock on a select table
the lock policy of self-increasing ID when a field in a table is InnoDB, the end of the index is added with an exclusive lock. To access this increment, a table-level lock is required, but the duration of this table-level lock is only the current SQL, not the entire transaction, that is, the table-level lock is released when the current SQL executes. Other sessions cannot insert any records when this table-level lock is held.
lock policy for foreign key detection if a foreign key constraint exists, any insert,update,delete will detect the constraint and will add the shared record lock on the corresponding records, regardless of whether there is a foreign key conflict.