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

MySQL 并发 insert 导致间隙锁与插入意向锁形成死锁

小半 • • 数据库 • 阅读 379

介绍

生产环境中发现了一个死锁案例，显示并发 insert 导致死锁，查阅官方文档发现与其中一个示例特征一致。本文复现死锁并结合 insert 执行过程中使用的锁分析死锁产生的原因。

现象

业务报错

时间：2023-04-25 16:55:03

业务报错：insert 语句发生死锁后回滚

2023-04-25 16:55:03.719 [http-nio-6000-exec-3] ERROR c.j.w.c.c.a.AdminSubmitAuthController  - 1956991.56478.16824129014202387
org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/jd/wl/contract/repository/mapper/WlCustomerMapper.java (best guess)
### The error may involve com.jd.wl.contract.repository.mapper.WlCustomerMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO wl_customer  ( customer_type,  customer_name, jd_pin, sales_man_erp, certificate_type, certificate_code,   is_long_range,  scope_of_business, real_name_certificate_status,      create_user, update_user, create_time, update_time,       sys_source, scope_of_business_url, social_credit_code, business_lisence_no,  buss_no )  VALUES  ( ?,  ?, ?, ?, ?, ?,   ?,  ?, ?,      ?, ?, ?, ?,       ?, ?, ?, ?,  ? )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266)

死锁日志

两条 insert 触发死锁

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-04-25 16:55:03 0x7f764af79700
*** (1) TRANSACTION:
TRANSACTION 326805335, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 982015765, OS thread handle 140145989490432, query id 437458541159 x.x.x.x eclp-ec_rw update
INSERT INTO wl_customer (jd_pin, sys_source, ...)  VALUES  ('珍惜拥有01230', 'wl-contract', ...)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1880 page no 19253 n bits 352 index customer_pin_source_index of table `lcc_contract`.`wl_customer` trx id 326805335 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

*** (2) TRANSACTION:
TRANSACTION 326805323, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 981522342, OS thread handle 140146040608512, query id 437458540636 x.x.x.x eclp-ec_rw update
INSERT INTO wl_customer (jd_pin, sys_source, ...)  VALUES  ('珍惜拥有01230', 'wl-contract', ...)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1880 page no 19253 n bits 352 index customer_pin_source_index of table `lcc_contract`.`wl_customer` trx id 326805323 lock mode S locks gap before rec
Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1880 page no 19253 n bits 352 index customer_pin_source_index of table `lcc_contract`.`wl_customer` trx id 326805323 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

*** WE ROLL BACK TRANSACTION (2)

日志显示发生死锁的索引是 index customer_pin_source_index，因此通过表结构查看冲突字段。

表结构

查看表结构。

mysql> show create table wl_customer G
*************************** 1. row ***************************
       Table: wl_customer
