Mysql 死锁案例7-并发更新索引列导致的死锁

最新推荐文章于 2024-05-12 04:17:31 发布

码上得天下

最新推荐文章于 2024-05-12 04:17:31 发布

阅读量93

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

原文链接：https://help.aliyun.com/practice_detail/606888?spm=a2c4g.26937911.0.0.389b12ba1jOfO2

版权

Mysql 专栏收录该内容

107 篇文章 12 订阅

订阅专栏

死锁复现

mysql 5.7.12 ,事务隔离级别RR

create table  x
(id int not null auto_increment,
 c int not null default  0,
 d int not null default 0,
 primary key(id),
 key idxcd(c,d));
insert into x(c,d) values(1,0),(3,0),(5,0),(7,0),(10,0),(12,0),(14,0),(16,0);

	事务1	事务2
T1		BEGIN; UPDATE X SET d=1 WHERE c=6
T2	BEGIN; UPDATE X SET d=1 WHERE c IN (5,10)(阻塞)
T3		UPDATE X SET d=1 WHERE c=7 (死锁)

SHOW ENGINE INNODB STATUS 死锁日志：


LATEST DETECTED DEADLOCK
------------------------
2024-03-14 10:41:36 0x1d94
*** (1) TRANSACTION:
TRANSACTION 489120, ACTIVE 5 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 1528, query id 2630 localhost ::1 root updating
UPDATE X SET d=1 WHERE c IN (5,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1089 page no 4 n bits 80 index idxcd of table `test`.`x` trx id 489120 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000004; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 489119, ACTIVE 8 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 7572, query id 2635 localhost ::1 root updating
update x set d=1 where c=7
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1089 page no 4 n bits 80 index idxcd of table `test`.`x` trx id 489119 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000005; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000004; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1089 page no 4 n bits 80 index idxcd of table `test`.`x` trx id 489119 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS

死锁分析

两个前提知识点

RR事务隔离级别下,对于通过索引(唯一或者非唯一)更新或者删除不存在的记录，会申请加上gap锁。
当update 更新索引字段时，相当于删除之后重新插入新的记录，需要重新组织索引节点。

死锁过程：

T1时刻事务2 更新c=6的值，但是c=6 不存在，申请加上(5,0)---(7,0)的X gap锁，日志提示:持有idxcd of table `test`.`x` trx id 489119 lock_mode X locks gap before rec。
T2时刻事务1 更新2行记录c=5,c=10,并且修改d=1,事务1 会申请(5,0),(10,0) 记录之间的Next-key锁,由于事务1 需要插入新的记录(5,1)事务2 持有的X GAP 锁范围内,根据锁的兼容性矩阵，GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION，故日志提示:index idxcd of table `test`.`x` trx id 489120 lock_mode X locks gap before rec insert intention waiting。
T3时刻事务2 更新c=7对应d=1,同样相当于insert (7,1) 在(5,0),(10,0)之间。根据兼容矩阵GAP,Next-Key会阻塞插入意向锁INSERT_INTENTION 故日志提示: index idxcd of table `test`.`x` trx id 489119 lock_mode X locks gap before rec insert intention waiting 。
至此,事务2 持有gap锁阻塞事务1 插入(5,1)，事务1持有Next-key 阻塞事务2 插入(7,1),循环等待出现死锁。

解决方法

其实解决方法比较简单把组合索引 idxcd(c,d) 中的d去掉，改为idxc(c),避免GAP/Next-key 阻塞插入意向锁INSERT_INTENTION 即可，开发同学18:10分左右修改索引之后，业务不再出现死锁。（注意如果单列索引，更新的字段还是索引字段还是会死锁，这跟是否是单列索引和联合索引名没关系，只能更新字段是否存在索引有关）