About innodb locks (record, gap, Next-Key lock) and innodbnext-key

About innodb locks (record, gap, Next-Key lock) and innodbnext-key

Record lock locks a single index record. Record lock always locks the index instead of the record itself, even if the table does not have any indexes, then innodb will create a hidden clustered primary key index in the background, and the lock is the hidden clustered primary key index. Therefore, when an SQL statement does not take any index, the X lock will be applied after each clustered index. This is similar to the table lock, but the principle and table lock should be completely different.

Gap lock locks between index records, or before or after an index record, and does not include the index record itself. The gap lock mechanism is mainly to solve the phantom read problem in the Repeatable read mode. The Phantom read demonstration and the gap lock solve the phantom read problem. Here are several definitions of this part.

Snapshot read:

Simple select operations without lock in share mode or for update. snapshot reading does not apply any locks, and because of mysql's consistent non-locked read mechanism, any snapshot read will not be blocked. However, if the transaction isolation level is SERIALIZABLE, the snapshot read will also be added with the shared next-key lock. This article does not describe the SERIALIZABLE isolation level.

Current read:

The term in the official documentation is locking read, that is, insert, update, delete, select .. in share mode and select .. for update, the current read locks all the indexed records that have been scanned, regardless of whether the where condition after it hits the corresponding row record. The current read may cause a deadlock.

Intention lock:

Intention locks of innodb coexist with multiple granularities. For example, transaction A needs to apply the S lock to A table. If A row in the table has been locked by transaction B, the application for this lock should also be blocked. If there is a lot of data in the table, the overhead of checking the lock mark row by row will be very large, 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 of the row to which the lock belongs. This introduces the concept of "intention lock. For example, if the table records 0.1 billion, transaction A locks several of the records, and transaction B needs to add table-level locks to the table, if there is no intention to lock, you should go to the table to find whether the 0.1 billion records are locked. If the intention lock exists, if transaction A adds the intention lock and X lock before updating A record, transaction B first checks whether the table has the intention lock, whether the existing intention lock conflicts with the lock that you are about to add. If there is A conflict, wait until transaction A is released, without the need to record it 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 a row is locked.
To put it bluntly, the main function of the intention lock is to deal with the conflict between the row lock and the table lock. It can display that "a transaction is holding a lock on a row, or is ready to hold the lock"

Repeatable read:

Refers to the number of consecutive snapshot reads in the same transaction, and the read records should be the same

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

Phantom read:

This refers to the execution of A current read operation in transaction A, while another transaction B inserts A record in the impact interval of transaction, when transaction A executes another current read operation, the phantom line appears. The main difference between this and non-repeatable read is that one of transaction A is snapshot read, one is the current read, and one of transaction B is any dml operation, and the other is insert. For example, in A select * from test where id <10 lock in share mode result set is (1, 2, 3), then A record 4 is inserted to table test in B, in this case, re-query the result set in a is (1, 2, 3, 4), which is inconsistent with the result set queried by transaction A for the first time. Here, 4 is A phantom row.

Demo conditions:At the repeatable isolation level, the Next-Key Locks is used by default, which is the combination of Record lock and gap lock. That is, in addition to locking the Record itself, the gap between indexes is also locked, therefore, this gap lock mechanism is enabled by default and does not generate phantom rows. To demonstrate phantom rows, we must change the isolation level to read-commited, either disable gap lock in REPEATABLE-READ mode, here we use the second method.

The innodb_locks_unsafe_for_binlog parameter was introduced before the demo of phantom read. This parameter can disable gap lock.

Innodb_locks_unsafe_for_binlog: static parameter. The default value is 0, indicating that gap lock is enabled. If it is set to 1, gap lock is disabled. mysql only has record lock, but it is worth noting that, even if 1 is set, the gap lock used for repeat checks on Foreign keys and unique keys is still valid. In this case, the isolation level of the transaction can be simply parsed into repeated reads, and the two should be different. We recommend that you do not set it as needed. Here we just set it as a simple phantom read demonstration. mysql may discard this parameter in subsequent versions.

