InnoDB Next-key Lock

Gap Lock's internal locking principle gap Lock's front conditions:

1 transaction ISOLATION level is Repeatable-read,innodb_locks_unsafe_for_binlog parameter is 0, and SQL Walk index is non-unique index

2 The Transaction isolation level is the Repeatable-read,innodb_locks_unsafe_for_binlog parameter of 0, and SQL is the current read operation of a range, and if not a non-unique index adds Gap lock

Record Lock

A single index record is locked, record lock is always indexed, not the record itself, even if there is no index on the table, then InnoDB will create a hidden clustered primary key index in the background, then the lock is the hidden clustered primary key index. So when a SQL does not walk any index, it will be followed by an X lock on each clustered index, similar to a table lock, but the principle and table locks should be completely different.

Gap Lock

Locks are added to the gap between index records, either before or after an index record, and not including the index record itself. The mechanism of gap lock is mainly to solve the problem of phantom reading in repeatable reading mode, and how to solve the Phantom reading with the demonstration of Phantom reading and Gap lock. For this piece, give a couple of definitions first.

Snapshot read:

Simple select operation, no lock in share mode or for update, snapshot read does not add any locks, and because of the consistency of MySQL non-locking 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 this article does not describe the SERIALIZABLE isolation level.

Current read:

The term official documentation is called locking read, which is insert,update,delete,select. In Share mode and select. For update, the current read is locked on all scanned index records, regardless of whether the where condition after it has hit the corresponding row record. The current read may cause a deadlock.

Intent Lock:

InnoDB's intention to lock the main user multi-granularity lock coexistence situation. For example, transaction A should have an S lock on a table, and if a row in the table has been X-locked by transaction B, the lock request 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. To solve this problem, a new lock type can be introduced at the table level to indicate the locking of the row it belongs to, 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 a table level lock, if there is no intention to lock, then go to the table to find out whether the 100 million records are locked. If there is an intent lock, then if transaction a before updating a record, plus an intent lock, plus x lock, transaction b first check whether there is an intent lock on the table, if there is an intent lock with the lock you are prepared to conflict, if there is a conflict, then wait until transaction a release, without the need to record to detect. When transaction b updates the table, there is no need to know exactly which line is locked, as long as it knows that a row is locked anyway.
The main function 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 row, or is ready to hold a lock"

Non-repeatable READ:

Refers to the same transaction, a number of consecutive snapshots read, read the record should be the same

The non-repeatable presentation is simple and is not discussed in this article.

Phantom read:

Refers to the execution of a current read operation in a transaction a, while another transaction B inserts a record within the affected interval of transaction A, when transaction a then performs a current read operation, a phantom line appears. The main difference between this and non-repeatable reads is that one in transaction A is a snapshot read, one is currently read, and one in transaction B is any DML operation, and one is just insert. For example, in a, the select * from Test where the id<10 lock in Share mode result set is (three-way), when the test table in B is inserted a record 4, when a query result set is (1,2,3,4), and transaction A are inconsistent with the result set of the first query, where 4 is the Phantom row.

Demo Condition: Due to the stress level of the Next-key Locks, the default is the combination of record lock and Gap lock, that is, in addition to lock the record itself, but also to lock the gap between the index, so the gap lock mechanism is opened by default, does not produce magic line, So we're going to show the magic line, either change the isolation level to read-commited, or disable gap lock in Repeatable-read mode, and here we take the second approach.

A demo of Phantom Reading

The Innodb_locks_unsafe_for_binlog parameter was introduced before the demo, which disables Gap lock.

Innodb_locks_unsafe_for_binlog: Static parameter, default is 0, means to start gap lock, if set to 1, means to disable gap lock, then MySQL only record lock, but it is worth noting that even if the set of 1 , the gap lock used for foreign key and unique key repeat checks is still valid. At this point it can be simply understood that the isolation level of the transaction is degraded into repeatable read, and then the two should be different. The suggestion is not to set casually, we set up here just to do a simple magic reading demo, MySQL later version may be discarded this parameter.

Session 1 First add the myid>95 record to a 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) is 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 inserted 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 to exist, and this record is the 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)

Gap lock mechanism to solve the Phantom reading problem demo

Condition: We then change the Innodb_locks_unsafe_for_binlog value back to the default value of 0, and tx_isolation to Repeatable-read, be sure to explain the presentation, make sure that the SQL went non-unique index idx_ myID (because if the test data is small, it is possible for the optimizer to go directly through the full table scan, which results in locking all records and not simulating the 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) is 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 make sure that the current read SQL execution of the session goes through the index 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 First Insert myid=56 success, because the lock gap is myid>100,56 not within the range, and then insert myid=109, will remain stuck until the session 1commit,rollback or the direct lock wait timeout, The same SQL is executed in session 1 before the lock wait time-out, and the results are still only id=5,98 records, which avoids the phantom reading problem
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 record is still blocked, there is gap lock; Insert myid=97 record successfully
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 > + 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 > + 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, there is a record of gap lock;id=123 cannot select. In share mode, a record lock;id=125 can be select if there are records on it. In share mode and update, this is strange, it should be the current reading, but later to view the official document that the gap lock will only block the insert operation, because there is no record in the Gap Gap, in addition to the insert operation, Other operation results should be equivalent to the empty operation, 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

