关于死锁:故障分析-MySQL死锁案例分析

作者：杨奇龙

网名“北在北方”，资深 DBA，次要负责数据库架构设计和运维平台开发工作，善于数据库性能调优、故障诊断。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

一 背景

死锁，其实是一个很有意思也很有挑战的技术问题，大略每个 DBA 和局部开发同学都会在工作过程中遇见。

本次分享的一个死锁案例是 波及通过辅助索引的更新以及通过主键删除导致的死锁。心愿可能对想理解死锁的敌人有所帮忙。

二 案例剖析

2.1 业务逻辑

select for update 表记录并加上 x 锁，查问数据，做业务逻辑解决，而后删除该记录。还有其余业务逻辑要更新记录，导致死锁。

2.2 环境阐明

数据库 MySQL 8.0.30

事务隔离级别 REPEATABLE-READ

create table dl(
id int auto_increment primary key,
c1 int not null ,
c2 int not null,
key idx_c1(c1));

insert into dl(c1,c2) values (3,1),(3,2),(3,2),(3,3),(4,4),(5,5);

2.3 测试用例

2.4 死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-12-03 16:43:59 140261132850944
*** (1) TRANSACTION:
TRANSACTION 1416764, ACTIVE 15 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 15, OS thread handle 140261086668544, query id 283 localhost msandbox updating
update dl set c2=10 where c1=5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416764 lock_mode X
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416764 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32

*** (2) TRANSACTION:
TRANSACTION 1416759, ACTIVE 23 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140261085611776, query id 286 localhost msandbox updating
delete from dl where id=6

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 5 n bits 80 index idx_c1 of table `test`.`dl` trx id 1416759 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (2)

2.5 死锁剖析

1. sess1 开启一个事务，在 T2 时刻执行 select for update，持有 id= 6 的 lock_mode X record lock.
2. sess2 在 T3 时刻执行依据 c1= 5 的更新，然而其加锁程序是先在 索引 idx_c1上加锁，顺利加锁，而后到申请加 主键上加 id=6的锁，发现 sess1 曾经持有主键 id=6 的 X 的锁，因而须要期待。如日志中 (1) TRANSACTION: 中 WAITING FOR 的提醒
   RECORD LOCKS space id 49 page no 4 n bits 80 index PRIMARY of table test.dl trx id 1416764 lock_mode X locks rec but not gap waiting
3. sess1 执行 delete id=6 的操作，因为事务自身曾经持有了主键上的锁，删除记录同时要对索引 idx_c1 上的记录加上 lock_mode X record lock，发现该锁曾经被 sess2 持有，造成了死锁条件，sess1 报错，产生回滚。

2.6 如何解决

本文中死锁的起因是因为 sess2 通过辅助索引进行更新，因而举荐的防止死锁计划是把 sess2 应用辅助索引的更新改成基于主键进行更新，从而防止申请 idx_c1 上 的加锁造成循环期待产生死锁。

三 小结

敲黑板，重点: 死锁是因为不同事务对表记录加锁的程序不统一导致互相期待对方持有的锁导致的。大家在剖析死锁的时候能基于该准则去剖析理清业务的 sql 逻辑，基本上都能解决大部分的问题场景。

另外文章的最初咱们再次温习一下 MySQL 的加几个根本准则，不便大家前面遇到死锁案例进行剖析:

准则 1：加锁的根本单位是 next-key lock。准则 2：查找过程中拜访到的对象才会加锁。优化 1：索引上的等值查问，给惟一索引加锁的时候，next-key lock 进化为行锁。优化 2：索引上的等值查问，向右遍历时且最初一个值不满足等值条件的时候，next-key lock 进化为间隙锁。一个 bug：惟一索引上的范畴查问会拜访到不满足条件的第一个值为止。

在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行实现后，就要把“不满足条件的行”上的行锁间接开释了，不须要等到事务提交