Mother Ship的笔记

业务场景：微信支付模块，每一次请求都会在pay_log表里插入订单号等记录，支付回调时会更新pay_log表的记录

同时还有一个定时任务用来定时将所有1小时前的订单标记为交易超时

编写单元测试，模拟支付回调通知，结果出了异常，更新记录时出现死锁

大致SQL如下：

事务A

Update pay_log SET tradeState = 2 WHERE tradeState=0 AND createTime < '2019-08-20 15:23:04.167'

事务B

INSERT pay_log VALUES(NULL,?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?) 
Update pay_log SET tradeState =? WHERE outTradeNo = ? AND tradeState = ? AND payType = ? 

然后事务B被回滚了

解决方案：要么禁用定时任务，要么去掉单元测试的事务手动回滚，要么单元测试不要打断点调试（定时任务设置了项目启动后10秒后启动）

附上MySQL死锁日志，其实这玩意只能大致的定位故障，因为可以看出事务2的SQL只有最后执行的那一条

2019-08-20 16:23:07 0x7ff1b2039700
*** (1) TRANSACTION:
TRANSACTION 43869195, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 269 row lock(s)
MySQL thread id 746830, OS thread handle 140676071700224, query id 184518127 192.168.3.108 root updating
Update pay_log SET tradeState = 2 WHERE tradeState=0 AND createTime < '2019-08-20 15:23:04.167'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3179 page no 7 n bits 104 index PRIMARY of table `pay`.`pay_log` trx id 43869195 lock_mode X waiting
Record lock, heap no 37 PHYSICAL RECORD: n_fields 17; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 43869181, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 747008, OS thread handle 140676050425600, query id 184518193 192.168.3.49 root updating
Update pay_log SET tradeState =1 WHERE outTradeNo = 'T9744695806' AND tradeState = 0 AND payType = 0

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3179 page no 7 n bits 104 index PRIMARY of table `pay`.`pay_log` trx id 43869181 lock_mode X locks rec but not gap
Record lock, heap no 37 PHYSICAL RECORD: n_fields 17; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3179 page no 4 n bits 168 index PRIMARY of table `pay`.`pay_log` trx id 43869181 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
 
*** WE ROLL BACK TRANSACTION (2)