Create Table: CREATE TABLE `wl_customer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `customer_type` int(4) DEFAULT NULL COMMENT '商家类型',
  `customer_code` varchar(50) DEFAULT NULL COMMENT '商家c码',
  `customer_name` varchar(300) DEFAULT NULL COMMENT '商家名称',
  `jd_pin` varchar(100) DEFAULT NULL COMMENT '登录账号',
  `sales_man_erp` varchar(50) DEFAULT NULL COMMENT '签约销售',
  `certificate_type` int(4) DEFAULT NULL COMMENT '证件类型',
  `certificate_code` varchar(50) DEFAULT NULL COMMENT '证件号',
  `certificate_code_encrypt` varchar(150) DEFAULT '' COMMENT '证件号-密文',
  `start_time` datetime DEFAULT NULL COMMENT '证件开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '证件截止时间',
  `is_long_range` tinyint(4) DEFAULT '0' COMMENT '是否长期',
  `phone_number` varchar(20) DEFAULT NULL COMMENT '手机号码',
  `phone_number_encrypt` varchar(150) DEFAULT '' COMMENT '手机号-密文',
  `scope_of_business` varchar(1200) DEFAULT NULL,
  `real_name_certificate_status` int(4) NOT NULL DEFAULT '2' COMMENT '实名认证状态 1:无需校验 2：未认证 3：已信息校验 4：认证审核中 5：已物流认证',
  `is_bank_certification` int(4) DEFAULT NULL COMMENT '银行账号校验状态',
  `is_e_sign` int(4) DEFAULT '0' COMMENT '电子签章开通状态: 0:未开通 1:已开通',
  `supplier_sign` int(4) DEFAULT '0' COMMENT '自营供应商状态标识：0非自营供应商，1自营供应商',
  `pop_sign` int(4) unsigned DEFAULT '0' COMMENT 'pop商家状态标识：0非pop商家，1pop商家',
  `wk_nation` varchar(20) DEFAULT NULL COMMENT '办公地址国籍编码',
  `wk_addr` varchar(300) DEFAULT NULL COMMENT '办公地址',
  `wk_pcc` varchar(50) DEFAULT NULL COMMENT '办公地址省市县编码，以下划线分割',
  `fax` varchar(50) DEFAULT NULL COMMENT '传真',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `is_delete` tinyint(4) DEFAULT '0' COMMENT '是否删除',
  `create_user` varchar(50) DEFAULT NULL COMMENT '创建用户',
  `update_user` varchar(50) DEFAULT NULL COMMENT '更新用户',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  `sign_pic_url` varchar(100) NOT NULL DEFAULT '' COMMENT '电子签章图片云存储key',
  `sys_source` varchar(100) DEFAULT 'wl-contract' COMMENT '来源',
  `scope_of_business_url` varchar(100) DEFAULT '' COMMENT '经营范围jss存储url',
  `jd_pin_reserve` varchar(50) DEFAULT NULL COMMENT '备用jdPin',
  `lop_clean_remark` varchar(200) DEFAULT NULL COMMENT 'lop数据清洗打标备注',
  `buss_no` varchar(64) DEFAULT NULL COMMENT '业务编号',
  `social_credit_code` varchar(32) DEFAULT NULL COMMENT '社会统一信用码',
  `business_lisence_no` varchar(32) DEFAULT NULL COMMENT '工商注册号',
  `invoice_qualification` int(4) DEFAULT NULL COMMENT '发票资质 1：增值税发票 2：普通发票',
  `invoice_condition` int(4) DEFAULT NULL COMMENT '发票条件 1：先票 2：后票',
  `spm_company_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '京销易同步客户id',
  `spm_sync` varchar(20) NOT NULL DEFAULT '' COMMENT '京销易同步客户标识',
  `e_sign_end_date` varchar(30) NOT NULL DEFAULT '' COMMENT '电子签章到期时间',
  `strong_certificate_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '实强实名认证状态 0：默认  1：强实名认证通过  2：强实名认证中 3：强实名认证未通过',
  `country_code` varchar(20) NOT NULL DEFAULT '' COMMENT '国家地区编码',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `customer_pin_source_index` (`jd_pin`,`sys_source`),
  KEY `idx_customer_type` (`customer_type`) USING BTREE,
  KEY `idx_customer_code` (`customer_code`) USING BTREE,
  KEY `idx_real_name_certificate_status` (`real_name_certificate_status`) USING BTREE,
  KEY `idx_certificate_code` (`certificate_code`) USING BTREE,
  KEY `idx_certificate_code_encrypt` (`certificate_code_encrypt`) USING BTREE,
  KEY `idx_buss_no` (`buss_no`) USING BTREE,
  KEY `idx_customer_name` (`customer_name`(64)),
  KEY `wl_customer_spm_company_id_IDX` (`spm_company_id`) USING BTREE,
  KEY `idx_social_credit_code` (`social_credit_code`) USING BTREE,
  KEY `idx_business_lisence_no` (`business_lisence_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3335280 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='商家信息表'
1 row in set (0.01 sec)

显示 index customer_pin_source_index 是由两个字段组成的联合唯一索引。

UNIQUE KEY `customer_pin_source_index` (`jd_pin`,`sys_source`)

`jd_pin` varchar(100) DEFAULT NULL COMMENT '登录账号',
`sys_source` varchar(100) DEFAULT 'wl-contract' COMMENT '来源'

报错的两条数据中对应的两个字段均为 (‘珍惜拥有01230’, ‘wl-contract’)。

简化后的死锁信息如下所示。

*** (1) TRANSACTION:
INSERT INTO wl_customer (jd_pin, sys_source)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1880 page no 19253 n bits 352 index customer_pin_source_index of table `lcc_contract`.`wl_customer` trx id 326805335 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

*** (2) TRANSACTION:
INSERT INTO wl_customer (jd_pin, sys_source)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1880 page no 19253 n bits 352 index customer_pin_source_index of table `lcc_contract`.`wl_customer` trx id 326805323 lock mode S locks gap before rec
Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1880 page no 19253 n bits 352 index customer_pin_source_index of table `lcc_contract`.`wl_customer` trx id 326805323 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

*** WE ROLL BACK TRANSACTION (2)

其中：

  • 事务 1，等待插入意向锁；
  • 事务 2，持有 S 级间隙锁，等待插入意向锁。

参考官方文档 https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html，多条 insert 唯一键冲突时可能导致死锁。

