Skip to content

Latest commit

 

History

History
126 lines (109 loc) · 4.95 KB

19.md

File metadata and controls

126 lines (109 loc) · 4.95 KB
Raw

update-wait-lock-mode-x-locks-rec-but-not-gap-vs-delete-wait-lock-mode-x-holds-lock-mode-s

死锁特征

  1. update WAITING FOR lock_mode X locks rec but not gap
  2. delete WAITING FOR lock_mode X , HOLDS lock mode S

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-08-02 11:46:04 0x7f2a1409b700
*** (1) TRANSACTION:
TRANSACTION 25567, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 97, OS thread handle 139818703664896, query id 1444 10.0.56.104 root updating
UPDATE order_pay_status
        SET curr_status = 4,
        modified = now()
        WHERE
        id = 9
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 259 page no 3 n bits 72 index PRIMARY of table `med_settle_purse`.`order_pay_status` trx id 25567 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 0000000000000009; asc         ;;
 1: len 6; hex 0000000063de; asc     c ;;
 2: len 7; hex 340000021c1184; asc 4      ;;
 3: len 1; hex 81; asc  ;;
 4: len 8; hex 800000000000007b; asc        {;;
 5: len 1; hex 83; asc  ;;
 6: SQL NULL;
 7: len 1; hex 81; asc  ;;
 8: len 5; hex 99a36afc59; asc   j Y;;
 9: len 5; hex 99a3c4bb41; asc     A;;

*** (2) TRANSACTION:
TRANSACTION 25569, ACTIVE 3 sec fetching rows
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 98, OS thread handle 139818701534976, query id 1450 10.0.56.104 root Sending data
DELETE from order_pay_status
        where id in (
          select b.id from (
            select id from order_pay_status
            where id > 0
            AND DATE_FORMAT(created,'%Y-%m-%d')  <  DATE_FORMAT('2019-05-02 19:46:02.555','%Y-%m-%d')
            order by id
            limit 500
          ) b
        )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 259 page no 3 n bits 72 index PRIMARY of table `med_settle_purse`.`order_pay_status` trx id 25569 lock mode S
Record lock, heap no 3 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 0000000000000009; asc         ;;
 1: len 6; hex 0000000063de; asc     c ;;
 2: len 7; hex 340000021c1184; asc 4      ;;
 3: len 1; hex 81; asc  ;;
 4: len 8; hex 800000000000007b; asc        {;;
 5: len 1; hex 83; asc  ;;
 6: SQL NULL;
 7: len 1; hex 81; asc  ;;
 8: len 5; hex 99a36afc59; asc   j Y;;
 9: len 5; hex 99a3c4bb41; asc     A;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 259 page no 3 n bits 72 index PRIMARY of table `med_settle_purse`.`order_pay_status` trx id 25569 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
 0: len 8; hex 0000000000000009; asc         ;;
 1: len 6; hex 0000000063de; asc     c ;;
 2: len 7; hex 340000021c1184; asc 4      ;;
 3: len 1; hex 81; asc  ;;
 4: len 8; hex 800000000000007b; asc        {;;
 5: len 1; hex 83; asc  ;;
 6: SQL NULL;
 7: len 1; hex 81; asc  ;;
 8: len 5; hex 99a36afc59; asc   j Y;;
 9: len 5; hex 99a3c4bb41; asc     A;;

*** WE ROLL BACK TRANSACTION (2)

表结构

CREATE TABLE `order_pay_status` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `curr_status` tinyint(1) DEFAULT NULL COMMENT '当前支付状态，值来自account_statement.pay_status',
  `status` tinyint(1) DEFAULT NULL COMMENT '1:有效 0：无效',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 COMMENT='支付状态表';

初始数据：

INSERT INTO `order_pay_status` VALUES (1, 1, 1);
INSERT INTO `order_pay_status` VALUES (2, 1, 1);
INSERT INTO `order_pay_status` VALUES (3, 1, 1);
INSERT INTO `order_pay_status` VALUES (4, 1, 1);
INSERT INTO `order_pay_status` VALUES (9, 1, 1);
INSERT INTO `order_pay_status` VALUES (10, 1, 1);

重现步骤

Session 1 Session 2
DELETE from order_pay_status where id in ( select b.id from ( select id from order_pay_status where id > 3 order by id limit 500 ) b);// 先执行子查询加S锁
update order_pay_status set curr_status = 4 where id = 9; //lock_mode X locks rec but not gap wating,被阻塞
DELETE from order_pay_status where id in ( select b.id from ( select id from order_pay_status where id > 3 order by id limit 500 ) b);// lock_mode X waiting
ERROR 1213 (40001): Deadlock found when trying to get lock;

分析

  1. 事物A对id=9的记录加lock mode S，成功.
  2. 事物B对id=9的记录加lock_mode X locks rec but not gap waiting，等待。
  3. 事物A对id=9的记录加lock_mode X，等待。因为锁队列是先到先得的，事务B等待事务A的lock mode S锁，事务A等待事务B的lock_mode X locks rec but not gap锁，所以发生死锁。

参考

  1. 并发delete+insert duplicate-key冲突导致死锁
  2. InnoDB 事务锁系统简介