MySQL Forums
Forum List  »  InnoDB

Deadlock information incomplete
Posted by: Vasudevan Sankaran
Date: August 26, 2015 09:52AM

I am investigating a deadlock in our application and find that the MySQL log is providing insufficient information. Specifically the LATEST DETECTED DEADLOCK section from SHOW ENGINE INNODB STATUS\G is copied below.
Transaction 2 - We see lock HOLDS and WAITING information,
Transaction 1 - only WAITING section.
What is Transaction 1 holding and where is the cycle to cause deadlock?

The relevant table definitions are as follows.

CREATE TABLE IF NOT EXISTS SystemSequence (
NextId int NOT NULL AUTO_INCREMENT PRIMARY KEY
) AUTO_INCREMENT = 1;

CREATE TABLE IF NOT EXISTS CustomSystem (
SystemID int NOT NULL PRIMARY KEY,
NetworkAddress varchar (255),
Port int NULL ,
PartID int NOT NULL,

CONSTRAINT FOREIGN KEY (SystemID) REFERENCES System (ID),
CONSTRAINT FOREIGN KEY (PartID) REFERENCES Parts (ID)
);

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-08-25 20:24:58 0x1378
*** (1) TRANSACTION:
TRANSACTION 5564, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 2280, query id 6587 localhost 127.0.0.1 CoreSvc updating
delete from SystemSequence where NextId < _nextId
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 3 n bits 72 index `PRIMARY` of table `pangaea`.`systemsequence` trx id 5564 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32

0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000015bb; asc ;;
2: len 7; hex 3500000177102e; asc 5 w .;;

*** (2) TRANSACTION:
TRANSACTION 5563, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 10, locked 10
17 lock struct(s), heap size 1136, 9 row lock(s), undo log entries 8
MySQL thread id 30, OS thread handle 4984, query id 6677 localhost 127.0.0.1 CoreSvc update
insert CustomSystem (SystemID, NetworkAddress,Port, PartID )
values (_SystemID, _NetworkAddress, _Port, _PartID )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 50 page no 3 n bits 72 index `PRIMARY` of table `pangaea`.`systemsequence` trx id 5563 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32

0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000015bb; asc ;;
2: len 7; hex 3500000177102e; asc 5 w .;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000000015bb; asc ;;
2: len 7; hex b5000001280110; asc ( ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 68 page no 7 n bits 72 index `PartID` of table `pangaea`.`customsystem` trx id 5563 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)
------------

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock information incomplete
2236
August 26, 2015 09:52AM
966
August 27, 2015 11:35AM
802
August 29, 2015 11:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.