记MYSQL一次死锁排查

在2022.05.09 晚上的放款日报中发现，有2笔报 Deadlock 的异常，这种异常不属于业务异常，故需要人为介入排查。

Error 1213: Deadlock found when trying to get lock; try restarting transaction

根据日志排查怀疑是同一个表不同的索引之间产生了死锁

所以模拟线上环境复现死锁场景

线下模拟复现

线下模拟Live环境复现死锁场景

创建一个测试表:

CREATE TABLE `user_infos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `profile_picture_url` varchar(255) DEFAULT NULL,
  `nickname` varchar(255) DEFAULT NULL,
  `version` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_username` (`username`),
  UNIQUE KEY `index_nickname` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=4 ;

表中有username、nickname 2个字段的索引

复现步骤：

步骤	事务1	事务2	截图	描述
1	begin;	begin;		开启2个事务
2	select * from user_infos where nickname="zhangsan" for update;			事务1使用nickname 索引加锁成功
3		select * from user_infos where username="zhangsan" for update;		事务2通过username索引查询等待
4	UPDATE `user_infos` SET `username` = 'zhangsan', `password` = 'pwd-8186967', `profile_picture_url` = 'zhangsanggg.jpg', `nickname` = 'zhangsan', `version` = 2341 WHERE  username = 'zhangsan' ;			事务1 执行更新语句中where 条件中使用了username 索引 此时发生了死锁

 死锁日志：

2022-05-11 15:24:06 0x70000a1eb000
*** (1) TRANSACTION:
TRANSACTION 288657, ACTIVE 0 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 777, OS thread handle 123145456775168, query id 59568 localhost ::1 root statistics
SELECT `id`, `username`, `password`, `profile_picture_url`, `nickname`, `version` FROM `user_infos` WHERE (username='zhangsan') LIMIT 1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 243 page no 3 n bits 184 index PRIMARY of table `entry_task`.`user_infos` trx id 288657 lock_mode X locks rec but not gap waiting
Record lock, heap no 115 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000046788; asc     g ;;
 2: len 7; hex 40000001662cb8; asc @   f, ;;
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;
 4: len 11; hex 7077642d38313836393637; asc pwd-8186967;;
 5: len 19; hex 7a68616e6773616e383933383234302e6a7067; asc zhangsan8938240.jpg;;
 6: len 8; hex 7a68616e6773616e; asc zhangsan;;
 7: len 4; hex 00000924; asc    $;;

*** (2) TRANSACTION:
TRANSACTION 288656, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 775, OS thread handle 123145472094208, query id 59569 localhost ::1 root updating
UPDATE `user_infos` SET `username` = 'zhangsan', `password` = 'pwd-8186967', `profile_picture_url` = 'zhangsan3184430.jpg', `nickname` = 'zhangsan', `version` = 2341 WHERE (username='zhangsan' AND version=2340)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 243 page no 3 n bits 184 index PRIMARY of table `entry_task`.`user_infos` trx id 288656 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

原因分析

第1步：开启事务

第2步：事务1 拿到了 index_nickname AND index primary 锁

第3步：事务2 拿到了 index_username  waiting for index primary 锁

第4步：事务1 拿到了 更新语句中需要获取 index_username 锁 （ hold index primary  but waiting for index_username） 故与事务2 发生了死锁

解决方案

1、更新获取查询尽量使用主键加锁，不使用其非聚簇索引，可以避免单表死锁

例如：

序号	现有语句	修改后
1	select * from user_infos where nickname="zhangsan" for update;	select * from user_infos where nickname="zhangsan";  select * from user_infos where id="1" for update;
2	UPDATE `user_infos` SET `username` = 'zhangsan', `nickname` = 'zhangsan' WHERE  username = 'zhangsan' ;	UPDATE `user_infos` SET `username` = 'zhangsan', `nickname` = 'zhangsan' WHERE  id = '1' ;

  

2 、使用乐观锁，并发失败后立即重试，直到成功位置（或设置最大重试次数，仍然失败就报错告警）