content_image/713236.jpg content_image/713237.jpg
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM performance_schema.data_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id; 

Or, more simply, use the sys  schema innodb_lock_waits  view: 

SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits; 

If a NULL value is reported for the blocking query, see Identifying a Blocking Query After the Issuing Session Becomes Idle. 

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

In the preceding table, you can identify sessions by the “waiting query”  or “blocking query”  columns. As you can see: 

•Session B (trx id A4 , thread 6 ) and Session C (trx id A5 , thread 7 ) are both waiting for Session A (trx id A3 , thread 5 ). 

•Session C is waiting for Session B as well as Session A. 

You can see the underlying data in the INFORMATION_SCHEMA INNODB_TRX  table and Performance Schema data_locks  and data_lock_waits  tables. 

The following table shows some sample contents of the INNODB_TRX  table. 

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

