Within the InnoDB plugin the INFORMATION_SCHEMA.INNODB_TRX table was added, which allowed you to at least get some of the information on each transaction, but not the full breadth of information that SHOW ENGINE INNODB STATUS provided.

“This is nice..” I thought “..but why not go the whole hog..?”.. And so I set about doing that, and opened up Bug#53336. In a very short time, I was in a review process with the InnoDB team, the patch was cleaned up, and (after a little hiccup) everything has been pushed for the next milestone.

Here’s the docs notes that I wrote for it today (with a little more annotation):

Most of these are self explanatory, a lot of them duplicate the information within SHOW ENGINE INNODB STATUS (so you now no longer need to parse the output of that to get complete transaction information).

Below are some of my notes. For comparison, here’s an example of a transaction from the SHOW ENGINE INNODB STATUS statement:

trx_operation_state – corresponds to “inserting”, InnoDB’s internal transaction statetrx_tables_in_use – corresponds to “mysql tables in use 2”, the number of table locks requested by MySQL via external_lock()trx_tables_locked – corresponds to “locked 2”, the number of actual table locks taken via external_lock()trx_lock_structs – corresponds to “189 lock struct(s)”, the size of the lock struct listtrx_lock_memory_bytes – corresponds to “heap size 27968”, the number of bytes allocated to locks structstrx_rows_locked – corresponds to “54389 row lock(s)”, an estimation of the number of rows locked (delete marked rows may make it imprecise)
trx_rows_modified – corresponds to “undo log entries 2406”, the number of rows modified in the transaction (inserted, updated, deleted)

Not seen in the transaction output above:

trx_concurrency_tickets – corresponds to “thread declared inside InnoDB 89” for a transaction in SHOW ENGINE INNODB STATUS, the number of concurrency tickets remaining for the transaction when innodb_thread_concurrency != 0trx_isolation_level – the transactions isolation leveltrx_unique_checks – whether the transaction has “SET UNIQUE_CHECKS = 0”trx_foreign_key_checks – whether the transaction has “SET FOREIGN_KEY_CHECKS = 0”trx_last_foreign_key_error – if the last statement in the transaction resulted in an FK error, the error text is printed heretrx_adaptive_hash_latched – corresponds to “holds adaptive hash latch” being printed for a transaction in SHOW ENGINE INNODB STATUStrx_adaptive_hash_timeout – when innodb_adapative_hash_index is enabled (default), statements that try to get the adapative hash latch spin 10000 (BTR_SEA_TIMEOUT) times, re-trying getting the adaptive hash latch, before giving up. lower numbers here for a lot of transactions may indicate contention on the adaptive hash latch

In summary – no more having to parse SHOW ENGINE INNODB STATUS output for transaction information, now you can just do it with SQL (with all it’s aggregation goodness as well if you want to)!