Detailed discussion of InnoDB locks (record, gap, Next-Key lock)

Record lock locks a single index record. Record lock always locks the index, not the record itself. Even if there is no index on the table, InnoDB will create a hidden clustered primary key index in the background, and it is this hidden clustered primary key index that is locked. So when a SQL statement does not follow any index, an X lock will be added behind each clustered index. This is similar to a table lock, but in principle it should be completely different from a table lock.

Gap lock locks the gap between index records, or locks before or after an index record, but not the index record itself. The gap lock mechanism is mainly used to solve the phantom read problem in repeatable read mode. The following is a demonstration of phantom reads and how gap locks solve phantom reads. Regarding this area, let’s first give a few definitions

Snapshot read:

Simple select operation, without lock in share mode or for update, snapshot read will not add any locks, and due to the existence of MySQL's consistent non-locking read mechanism, any snapshot read will not be blocked. However, if the transaction isolation level is SERIALIZABLE, then the snapshot read will also be added with a shared next-key lock. This article does not describe the SERIALIZABLE isolation level.

Currently reading:

The term in the official documentation is locking read, which means insert, update, delete, select..in share mode and select..for update. The current read will lock all scanned index records, regardless of whether the subsequent where condition hits the corresponding row record. The current read may cause deadlock.

Intention lock:

Innodb's intention lock is mainly used when multiple granularity locks coexist. For example, if transaction A wants to add an S lock on a table, if a row in the table has been locked by transaction B, then the application for the lock should also be blocked. If there is a lot of data in the table, the overhead of checking the lock flag row by row will be very high and the system performance will be affected. To solve this problem, a new lock type can be introduced at the table level to indicate the locking status of the row to which it belongs, which leads to the concept of "intention lock". For example, if there are 100 million records in a table, and transaction A has locked rows on several of them, transaction B needs to add a table-level lock to the table. If there is no intention lock, it needs to check the table to see whether the 100 million records are locked. If an intention lock exists, then if transaction A adds an intention lock and then an X lock before updating a record, transaction B first checks whether there is an intention lock on the table and whether the existing intention lock conflicts with the lock it is about to add. If there is a conflict, it waits until transaction A releases it without having to check each record one by one. When transaction B updates the table, it does not need to know which row is locked. It only needs to know that one row is locked.
To put it simply, the main function of the intention lock is to deal with the contradiction between row locks and table locks, and to show that "a transaction is holding a lock on a row, or is about to hold a lock"

Non-repeatable read:

It means that in the same transaction, the records read for several consecutive snapshot reads should be the same.

The demonstration of non-repeatable read is relatively simple and will not be discussed in this article.

Phantom reading:

It means that a current read operation is performed in a transaction A, and another transaction B inserts a record within the affected range of transaction A. At this time, when transaction A performs another current read operation, phantom rows appear. The main difference between this and non-repeatable read is that in transaction A, one is a snapshot read and the other is a current read; and in transaction B, one is any DML operation and the other is just insert. For example, in A, the result set of select * from test where id<10 lock in share mode is (1,2,3). At this time, a record 4 is inserted into the test table in B. Then, the result set of re-query in A is (1,2,3,4), which is inconsistent with the result set of the first query in transaction A. The 4 here is a phantom row.

Demonstration conditions: In the reread isolation level, Next-Key Locks is used by default, which is a combination of Record lock and gap lock. In other words, in addition to locking the record itself, the gaps between indexes are also locked. Therefore, this gap lock mechanism is turned on by default and does not generate phantom rows. If we want to demonstrate phantom rows, we can either change the isolation level to read-commited or disable gap lock in REPEATABLE-READ mode. Here we use the second method.

The demonstration of phantom read introduces the innodb_locks_unsafe_for_binlog parameter before the demonstration, which can disable gap lock.

