Deadlock information incomplete
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)
------------