View blocking locks (data_lock_waits)

Command

SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread, LEFT(r.trx_query,80) AS waiting_query, b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread, LEFT(b.trx_query,80) AS blocking_query FROM performance_schema.data_lock_waits w JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;

What it does

Shows which transaction is blocked by which, with the related queries and thread IDs.

When to use it

To diagnose lock waits and slowdowns caused by deadlocks.

Caution

performance_schema.data_lock_waits arrived in MySQL 8.0; older versions use information_schema.innodb_lock_waits. To stop the blocking thread, use KILL blocking_thread;.