Trying to understand MySQL deadlock on InnoDB table

Question

I'm trying to understand a deadlock found by MySQL during concurrent clients processing on the same tables. This is the interesting part of the "SHOW InnoDB STATUS" command:

------------------------
LATEST DETECTED DEADLOCK
------------------------
120704 16:17:51
*** (1) TRANSACTION:
TRANSACTION 0 3547576, ACTIVE 0 sec, process no 10886, OS thread id 140547111458560 inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 41941, query id 1725666 localhost testsuite update
insert into `INode` (`status`,`_type`,`group`,`ctime`,`parent`,`shared`,`basename`,`_rowid`,`displayname`,`user`,`content_type`,`mtime`,`position`,`atime`,`size`) values ('Published','Group','12','2012-07-04 16:17:48.996869','2',null,'1','12','1','3','application/x-empty','2012-07-04 16:17:48.996896','1','2012-07-04 16:17:48.996914',null)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5554 n bits 80 index `INodeparent_basename` of table `testsuite`.`INode` trx id 0 3547576 lock mode S waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;; 1: len 1; hex 31; asc 1;; 2: len 4; hex 8000000b; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 0 3547575, ACTIVE 0 sec, process no 10886, OS thread id 140547107845888 inserting, thread declared inside InnoDB 493
mysql tables in use 1, locked 1
13 lock struct(s), heap size 3024, 17 row lock(s), undo log entries 21
MySQL thread id 41940, query id 1725808 localhost testsuite update
replace into `INode` (`status`,`_type`,`position`,`group`,`ctime`,`parent`,`basename`,`_rowid`,`displayname`,`user`,`content_type`,`mtime`,`shared`,`atime`,`size`) values ('Published','Group','0','2','2012-07-04 16:17:49','1','groups','2','admin','3','application/x-empty','2012-07-04 16:17:49',null,'2012-07-04 16:17:49',null),('Published','Group','1','11','2012-07-04 16:17:51.064074','2','1','11','1','3','inode/directory','2012-07-04 16:17:51.064074',null,'2012-07-04 16:17:51.064074',null)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 5554 n bits 80 index `INodeparent_basename` of table `testsuite`.`INode` trx id 0 3547575 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 67726f757073; asc groups;; 2: len 4; hex 80000002; asc     ;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;; 1: len 1; hex 31; asc 1;; 2: len 4; hex 8000000b; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 5554 n bits 80 index `INodeparent_basename` of table `testsuite`.`INode` trx id 0 3547575 lock_mode X waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000002; asc     ;; 1: len 1; hex 31; asc 1;; 2: len 4; hex 8000000b; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

What I understand from this log is that the transaction (1) is waiting to get a shared lock on the index INodeparent_basename of table testsuite.INode. Now, transaction (2) has an exclusive lock over this same very index. But what is strange, is that transaction (2) is also waiting for an X lock over given table. Why is that? If transaction (2) already has the lock, why does it wait for it?

(in general, I haven't found any documentation which exactly explains how to read the output of the status command - it would be interesting if somebody could point that out to me too)

Answer 1

In this case, transaction 2 acquired a shared lock for a previous statement.(* (2) HOLDS THE LOCK(S):)

Then, transaction 1 attempted to acquire an exclusive lock on the same row, and is waiting for the shared lock to be removed.

Then, transaction 2, in another statement, attempted to acquire an exclusive lock on the same row. Deadlock occured.

Answer 2

I don't know if it will help or not, but one thing to consider is the sequence of operations that cause locking. It appears that in one location of the app you are creating locks such as

Table 1 Table 2

and in another doing

Table 2 Table 1 (just example)

Without seeing the actual code, I suggest looking into things like this and ensuring you do them in the same sequence of working / updating / inserting records so they all work something like

insert/update from table 1 then insert/update table 2

This way, first lock is always attempted on table 1 first.. if that can't go, then it doesn't even try table 2 until table 1 is released.

Then, apply whatever changes to table 2 and finish your transaction. When both table 2 and 1 are then released, the next transaction waiting for release on table 1 can then proceed.