Oct 10, 2018

How to get Active Transactions and Locks from MySQL

To get a list of active transactions and locks that are currently executing against our target database

MySQL captures transaction and lock information in the INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS INFORMATION_SCHEMA tables.
  • INNODB_TRX provides us with information on the currently executing transactions.
  • INNODB_LOCKS gives us information on current lock.
  • INNODB_LOCK_WAITS provides information on who is actually waiting for locks. 
The query below enables one to see active locks filtered by a particular database: