投稿
  1. 极客之音首页
  2. 数据库

MySQL 并发 delete 后 insert 导致死锁

小半 • • 数据库 • 阅读 451

引言

死锁本质上是两组锁等待，因此在分析过锁等待以后，本文介绍死锁的分析方法，还是从最简单的开始，并发 delete 后 insert 导致死锁。

介绍

日常工作中死锁遇到的不少，其中很多都是在并发条件下出现的，比如并发 delete 后 insert、并发 insert on duplicate key、并发 REPLACE INTO 等。

本文基于并发 delete 后 insert 导致死锁的案例分析死锁发生的原因。


死锁日志的格式如下所示。

InnoDB: *** (1) TRANSACTION:
InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
InnoDB: *** (2) TRANSACTION:
InnoDB: *** (2) HOLDS THE LOCK(S):
InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
InnoDB: *** WE ROLL BACK TRANSACTION (1)/(2)

其中事务 1 的日志不完整，只打印出了等待的锁，而没有持有的锁，因此业内很多大佬通过修改内核代码来将这个信息补充完整。

可见分析死锁日志的过程中，由于得到的是不够完整的信息，因此就需要不断推导和梳理，类似于完形填空。

复现

准备数据

创建测试表 tb，仅包括一个字段 order_id，该字段上创建了一个普通索引。

