由不同的索引更新解决MySQL死锁套路

前几篇文章介绍了用源码的方式来调试锁相关的信息，这里同样用这个工具来解决一个线上实际的死锁案例，也是我们介绍的第一个两条 sql 就造成死锁的情况。因为线上的表结构比较复杂，做了一些简化以后如下

create table `t3` (
 `id` int(11) not null auto_increment,
 `a` varchar(5),
 `b` varchar(5),
 primary key (`id`),
 unique key `uk_a` (`a`),
 key `idx_b` (`b`) 
)
insert into `t3` (`id`, `a`, `b`) values 
 (1,'1','2');
# sql语句如下

# 事务1：t1
update t3 set b = '' where a = "1";

# 事务2：t2
update t3 set b = '' where b = "2";

两条语句造成死锁的情况用手动的方式比较难复现，我们先来分析一下加锁的过程

第一条语句（通过唯一索引去更新记录）

update t3 set b = '' where a = "1";

 

整理一下，加了3个x锁，顺序分别是

序号	索引	锁类型
1	uk_a	x
2	primary	x
3	idx_b	x

第二条语句

update t3 set b = '' where b = "2";

整理一下，加了 3 个 x 锁，顺序分别是

序号	索引	锁类型
1	idx_b	x
2	primary	x
3	idx_b	x

两条语句从加锁顺序看起来就已经有构成死锁的条件了

手动是比较难模拟的，写个代码并发的去同时执行那两条 sql 语句，马上就出现死锁了

------------------------
latest detected deadlock
------------------------
181102 12:45:05
*** (1) transaction:
transaction 50af, active 0 sec starting index read
mysql tables in use 1, locked 1
lock wait 3 lock struct(s), heap size 376, 2 row lock(s)
mysql thread id 34, os thread handle 0x70000d842000, query id 549 localhost 127.0.0.1 root searching rows for update
update t3 set b = '' where b = "2"
*** (1) waiting for this lock to be granted:
record locks space id 67 page no 3 n bits 72 index `primary` of table `d1`.`t3` trx id 50af lock_mode x locks rec but not gap waiting
record lock, heap no 2 physical record: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc p ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 4: len 0; hex ; asc ;;

*** (2) transaction:
transaction 50ae, active 0 sec updating or deleting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
mysql thread id 35, os thread handle 0x70000d885000, query id 548 localhost 127.0.0.1 root updating
update t3 set b = '' where a = "1"
*** (2) holds the lock(s):
record locks space id 67 page no 3 n bits 72 index `primary` of table `d1`.`t3` trx id 50ae lock_mode x locks rec but not gap
record lock, heap no 2 physical record: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc ;;
 1: len 6; hex 0000000050ae; asc p ;;
 2: len 7; hex 03000001341003; asc 4 ;;
 3: len 1; hex 31; asc 1;;
 4: len 0; hex ; asc ;;

*** (2) waiting for this lock to be granted:
record locks space id 67 page no 5 n bits 72 index `idx_b` of table `d1`.`t3` trx id 50ae lock_mode x locks rec but not gap waiting
record lock, heap no 2 physical record: n_fields 2; compact format; info bits 0
 0: len 1; hex 32; asc 2;;
 1: len 4; hex 80000001; asc ;;

*** we roll back transaction (1)

分析一下死锁日志

*** (1) waiting for this lock to be granted:
record locks space id 67 page no 3 n bits 72 index primary of table d1.t3 trx id 50af lock_mode x locks rec but not gap waiting

事务2：想获取主键索引的 x 锁

*** (2) holds the lock(s):
record locks space id 67 page no 3 n bits 72 index primary of table d1.t3 trx id 50ae lock_mode x locks rec but not gap

事务1：持有主键索引的 x 锁

*** (2) waiting for this lock to be granted:
record locks space id 67 page no 5 n bits 72 index idx_b of table d1.t3 trx id 50ae lock_mode x locks rec but not gap waiting

事务1：想获取普通索引 idx_b 的 x 锁

与我们分析的完全一致，也与线上的死锁日志一模一样

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。