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

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

2020-09-28 15:49挖坑的张师傅 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";

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

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

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

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

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

序号	索引	锁类型
1	uk_a	X
2	PRIMARY	X
3	idx_b	X

第二条语句

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

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

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

序号	索引	锁类型
1	idx_b	X
2	PRIMARY	X
3	idx_b	X

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

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

手动是比较难模拟的，写个代码并发的去同时执行那两条 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 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`

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

1 2	`*** (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 锁

1 2	`*** (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`