From “show engine innodb status” LATEST DETECTED DEADLOCK section we never have statements for both transaction. Always only for one of them.
For example:
2014-05-21 00:06:48 7f8f48127700
*** (1) TRANSACTION:
TRANSACTION 2307529851, ACTIVE 7 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 3112, 7 row lock(s), undo log entries 10
MySQL thread id 7592416, OS thread handle 0x7f8f33de4700, query id 15406475198 10.58.0.147 database1 Searching rows for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 97 page no 237594 n bits 456 index i_certainindex of table database1.certaintable trx id 2307529851 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 2307529808, ACTIVE 8 sec updating or deleting
mysql tables in use 1, locked 1
25 lock struct(s), heap size 3112, 189 row lock(s), undo log entries 122
MySQL thread id 7266970, OS thread handle 0x7f8f48127700, query id 15406475505 10.58.0.147 database1 updating
update certaintable set program_status=‘INACTIVE’, date_expired=‘2014-05-21 00:06:48’ where (t_id in (52088)) and program_status=‘ACTIVE’ and (day_of_week_local in (0 , 1 , 2 , 3 , 4 , 5 , 6))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 97 page no 237594 n bits 456 index i_certainindex of table database1.certaintable trx id 2307529808 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 97 page no 237594 n bits 464 index i_certainindex of table database1.certaintable trx id 2307529808 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
Sometimes there is no statement for committed transaction, sometimes not for rollbacked but always only one
We run Percona server version 5.6.17-65.0-56-log
Any ideas?