下面通过复现分析死锁的触发条件。

复现

准备数据

测试数据库版本

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

隔离级别

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

创建表

mysql> create table t_unique(
    id int primary key auto_increment, 
    age int default 0, 
    unique key uk_age(age)
);
Query OK, 0 rows affected (0.01 sec)

初始化

mysql> insert into t_unique values(1,1),(5,5),(10,10);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_unique;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.00 sec)

下面分别测试两个事务与三个事务执行唯一键冲突的 insert 语句，将分别导致锁等待与死锁。

一个事务隐式锁

一条 insert，事务未提交。

session 1
begin;  
insert into t_unique values(2,2);
Query OK, 1 row affected (0.00 sec)

从show engine innodb status的结果中的TRANSACTIONS显示事务仅持有表锁。

---TRANSACTION 9415, ACTIVE 3 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 198, OS thread handle 139933592164096, query id 35159 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 9415 lock mode IX

实际上，插入操作也需要持有行锁，比如真正插入时需要持有 X 级记录锁？

实际上，插入操作时使用隐式锁，用于提升并发能力。

因此事务 T 要插入一条记录 R，只要即将插入记录的目标位置没有被其它事务上锁，事务 T 就不需要申请对目标位置加锁，可以直接插入记录。

两个事务锁等待

两条 insert，唯一键冲突。

session 1 session 2
begin;
insert into t_unique values(2,2);

begin;
insert into t_unique values(3,2);
阻塞，等待事务1中唯一键2的X锁

查看事务与锁，显示锁等待。

mysql> select * from information_schema.innodb_trxG
*************************** 1. row ***************************
                    trx_id: 9420
                 trx_state: LOCK WAIT
               trx_started: 2023-06-13 17:52:55
     trx_requested_lock_id: 9420:141:4:5
          trx_wait_started: 2023-06-13 17:52:55
                trx_weight: 3
       trx_mysql_thread_id: 199
                 trx_query: insert into t_unique values(3,2)
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         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. row ***************************
                    trx_id: 9415
                 trx_state: RUNNING
               trx_started: 2023-06-13 17:46:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 198
                 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: 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: 9420:141:4:5
lock_trx_id: 9420
  lock_mode: S
  lock_type: RECORD
 lock_table: `test_zk`.`t_unique`
 lock_index: uk_age
 lock_space: 141
  lock_page: 4
   lock_rec: 5
  lock_data: 2
*************************** 2. row ***************************
    lock_id: 9415:141:4:5
lock_trx_id: 9415
  lock_mode: X
  lock_type: RECORD
 lock_table: `test_zk`.`t_unique`
 lock_index: uk_age
 lock_space: 141
  lock_page: 4
   lock_rec: 5
  lock_data: 2
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 |
+-------------------+-------------------+-----------------+------------------+
| 9420              | 9420:141:4:5      | 9415            | 9415:141:4:5     |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

查看锁等待关系

mysql> select
  wt.thread_id waiting_thread_id,
  wt.processlist_id waiting_processlist_id,
  wt.processlist_time waiting_time,
  wt.processlist_info waiting_query,
  wl.lock_index waiting_lock_index,
  wl.lock_type waiting_lock_type,
  wl.lock_mode waiting_lock_mode,
  wl.lock_data waiting_lock_data,
  bt.thread_id blocking_thread_id,
  bt.processlist_id blocking_processlist_id,
  bt.processlist_time blocking_time,
  c.sql_text blocking_query,
  bl.lock_index blocking_lock_index,
  bl.lock_type blocking_lock_type,
  bl.lock_mode blocking_lock_mode,
  bl.lock_data waiting_lock_data,
  concat('kill ', bt.processlist_id, ';') sql_kill_blocking_connection
from
  information_schema.innodb_lock_waits l
  join information_schema.innodb_trx b on b.trx_id = l.blocking_trx_id
  join information_schema.innodb_trx w on w.trx_id = l.requesting_trx_id
  join performance_schema.threads wt on w.trx_mysql_thread_id = wt.processlist_id
  join performance_schema.threads bt on b.trx_mysql_thread_id = bt.processlist_id
  join performance_schema.events_statements_current c on bt.thread_id = c.thread_id
  join information_schema.innodb_locks bl on l.blocking_lock_id = bl.lock_id
  join information_schema.innodb_locks wl on l.requested_lock_id = wl.lock_id G
