MySQL死锁案例分析（一）

二、死锁输出

2017-10-10 17:07:21 7f45a5104700InnoDB: transactions deadlock detected, dumping detailed information.
2017-10-10 17:07:21 7f45a5104700
*** (1) TRANSACTION:
TRANSACTION 47225424098, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 40396441, OS thread handle 0x7f569a68e700, query id 9746347697 10.200.181.72 trade updating
update table_b
        set updated_at = now(),
         price = 36900,
        where id = 1 and sku_id = 36171933 AND goods_id = 2
        and kdt_id = 3 and offline_id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424098 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 47225424090, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1184, 13 row lock(s), undo log entries 1
MySQL thread id 40397515, OS thread handle 0x7f45a5104700, query id 9746347700 10.200.181.72 trade updating
update table_a
        set updated_at = now(),
        stock_num = 0,
        where goods_id = 2
        and offline_id = 1
        and kdt_id = 3
        and id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 13387 page no 67 n bits 344 index `PRIMARY` of table `dbname`.`table_b` trx id 47225424090 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 13451 page no 193 n bits 192 index `PRIMARY` of table `dbname`.`table_a` trx id 47225424090 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)