噢不，一条update.where无索引导致的MySQL死锁

约定不等于承诺〃 2023-09-26 16:03 146阅读 0赞

登↗登↗登↗，随着我被拉入一个新的群聊“生产环境死锁问题排查解决”，打破了午后的悠然惬意，点开群聊秒送了一个648超级大礼包（业务不正常，死锁异常日志输出），领导怒斥并要求赶紧排除解决并总结经验，刚好我略懂略懂一点MySQL锁知识，这不得秀一下自己的实力

![format_png][]

#### 死锁的日志 ####

既然死锁已经发生，也完全不要慌啊，按我说着做，一定能找到原因然后解决

触发下面这条命令获取到线索
    
    SHOW ENGINE INNODB STATUS;
    复制代码

执行后你会得到一段让人看了有点迷迷的死锁日志，具体我们该怎么分析死锁，可以分成一下三步

1.  查看发生死锁的事务（一）信息（包括持有的锁，等待的锁）
2.  查看发生死锁的事务（二）信息（包括持有的锁，等待的锁）
3.  查看回滚的是事务（一）还是事务（二），做好异常业务恢复的方案
4.  根据mysql的加锁机制分析发生的死锁的原因

由于死锁日志过于长，下面的日志只截取部分有用的记录

------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    2022-10-14 15:51:34 0x1a00
    
    发生死锁的事务（一）
    *** (1) TRANSACTION:
    TRANSACTION 32828384, ACTIVE 0 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 12 lock struct(s), heap size 1128, 285 row lock(s)
    MySQL thread id 26352, OS thread handle 8548, query id 987590925 WIN-6Q9NIAJLCDR 172.27.15.57 swgj updating
    事务（一）导致死锁的sql
    update xxx
         SET STATUS = '2',
            DESCRIPTION = 'xxx',
            MODIFY_TIME = '2022-10-14 15:51:35.707' 
         WHERE  BATCH_NO = 'xxx'
            and SFSB = '1'
            
    事务（一）持有的锁
    *** (1) HOLDS THE LOCK(S):
    RECORD LOCKS space id 8575 page no 82 n bits 104 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828384 lock_mode X
    事务（一）持有锁的数据记录信息 （supremum虚拟最大记录）
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    事务（一）持有锁的数据记录信息 （哪一行数据被锁了）
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
     0: len 30; hex 30663730333038302d313164662d346439642d626338662d393439333333; asc 0f703080-11df-4d9d-bc8f-949333; (total 36 bytes);
     1: len 6; hex 0000015129e1; asc    Q) ;;
     2: len 7; hex 010000402103cd; asc    @!  ;;
     3: len 6; hex 313635303036; asc 165006;;
     .....
     .....
    
    事务（一）等待的锁
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 8575 page no 37 n bits 112 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828384 lock_mode X waiting
    事务（一）等待锁的数据记录信息
    Record lock, heap no 45 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
     0: len 30; hex 32643736613832362d343763362d343861332d613038662d343539333061; asc 2d76a826-47c6-48a3-a08f-45930a; (total 36 bytes);
     1: len 6; hex 000001f4ebdd; asc       ;;
     2: len 7; hex 820000402b3c96; asc    @+< ;;
     3: SQL NULL;
     .....
     .....
     
     发生死锁的事务（二）
     *** (2) TRANSACTION:
    TRANSACTION 32828381, ACTIVE 5 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 125 lock struct(s), heap size 24696, 137 row lock(s), undo log entries 724
    MySQL thread id 26357, OS thread handle 12700, query id 987590958 WIN-6Q9NIAJLCDR 172.27.15.57 swgj update
    事务（二）导致死锁的sql
    insert into xxx
         ( ID, BATCH_NO, DFSJZJ,... ) 
         values ( 'xxx', 'xxx','xxx' )
         
    事务（二）持有的锁
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 8575 page no 37 n bits 112 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828381 lock_mode X locks rec but not gap
    
    事务（二）持有锁的数据记录信息
    Record lock, heap no 45 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
     0: len 30; hex 32643736613832362d343763362d343861332d613038662d343539333061; asc 2d76a826-47c6-48a3-a08f-45930a; (total 36 bytes);
     1: len 6; hex 000001f4ebdd; asc       ;;
     2: len 7; hex 820000402b3c96; asc    @+< ;;
     3: SQL NULL;
     ...
     ...
     
    事务（二）等待的锁
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 8575 page no 82 n bits 104 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828381 lock_mode X locks gap before rec insert intention waiting
    
    事务（二）等待锁的数据记录信息
    Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
     0: len 30; hex 30666435313133662d393462382d346636632d383362372d303463656536; asc 0fd5113f-94b8-4f6c-83b7-04cee6; (total 36 bytes);
     1: len 6; hex 000001dbb470; asc      p;;
     2: len 7; hex 020000013619a1; asc     6  ;;
     3: len 6; hex 313636333830; asc 166380;;
     ...
     ...
    
    复制代码

