Skip to content

Latest commit

 

History

History
69 lines (55 loc) · 3.32 KB

10.md

File metadata and controls

69 lines (55 loc) · 3.32 KB
Raw

delete-wait-lock-mode-x-vs-insert-wait-lock-mode-x-locks-gap-before-rec-insert-intention-holds-lock-mode-s

死锁特征

  1. delete WAITING FOR lock_mode X
  2. insert WAITING FOR lock_mode X locks gap before rec insert intention, HOLDS lock_mode S

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
141009 12:54:59
*** (1) TRANSACTION:
TRANSACTION AEE50DCB, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 6055694, OS thread handle 0x7f4345c8d700, query id 2443700084 192.168.249.154 crm_w updating
DELETE FROM crm_business WHERE serial_number = 'CH01313320'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 244 page no 817 n bits 824 index `uniq_serial_number_business_type` of table `crm`.`crm_business` trx id AEE50DCB lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION AEE50DCA, ACTIVE 0 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 6055696, OS thread handle 0x7f4344941700, query id 2443700084 192.168.249.154 crm_w update
INSERT INTO crm_business(serial_number, business_type) values ('CH01313318', 2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 244 page no 817 n bits 824 index `uniq_serial_number_business_type` of table `crm`.`crm_business` trx id AEE50DCA lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 244 page no 817 n bits 824 index `uniq_serial_number_business_type` of table `crm`.`crm_business` trx id AEE50DCA lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

表结构

CREATE TABLE `crm_business` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `serial_number` varchar(50) NOT NULL COMMENT '商户编号',
  `business_type` tinyint(1) NOT NULL COMMENT '业务类型',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_serial_number_business_type` (`serial_number`,`business_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='合作业务'

重现步骤

Session 1 Session 2
DELETE FROM crm_business WHERE serial_number = 'CH01313320' INSERT INTO crm_business(serial_number, business_type) values ('CH01313318', 2)

分析

这又是一个单语句导致死锁的案例，具体的分析还是要将 insert 语句的加锁过程进行拆解，和 delete-wait-lock-mode-x-vs-insert-wait-lock-mode-x-locks-gap-before-rec-insert-intention-holds-lock-mode-x-locks-rec-but-not-gap 一样。

分析如下：

Session 1 Session 2
INSERT INTO crm_business(serial_number, business_type) values ('CH01313318', 2); insert第1阶段：事务2申请S锁进行duplicate key进行检查。检查成功。
DELETE FROM crm_business WHERE serial_number = 'CH01313320'; 对索引加 X 锁，和 S 锁冲突，等待
INSERT INTO crm_business(serial_number, business_type) values ('CH01313318', 2); insert第2阶段：事务2开始插入数据，S锁升级为X锁，类型为insert intention。同理，X锁进入队列排队，形成循环等待，死锁产生。

参考

  1. 线上Mysql Delete 和 Insert 操作导致死锁问题分析