*************************** 1. row ***************************
           waiting_thread_id: 224
      waiting_processlist_id: 199
                waiting_time: 22
               waiting_query: insert into t_unique values(3,2)
          waiting_lock_index: uk_age
           waiting_lock_type: RECORD
           waiting_lock_mode: S
           waiting_lock_data: 2
          blocking_thread_id: 223
     blocking_processlist_id: 198
               blocking_time: 392
              blocking_query: insert into t_unique values(2,2)
         blocking_lock_index: uk_age
          blocking_lock_type: RECORD
          blocking_lock_mode: X
           waiting_lock_data: 2
sql_kill_blocking_connection: kill 198;
1 row in set, 3 warnings (0.00 sec)

其中：

  • 事务 1 的 insert 持有唯一索引的 X 锁，但是锁的范围不确定；
  • 事务 2 的 insert 等待唯一索引的 S 锁，但是锁的范围不确定。

TRANSACTIONS显示：

  • 事务 1 lock_mode X locks rec but not gap Record lock，持有 X 级记录锁（Record Lock）；
  • 事务 2 lock mode S waiting Record lock，等待 S 级行锁（Next-key Lock）。
---TRANSACTION 9420, ACTIVE 50 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 199, OS thread handle 139933461223168, query id 35162 127.0.0.1 admin update
insert into t_unique values(3,2)
------- TRX HAS BEEN WAITING 50 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 141 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 9420 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

------------------
TABLE LOCK table `test_zk`.`t_unique` trx id 9420 lock mode IX
RECORD LOCKS space id 141 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 9420 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

---TRANSACTION 9415, ACTIVE 420 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 198, OS thread handle 139933592164096, query id 35159 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 9415 lock mode IX
RECORD LOCKS space id 141 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 9415 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 4; hex 80000002; asc     ;;

这里需要解释两个现象：

  • 事务 1 一开始查看锁时没有行锁，但是发生唯一键冲突后持有记录锁；
  • 事务 2 发生唯一键冲突时等待 S 级行锁（next-key lock = gap lock+record lock）。

实际上，事务 2 在检测到唯一键冲突时，会帮事务 1 添加排他锁，然后给自己申请共享锁，并等待事务 1 释放排他锁。

到这里，就可以理解隐式锁的定义了：

未提交事务 T 插入的记录上，这种隐性的、由其它事务在需要时帮忙创建的锁，就是隐式锁。

三个事务死锁

三条 insert，唯一键冲突。

session 1 session 2 session 3
begin;
insert into t_unique values(2,2);


begin;
insert into t_unique values(3,2);
阻塞，等待事务1中唯一键2的X锁


begin;
insert into t_unique values(4,2);
阻塞，等待事务1中唯一键2的X锁
rollback;


Deadlock found Query OK, 1 row affected

