mysql三例死锁场景分析

一、商户批量代发业务

1.表结构（简化）：

CREATE TABLE `batch` (

 `batch_id` varchar(32) NOT NULL COMMENT '批次号',

 `cash_id` varchar(32) NOT NULL COMMENT '明细单号',

 `status` int(11) NOT NULL COMMENT '状态：0初始化，1已处理，2成功，3处理中，4失败，5 入库',

 PRIMARY KEY (`cash_id`),

  KEY`idx_batch_id` (`batch_id`),

KEY `idx_status` (`status`)

) ENGINE=InnoDB

2.产生死锁的两个事务：

tx1

update batch set status=0 wherebatch_id=’142’ and status=5

tx2

begin

insert …..

insert into batch (batch_id,cash_id, status)values(‘143’, ‘385’,5)

insert into batch (batch_id,cash_id, status)values(‘143’, ‘386’,5)

insert …..

 

3.死锁日志如下：

*** (1) TRANSACTION:

TRANSACTION 1576868964, ACTIVE 0 sec fetching rows

mysql tables in use 3, locked 3

LOCK WAIT 10 lock struct(s), heap size 2936, 103 rowlock(s), undo log entries 1

MySQL thread id 604347, OS thread handle0x7faf68903700, query id 2125933789 192.168.40.214 pay Searching rows forupdate

update pay_batch_paid

       set status = 0 where batch_id = '142' and status = 5

*** (1) WAITING FOR THIS LOCKTO BE GRANTED:

RECORD LOCKS space id 337 page no 963 n bits 504 index `idx_status` of table `pay`.`pay_batch_paid` trx id1576868964 lock_mode X waiting

Record lock, heap no 330 PHYSICAL RECORD: n_fields 2;compact format; info bits 0

 0: len 4; hex80000005; asc     ;;

 1: len 27; hex323031363037303631343637373932373637313432333536333835; asc 385;;

 

*** (2) TRANSACTION:

TRANSACTION 1576868930, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1184, 2 row lock(s), undolog entries 43

MySQL thread id 604176, OS thread handle0x7faf66b0d700, query id 2125933793 192.168.40.44 pay update

insert into batch (batch_id, cash_id, status)values(‘143’,‘386’,5)   

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 337 page no 963 n bits 504 index `idx_status` of table `pay`.`pay_batch_paid` trx id1576868930 lock_mode X locks rec but not gap

Record lock, heap no 330 PHYSICAL RECORD: n_fields 2;compact format; info bits 0

 0: len 4; hex80000005; asc     ;;

 1: len 27; hex323031363037303631343637373932373637313432333536333835; asc 385;;

 

*** (2) WAITING FOR THIS LOCKTO BE GRANTED:

RECORD LOCKS space id 337 page no 963 n bits 504 index`idx_status` of table `pay`.`pay_batch_paid` trx id 1576868930 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 330