MySQL Bugs: #59678: INFORMATION_SCHEMA.INNODB_TRX Does not contain all InnoDB transactions

Bug #59678	INFORMATION_SCHEMA.INNODB_TRX Does not contain all InnoDB transactions
Submitted:	23 Jan 2011 9:14	Modified:	6 Apr 2011 13:15
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.8, 5.6.2m5	OS:	Any
Assigned to:	Vasil Dimov	CPU Architecture:	Any
Tags:	information_schema, innodb, innodb_trx

Description:
The manual says for the INNODB_TRX table:
http://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-transactions.html#innodb-...

"Contains information about every transaction currently executing inside InnoDB,.."

This is not true. It seems that transactions "Waiting for table metadata lock" are not shown. This might be a special case for a transaction, but it's still a transaction.

mysql> SHOW ENGINE INNODB STATUS\G
..
---TRANSACTION 15FB2A, not started, OS thread id 4895383552
MySQL thread id 12, query id 257 localhost root Waiting for table metadata lock
DROP TABLE t1
---TRANSACTION 15FB2C, ACTIVE 213 sec, OS thread id 4895109120
2 lock struct(s), heap size 376, 4 row lock(s)
MySQL thread id 7, query id 270 localhost root
SHOW ENGINE INNODB STATUS
..

How to repeat:
In one connection: start a transaction doing some DML on a record.
In another connection, drop the table.

The SHOW ENGINE output will show more transaction than in the INFORMATION_SCHEMA.INNODB_TRX table.

Suggested fix:
Either mention in manual that not all transactions are in INNODB_TRX, or fix that all transaction are available in the table.

With 5.5.9 on Mac OS X I do not see DROP TABLE in the SHOW ENGINE INNODB STATUS results:

...
------------
TRANSACTIONS
------------
Trx id counter F02
Purge done for trx's n:o < D08 undo n:o < 0
History list length 3
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, OS thread id 2961059840
MySQL thread id 3, query id 54 localhost root
show engine innodb status
---TRANSACTION F01, ACTIVE 197 sec, OS thread id 2960650240
1 lock struct(s), heap size 320, 0 row lock(s), undo log entries 1
MySQL thread id 1, query id 17 localhost root
--------
FILE I/O
--------
...

Verified on 5.6.2MR. Many transactions in 'Waiting for table metadata lock' (which do show in SHOW ENGINE INNODB STATUS) are missing from INFORMATION_SCHEMA.INNODB_TRX. See attached output.

Transaction output difference between SHOW ENGINE INNODB STATUS and INFORMATION_SCHEMA.INNODB_TRX

Attachment: difference.txt (text/plain), 44.10 KiB.

Transactions in "Waiting for table metadata lock" state are not active in InnoDB.