mysql 删除死锁_MySQL死锁案例分析一(先delete，再insert，导致死锁)-CSDN博客

本文通过一个实际案例分析了在Percona MySQL 5.7.19中，当并发环境下按索引字段先执行删除再进行插入操作时可能出现的死锁现象。在可重复读隔离级别下，由于间隙锁和插入意向锁的冲突，导致两个事务相互等待对方释放资源而形成死锁。解决办法包括调整事务处理顺序或使用更高级别的事务隔离级别。

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

一、死锁案例

MySQL版本：Percona MySQL Server 5.7.19

隔离级别：可重复读(RR)

业务逻辑：并发下按某个索引字段先delete记录，再insert记录

比如：begin;delete from tb where order_id = xxx;insert into tb(order_id) values(xxx);

commit;

二、MySQL锁基本概念

S：共享锁(行级锁)

X：排他锁(行级锁)

IS：意向共享锁(表级锁)

IX：意向排他锁(表级锁)

以上4种锁的兼容性见下表：

AAffA0nNPuCLAAAAAElFTkSuQmCC

锁模式兼容性表gap锁与gap锁之间不冲突

rec insert intention(插入意向锁)与gap锁冲突。

三、模拟复现死锁

打开参数，从innodb status获取更多的锁信息。

set GLOBAL innodb_status_output_locks=ON;

表结构：CREATE TABLE `tb` ( `order_id` int(11) DEFAULT NULL,

KEY `idx_order_id` (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

表中数据：mysql> select * from tb;

+----------+| order_id |+----------+| 10 || 20 |+----------+2 rows in set (0.00 sec)

事务执行步骤：session1session2begin

begin

delete from tb where order_id=15;

insert into tb select 15;(等待锁)

insert into tb select 15;(死锁)当session1执行delete from tb where order_id=15;，由于条件order_id=15的记录不存在，session1 获得2个锁结构，分别是意向排他锁IX(表级锁)、gap锁(行级锁)，如下：---TRANSACTION 1055191443, ACTIVE 20 sec2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 315642, OS thread handle 139960342456064, query id 150462030 localhost root

TABLE LOCK table `db`.`tb` trx id 1055191443 lock mode IX

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec当session2执行delete from tb where order_id=15;，同样由于order_id=15的记录不存在，session2 也获得2个锁结构，分别是意向排他锁IX(表级锁)、gap锁(行级锁)，如下：---TRANSACTION 1055191444, ACTIVE 3 sec2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 315336, OS thread handle 139960562685696, query id 150462412 localhost root

TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec当session2执行insert into tb select 15;， session2 已经获取到IX锁，gap锁，等待 rec insert intention(插入意向锁)---TRANSACTION 1055191444, ACTIVE 68 sec inserting

mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315336, OS thread handle 139960562685696, query id 150462778 localhost root executing

insert into tb select 15------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting

------------------

TABLE LOCK table `db`.`tb` trx id 1055191444 lock mode IX

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting当session1执行insert into tb select 15;，session1 已获取到IX锁，gap锁，等待rec insert intention(插入意向锁)， session1, session2 都在等待插入意向锁，插入意向锁与gap锁冲突，双方都没有释放gap锁，又都在等待插入意向锁，死锁发生。LATEST DETECTED DEADLOCK

------------------------2018-11-03 17:15:11 0x7f4b0e7ea700*** (1) TRANSACTION:

TRANSACTION 1055191444, ACTIVE 135 sec inserting

insert into tb select 15*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191444 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:

TRANSACTION 1055191443, ACTIVE 201 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 315642, OS thread handle 139960342456064, query id 150463172 localhost root executing

insert into tb select 15*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191443 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (2)

四、案例扩展

以上死锁案例，业务代码逻辑是多线程并发下，有可能多个线程会执行相同order_id的job，比如两个线程执行的order_id 都是15。

另外一种情况，多个线程间，不会执行到相同order_id的情况，也可能发生死锁。比如一个线程order_id=15，另外一个线程order_id=16，如下所示：

事务执行步骤：session1session2begin

begin

delete from tb where order_id=15;

delete from tb where order_id=16;

insert into tb select 16;(等待锁)

insert into tb select 15;(死锁)

锁情况与上述相同，不再赘述，死锁信息如下：LATEST DETECTED DEADLOCK

------------------------2018-11-03 17:28:30 0x7f4b0e667700*** (1) TRANSACTION:

TRANSACTION 1055191450, ACTIVE 18 sec inserting

mysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 316221, OS thread handle 139960338228992, query id 150467652 localhost root executing

insert into tb select 16*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191450 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:

TRANSACTION 1055191449, ACTIVE 28 sec inserting, thread declared inside InnoDB 5000mysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 316222, OS thread handle 139960340870912, query id 150467681 localhost root executing

insert into tb select 15*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1337 page no 4 n bits 72 index idx_order_id of table `db`.`tb` trx id 1055191449 lock_mode X locks gap before rec insert intention waiting

*** WE ROLL BACK TRANSACTION (2)

作者：偏执的工匠

链接：https://www.jianshu.com/p/f8495015dace