Gap Lock's internal locking principle gap Lock's front conditions:

1 transaction ISOLATION level is Repeatable-read,innodb_locks_unsafe_for_binlog parameter is 0, and SQL Walk index is non-unique index

2 The Transaction isolation level is the Repeatable-read,innodb_locks_unsafe_for_binlog parameter of 0, and SQL is the current read operation of a range, and if not a non-unique index adds Gap lock

Lock step for Gap lock

For example 1 above (non-unique index + range current read) and Example 3 (primary key index + range current read) better understand, that is why example 2 (non-primary key index + equivalent current read) for what also will produce gap lock, this from the principle of btree index, we all know that Btree index is arranged in order, and InnoDB there is a primary key clustered index, I have limited ability to draw, example 2 of the lock process analysis example, the handwriting lock process such as

The data organization order can be seen, myid=100 record has two, if add gap lock will produce three clearance, respectively is GAP1 (98,100), GAP2 (100,100), GAP3 (100,105), In these three open intervals (if my high school math is remembered correctly) the myID values cannot be inserted, apparently gap1 (myid=99,id=3) (myID

=99,id=4) and other records, GAP2 no actual clearance, GAP3 also (myid=101,id=7) and other records. Also, the record lock was added to the two records of myid=100, which means that the two data services could not be read by the other session (see example three)

Next-key Locks

By default, the transaction isolation level for 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 lock the record itself, but also to lock the gap between the index.

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

Select: From

Do not add any type of lock

Select...from lock in Share mode

Shared Next-key lock on any index record scanned, plus primary key clustered index and exclusive lock

Select: From for update

Next-key Lock is added to any index record scanned, and there is a primary key clustered index plus an exclusive lock.

Update: where delete from: where

Add Next-key Lock to any index record scanned, and a primary key clustered index plus an exclusive lock

INSERT INTO..

A simple insert will have an exclusive lock on the index record for the inserted row, which is a record lock with no gap, so it does not block other sessions from inserting records into the gap gap. However, before the insert operation, there will be a lock, the official document called it insertion intention gap lock, which is the intention of the gap lock. The purpose of this intent 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 the gap gap, you do not have to wait for the other session to complete, so that the insert operation does not need to add a real gap lock. Imagine that if a table has an index idx_test, with records 1 and 8 in the table, then each transaction can insert any record between 2 and 7, only the record lock will be added to the currently inserted records, and it will not block other sessions from inserting records that are different from the other session. Because they don't have any conflict.

Assuming a unique key violation error occurs, a read lock is added to the duplicate index record. When more than one session inserts the same row record at the same time, if another session has been locked by a row, it will cause a deadlock.

Insert-induced Deadlock Behavior 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 has been stuck.

Mysql> begin;

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into T1 VALUES (1);

Session 1 Then we roll back Session1

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

The insert for session 2 was found to be successful, while 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 insert a record, get the record of the exclusive lock, then Session2 and Session3 all detected the primary key conflict error, but because Session1 did not commit, so session1 does not count as insert success, so it can not directly error it, So Session2 and Session3 both applied for the shared lock of the record, and the shared lock was not acquired and was in the waiting queue. At this time Session1 rollback, also released the row of records of its lock, then Session2 and Session3 both get the shared lock on the line. While Session2 and Session3 want to insert records, they have to get exclusive locks, but because they own a shared lock, they can never get to the exclusive lock, so the deadlock occurs. If the session1 is a commit instead of rollback, then both Session2 and Session3 directly error the primary key violation. Viewing the deadlock log is also a glance

Insert-induced deadlock behavior 2

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

INSERT ... On DUPLICATE KEY UPDATE

The difference between this SQL and the insert lock is that if a key conflict is detected, it directly requests an exclusive lock instead of a shared lock. Replacereplace operation if the key conflict is not detected, then its lock policy and INSERT similar, if a key conflict detected, then it is also directly re-apply the lock INSERT into T SELECT ... From S WHERE ...
The lock policy 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 locking) CREATE TABLE ... SELECT ... The lock policy for the shared next-key lock self-increment ID on the Select table when a field in a table is a self-increment column, InnoDB adds an exclusive lock at the bottom of the index. To access this self-increment, a table-level lock is required, but the table-level lock lasts only for the current SQL, not the entire transaction, that is, the table-level lock is freed when the current SQL is executed. Other sessions cannot insert any records when this table-level lock is held. Locking policy for foreign key detection if a FOREIGN key constraint exists, any insert,update,delete will detect the constraint, and the record lock will be shared on the corresponding records, regardless of the presence of a foreign key conflict.

InnoDB Next-key Lock