innodb next-key lock引發(fā)的死鎖現(xiàn)象解析

2024-09-07 22:12:45

字體：大中小

供稿：網(wǎng)友

這個(gè)例子是我在網(wǎng)上看到的，我分析了很久才弄明白鎖產(chǎn)生的具體過(guò)程。

數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別是RR。

建測(cè)試表：
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;

測(cè)試步驟：
事務(wù)1 事務(wù)2
begin

測(cè)試結(jié)果：
事務(wù)1 執(zhí)行到insert語(yǔ)句會(huì)block住，事務(wù)2執(zhí)行insert語(yǔ)句會(huì)提示死鎖錯(cuò)誤。

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

如果你將數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別修改為RC，上述事務(wù)會(huì)各自成功運(yùn)行，不會(huì)互相影響。