Analysis of deadlock caused by innodb next-key lock

I saw this example on the Internet, and it took me a long time to figure out the specific process of the lock.

The database's transaction isolation level is RR.

Build a test table:

CREATE TABLE `LockTest` (

`order_ id` varchar (20) NOT NULL

`id` bigint (20) NOT NULL AUTO_INCREMENT

PRIMARY KEY (`id`)

KEY `idx_order_ id` (`order_ id`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

Test steps:

Transaction 1 transaction 2

Begin

Delete from LockTest where order_id = 'D20'

Begin

Delete from LockTest where order_id = 'D19'

Insert into LockTest (order_id) values ('D20')

Insert into LockTest (order_id) values ('D19')

Commit

Commit

Test results:

Transaction 1 executes to the insert statement block, and transaction 2 executes the insert statement to prompt a deadlock error.

Cause analysis:

1. First look at the table-building statement of the test table. Id is the primary key index, and the primary key is the self-increasing primary key. Order_id is a normal index.

2. Transaction 1 executes delete from LockTest where order_id = 'D20 statements; when the isolation level of the database is RR, transaction 1 acquires a next-key lock on the primary key id, with a lock range of [16, + ∞).

This 16 comes from AUTO_INCREMENT=16 because LockTest is currently an empty table.

3. Similarly, transaction 2 executes delete from LockTest where order_id = 'D19 ∞; when the statement is executed, because the isolation level of the database is RR, transaction 2 also gets a next-key lock on the primary key id, and the scope of this lock is [16, + lock).

That is to say, transaction 1 and transaction 2 acquire the same lock at this time.

4. Transaction 1 continues to execute the insert into LockTest (order_id) values ('D20') statement, which attempts to insert a row of id=16,order_id=D20 data into the LockTest table

However, because in the delete statement of transaction 2, there is already a lock with a range of [16, + ∞) on the primary key id, transaction 1 can not insert data at this time and is blocked.

5. Continue transaction 2's insert statement insert into LockTest (order_id) values ('D19'); the insert statement also wants to insert a row of id=16,order_id=D19 data to the LockTest table

However, because there is already a lock with a range of [16, + ∞) on the primary key id in the delete statement of transaction 1, transaction 2 can not insert data at this time and is blocked.

At this point, it can be found that the locks of transaction 1 and transaction 2 hold each other and wait for each other. So innodb determines that the transaction has encountered a deadlock and rolls back transaction 2 directly. Then go back to transaction 1 insert into LockTest (order_id) values ('D20'); which was executed successfully.

If you change the transaction isolation level of the database to RC, the above transactions will run successfully and will not affect each other.