并发delete+insert duplicate-key冲突导致死锁

最新推荐文章于 2024-04-03 09:50:59 发布

转载最新推荐文章于 2024-04-03 09:50:59 发布 · 1.7k 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/hebaodan/blog/3033276

文章标签：

#数据库 #java #python

本文通过一个具体的MySQL死锁案例，详细分析了死锁产生的原因及解决方法。案例发生在REPEATABLE-READ隔离级别下，涉及并发事务中的DELETE和INSERT操作。文章还探讨了不同事务隔离级别对死锁的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

死锁案例

简化后的死锁场景如下

环境： mysql5.7,事务隔离级别REPEATABLE-READ

表结构

 CREATE TABLE `t4` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB
数据：
mysql> select * from t4;
+---+
| a |
+---+
| 1 |
+---+

并发事务

T1	T2
begin；	begin
delete from t4 where a = 1;//ok, 0 rows affected
	delete from t4 where a = 1; //wating,被阻塞
insert into t4 values(1);//Query OK, 1 row affected (0.01 sec)
	ERROR 1213 (40001): Deadlock found when trying to get lock;

从上面可以看出，并发事务执行delete，T2等待，T1再执行insert相同value的数据时出现死锁。

死锁分析

死锁原因

insert时如果数据出现duplicate key，会加LOCK_S锁，类型为NEXT-KEY LOCK，此时事务2已经在申请record lock X锁，在申请队列中了，事务1再加NEXT-KEY LOCK S锁则需要等待事务2提交，这就造成了相互等待。

加锁分析

T2 delete语句需要等待记录上的X锁；
普通insert时，其加锁过程为先在插入间隙上获取插入意向锁，插入数据后再获取插入行上的排它锁。由于这里T1 insert的数据检测到duplicate key，会加S锁（如果扫描到的记录被标记删除时，也会加S 锁），且针对主键索引加LOCK_ORDINARY类型的记录锁（NEXT-KEY LOCK），而NEXT-KEY LOCK与record互斥。

另外，如果delete的where子句没有满足条件的记录，而对于不存在的记录并且在RR级别下，delete加锁类型为gap lock。可以参考我的这篇博客https://my.oschina.net/hebaodan/blog/1835966

在RC级别测试上述场景无死锁发生。

死锁解决

避免出现按某个key delete后又插入相同key的场景，规避insert数据duplicate key的问题
如果非特殊需求修改为非唯一索引
通过其他手段如分布式锁实现串行化处理

附：死锁日志

*** (1) TRANSACTION:
TRANSACTION 11074, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 123145442168832, query id 160 localhost 127.0.0.1 root updating
delete from t4 where a = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `aliyun`.`t4` trx id 11074 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002b41; asc     +A;;
 2: len 7; hex 2e000001dc13c4; asc .      ;;

*** (2) TRANSACTION:
TRANSACTION 11073, ACTIVE 40 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 9, OS thread handle 123145442725888, query id 161 localhost 127.0.0.1 root update
insert into t4 values(1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `aliyun`.`t4` trx id 11073 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002b41; asc     +A;;
 2: len 7; hex 2e000001dc13c4; asc .      ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 68 page no 3 n bits 72 index PRIMARY of table `aliyun`.`t4` trx id 11073 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000002b41; asc     +A;;
 2: len 7; hex 2e000001dc13c4; asc .      ;;

*** WE ROLL BACK TRANSACTION (1)