Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.