mysql 死锁(先delete 后insert)日志分析

最新推荐文章于 2024-06-18 19:00:00 发布

原创最新推荐文章于 2024-06-18 19:00:00 发布 · 3.5k 阅读

7 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

5 篇文章

订阅专栏

本文深入探讨了MySQL中死锁的发生原因，通过具体案例分析了死锁的产生过程，并提出了有效的解决策略，包括使用主键更新替代删除插入操作及调整事务隔离级别。

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

前言：

通过show engine innodb status，获取最近一次发生的死锁详细信息。

背景：

项目最近代码发生了一次死锁，大致场景是A表数据和B表数据需要保存在中间表C中，但是A的Id在C表中只能有一条；

当时想的方案是在每次insert前先根据A的id做一次删除，再将最新的数据保存进C表；

由于对mysql锁的机制不是很了解，导致了一些问题。

下面我们重现一下当时的场景并作死锁日志的分析

建表语句

CREATE TABLE `table_c` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `aid` bigint(20) NOT NULL,
  `bids` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_aid` (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

插入两条数据

INSERT INTO table_c (aid, bids) VALUES (1, '1,2,4');
INSERT INTO table_c (aid, bids) VALUES (2, '44,55');

事务隔离级别

show variables  like '%isolation%';

然后按照以下流程执行sql(高并发的场景下出现)

-----client1

begin
delete from table_c where aid = 3;
insert table_c (aid, bids) values(3,'44,55');
commit;

-----client2

begin
delete from table_c where aid = 6;
insert table_c (aid, bids) values(6,'448,558');
commit;

你会发现当客户端2执行到inert语句的时候就会死锁的异常了：

日志分析：

通过 show engine innodb status ; 获得最近一次mysql的死锁日志


------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-21 18:50:02 1930
*** (1) TRANSACTION:
TRANSACTION 7427, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 29, OS thread handle 0x3994, query id 2481 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2019.1.4 */ insert table_c (aid, bids) values(3,'44,55')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 4 n bits 72 index `idx_aid` of table `test`.`table_c` trx id 7427 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 7428, ACTIVE 6 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 30, OS thread handle 0x1930, query id 2491 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2019.1.4 */ insert table_c (aid, bids) values(6,'448,558')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 40 page no 4 n bits 72 index `idx_aid` of table `test`.`table_c` trx id 7428 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 40 page no 4 n bits 72 index `idx_aid` of table `test`.`table_c` trx id 7428 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

通过日志分析 TRANSACTION(1)和TRANSACTION(2)都在等待排他的插入意向锁（关键字：WAITING FOR THIS LOCK TO BE GRANTED:），在这个等待之前TRANSACTION(2)持有了一个排他记录锁（关键字：HOLDS THE LOCK(S)，这个锁是delete语句获得的，从日志分析和表数据分析可知delete语句获得了aid在 6-73757072656d756d（16进制）之间数据的锁）；

虽然日志里没有打印TRANSACTION(1)在等待插入意向锁前获得了什么锁，但是通过推断，我们可以分析出TRANSACTION(1)也应该是持有了一个排他记录锁（delete语句）；

这样双方都在等待对方释放锁，就导致了死锁