1

MySQL死锁案例分析(一)

 1 year ago
source link: https://www.wencst.com/archives/818
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

二、死锁输出

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)

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK