Transaction Monitoring
Monitor active transactions, lock waits, and deadlocks using Performance Schema and InnoDB status.
MySQL
Intermediate
4 min read
Monitoring Transactions
Example
-- Active transactions
SELECT * FROM information_schema.INNODB_TRX\G
-- Lock waits
SELECT * FROM performance_schema.data_lock_waits\G
-- Deadlock history
SHOW ENGINE INNODB STATUS\G -- look for LATEST DETECTED DEADLOCK
-- Long-running transactions (>30s)
SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30; Pro Tip
Set innodb_print_all_deadlocks=ON in production to log every deadlock to the error log.