注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

两个INSERT发生死锁原因剖析

MySQL FAQ 彭东稳 514次浏览 已收录 0个评论

开始之前,关于锁、死锁,我们要先统一下几点认知:

  • 死锁是由于多个事务相互持有其他事务所需要的锁,结果导致事务都无法继续,进而触发死锁检测,其中某个事务会被回滚,释放相应的锁,其他事务得以正常继续;简言之,就是多个事务之间的锁等待产生了回路,死循环了
  • 死锁发生时,会立刻被检测到,并且回滚其中某个事务,而不会长时间阻塞、等待;
  • 从MySQL 5.7.15开始,新增选项 innodb_deadlock_detect,没记错的话应该是阿里团队率先实现的。当它设置为 OFF 时(默认值是 ON),InnoDB会不检测死锁,在高并发场景(例如“秒杀”)业务中特别有用,可以有效提高事务并发性能
  • 在启用死锁检测时,InnoDB默认的最大检测深度为200,在上面提到的高并发高竞争场景下,在热点数据上的锁等待队列可能很长,死锁检测代价很大。或者当等待队列中所有的行锁总数超过 100万 时,也会被认为认为发生死锁了,直接触发死锁检测处理机制;
  • InnoDB行锁等待超时默认为50秒,一般建议设置5-10秒就够了;
  • 有时候,可能会口误把 长时间的行锁等待 说成是 死锁,其实二者完全不一样,不要犯这种常识性口误。

好了,正式开始今天的案例。

先看测试表:

然后我们执行下面的测试:

session1 session2 session3
begin;
delete from ld where id=1;
begin;
insert into ld select 1,’dkey';
begin;
insert into ld select 1,’mark';
commit;
Query OK, 1 row affected (11.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这时候我们看下InnoDB STATUS的输出:

从上面这个输出来看,我们看到的现场是两个 insert 请求发生了死锁。单纯看这2个SQL的话,应该是产生锁等待才对,而不是死锁。

按照我们常规理解,session1未commit前,应该是持有id=1上的record lock(X),而session2和session3则都在等待这个锁的释放。而实际上呢,肯定不是这样的,否则也不至于发生死锁了。

这次的案例其实在MySQL官方文档上已经解释过了,而且也给了演示案例(如本例)。文档中是这么说的:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

核心内容是:当需要进行唯一性冲突检测时,需要先加一个S锁。

这样的话,上面案例的加锁过程就不是之前推测的那样,而是像下面这样了:

session1 session2 session3
begin;
delete from ld where id=1;持有id=1的record lock(X)
begin;
insert into ld select 1,’dkey';需要判断唯一性,检测到冲突,请求id=1的next-key lock(S)被阻塞,等待ing
begin;
insert into ld select 1,’mark';需要判断唯一性,检测到冲突,请求id=1的next-key lock(S)被阻塞,等待ing
commit;提交,释放id=1上的锁
成功获取id=1的next-key lock(S);请求id=1的record lock(X)锁;但session3上已持有id=1的next-key(S),被阻塞、等待中;后面session3检测到死锁冲突后,session2才insert成功;Query OK, 1 row affected (11.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
成功获取id=1的next-key lock(S);请求id=1的record lock(X)锁;触发死锁检测,失败、回滚;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

下面是另一个类似的案例:

session1 session2
begin;
select * from d where id = 1 lock in share mode;持有id=1上的record lock(S)
begin;
select * from d where id = 1 lock in share mode;持有id=1上的record lock(S)
delete from d where id = 1;请求id=1上的record lock(X),被session2阻塞了,等待中
delete from d where id = 1;请求id=1上的record lock(X),检测到死锁,失败,回滚

通过上面这两个案例,其实想要告诉大家的是:发生死锁时,不能只看现场,还得分析过程,才能知道真正的原因,死锁发生的原因也并不复杂,但是得能想办法还原过程。

下面提供一个更加诡异的死锁案例,这个死锁案例出现了S GAP锁,可能从来没有见过。

死锁模拟:

session1 session2 session3
begin;
insert into testunj1 values(17,17,’gaopeng’);
insert into testunj1 values(15,15,’gaopeng’);
begin;
insert into testunj1 values(14,15,’gaopeng’);堵塞
begin;
insert into testunj1 values(16,17,’gaopeng’);堵塞
rollback;
Query OK, 1 row affected (11.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这时候我们看下InnoDB STATUS的输出:

可以看到lock mode S locks gap,完结。。。

来自:老叶茶馆公众号


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)or分享 (0)
关于作者:

您必须 登录 才能发表评论!