innodb next-key lock引發的死鎖現象解析

2024-09-07 22:12:45

字體：大中小

來源：轉載

供稿：網友

這個例子是我在網上看到的，我分析了很久才弄明白鎖產生的具體過程。

數據庫的事務隔離級別是RR。

建測試表：
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;

測試步驟：
事務1 事務2
begin

測試結果：
事務1 執行到insert語句會block住，事務2執行insert語句會提示死鎖錯誤。

原因分析：
1、首先看測試表的建表語句，id是主鍵索引，同時該主鍵是自增主鍵。order_id是普通索引。
2、事務1執行delete from LockTest where order_id = 'D20';語句時，由于數據庫的隔離級別是RR，因此此時事務1在主鍵id上獲得了一個next-key lock，這個鎖的范圍是[16, +∞)。
   這個16就來自于AUTO_INCREMENT=16，因為LockTest目前是張空表。
3、同理，事務2執行delete from LockTest where order_id = 'D19';語句時，由于數據庫的隔離級別是RR，事務2在主鍵id上也獲得了一個next-key lock，這個鎖的范圍是[16, +∞)。
   也就是說此時，事務1和事務2獲得的鎖是一樣的。
4、事務1繼續執行insert into LockTest (order_id) values ('D20');語句，這個時候由于該語句企圖往LockTest表insert一行id=16，order_id=D20的數據，
   但是由于在事務2的delete語句中，主鍵id上已經有了一個范圍為[16, +∞)的鎖，導致事務1此時想插入數據插不進去，被阻塞了。
5、繼續事務2的插入語句insert into LockTest (order_id) values ('D19'); 該插入語句同樣也想往LockTest表insert一行id=16，order_id=D19的數據，
   但是由于由于在事務1的delete語句中，主鍵id上已經有了一個范圍為[16, +∞)的鎖，導致事務2此時想插入數據插不進去，被阻塞了。
   此時，可以發現，事務1和事務2的鎖是互相持有，互相等待的。所以innodb判斷該事務遇到了死鎖，直接將事務2進行了回滾。然后回頭去看事務1，insert into LockTest (order_id) values ('D20')；被成功執行。

如果你將數據庫的事務隔離級別修改為RC，上述事務會各自成功運行，不會互相影響。