Deadlock in MySQL: engine.log analyze -

how сomprehend reason of deadlock - namely, how find out transactions have captured locks?

i have engine.log file following deadlock:

------------------------ latest detected deadlock ------------------------ 170327 11:09:53 *** (1) transaction: transaction 4 2719072253, active 5 sec, os thread id 26215 starting index read ... insert into... (the first transaction)  *** (1) waiting lock granted: record locks space id 0 page no 36025889 n bits 96 index `primary` of table `mydb`.`mytable` trx id 4 2719072253 lock mode s locks rec not gap waiting  ... *** (2) transaction: transaction 4 2719072205, active 35 sec, os thread id 25564 starting index read, thread declared inside innodb 485  update ... (the second transaction)  *** (2) holds lock(s): record locks space id 0 page no 36025889 n bits 96 index `primary` of table `mydb`.`mytable` trx id 4 2719072205 lock_mode x locks rec not gap record lock, heap no 27 physical record: n_fields 72; compact format; info bits 0 ...   *** (2) waiting lock granted:  record locks space id 0 page no 42767646 n bits 120 index `primary` of table `mydb`.`mytable` trx id 4 2719072205 lock_mode x locks rec not gap waiting  ...   *** roll transaction (1) 

and vision of described in logs following:

1. transaction №2 has 1 lock (and type of lock isn't clear logs):

*** (2) holds lock(s)

record locks space id 0 page no 36025889 n bits 96 index primary of table mydb.mytable trx id 4 2719072205 lock_mode x locks rec not gap

record lock, heap no 27 physical record: n_fields 72; compact format; info bits 0

2. transaction №1 trying lock of s type:

*** (1) waiting lock granted:

record locks ... trx id 4 2719072253 lock mode s locks rec not gap waiting

and after unsuccessful attempt begins wait releasing of transaction №2 lock;

3. transaction №2 trying lock of x type:

*** (2) waiting lock granted:

record locks ... trx id 4 2719072205 lock_mode x locks rec not gap waiting

and after unsuccessful attempt begins wait until transaction №1 gets s lock , release it.

do correctly understand logs, or interpretation wrong?

your interpretation correct. few thoughts add:

• in innodb there two basic types of locks: shared (s) , exclusive (x). if transaction holds x lock on record, neither s or x locks can granted on same records until transaction completes. if transaction holds s lock on record, s lock can granted immediately, x lock request must wait until 1st transaction completes.

• the 2nd transaction being update holds exclusive (x) lock on index records being updated. therefore 1st transaction cannot hold of s lock on same records. however, 2nd transaction waiting x lock granted on different set of records (page no different other locks').

• the excerpt not tell transaction locked records 2nd transaction waiting for. can presume 1st transaction - otherwise not deadlock.