死锁案例六

来源：公众号yangyidba一、前言死锁，其实是一个很有意思也很有挑战的技术问题，大概每个 DBA 和部分开发同学都会在工作过程中遇见 。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二、案例分析2.1 环境说明MySQL 5.6.24 事务隔离级别为 RR
create table tx (
id int not null primary key auto_increment ,
c1 int not null default 0,
c2 int not null default 0,
key idx_c1(c1)
) engine=innodb ;
insert into tx values(24,3,4),(25,3,4),
(26,3,4),(30,5,8);
2.2 测试用例
2.3 死锁日志———————————-
LATEST DETECTED DEADLOCK
————————
2018-03-27 15:40:40 0x7f75cafce700
*** (1) TRANSACTION:
TRANSACTION 1850, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 379040, OS thread handle 140143994337024, query id 1521958 localhost root updating
update tx set c2=8 where c1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 1850 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1849, ACTIVE 32 sec updating or deleting, thread declared inside InnoDB 4999
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 379016, OS thread handle 140143893473024, query id 1521976 localhost root updating
delete from tx where id=30
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 3 n bits 72 index PRIMARY of table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 5 n bits 72 index idx_c1 of table `test`.`tx` trx id 1849 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
2.4 分析死锁日志首先要理解的是 对同一个字段申请加锁是需要排队的。
其次表 ty 中索引 idx_c1 为非唯一普通索引，我们根据事务执行的时间顺序来解释，这样比较好理解。
T1: sess2 执行select for update 操作持有记录id=30的主键行锁:PRIMARY of table test.tx lock_mode X locks rec but not gap
T2: sess1 语句 update 通过普通索引idx_c1更新c2,先获取idx_c1 c1=5的X锁lock_mode X locks rec but not gap,然后去申请对应主键id=30的行锁，但是sess2 已经持有主键的行锁，于是 sess1 等待。
T3: sess2 执行根据主键 id=30 删除记录，需要申请 id=30 的行锁以及 c1=5 的索引行锁。但是 sess1 以及持有该锁，故会出现 index idx_c1 of table test.tx trx id 1849 lock_mode X locks rec but not gap waiting
sess2(delete) 等待 sess1(update),sess1(update) 等待 sess2(select for update) 循环等待，造成死锁。
对于 RDBMS 系统出现死锁的根本原因都可以概括为:不同的事务加锁的顺序不一样导致循环等待，进而导致死锁。
2.5 解决方法修改 sess1 的 update 为根据主键来更新 也即 update tx set c2=x where id=30，把加锁方式改为顺序加锁,申请主键 id 的锁，避免通过交叉加锁，相互申请对方持有的锁。
三、小结上面的案例中出现死锁是由于不同会话对普通索引 idx_c1 和主键相互竞争导致循环等待而出现死锁的。生产过程中遇到高并发更新同一行的的时候可以考虑避免通过不同的索引进行更新，进而避免死锁。
扩展阅读
死锁案例五
死锁案例之四
死锁案例之三
死锁案例之二
死锁案例之一
漫谈死锁
如何阅读死锁日志
全文完。Enjoy MySQL:)叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