mysql> CREATE TABLE `tb` (
    ->   `order_id` int(11) DEFAULT NULL,
    ->   KEY `idx_order_id` (`order_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb values(10),(20);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tb;
+----------+
| order_id |
+----------+
|       10 |
|       20 |
+----------+
2 rows in set (0.00 sec)

由于没有显示声明主键，因此该表将自动创建隐式主键。

流程

两个事务分别依次执行 delete 与 insert 操作，其中：

  • session 1 中执行 insert 时发生锁等待；
  • session 2 中执行 insert 时发生死锁。
session 1 session 2
begin;

begin;
delete from tb where order_id=15;

delete from tb where order_id=16;
insert into tb select 15;
blocked

insert into tb select 16;
Deadlock found

死锁已复现，下面查看现场并分析原因。

现场

锁等待

分别通过 information_schema 库中的 innodb_trx、innodb_locks、innodb_lock_waits 三张表查看当前的锁。

mysql> select * from information_schema.innodb_trxG 
*************************** 1. row ***************************
                    trx_id: 11309055
                 trx_state: RUNNING
               trx_started: 2022-12-05 17:18:07
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 1156
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 11309054
                 trx_state: LOCK WAIT
               trx_started: 2022-12-05 17:15:53
     trx_requested_lock_id: 11309054:320:4:3
          trx_wait_started: 2022-12-05 17:21:44
                trx_weight: 4
       trx_mysql_thread_id: 1155
                 trx_query: insert into tb select 15
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 3
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 3
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_locks G
*************************** 1. row ***************************
    lock_id: 11309054:320:4:3
lock_trx_id: 11309054
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test_zk`.`tb`
 lock_index: idx_order_id
 lock_space: 320
  lock_page: 4
   lock_rec: 3
  lock_data: 20, 0x000000000603
*************************** 2. row ***************************
    lock_id: 11309055:320:4:3
lock_trx_id: 11309055
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test_zk`.`tb`
 lock_index: idx_order_id
 lock_space: 320
  lock_page: 4
   lock_rec: 3
  lock_data: 20, 0x000000000603
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 11309054          | 11309054:320:4:3  | 11309055        | 11309055:320:4:3 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW PROCESSLIST;
+------+------------+-----------------+---------+-------------+---------+---------------------------------------------------------------+--------------------------+
| Id   | User       | Host            | db      | Command     | Time    | State                                                         | Info                     |
+------+------------+-----------------+---------+-------------+---------+---------------------------------------------------------------+--------------------------+
| 1124 | replicater | 127.0.0.1:54201 | NULL    | Binlog Dump | 2490917 | Master has sent all binlog to slave; waiting for more updates | NULL                     |
| 1155 | admin      | 127.0.0.1:47259 | test_zk | Query       |      58 | executing                                                     | insert into tb select 15 |
| 1156 | admin      | 127.0.0.1:40520 | test_zk | Sleep       |     273 |                                                               | NULL                     |
| 1157 | admin      | 127.0.0.1:40570 | NULL    | Query       |       0 | starting                                                      | SHOW PROCESSLIST         |
+------+------------+-----------------+---------+-------------+---------+---------------------------------------------------------------+--------------------------+
4 rows in set (0.01 sec)

由于检测到死锁后立即回滚，因此无法查看到完整的锁信息，只能看到其中一个事务锁等待。

TRANSACTION

导致死锁的 SQL 共 4 条，每执行一条 SQL 查询一次 show engine innodb status 命令，通过其中的事务信息查看完整的锁信息，结果如下所示。

事务 11309054，执行 delete from tb where order_id=15，获取到了表级排它锁 IX、行级间隙锁 Gap Lock。

---TRANSACTION 11309054, ACTIVE 7 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1155, OS thread handle 140689703233280, query id 13804408 127.0.0.1 admin
Trx read view will not see trx with id >= 11309054, sees < 11309054
TABLE LOCK table `test_zk`.`tb` trx id 11309054 lock mode IX
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309054 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

事务 11309055，执行 delete from tb where order_id=16，获取到了表级排它锁 IX、行级间隙锁 Gap Lock。

---TRANSACTION 11309055, ACTIVE 5 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 1156, OS thread handle 140689701902080, query id 13804412 127.0.0.1 admin
Trx read view will not see trx with id >= 11309055, sees < 11309054
TABLE LOCK table `test_zk`.`tb` trx id 11309055 lock mode IX
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309055 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

事务 11309054，执行 insert into tb select 15，获取到了表级排它锁 IX，等待行级插入意向锁。

---TRANSACTION 11309054, ACTIVE 222 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 1155, OS thread handle 140689703233280, query id 13804414 127.0.0.1 admin executing
insert into tb select 15
Trx read view will not see trx with id >= 11309054, sees < 11309054
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309054 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

------------------
TABLE LOCK table `test_zk`.`tb` trx id 11309054 lock mode IX
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309054 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309054 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

事务 11309055，执行 insert into tb select 16，由于导致死锁，事务回滚，因此从死锁日志中查看锁信息。

获取到了表级排它锁 IX，等待行级插入意向锁。因此事务 11309054 与 11309055 都持有间隙锁，又都在等待插入意向锁，发生死锁。

*** (2) TRANSACTION:
TRANSACTION 11309055, ACTIVE 298 sec inserting, thread declared inside InnoDB 5000
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 1156, OS thread handle 140689701902080, query id 13804425 127.0.0.1 admin executing
insert into tb select 16
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309055 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309055 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

死锁日志

完整的死锁日志如下所示，事务 11309054 与事务 11309055 发生死锁，其中回滚事务 11309055。

mysql> show engine innodb status G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-12-05 17:23:11 0x7ff4ba17a700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6365 srv_active, 0 srv_shutdown, 19433348 srv_idle
srv_master_thread log flush and writes: 19439713
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 227948
OS WAIT ARRAY INFO: signal count 1326050
RW-shared spins 0, rounds 2223509, OS waits 60439
RW-excl spins 0, rounds 15247716, OS waits 68016
RW-sx spins 704881, rounds 9576980, OS waits 37274
Spin rounds per wait: 2223509.00 RW-shared, 15247716.00 RW-excl, 13.59 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-12-05 17:23:05 0x7ff4dfb49700
*** (1) TRANSACTION:
TRANSACTION 11309054, ACTIVE 432 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 1155, OS thread handle 140689703233280, query id 13804418 127.0.0.1 admin executing
insert into tb select 15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309054 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

*** (2) TRANSACTION:
TRANSACTION 11309055, ACTIVE 298 sec inserting, thread declared inside InnoDB 5000
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 1156, OS thread handle 140689701902080, query id 13804425 127.0.0.1 admin executing
insert into tb select 16
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309055 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 320 page no 4 n bits 72 index idx_order_id of table `test_zk`.`tb` trx id 11309055 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000000603; asc       ;;

*** WE ROLL BACK TRANSACTION (2)

成功复现以后，分析死锁发生的原因。

分析

锁

行级锁

首先，需要明确行级锁的种类。

不同事务隔离级别下，行级锁的种类不同。

其中：

  • RC 隔离级别下行级锁的种类只有记录锁，即仅仅把一条记录锁上；

  • RR 隔离级别下行级锁主要分为三类：

    • 记录锁（Record Lock），对索引项加锁，即锁定一条记录；

    • 间隙锁（Gap Lock），对索引项之间的间隙、对第一条记录前的间隙或最后一条记录后间隙加锁，即锁定一个范围，不包含记录本身。

    • Next-key Lock，锁定一个范围的记录包括记录本身，因此 Next-key Lock = Record Lock + Gap Lock，因此 Next-key Lock 是左开右闭的区间。


然后，需要明确行级锁的加锁规则。

加锁的对象是索引，加锁的基本单位是 Next-key Lock，由记录锁和间隙锁组成。

但是，Next-key Lock 在一些场景下会退化成记录锁或间隙锁。

具体是在能使用记录锁或者间隙锁就能避免幻读现象的场景下， Next-key Lock  就会退化成退化成记录锁或间隙锁。

行级锁的详细加锁规则与很多因素有关，如是否使用索引、使用的索引类型、查询的记录是否存在。具体将单独一篇讲解。

间隙锁

如果行锁仅支持锁定行，那么就无法避免幻读现象的发生，即一个事务中相同条件的的两次查询结果不同。

假如存在两条记录 id=1 与 id=3，通过锁定行无法防止其他事务插入 id=2 的记录，因此出现幻读。而事务在第一次执行读取操作时，幻影记录并不存在，因此无法加锁。


RR 隔离级别下可以在很大程度上解决幻读现象，具体就是通过间隙锁（gap lock）防止插入幻影记录。

间隙锁是对索引项之间的间隙、对第一条记录前的间隙或最后一条记录后间隙加锁，即锁定一个范围，不包含记录本身。

假如给 id=3 的记录添加间隙锁，其他事务就无法在 (1, 3) 范围内插入新记录，因此可以避免幻读。


需要注意两点：

  • 明确间隙锁仅仅用于防止幻读，因此尽管间隙锁分为共享间隙锁与独占间隙锁，但作用相同，而且间隙锁之间兼容。因此如果对一条记录加了间隙锁，并不会限制其他事务对这条记录加 Next-key Lock 或继续加间隙锁；
  • 间隙锁仅存在于 RR 事务隔离级别，RC 中不存在这种锁，因此极端条件下可以通过将事务隔离级别调整为 RC 来降低锁发生的概率。

然后，介绍与间隙锁冲突的一种锁即插入意向锁。

插入意向锁

首先插入意向锁不是意向锁，意向锁是表锁，而插入意向锁是行锁，发生在插入操作过程中。

一个事务在插入一条记录时，需要判断插入位置是否已经被其他事务加了间隙锁（Next-key Lock 中包含间隙锁）。

事务在等待过程中也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在处于等待状态。这种锁称为插入意向锁（Insert Intention Lock）。

插入意向锁仅与间隙锁冲突，与其他任何锁都兼容。


因此数据插入过程中：

  • 如果没有间隙锁，多个事务可以在相同的索引间隙插入而不需要等待，只要不是插入间隙中相同的位置；
  • 如果有间隙锁，插入操作需要等待，直到持有间隙锁的事务提交。

假如索引间隙 4 和 7 之间，不同的事务分别插入 5 和 6，每个事务都会持有一个插入意向锁，并得到插入行上的排它锁，但是相互之间不影响，因为数据行不冲突。

但是如果 (4, 7) 范围内有间隙锁，5 和 6 都会发生插入意向锁锁等待。

可见，多个间隙锁、多个插入意向锁之间兼容，但是插入意向锁与间隙锁之间冲突。

锁的兼容性

四种锁之间的兼容性见下表，其中横向是已持有的锁，纵向是正在请求的锁。

兼容性 Gap Insert Intention Record Next-key
Gap 兼容 兼容 兼容 兼容
Insert Intention 冲突 兼容 兼容 冲突
Record 兼容 兼容 冲突 冲突
Next-key 兼容 兼容 冲突 冲突

查看锁

data_locks

在 8.0.17 版本中分别执行上述 SQL 语句并通过 performance_schema.data_locks 表分析需要获取的锁。

首先需要明确的是 performance_schema.data_locks 表中几个重要字段的含义。

其中：

  • LOCK_TYPE：

    • TABLE，表级锁
    • RECORD，行级锁

  • LOCK_MODE：

    • X，Next-key Lock
    • X, REC_NOT_GAP，记录锁
    • X, GAP，间隙锁

  • LOCK_DATA：

    • LOCK_TYPE = TABLE 时，LOCK_DATA  = NULL，即对于表锁，该字段值为空；

    • 对于主键索引，该字段值为主键值；

    • 对于二级索引，该字段值由二级索引值与主键值两部分组成。

其中，如果 LOCK_MODE 是 Next-key Lock 或间隙锁，LOCK_DATA 表示锁的范围右边界，左边界为前一条记录的索引值。

上面我们通过 TRANSACTION 部分查看了每条 SQL 执行需要获取到的锁，下面我们通过 data_locks 更直观的查看需要的锁信息。

每条SQL的锁

下面依次执行这几条语句查看需要的锁，首先 delete 一条不存在的记录。

delete from tb where order_id=15;

由于表中不存在 order_id=15 的记录，因此加锁变化过程如下：

  • 定位到第一条不符合查询条件的二级索引记录，即扫描到 order_id=15，于是该二级索引的 Next-key Lock 会退化成间隙锁，范围是 (10, 20)；
  • 停止查询。

由于事务在 order_id=15 记录的二级索引上，加了 X 型间隙锁，范围是 (10, 20)，因此其他事务无法插入 order_id 值为 11、12、13、14、15、16、17、18、19 这些新记录。


查看锁，与 TRANSACTION 部分的结果一致，包括表级排它锁 IX 与行级间隙锁 Gap Lock。

其中间隙锁 LOCK_DATA = 20, 0x000000000201，表明锁住的是二级索引，其中 0x000000000201 是隐式主键。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1071:140122972540608
ENGINE_TRANSACTION_ID: 2032047
            THREAD_ID: 72
             EVENT_ID: 31
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: tb
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140122972540608
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:9:5:3:140122972537552
ENGINE_TRANSACTION_ID: 2032047
            THREAD_ID: 72
             EVENT_ID: 31
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: tb
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_order_id
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20, 0x000000000201
2 rows in set (0.00 sec)


然后，另一个事务中 delete 另一条不存在的记录。

delete from tb where order_id=16;

查看锁，由于表中仅有 order_id=10、20 两条记录，而 order_id=15、16 的记录都不存在，因此两条 SQL 的间隙锁范围相同。·

*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1071:140122972540608
ENGINE_TRANSACTION_ID: 2032236
            THREAD_ID: 91
             EVENT_ID: 15
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: tb
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140122972540608
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:9:5:3:140122972537552
ENGINE_TRANSACTION_ID: 2032236
            THREAD_ID: 91
             EVENT_ID: 15
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: tb
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_order_id
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20, 0x000000000201


最后， insert 一条新记录。

insert into tb select 15;

查看锁，显示插入意向锁等待。

*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:9:5:3:140122972537896
ENGINE_TRANSACTION_ID: 2032236
            THREAD_ID: 91
             EVENT_ID: 17
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: tb
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_order_id
OBJECT_INSTANCE_BEGIN: 140122972537896
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: 20, 0x000000000201


如果同一个事务中先 delete 后 insert，会发生什么呢？

delete from tb where order_id=15;
insert into tb select 15;

查看锁，显示多了一把锁，具体是 X 型间隙锁，范围是 (10, 15)，行锁退化为间隙锁。

mysql> select * from performance_schema.data_locks G
...
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:9:5:4:140122972537552
ENGINE_TRANSACTION_ID: 2032057
            THREAD_ID: 76
             EVENT_ID: 20
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: tb
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_order_id
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15, 0x000000000207
3 rows in set (0.00 sec)

结论

两个事务的 delete 操作均持有间隙锁，insert 操作均在等待插入意向锁，而插入意向锁等待对方释放间隙锁，所以导致发生死锁。

多个间隙锁、多个插入意向锁之间兼容，但是插入意向锁与间隙锁之间冲突。

间隙锁的唯一作用是防止幻读，仅存在于 RR 事务隔离级别。


加锁的对象是索引，加锁的基本单位是 Next-key Lock，由记录锁和间隙锁组成。

但是，如果仅使用记录锁或者间隙锁就能避免幻读现象，Next-key Lock 会退化成记录锁或间隙锁。

待办

  • MVCC
  • 事务隔离级别
  • 不同场景下SQL语句中需要的锁

参考教程

  • MySQL死锁案例分:先delete，再insert，导致死锁
https://blog.csdn.net/hellozhxy/article/details/88052296
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
  • 《MySQL 是怎样运行的》
  • 《MySQL DBA 工作笔记》

原文始发于微信公众号（丹柿小院）：MySQL 并发 delete 后 insert 导致死锁

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/178649.html

赞 (0)
小半的头像小半
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！