#### 分析日志 ####

从数据库版本5.7、事务的隔离级别 REPEATABLE READ

![format_png 1][]

官方文档明确指出在 **REPEATABLE READ** 隔离级别下，默认查询条件下是加 **next-key locks (record locks + gap locks )** 或 **gap locks**，当查询条件使用了唯一索引时，只会对当前查询的唯一记录进行加锁，此时锁为 record locks

> *官方文档强势占位*
> 
> [MySQL 隔离级别][MySQL]
> 
> [MySQL InnoDB 锁的类型][MySQL InnoDB]
> 
> [MySQL InnoDB 中不同 SQL 语句如何置锁][MySQL InnoDB _ SQL]

从死锁日志信息可以得出

1.  根据事务id大小可得出事务（二）**32828381** 比事务（一）**32828384** 先执行
2.  从日志中的 **index PRIMARY** 得出锁是加在主键索引上
3.  根据业务代码，事务（二）将执行 **N** 条同表插入 **insert** 语句，加上持有锁信息得出，事务（二）先插入了一条**新数据A**，并得到**新数据A**的行锁 **Record Locks**
4.  事务（一）执行 **update** 时直接阻塞，为什么呢，直接给出答案吧，因为这条 update 的查询条件是没有索引，导致需要所有的记录都要加 **Record Locks** 和 **Gap Locks**，接着由于事务（二）已经持有**新数据A**的行锁，导致无法上锁而阻塞等待
5.  事务（二）继续插入一条**新数据B**时获取 **insert intention locks** 阻塞等待，很显然，事务（一）抢先占有插入数据上下索引的 **Gap Locks**，死锁产生，MySQL提示错误，并回滚事务（二）让事务（一）提交

#### 复盘 ####

接下来我将用一个小例子来复现这次死锁现象

1.  建一个简单的表

CREATE TABLE `dead_lock` (
      `id` varchar(10) NOT NULL,
      `batch_no` varchar(10) DEFAULT NULL,
      `status` varchar(1) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    INSERT INTO dead_lock (id,batch_no,status) VALUES
    	 ('10','1','1'),
    	 ('20','20','1'),
    	 ('30','30',NULL),
    	 ('32','32',NULL);
    复制代码

1.  开始事务A进行插入数据，但先不提交

begin;
    
    insert into dead_lock values ('34', '34', null);
    
    复制代码

![format_png 2][]

1.  开始新的事务B进行更新数据，此时你会发现该事务被阻塞

begin;
    
    update dead_lock set status = '1' where batch_no = '20';
    
    复制代码

![format_png 3][]

1.  在事务A里插入一条特殊的数据，id为11的数据，当然不在事务B的持有锁的范围插入数据是不会造成死锁

insert into dead_lock values ('11', '11', null);
    
    复制代码

![format_png 4][]

#### 总结 ####

在 **MySQL** 事务隔离级别 **REPEATABLE READ** 的情况下，对于 **update，delete** 等操作语句，查询条件尽量使用索引，减少锁的范围，提高写的并发量，避免不必要的死锁发生影响业务正常运行

#### 最后 ####

这次死锁的说明就到这里，希望大家能看得懂并有所收获，不得不说有些知识我是略过了，大家可以自行查看官方文档补充了解！

[format_png]: https://img-blog.csdnimg.cn/img_convert/3b757f132d8b0f3f4f939989dba256c3.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/a2563ee1f19093153b4d6589b9589d84.webp?x-oss-process=image/format,png
[MySQL]: https://link.juejin.cn?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Finnodb-transaction-isolation-levels.html
[MySQL InnoDB]: https://link.juejin.cn?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Finnodb-locking.html
[MySQL InnoDB _ SQL]: https://link.juejin.cn?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Finnodb-locks-set.html
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/c59435d104c1b7f38b5f51f8e5a7cfe1.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/94f17fe3647da8d0919106db2ae3c5d6.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/807df01b701591dc4947941206ec6495.webp?x-oss-process=image/format,png