复现死锁，死锁日志如下所示。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-04-26 15:47:43 0x7f44cc377700
*** (1) TRANSACTION:
TRANSACTION 8311, ACTIVE 174 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 112, OS thread handle 139933417838336, query id 30952 127.0.0.1 admin update
insert into t_unique values(4,2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 141 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 8311 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 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 8324, ACTIVE 50 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 110, OS thread handle 139933460690688, query id 30945 127.0.0.1 admin update
insert into t_unique values(3,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 141 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 8324 lock mode S locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 141 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 8324 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 80000005; asc     ;;
 1: len 4; hex 80000005; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

其中：

  • 事务 2，lock_mode X locks gap before rec insert intention waiting；
  • 事务 3，持有 S 级间隙锁，lock_mode X locks gap before rec insert intention waiting。

下面按照时间线分析死锁的原因：

  • 事务 1，持有 X 级记录锁；
  • 事务 2 与 3，发生唯一键冲突前均持有插入意向锁，发生唯一键冲突后均等待 S 级行锁；
  • 事务 1 回滚，事务 1 释放 X 级记录锁，事务 2 与 3 获取到 S 级行锁。而由于冲突记录消失，S 级行锁 (1, 2] 退化为 S 级间隙锁 (1, 5)；
  • 两个插入操作均申请 X 级记录锁，又均持有 S 级间隙锁，相互等待形成死锁。

注意 insert intention waiting 并不表示在等待插入意向锁，只表示插入记录正在等待获取 X 锁。

因此最终现象是两个事务分别持有间隙锁，而且相互等待形成死锁。

本质上间隙锁之间不冲突，但由于插入意向锁需要等待间隙锁，因此导致两个间隙锁相互等待。

经确认，业务逻辑中有回滚逻辑。


此外，生产环境的死锁日志中显示锁结构中显示冲突索引的第一个字段的十六进制值为 e78f8de6839ce7bc98e78fa0e5ae9d，转换成字符串为 ‘珍惜缘珠宝’，与报错 SQL 中的值 ‘珍惜拥有01230’ 不同。

Record lock, heap no 277 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 15; hex e78f8de6839ce7bc98e78fa0e5ae9d; asc                ;;
 1: len 11; hex 776c2d636f6e7472616374; asc wl-contract;;
 2: len 8; hex 800000000030920b; asc      0  ;;

原因是三个事务插入时都是 ‘珍惜拥有01230’，但是事务 1 回滚后这条记录就不存在了，而事务 2 与 3 持有的间隙锁自动变大，向下关联到下一行即 ‘珍惜缘珠宝’。

查看数据，显示 ‘珍惜拥有01230’ 的下一行正是 ‘珍惜缘珠宝’。

mysql> select id, jd_pin,sys_source,create_time from wl_customer where jd_pin>='珍惜拥有01230' order by jd_pin,sys_source limit 2;
+---------+-------------------+-------------+---------------------+
| id      | jd_pin            | sys_source  | create_time         |
+---------+-------------------+-------------+---------------------+
| 3227201 | 珍惜拥有01230     | wl-contract | 2023-04-25 16:55:03 |
| 3183115 | 珍惜缘珠宝        | wl-contract | 2023-04-09 21:09:45 |
+---------+-------------------+-------------+---------------------+
2 rows in set (0.00 sec)

死锁日志中 len 11表示字段长度，如变长字符串 ‘wl-contract’ 长度等于 11。

mysql> select length("wl-contract");
+-----------------------+
| length("wl-contract") |
+-----------------------+
|                    11 |
+-----------------------+
1 row in set (0.00 sec)

下面通过详细分析 insert 执行过程中使用的锁解释死锁发生的原因。

原理

insert 语句持有的锁

insert 语句的执行流程以及可能需要持有的锁如下所示：

  • 首先对要插入的间隙加插入意向锁；
    • 如果该间隙已经被加上了 gap lock 或 next-key lock，加锁失败，则加插入意向锁等待；
    • 如果没有，加锁成功，表示可以插入；
  • 然后判断插入记录是否有唯一键，如果有，进行唯一约束检查；
    • 如果有锁，说明该记录正在处理（增删改），且事务未提交，则加 S 锁等待；
    • 如果没有锁，判断该记录是否已被标记为删除；
    • 如果标记为删除，说明事务已提交，但是未 purge 掉，则加 S 锁等待；
    • 如果没有标记为删除，则报 1062 duplicate key 错误；
    • 如果没发生唯一键冲突，则完成插入；
    • 如果发生唯一键冲突，则判断该键值是否已被加锁；
  • 插入记录并对记录加 X 记录锁；
    • 如果该间隙已经被加上了 gap lock 或 next-key lock，加锁失败，则加插入意向锁等待；
    • 如果没有，加锁成功，则完成插入。


需要注意的是正常情况下插入操作不加锁，准确说是加隐式锁。

隐式锁的定义如下所示：

事务 T 要插入一条记录 R，只要即将插入记录的目标位置没有被其它事务上锁，事务 T 就不需要申请对目标位置加锁，可以直接插入记录。

事务 T 提交之前，如果其它事务出现以下 2 种情况，都必须帮助事务 T 给记录 R 加上排他锁：

  • 其它事务执行 UPDATE、DELETE 语句时扫描到了记录 R。
  • 其它事务插入的记录和 R 存在主键或唯一索引冲突。

未提交事务 T 插入的记录上，这种隐性的、由其它事务在需要时帮忙创建的锁，就是隐式锁。

隐式锁是针对被修改的 B+Tree 记录，因此都是 Record 类型的锁，不可能是 Gap 或 Next-Key 类型。

根据源码，lock_rec_convert_impl_to_expl方法中判断该记录上的事务是否活跃，如果活跃，会帮该事务将隐式锁提升为显式锁。

/*********************************************************************//**
If a transaction has an implicit x-lock on a record, but no explicit x-lock
set on the record, sets one for it. */
static
void
lock_rec_convert_impl_to_expl(...){
    // 主键索引
 if (dict_index_is_clust(index)) {
  trx_id_t trx_id;

  trx_id = lock_clust_rec_some_has_impl(rec, index, offsets);
  // 判断事务是否是活跃状态
  trx = trx_rw_is_active(trx_id, NULL, true);
 } else { // 二级索引
        ...
 }

 if (trx != 0) {
  ulint heap_no = page_rec_get_heap_no(rec);

  ut_ad(trx_is_referenced(trx));

  /* If the transaction is still active and has no
  explicit x-lock set on the record, set one for it.
  trx cannot be committed until the ref count is zero. */
  // 把 rec 记录上的隐式锁转换为显式锁
  lock_rec_convert_impl_to_expl_for_trx(
   block, rec, index, offsets, trx, heap_no);
 }
}

MySQL 8.0 中查看performance_schema.data_locks表，显示只有一个表锁。

事务在请求 S 锁和 X 锁前，需要先获得对应的 IS、IX 锁。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1083:140122972540608
ENGINE_TRANSACTION_ID: 2032637
            THREAD_ID: 172
             EVENT_ID: 52
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       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
1 row in set (0.00 sec)

死锁日志显示文中死锁相关的锁包括插入意向锁与间隙锁，这两种锁的作用分别是什么呢？

插入意向锁与间隙锁

插入意向锁的作用是什么？

根据官方文档，插入意向锁是在插入操作一开始获取的一种特殊的间隙锁。

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion.

根据源码注释，插入操作时如果需要申请 X 锁，如果其他事务持有间隙锁，就会给 X 锁打上 LOCK_INSERT_INTENTION 标记，因此插入意向锁等待表示插入操作的等待状态。

#define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waiting
    gap type record lock request in order to let
    an insert of an index record to wait until
    there are no conflicting locks by other
    transactions on the gap; note that this flag
    remains set when the waiting lock is granted,
    or if the lock is inherited to a neighboring
    record */

根据源码，lock_rec_insert_check_and_lock方法中检查是否有与插入意向锁冲突的锁，如果有冲突，将插入意向锁加入到锁等待队列。

 /* If another transaction has an explicit lock request which locks
 the gap, waiting or granted, on the successor, the insert has to wait.*/

 const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;

 const lock_t* wait_for = lock_rec_other_has_conflicting(
    type_mode, block, heap_no, trx);

 if (wait_for != NULL) {

  RecLock rec_lock(thr, index, block, heap_no, type_mode);

  trx_mutex_enter(trx);

  err = rec_lock.add_to_waitq(wait_for);

  trx_mutex_exit(trx);

 } else {
  err = DB_SUCCESS;
 }

插入意向锁等待的触发条件时什么？

插入意向锁与间隙锁冲突，因此如果插入操作中检测到有间隙锁，触发插入意向锁等待。

注意插入意向锁（LOCK_INSERT_INTENTION）虽然名字也叫锁，但它只是一个标记，并不会脱离 LOCK_X 单独存在。 插入记录之前，如果需要申请加 X 锁，都会带上 LOCK_INSERT_INTENTION 标记，表示插入记录正在等待获取 X 锁。 所以，insert intention waiting 并不表示在等待插入意向锁，只表示插入记录正在等待获取 X 锁。


间隙锁的作用是什么？

间隙锁用于解决幻读现象，通过给索引项之间的间隙加锁的方式防止插入幻影记录。

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

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

间隙锁之间兼容，因此多个事务可以同时读取同一间隙。


插入意向锁与间隙锁的区别是什么？

  • 普通的间隙锁不允许在间隙插入数据
  • 插入意向锁允许在间隙插入数据

插入意向锁之间兼容，因此多个事务可以在相同的索引间隙插入而不需要等待，只要不是插入间隙中相同的位置。可以提高并发插入性能。


测试插入意向锁与间隙锁。

首先再次查看下测试表的数据。

mysql> show create table t_unique G
*************************** 1. row ***************************
       Table: t_unique
Create Table: CREATE TABLE `t_unique` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` int DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from t_unique ;
+----+------+
| id | age  |
+----+------+
|  1 |    1 |
|  5 |    5 |
| 10 |   10 |
+----+------+
3 rows in set (0.00 sec)

事务1锁定读一条不存在的记录（5<6<10），事务2在同一间隙（5-10）插入记录，发生锁等待。

一个事务在执行INSERT操作时，如果即将插入的间隙已经被其他事务加了gap锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下INSERT操作是不加锁的。

session 1 session 2
begin;
select * from t_unique where id=8 for update;

begin;
insert into t_unique(id, age) values(6,6);
阻塞，等待事务1中唯一键2的间隙锁

事务1执行select for update后，查看performance_schema.data_locks表，显示持有主键的间隙锁。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1083:140122972540608
ENGINE_TRANSACTION_ID: 2032743
            THREAD_ID: 212
             EVENT_ID: 21
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       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:21:4:4:140122972537552
ENGINE_TRANSACTION_ID: 2032743
            THREAD_ID: 212
             EVENT_ID: 21
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
2 rows in set (0.00 sec)

查看show engine innodb status，结果同样显示持有间隙锁。

---TRANSACTION 2032743, ACTIVE 186 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 139, OS thread handle 140122958722816, query id 3002102 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 2032743 lock mode IX
RECORD LOCKS space id 21 page no 4 n bits 72 index PRIMARY of table `test_zk`.`t_unique` trx id 2032743 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000001f03e0; asc       ;;
 2: len 7; hex 8100000121012a; asc     ! *;;
 3: len 4; hex 8000000a; asc     ;;

事务2锁等待后再次查看，显示新增X,GAP,INSERT_INTENTION锁等待。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070939944:1083:140122972552704
ENGINE_TRANSACTION_ID: 2032744
            THREAD_ID: 213
             EVENT_ID: 19
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140122972552704
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070939944:21:4:4:140122972549600
ENGINE_TRANSACTION_ID: 2032744
            THREAD_ID: 213
             EVENT_ID: 19
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972549600
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: 10
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1083:140122972540608
ENGINE_TRANSACTION_ID: 2032743
            THREAD_ID: 212
             EVENT_ID: 21
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       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:21:4:4:140122972537552
ENGINE_TRANSACTION_ID: 2032743
            THREAD_ID: 212
             EVENT_ID: 21
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10
4 rows in set (0.00 sec)

注意这里插入意向锁显示LOCK_MODE: X,GAP,INSERT_INTENTION。

实际上，插入意向锁也可能显示为LOCK_MODE: X,INSERT_INTENTION。

事务1锁定读一条不存在的记录（10<12），事务2在同一间隙（10, +∞）插入记录，同样发生锁等待。

session 1 session 2
begin;
select * from t_unique where id=12 for update;

begin;
insert into t_unique(id, age) values(13,13);
阻塞，等待事务1中唯一键2的间隙锁

事务1执行select for update后，查看performance_schema.data_locks表，显示持有主键的行锁，而不是间隙锁。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1083:140122972540608
ENGINE_TRANSACTION_ID: 2032787
            THREAD_ID: 227
             EVENT_ID: 39
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       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:21:5:1:140122972537552
ENGINE_TRANSACTION_ID: 2032787
            THREAD_ID: 227
             EVENT_ID: 39
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_age
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
2 rows in set (0.00 sec)

行锁没有退化为间隙锁的原因是由于 next-key lock 本身是一个左开右闭区间，而 supremum record 记录并不存在，因此不存在记录锁，也就无法退化为记录锁。

因此尽管对于唯一索引，Next-key Lock 会退化为记录锁。但是对于 supremum record，next-key lock 并不会退化为记录锁。

查看show engine innodb status，显示持有行锁 next-key lock。

---TRANSACTION 2032787, ACTIVE 87 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 154, OS thread handle 140122909542144, query id 3002345 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 2032787 lock mode IX
RECORD LOCKS space id 21 page no 5 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 2032787 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锁等待后再次查看，显示新增X,INSERT_INTENTION锁等待。

mysql> select * from performance_schema.data_locks G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070939136:1083:140122972546688
ENGINE_TRANSACTION_ID: 2032789
            THREAD_ID: 230
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140122972546688
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070939136:21:5:1:140122972543584
ENGINE_TRANSACTION_ID: 2032789
            THREAD_ID: 230
             EVENT_ID: 23
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_age
OBJECT_INSTANCE_BEGIN: 140122972543584
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140123070938328:1083:140122972540608
ENGINE_TRANSACTION_ID: 2032787
            THREAD_ID: 227
             EVENT_ID: 39
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       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:21:5:1:140122972537552
ENGINE_TRANSACTION_ID: 2032787
            THREAD_ID: 227
             EVENT_ID: 39
        OBJECT_SCHEMA: test_zk
          OBJECT_NAME: t_unique
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: uk_age
OBJECT_INSTANCE_BEGIN: 140122972537552
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
4 rows in set (0.00 sec)

并发 insert 导致死锁

根据官方文档，并发 insert 在以下两种典型场景下可能导致死锁：

  • insert 回滚
    • insert 操作持有 X 级记录锁
    • 事务 2 与 3 insert 唯一键冲突等待 S 级行锁
    • 事务 1 回滚，事务 2 与 3 形成死锁
  • delete 提交
    • delete 操作持有 X 级记录锁
    • 事务 2 与 3 insert 唯一键冲突等待 S 级行锁
    • 事务 1 提交，事务 2 与 3 形成死锁

The first operation by session 1 acquires an exclusive lock for the row. The operations by sessions 2 and 3 both result in a duplicate-key error and they both request a shared lock for the row. When session 1 rolls back, it releases its exclusive lock on the row and the queued shared lock requests for sessions 2 and 3 are granted. At this point, sessions 2 and 3 deadlock: Neither can acquire an exclusive lock for the row because of the shared lock held by the other.

小技巧

innodb_status_output_locks

MySQL 5.6.16 引入 innodb_status_output_locks 变量用于开启 InnoDB Lock Monitor。

开启以后，就可以从show engine innodb status的结果中的TRANSACTIONS中看到事务持有的锁。

默认关闭

mysql> show variables like '%output%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_status_output       | OFF   |
| innodb_status_output_locks | OFF   |
| log_output                 | FILE  |
+----------------------------+-------+
3 rows in set (0.00 sec)

执行 SQL

mysql> select * from t_unique where id=12 for update;
Empty set (0.00 sec)

查看事务信息，无法看到持有的锁。

---TRANSACTION 2032782, ACTIVE 12 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 153, OS thread handle 140122958722816, query id 3002304 127.0.0.1 admin

开启

mysql> set global innodb_status_output_locks=1;
Query OK, 0 rows affected (0.00 sec)

再次查看事务信息，可以看到持有的锁。

---TRANSACTION 2032782, ACTIVE 110 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 153, OS thread handle 140122958722816, query id 3002307 127.0.0.1 admin
TABLE LOCK table `test_zk`.`t_unique` trx id 2032782 lock mode IX
RECORD LOCKS space id 21 page no 4 n bits 72 index PRIMARY of table `test_zk`.`t_unique` trx id 2032782 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;;

RECORD LOCKS space id 21 page no 4 n bits 72 index PRIMARY of table `test_zk`.`t_unique` trx id 2032782 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000001f03e0; asc       ;;
 2: len 7; hex 8100000121012a; asc     ! *;;
 3: len 4; hex 8000000a; asc     ;;

结论

正常情况下，insert 语句不加锁，尽管要插入的记录表中还没有。

至少有两个原因可以说明 INSERT 加了锁：

  • 为了防止幻读，如果记录之间加有 GAP 锁，此时不能 INSERT；
  • 如果 INSERT 的记录和已有记录造成唯一键冲突，此时不能 INSERT；

要解决这两个问题，都是靠锁来解决的（第一个加插入意向锁，第二个加 S 锁进行当前读）。

实际上 insert 操作使用隐式锁，隐式锁不代表没有锁，而是在需要时由其它事务帮忙创建锁，通过延迟加锁的机制减少加锁的数量。


本文死锁案例的日志显示锁结构：

  • 事务 2，lock_mode X locks gap before rec insert intention waiting
  • 事务 3，lock mode S locks gap before rec Record lock
  • 事务3，lock_mode X locks gap before rec insert intention waiting

经分析，死锁的触发条件为：

  • 三个事务 insert，唯一键冲突；
  • 事务 1 回滚后事务 2 与事务 3 分别持有 S 级间隙锁，并分别等待 X 级记录锁。


注意插入意向锁（LOCK_INSERT_INTENTION）虽然名字也叫锁，但它只是一个标记，并不会脱离 LOCK_X 单独存在。 插入记录之前，如果需要申请加 X 锁，都会带上 LOCK_INSERT_INTENTION 标记，表示插入记录正在等待获取 X 锁。 所以，insert intention waiting 并不表示在等待插入意向锁，只表示插入记录正在等待获取 X 锁。


因此，insert 操作可能需要持有的锁包括：

  • 插入前判断插入间隙是否有锁，如果有锁，发生插入意向锁等待；
  • 插入时判断是否发生唯一键冲突，发生冲突时，如果有未提交事务写入或已提交事务未 purge 导致行被锁定，发生 S 级锁等待；
  • 实际插入时需要对插入记录加 X 记录锁，如果插入间隙有间隙锁，发生插入意向锁等待。

本文死锁发生在实际插入时，事务 2 与 3 分别持有 S 级 (1, 5) 的间隙锁，并申请 X 级 2 的记录锁，X 锁与 S 锁冲突，因此锁等待，然后标记为插入意向锁等待。

待办

  • 插入意向锁的加锁时间
  • 隐式锁
  • 锁继承机制

参考教程

  • Locks Set by Different SQL Statements in InnoDB
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html
  • MySQL 死锁套路：一次诡异的批量插入死锁问题分析
https://heapdump.cn/article/2342623
https://www.aneasystone.com/archives/2018/06/insert-locks-via-mysql-source-code.html
  • 常见 SQL 语句的加锁分析
http://www.seiang.com/?p=682
  • MySQL锁系列（七）之 锁算法详解
http://keithlan.github.io/2017/06/21/innodb_locks_algorithms/


原文始发于微信公众号（丹柿小院）：MySQL 并发 insert 导致间隙锁与插入意向锁形成死锁

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

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

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

相关推荐

发表回复

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