content_image/849569.jpg
## Correlating InnoDB Transactions with MySQL Sessions 

Sometimes it is useful to correlate internal InnoDB  locking information with the session-level information maintained by MySQL. For example, you might like to know, for a given InnoDB  transaction ID, the corresponding MySQL session ID and name of the session that may be holding a lock, and thus blocking other transactions. 

The following output from the INFORMATION_SCHEMA INNODB_TRX  table and Performance Schema data_locks  and data_lock_waits  tables is taken from a somewhat loaded system. As can be seen, there are several transactions running. 

The following data_locks  and data_lock_waits  tables show that: 

•Transaction 77F  (executing an INSERT ) is waiting for transactions 77E , 77D , and 77B  to commit. 

•Transaction 77E  (executing an INSERT ) is waiting for transactions 77D  and 77B  to commit. 

•Transaction 77D  (executing an INSERT ) is waiting for transaction 77B  to commit. 

•Transaction 77B  (executing an INSERT ) is waiting for transaction 77A  to commit. 

•Transaction 77A  is running, currently executing SELECT . 

•Transaction E56  (executing an INSERT ) is waiting for transaction E55  to commit. 

•Transaction E55  (executing an INSERT ) is waiting for transaction 19C  to commit. 

•Transaction 19C  is running, currently executing an INSERT . 

## Note 

There may be inconsistencies between queries shown in the INFORMATION_SCHEMA PROCESSLIST  and INNODB_TRX  tables. For an explanation, see Section 15.15.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information” . 

The following table shows the contents of the PROCESSLIST  table for a system running a heavy workload. 

<img src='content_image/849569.jpg'>

