ITPub博客

首页 > 数据库 > MySQL > 【MySQL】死锁案例之七

【MySQL】死锁案例之七

原创 MySQL 作者:杨奇龙 时间:2018-03-27 23:07:34 0 删除 编辑

一 前言

死锁,其实是一个很有意思也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。

二 案例分析

2.1 业务场景

业务开发同学想同步数据,他们的逻辑是通过update 更新操作,如果更新记录返回的affect_rows为0,然后就调用insert语句进行插入初始化。如果插入失败则再进行更新操作,多个会话并发操作的情况下就出现死锁。

2.2 环境说明

MySQL 5.6.24 事务隔离级别为RR

create table ty (
  id int not null primary key auto_increment ,
  c1 int not null default 0,
  c2 int not null default 0,
  c3 int not null default 0,
  unique key uc1(c1),
  unique key uc2(c2)
) engine=innodb ;

insert into ty(c1,c2,c3) 
values(1,3,4),(6,6,10),(9,9,14);

2.3 测试用例


sess1

sess2


begin;

begin;

T1

update ty set c3=2 where c2=4;


T2


update ty set c3=2 where c2=4;

T3

insert into ty (c1,c2,c3) values(3,4,2);


T4


insert into ty (c1,c2,c3) values(3,4,2);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2.4 死锁日志

2018-03-27 17:59:23 0x7f75bf39d700
*** (1) TRANSACTION:
TRANSACTION 1863, ACTIVE 76 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 382150, OS thread handle 56640, query id 28 localhost root update
insert into ty (c1,c2,c3) values(3,4,2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1863 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 1864, ACTIVE 65 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 382125, OS thread handle 40032, query id 62 localhost root update
insert into ty (c1,c2,c3) values(3,4,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 5 n bits 72 index uc2 of table `test`.`ty` trx id 1864 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 72 index uc1 of table `test`.`ty` trx id 1864 lock mode S waiting
*** WE ROLL BACK TRANSACTION (2)

2.5 分析死锁日志

首先我们要再次强调insert 插入操作的加锁逻辑。

第一阶段: 唯一性约束检查,先申请LOCK_S + LOCK_ORDINARY

第二阶段: 获取阶段一的锁并且insert成功之后,插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert唯一键冲突。

新数据插入完成之后:LOCK_X + LOCK_REC_NOT_GAP

对于insert操作来说,若发生唯一约束冲突,则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现,即使是RC事务隔离级别,也同样会存在Next-Key Lock锁,从而阻塞并发。然而,文档没有说明的是,对于检测到冲突的唯一索引,等待线程在获得S Lock之后,还需要对下一个记录进行加锁,在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断.

其次 我们需要了解锁的兼容性矩阵。

从兼容性矩阵我们可以得到如下结论:

INSERT操作之间不会有冲突。

GAP,Next-Key会阻止Insert。

GAP和Record,Next-Key不会冲突

Record和Record、Next-Key之间相互冲突。

已有的Insert锁不阻止任何准备加的锁。

已经持有的gap 锁会阻塞插入意向锁INSERT_INTENTION 

另外 对于通过唯一索引更新或者删除不存在的记录,会申请加上 gap锁。

了解上面的基础知识,我们开始对死锁日志进行分析:

T1: sess1 通过唯一键更新数据,由于c2=4 不存在,返回affect row为0,MySQL会申请(3,6)之间的gap锁。

T2: sess2 的情况和sess1 类似,也会申请(3,6)之间的gap锁,从上面的兼容性矩阵来看两个GAP 锁并不会冲突。

T3: sess1 根据update语句返回affect row为0,执行insert操作,此时需要申请插入意向锁,sess2会话持有的gap锁和sess1 申请的插入意向锁冲突,出现等待。

index uc2 of table test.ty trx id 1863 lock_mode X locks gap before rec insert intention waiting

T4:sess2与sess1类似,根据update语句返回affect row为0,执行insert操作。 申请的插入意向锁与sess1 的update语句持有的GAP锁冲突。sess1(持有gap锁),sess2(持有gap锁),sess1(插入意向锁等待sess2的gap锁释放) sess2(插入意向锁等待sess1的gap锁释放) 构成循环等待,进而导致死锁。

2.6 解决方法

从业务场景的处理逻辑上看,业务需要发送两次请求 一次update,一次insert 才能完成业务逻辑,不够友好和优化。

其实我们可以和开发同学沟通好,确认业务的幂等性,使用 insert on duplicate key的方式,没有就插入,存在就更新,一次调用即可完成之前2次操作的功能,提高性能。

三 小结

最后想说关于解决死锁问题的思路: 

1 具备扎实的锁相关的基础知识。

2 单单根据死锁日志其实比较难以判断具体的sql执行情况,需要和开发同学沟通好,理清业务执行sql的逻辑,然后去模拟测试。


推荐阅读



如何阅读死锁日志

漫谈死锁

死锁案例之一

死锁案例之二

死锁案例之三

死锁案例之四

死锁案例之五

死锁案例之六

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-2152275/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7473121