innodb_locks_unsafe_for_binlog: static parameter, the default value is 0, which means gap lock is enabled. If it is set to 1, it means gap lock is disabled. At this time, MySQL only has record lock. However, it is worth noting that even if it is set to 1, the gap lock used for foreign key and unique key duplicate checks is still valid. At this point, it can be simply understood that the isolation level of the transaction degenerates to repeatable read, and there should still be some difference between the two. It is recommended not to set it casually. The setting here is just for a simple phantom read demonstration. Subsequent versions of MySQL may abandon this parameter.

Session 1 first adds a current read to the records with myid>95

mysql> show create table test_gap_lock\G
*************************** 1. row ***************************
Table: test_gap_lock
Create Table: CREATE TABLE `test_gap_lock` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`myid` int(11) 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 At this time, session 2 successfully inserts the record with myid=98.

insert into test_gap_lock values(6,'jiang2',98);

Query OK, 1 row affected (0.00 sec)

When session 1 checks again, it finds that the record with myid=98 already exists. This record is the phantom row.

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 solves phantom read problem Demonstration conditions: We change the innodb_locks_unsafe_for_binlog value back to the default value of 0, and tx_isolation is

REPEATABLE-READ, be sure to explain it during the demonstration to ensure that the SQL uses the non-unique index idx_myid (because if the test data is small, the optimizer may directly scan the entire table, which will result in locking all records and making it impossible to simulate a gap lock)

