Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

Question

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS from our DB:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-09-29 17:00:30 7f9eb7410700
*** (1) TRANSACTION:
TRANSACTION 2842247584, ACTIVE 0 sec, thread declared inside InnoDB 4998
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 26 row lock(s)
MySQL thread id 69517881, OS thread handle 0x7f9ee07bd700, query id 11929163563 cfsender01.cf.intela.local 192.168.20.116 cfsender01 updating
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
...

Notice that there is no SQL statement listed in transaction (1).

Does anyone know why this might happen, or what the lack of a sql statement in the show engine innodb status would indicate about the transaction?

FWIW, transaction (1) was chosen as the victim by MySQL and rolled back.

Also, we're running MySQL Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618.

Answer 1

What language is the client written in? You should test for errors after every statement (including the COMMIT) in every transaction. That is the way to locate the problem. SHOW ENGINE InnoDB STATUS should not, as you see, be depended upon. More significantly, it won't necessarily catch all deadlocks.

Answer 2

Because the query has finished and InnoDB doesn't know anymore what query it was. SHOW ENGINE InnoDB STATUS does not guarantee to give you this information.

However you have the thread id and you know at what time the deadlock was detected. You can use mysqlbinlog to check which queries that thread executed at that time or before that. Even if you use the ROW format (recommended), you will still see useful information.

Or, maybe you have the slow log enabled. You probably have that query logged - especially if you have long_query_time=0, which I recommend.