CODEWITHSUNDEEP
mysqldeadlock
Ksenia
Ksenia

Reputation: 3753

Deadlock in MySQL: engine.log analyze

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

I have the engine.log file with the 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 FOR THIS LOCK TO BE 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 but 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 THE 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 but not gap
Record lock, heap no 27 PHYSICAL RECORD: n_fields 72; compact format; info bits 0
...

 *** (2) WAITING FOR THIS LOCK TO BE 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 but not gap waiting

...

 *** WE ROLL BACK TRANSACTION (1)

And my vision of what is described in the logs is the following:

1. Transaction №2 initially has one lock (and the type of the lock isn't clear from logs):

*** (2) HOLDS THE 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 but not gap

Record lock, heap no 27 PHYSICAL RECORD: n_fields 72; compact format; info bits 0

2. Transaction №1 is trying to get the lock of S type:

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS ... trx id 4 2719072253 lock mode S locks rec but not gap waiting

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

3. Then transaction №2 is trying to get the lock of X type:

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS ... trx id 4 2719072205 lock_mode X locks rec but not gap waiting

and after unsuccessful attempt it begins to wait until transaction №1 gets the S lock and release it.

Do I correctly understand logs, or is my interpretation wrong?

Upvotes: 4

Views: 1898

Answers (1)

Shadow
Shadow

Reputation: 34230

Your interpretation is almost correct. A few thoughts to add:

  • In innodb there are two basic types of locks: shared (S) and exclusive (X). If a transaction holds an X lock on a record, then neither S or X locks can be granted on the same records until the transaction completes. If a transaction holds an S lock on a record, then another S lock can be granted immediately, but an X lock request must wait until the 1st transaction completes.

  • The 2nd transaction being an update holds an exclusive (X) lock on the index records that are being updated. Therefore the 1st transaction cannot get hold of an S lock on the same records. However, the 2nd transaction is waiting for X lock to be granted on a different set of records (page no is different from the other locks').

  • The excerpt does not tell us what transaction locked the records that the 2nd transaction is waiting for. We can only presume that it was the 1st transaction - otherwise it would not be a deadlock.

Upvotes: 6

Related Questions