delete & insert 死锁
一. 死锁信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-01 15:18:41 7ef63524f700
*** (1) TRANSACTION:
TRANSACTION 83329139031, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 9863052, OS thread handle 0x7ef640864700, query id 19582985062 172.16.158.198 merchant1 update
INSERT INTO mht_user_fund_quty_XXXX
(id, gmt_create) values
(19751548, now()), (18972066, now()), (19559993, now()), (20743625, now()), (19796843, now()), (19033019, now()), (19636160, now())
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2538 page no 4 n bits 568 index `PRIMARY` of table `merchant`.`mht_user_fund_quty_XXXX` trx id 83329139031 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 325 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 8; hex 8000000001217db1; asc !} ;;
1: len 6; hex 001366cc94d0; asc f ;;
2: len 7; hex 35000008d515af; asc 5 ;;
3: len 5; hex 99a742f4a3; asc B ;;
*** (2) TRANSACTION:
TRANSACTION 83329139028, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 343 row lock(s), undo log entries 13
MySQL thread id 9862919, OS thread handle 0x7ef63524f700, query id 19582985053 172.16.158.178 merchant1 updating
DELETE FROM mht_user_fund_quty_XXXX
where id in
(
19323879
,
19528620
,
19892711
,
20532275
,
19797795
,
19363049
.....
*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2538 page no 4 n bits 568 index `PRIMARY` of table `merchant`.`mht_user_fund_quty_reduce_lock` trx id 83329139028 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2538 page no 4 n bits 568 index `PRIMARY` of table `merchant`.`mht_user_fund_quty_XXXX` trx id 83329139028 lock_mode X waiting
Record lock, heap no 438 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000012d627c; asc -b|;;
1: len 6; hex 001366cdc157; asc f W;;
2: len 7; hex f8002000230084; asc # ;;
3: len 5; hex 99a742f4a9; asc B ;;
二. 死锁分析
和开发确认了下,两个事物分别都只有一条语句,就是说 delete 和 insert 都是独立事务。
通常情况,一个事物里既有 delete 又有 insert ,并发时很容易死锁。但是像这种单语句的死锁非常少见,而且从加锁的逻辑来说,是不可能出现死锁的。
先看下两条语句在统计信息中持有和等待锁的情况:
T1:
INSERT INTO mht_user_fund_quty_XXXX (id, gmt_create) values (19751548, now()).....
等待锁:primary 上 lock_mode X locks gap before rec insert intention
T2:
DELETE FROM mht_user_fund_quty_XXXX where id in (19323879,......)
等待锁:primary 上 lock_mode X
持有锁:primary 上 lock_mode X
能得到的有用信息只有以上这些,想分析出死锁的成因,还需要弄明白以下几个点:
T1 事务的插入意向锁被什么阻塞了?
T2 事务为什么会有阻塞 T1 事务?
T1 事务死锁时持有什么锁?
三. 加锁分析
1. 插入意向锁被什么阻塞了?
1.1 插入意向锁简介
Insert Intention Locks 插入意向锁是一种特殊的间隙锁,这个锁表示插入的意向,只有在 INSERT 的时候才会有这个锁。插入意向锁和插入意向锁之间互不冲突,所以可以在同一个间隙中有多个事务同时插入不同索引的记录。比如有索引键值分别是 3 和 7 ,不同的事务分别插入 4 和 5,每个事务都会产生一个加在 3-7 之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。
1.2 插入意向锁作用
Q:
再来看下 mysql 是如何解决幻读的?
A:
是用过间隙锁。
Q:
那间隙锁是如何防止幻读的?
A:
执行 INSERT 语句时需要加插入意向锁,而插入意向锁和间隙锁冲突,从而阻止了插入操作的执行。
1.3 行锁兼容矩阵:
第一行表示已有的锁,第一列表示要加的锁。
这里只对插入意向锁做个总结:
插入意向锁不影响其他事务加其他任何锁。也就是说,一个事务已经获取了插入意向锁,对其他事务是没有任何影响的;
插入意向锁与间隙锁和 Next-key 锁冲突。也就是说,一个事务想要获取插入意向锁,如果有其他事务已经加了间隙锁或 Next-key 锁,则会阻塞。
所以插入意向锁只会和间隙锁或 Next-key 锁冲突。
1.4 总结
回到上面死锁,insert 事务等待插入意向锁,也就能说明,delete 事务持有相对应间隙锁或者 Next-key 锁阻塞了 insert 事务的插入意向锁。
2. T2 事务为什么会有阻塞 T1 事务?
T2 事务 delete 的执行条件是主键id,先来看下等值查询针对不同索引类型的加锁情况把。
2.1 不同索引下不同的加锁方式
网上找了些图,方便理解:
聚簇索引,查询命中,UPDATE students SET score = 100 WHERE id = 15:
RC 和 RR 隔离级别下加锁情况一样,都是对这个聚簇索引加 X 锁
聚簇索引,查询未命中,UPDATE students SET score = 100 WHERE id = 16:
RC 隔离级别,不加锁 RR 隔离级别,在该记录区间加 GAP 锁
以上两个执行条件是主键,和本文死锁 delete 语句执行条件一样,后面的列出来的仅作为补充。
二级唯一索引,查询命中,UPDATE students SET score = 100 WHERE no = 'S0003':
RC 和 RR 隔离级别下加锁情况一样,二级索引加 X 锁,且对应的主键索引也会加上 X 锁