Demonstration 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(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`myid` int(11) 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 explains to ensure that the current read SQL of the session executes 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 inserts myid=56 successfully because the locked gap is myid>100, and 56 is not in this range. When inserting myid=109, it will be stuck until session 1 commits, rolls back, or the lock wait times out. Before the lock wait times out, the same SQL is executed in session 1, and the result is still only the record with id=5,98, which avoids the phantom read 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

Demonstration Example 2 (non-unique index + equal 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 inserts the record with myid=99, which is still blocked due to a gap lock; inserting the record with myid=97 succeeds

mysql> insert into test_gap_lock values(676,'gap recorded 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 > 100 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 > 100 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 because of a gap lock; the record with id=123 cannot be selected..in share mode because of a record lock on the record; id=125 can be selected..in share mode and updated, which is quite strange. This should also be considered as current read. However, I later checked the official documentation and learned that the gap lock will only block the insert operation because there are no records in the gap. Except for the insert operation, the results of other operations should be equivalent to no operations, so 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

Internal locking principle of gap lock Preconditions for gap lock: 1. The transaction isolation level is REPEATABLE-READ, the innodb_locks_unsafe_for_binlog parameter is 0, and the index used by SQL is a non-unique index.

2 The transaction isolation level is REPEATABLE-READ, the innodb_locks_unsafe_for_binlog parameter is 0, and sql is a current read operation of a range. In this case, a gap lock is added even if it is not a non-unique index.

Gap lock locking steps

The above examples 1 (non-unique index + range current read) and 3 (primary key index + range current read) are easy to understand. Why does example 2 (non-primary key index + equal current read) also generate gap lock? This should start from the principle of btree index. We all know that btree index is arranged in order, and innodb has primary key clustered index. My drawing ability is limited. I have analyzed the locking process of example 2 as an example. The handwritten locking process is as follows

From the data organization order in the figure, we can see that there are two records with myid=100. If a gap lock is added, three gaps will be generated, namely gap1 (98, 100), gap2 (100, 100), and gap3 (100, 105). The myid values ​​in these three open intervals (if I remember the high school math correctly) cannot be inserted. Obviously, gap1 also has (myid=99, id=3)(myid

=99,id=4), gap2 has no actual gap, and gap3 has records such as (myid=101,id=7). In addition, a record lock is added to the two records with myid=100, which means that these two data services cannot be read by other sessions (as can be seen in Example 3)

Next-Key Locks

By default, the transaction isolation level of MySQL is repeatable read, and the innodb_locks_unsafe_for_binlog parameter is 0, then next-key locks are used by default. The so-called Next-Key Locks are a combination of Record lock and gap lock, that is, in addition to locking the record itself, the gaps between indexes are also locked.

Next, we analyze how to lock most SQL types, assuming that the transaction isolation level is repeatable read .

select .. from

No lock of any kind

select...from lock in share mode

A shared next-key lock is placed on any index records scanned, as well as an exclusive lock on the primary key clustered index.

select..from for update

Add an exclusive next-key lock on any index records scanned, and an exclusive lock on the primary key clustered index

update..where delete from..where

Add a next-key lock to any index records scanned, and an exclusive lock to the primary key clustered index

insert into..

A simple insert will add an exclusive lock to the index record corresponding to the inserted row. This is a record lock and there is no gap, so it will not block other sessions from inserting records in the gap. However, before the insert operation, a lock will be added. The official document calls it the insertion intention gap lock, which is the intentional gap lock. The purpose of this intentional gap lock is to indicate that when multiple transactions insert into the same gap concurrently, as long as the inserted records are not in the same position in the gap, they can be completed without waiting for other sessions, so that the insert operation does not need to add a real gap lock. Imagine that if a table has an index idx_test and there are records 1 and 8 in the table, then each transaction can insert any record between 2 and 7, and will only add a record lock to the currently inserted record, and will not block other sessions from inserting records different from their own, because they do not have any conflicts.

Assuming a unique key violation error occurs, a read lock will be placed on the duplicate index record. When multiple sessions insert the same row record at the same time, if another session has obtained an exclusive lock on the row, a deadlock will occur.

Insert caused deadlock phenomenon demonstration 1

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) 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 is stuck at this time

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES(1);

Session 3 is also stuck at this time

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1);

Session 1 At this time we roll back session 1

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

It is found that the insert of session 2 is successful, but session 3 detects a deadlock and rolls back.

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 cause analysis:

First, session1 inserts a record and obtains an exclusive lock for the record. At this time, session2 and session3 both detect primary key conflict errors, but because session1 has not been committed, session1 is not considered to have successfully inserted the record, so it cannot directly report an error. Therefore, session2 and session3 both apply for a shared lock for the record, but at this time they have not yet obtained the shared lock and are in the waiting queue. At this time, session1 rolls back, releasing the exclusive lock on the row record, and then session2 and session3 both acquire the shared lock on the row. If session2 and session3 want to insert records, they must obtain exclusive locks, but because they both have shared locks, they can never obtain exclusive locks, so deadlock occurs. If session1 is committed instead of rolled back at this time, both session2 and session3 will directly report a primary key conflict error. Check the deadlock log and you will see the result at a glance

Deadlock caused by insert 2

Another similar deadlock is that session1 deletes the record with id=1 and does not commit it. At this time, session2 and session3 insert the record with id=1. At this time, session1 is committed. If session2 and session3 need to insert, they need to obtain exclusive locks, and then deadlock occurs; if session1 rolls back, session2 and session3 will report primary key conflict errors. No more demonstration here.

INSERT ... ON DUPLICATE KEY UPDATE

The difference between this type of SQL and insert locking is that if a key conflict is detected, it directly applies for an exclusive lock instead of a shared lock.

replace

If the replace operation does not detect a key conflict, its locking strategy is similar to that of the insert operation. If a key conflict is detected, it also directly applies for an exclusive lock.

INSERT INTO T SELECT ... FROM S WHERE ...

The locking strategy on the T table is the same as that for ordinary inserts. In addition, a shared next-key lock is added to the related records in the S table. (If it is repeatable read mode, it will not be locked)

CREATE TABLE ... SELECT ... adds a shared next-key lock on the selected table

Locking strategy for auto-increment id

When a field in a table is an auto-increment column, InnoDB will add an exclusive lock to the end of the index. In order to access this auto-increment value, a table-level lock is required. However, the duration of this table-level lock is only the current SQL, not the entire transaction. That is, the table-level lock is released after the current SQL is executed. Other sessions cannot insert any records while this table-level lock is held.

Locking strategy for foreign key detection

If a foreign key constraint exists, any insert, update, or delete operation will check the constraint and will place a shared record lock on the corresponding record, regardless of whether there is a foreign key conflict.

The above article discusses innodb locks (record, gap, Next-Key lock) in detail. This is all I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.