Session 1 first adds a record with myid> 95 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 (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 inserts the myid = 98 record in session 2.

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, it finds that the record myid = 98 already exists. 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 read problem. Demo condition: We can change the innodb_locks_unsafe_for_binlog value back to the default value 0, and tx_isolation is

REPEATABLE-READ, make sure that the SQL goes through a non-unique index idx_myid (because if the test data is small, the Optimizer may go directly to the full table scan, it will lock all records and cannot simulate the gap lock)

Demo 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 explain to ensure that the current read SQL statement of the session is executed using 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 | 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 | testing | 105 |
+ ---- + ------------ + ------ +
2 rows in set (0.00 sec)

Session 2 is successfully inserted with myid = 56, because the lock gap is myid> 109, and 56 is not in this range. When myid = is inserted, it will remain stuck until session 1 commit, rollback or direct lock wait timeout. Execute the same SQL statement in session 1 before the lock wait timeout. The result is still only records with id = 5, 98, which avoids phantom read problems.

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 2 (non-unique index + equivalent to 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 | testing | 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 | 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 still blocks records inserted with myid = 99, and the gap lock exists. Records inserted with myid = 97 are successfully inserted.

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 | testing | 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 | 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, and gap lock exists; records with id = 123 cannot be selected .. in share mode, because the record has a record lock; id = 125 can be selected .. in share mode and update, this is strange. It should be regarded as the current read, but later I checked the official documentation and learned that the gap lock will only block the insert operation, because there is no record in the gap, except the insert operation, the results of other operations should be equivalent to the null operation, 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 | 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 preconditions of gap lock: 1 transaction isolation level is REPEATABLE-READ, innodb_locks_unsafe_for_binlog parameter is 0, and the index taken by SQL is not unique index

2 transaction isolation level for REPEATABLE-READ, innodb_locks_unsafe_for_binlog parameter is 0, and SQL is a range of the current read operations, then even if not a unique index will add gap lock

Step of locking gap lock

It is easier to understand the preceding example 1 (non-unique index + range current read) and Example 3 (primary key index + range current read, so why does Example 2 (non-primary key index + equivalent current read) also generate gap lock? from the principle of the btree index, we all know that the btree index is arranged in order, in addition, innodb has a primary key clustered index, and my drawing capability is limited. Examples of the locking process analysis in example 2 are provided. The handwriting locking process is shown in figure

As shown in the Data Organization sequence of, there are two records with myid = 100. If the gap lock is applied, three gaps will be generated, namely, gap 1 (98,100), gap 2 (100,100 ), the value of myid in these three open intervals (if I remember correctly in high school mathematics) cannot be inserted. Obviously, there is still (myid = 99, id = 3) (myid

= 99, id = 4) and other records. There is no actual gap between the two. In addition, the record lock is added to the two records with myid = 100, that is, the two data services cannot be read by other sessions (Example 3 can be seen)

Next-Key Locks

By default, the mysql transaction isolation level is repeatable, And the innodb_locks_unsafe_for_binlog parameter is 0. 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 Record itself, but also locking the gap between indexes.

Next we will analyze how to lock most of the SQL types, assuming that the transaction isolation level isRepeatable read.

Select... from

No lock of any type

Select... from lock in share mode

Add shared next-key lock to any index records that are scanned, and add an exclusive lock to the primary key clustered index.

Select... from for update

Add the next-key lock to any index record scanned, and add the primary key clustered index and the exclusive lock.

Update... where delete from... where

Add the next-key lock to any index record scanned, and the primary key clustered index plus the exclusive lock.

Insert ..

A simple insert operation adds an exclusive lock to the index record corresponding to the insert row. This is a record lock and there is no gap, so it does not block other sessions from inserting records in the gap. However, a lock will be added before the insert operation. The official documents call it insertion intention gap lock, which is the gap lock of the intention. This intention gap lock indicates that when multiple transactions Insert the same gap concurrently, as long as the inserted record is not the same position in the gap, it can be completed without waiting for other sessions, in this way, the insert operation does not require a real gap lock. Imagine that if a table has an index idx_test with records 1 and 8 in it, every transaction can insert any records between 2 and 7, only record lock will be applied to the currently inserted records, and other sessions will not be blocked from inserting records different from their own, because they do not conflict with each other.

If a unique key conflict error occurs, a read lock will be applied to duplicate index records. When multiple sessions Insert the same row record at the same time, if the other session has obtained the row-changing exclusive lock, it will lead to a deadlock.

Example 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, session2 is stuck.

Mysql> begin;
Query OK, 0 rows affected (0.00 sec)
Mysql> insert into t1 VALUES (1 );

Session 3, session3 is also stuck.

Mysql> begin;

Query OK, 0 rows affected (0.00 sec)

Mysql> insert into t1 VALUES (1 );

Session 1. At this time, we will roll back session1.

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

Session 2 was successfully inserted, 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

Cause Analysis of deadlock:

First, session1 inserts a record and obtains the exclusive lock of the record. At this time, session2 and session3 both detect the primary key conflict error. However, session1 is not successfully inserted because session1 has not been submitted, therefore, it cannot directly report an error. Therefore, session2 and session3 both applied for a shared lock for the record. At this time, no shared lock is obtained and they are in the waiting queue. At this time, session1 rollback releases the exclusive lock for the row record, so session2 and session3 both obtain the shared lock on the row. Session2 and session3 must obtain the exclusive lock to insert records. However, because they all have a shared lock, they will never be able to obtain the exclusive lock, so a deadlock occurs. If session1 is commit rather than rollback, session2 and session3 both directly report the primary key conflict error. Check the deadlock log at a glance

Deadlock caused by insert 2

Another similar deadlock is that session1 does not commit the records whose id = 1 is deleted, and session2 and session3 Insert the records whose id = 1. In this case, session1 commit. If session2 and session3 need to be inserted, the exclusive lock will be obtained, and the deadlock will occur. If session1 rollback occurs, session2 and session3 report primary key conflicts. The demo is not provided here.

INSERT... ON DUPLICATE KEY UPDATE

The difference between this SQL statement and insert lock 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 policy is similar to that of insert. If a key conflict is detected, the replace operation directly applies for an exclusive lock.

Insert into t select... from s where...

The locking policy for table T is the same as that for normal insert, and the shared next-key lock will be added to the related records on table S. (If it is in Repeatable read mode, no lock will be applied)

Create table... SELECT... add the shared next-key lock to the select TABLE.

Auto-increment id locking Policy

When a field in a table is an auto-increment column, innodb adds an exclusive lock to the last position of the index. To access this auto-increment value, you need to add a table-Level Lock. However, the duration of this table-Level Lock is only the current SQL statement, not the entire transaction, that is, the current SQL statement is executed completely, the table-Level Lock is released. Other sessions cannot insert any records when the table-Level Lock is held.

Lock policy for foreign key Detection

If a foreign key constraint exists, any insert, update, and delete statements will detect the constraint conditions, and the shared record lock will be added to the corresponding record, regardless of whether there is a foreign key conflict.

The above details about innodb locks (record, gap, Next-Key lock) are all the content that I have shared with you. I hope to give you a reference and support for our customer service.