一次开发中并发删除插入死锁分析记录

MySQL 同时被 2 个专栏收录
2 篇文章 0 订阅
2 篇文章 0 订阅

这两天在开发一个项目的时候有一个操作是多线程同步数据,每个线程同步一个类型的数据,同步流程是先删除此类型下所有数据,然后批量插入新数据。但是测试过程中会发现有死锁现象,虽然可以直接对删除插入代码使用同步代码块的方式快速解决问题,但是本着学习的心态还是研究了一番死锁产生的根因,记录如下。

  1. 表结构如下(脱敏简化,主键ID实际是UUID)
CREATE TABLE `t2` (
  `id` char(1) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  1. 表数据如下
mysql> select * from t2;
+----+-------+
| id | type  |
+----+-------+
| e  | type2 |
| q  | type1 |
| r  | type2 |
| w  | type1 |
+----+-------+
4 rows in set (0.02 sec)
  1. 代码逻辑(多线程执行):
    1. 执行条件删除操作:DELETE * FROM t2 WHERE type = 'type1'
    2. 开启事务
    3. 执行批量插入type='type1’的操作(大批量)
    4. 提交事务
  2. 现象:批量插入执行时产生死锁。
  3. 场景复现:
事务1事务2
无操作BEGIN
BEGIN无操作
INSERT INTO t2 VALUES(‘s’,‘type2’) 正常插入无操作
无操作DELETE FROM t2 WHERE type=‘type1’ 阻塞
INSERT INTO t2 VALUES(‘a’,‘type2’) 死锁产生
  1. 查看死锁日志SHOW ENGINE INNODB STATUS
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-06-19 18:51:24 0x2b36fbefd700
*** (1) TRANSACTION:
TRANSACTION 3099, ACTIVE 6 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 47515155298048, query id 145 12.0.0.1 root updating
// 删除操作语句
DELETE FROM t2 WHERE type='type1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
// 删除操作给所有记录加上了X型Next-Key锁
RECORD LOCKS space id 37 page no 3 n bits 80 index PRIMARY of table `test`.`t2` trx id 3099 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 73; asc s;;
 1: len 6; hex 000000000c1a; asc       ;;
 2: len 7; hex b0000001240110; asc     $  ;;
 3: len 5; hex 7479706532; asc type2;;

*** (2) TRANSACTION:
TRANSACTION 3098, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 47515155027712, query id 149 12.0.0.1 root update
// 插入时会先根据主键a定位到B+树中的位置,如果该位置的下一条记录已经被加了gap锁(next-key锁也包含gap锁),那么当前事务会在该记录上加上一种类型为插入意向锁的锁,并且事务进入等待状态。
insert into t2 values('a','type2')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 37 page no 3 n bits 80 index PRIMARY of table `test`.`t2` trx id 3098 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 73; asc s;;
 1: len 6; hex 000000000c1a; asc       ;;
 2: len 7; hex b0000001240110; asc     $  ;;
 3: len 5; hex 7479706532; asc type2;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 3 n bits 80 index PRIMARY of table `test`.`t2` trx id 3098 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 1; hex 65; asc e;;
 1: len 6; hex 0000000009fa; asc       ;;
 2: len 7; hex bb000001310110; asc     1  ;;
 3: len 5; hex 7479706532; asc type2;;
// 事务2回滚
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 3111
Purge done for trx's n:o < 3110 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328989894251008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328989894250096, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3110, ACTIVE 560 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 6, OS thread handle 47515155298048, query id 191 12.0.0.1 root
---TRANSACTION 3105, ACTIVE 566 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 47515155027712, query id 187 12.0.0.1 root

  1. 分析
    • 在事务1第一次插入时会对插入成功的记录持有一个记录X锁;
    • 事务2因为删除语句的条件type没有索引,删除时就会按照主键索引从小到大的顺序依次给所有数据(包括最大记录)加上X型Next-Key锁,但是因为事务1持有了插入记录s的X锁,所以事务2在给记录s加锁时会创建锁结构并进入等待状态;
    • 然后事务1进行第二次插入,并且插入的主键为a,插入时判断插入位置是否有被其他事务加了Gap锁,也就是看插入位置的后一条记录e是否被加了Gap锁,由于在主键索引中从第一条记录到s之间(-∞,s]的所有记录都是被加了Next-Key锁(Next-Key锁也是Gap锁的一种)的,因此也要在a的下一条记录e上生成一个锁结构,状态为waiting并进入等待状态,等待事务1提交或者回滚,然而此时事务1也等待事务2释放锁,所以就造成了死锁。

如果第二次插入的主键是比目前表中的所有主键都要大的话,那么后一条记录就是supremum pseudo-record

如果第二次插入的主键是t(比s大),那么是可以正常插入而不会发生死锁,因为删除操作在s上面加锁后就开始等待了,s后面的记录都还没有加锁,所以不影响插入。

  1. 解决方案

知道了死锁是因为删除语句的条件字段没有索引导致全表记录被锁引起的,在type字段上加上索引即可解决(每个线程的type是不同的)。


更新:

type列加上索引只能解决死锁问题,事务1的第二次插入还是会阻塞等待事务2的删除提交。

原因分析:

有索引后数据库二级索引数据排列如下:

image-20210902232743764

  1. 事务1的第一次插入的数据应该在最后面
  2. 事务2的删除由于索引的原因锁的是(-∞,type2)这个区间,同时给e type2加上了Next Key锁,跟插入不冲突
  3. 事务1的第二次插入的数据为a type2应该在type1之后e之前,也就是w type1e type2之间,插入时检查后一条记录是否有GAP锁发现e type2被锁住了,所以阻塞等待。

因为有Next Key锁的存在,所以在主键不是自增的情况下并发的删除和插入操作必然会发生锁冲突,这是无法避免的,除非是自增主键,由于插入的数据永远都是在最后面,才可能避免和前面的锁冲突。


  1. 参考文档

    MySQL 是怎样运行的:从根儿上理解 MySQL

    收藏版MySQL语句加锁分析

    Mysql死锁日志阅读

  2. 附录

标记说明
LOCK_REC_NOT_GAPlocks rec but not gap记录锁
LOCK_GAPlocks gap before rec间隙锁
LOCK_ORDINARYNext Key锁
LOCK_INSERT_INTENTIONinsert intention插入意向锁,其实是特殊的GAP锁
组合说明
lock_mode X locks rec but not gap记录锁(LOCK_REC_NOT_GAP)
lock_mode X locks gap before rec间隙锁(LOCK_GAP)
lock_mode XNext-key 锁(LOCK_ORNIDARY)
lock_mode X locks gap before rec insert intention插入意向锁(LOCK_INSERT_INTENTION)
  • 0
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值