MySQL: Useful queries to debug locks

mysql_locks.sql

SHOW ENGINE INNODB STATUS;

-- Transactions waiting for a lock, including blocking transaction details
SELECT 
    waiting_trx.trx_id AS waiting_trx_id,
    waiting_trx.trx_started AS waiting_trx_started,
    waiting_trx.trx_state AS waiting_trx_state,
    waiting_trx.trx_rows_locked AS waiting_trx_rows_locked,
    waiting_trx.trx_rows_modified AS waiting_trx_rows_modified,
    waiting_trx.trx_mysql_thread_id AS waiting_thread,
    waiting_trx.trx_query AS waiting_query,
    requested_lock.lock_type AS req_lock_type,
    requested_lock.lock_mode AS req_lock_mode,
    requested_lock.lock_table AS req_lock_table,
    requested_lock.lock_index AS req_lock_index,
    requested_lock.lock_data AS req_lock_data,
    blocking_trx.trx_id AS blocking_trx_id,
    blocking_trx.trx_started AS blocking_trx_started,
    blocking_trx.trx_state AS blocking_trx_state,
    blocking_trx.trx_rows_locked AS blocking_trx_rows_locked,
    blocking_trx.trx_rows_modified AS blocking_trx_rows_modified,
    blocking_trx.trx_mysql_thread_id AS blocking_thread,
    blocking_trx.trx_query AS blocking_query,
    blocking_lock.lock_type AS blocking_lock_type,
    blocking_lock.lock_mode AS blocking_lock_mode,
    blocking_lock.lock_table AS blocking_lock_table,
    blocking_lock.lock_index AS blocking_lock_index,
    blocking_lock.lock_data AS blocking_lock_data
FROM information_schema.innodb_lock_waits AS lock_waits
LEFT JOIN  information_schema.innodb_trx AS blocking_trx ON blocking_trx.trx_id = lock_waits.blocking_trx_id
LEFT JOIN information_schema.innodb_trx AS waiting_trx ON waiting_trx.trx_id = lock_waits.requesting_trx_id
LEFT JOIN information_schema.innodb_locks AS blocking_lock ON blocking_lock.lock_id = lock_waits.blocking_lock_id
LEFT JOIN information_schema.innodb_locks AS requested_lock ON requested_lock.lock_id = lock_waits.requested_lock_id
ORDER BY blocking_trx.trx_rows_locked DESC; 

-- Transactions waiting for a lock, including blocking transaction details and statement events that were recently executed by the locking transaction thread
-- It requires events_statements_history_long performance schema consumer enabled: UPDATE performance_schema.setup_consumers SET enabled='YES' where name='events_statements_history_long';
SELECT 
    waiting_trx.trx_id AS waiting_trx_id,
    waiting_trx.trx_started AS waiting_trx_started,
    waiting_trx.trx_state AS waiting_trx_state,
    waiting_trx.trx_rows_locked AS waiting_trx_rows_locked,
    waiting_trx.trx_rows_modified AS waiting_trx_rows_modified,
    waiting_trx.trx_mysql_thread_id AS waiting_process,
    waiting_trx.trx_query AS waiting_query,
    locks.lock_mode,
    locks.lock_type,
    locks.lock_table,
    locks.lock_index,
    locks.lock_data,
    blocking_trx.trx_id AS blocking_trx_id,
    blocking_trx.trx_started AS blocking_trx_started,
    blocking_trx.trx_state AS blocking_trx_state,
    blocking_trx.trx_rows_locked AS blocking_trx_rows_locked,
    blocking_trx.trx_rows_modified AS blocking_trx_rows_modified,
    blocking_trx.trx_mysql_thread_id AS blocking_process,
    blocking_trx.trx_query AS blocking_query,
    events_history.thread_id AS event_thread_id, 
    events_history.event_name AS event_name,
    events_history.event_id AS event_id, 
    events_history.timer_start AS event_timer_start, 
    events_history.sql_text AS event_query
FROM information_schema.innodb_lock_waits AS lock_waits
LEFT JOIN information_schema.innodb_locks AS locks ON lock_waits.requested_lock_id = locks.lock_id
LEFT JOIN  information_schema.innodb_trx AS blocking_trx ON blocking_trx.trx_id = lock_waits.blocking_trx_id
LEFT JOIN information_schema.innodb_trx AS waiting_trx ON waiting_trx.trx_id = lock_waits.requesting_trx_id
LEFT JOIN performance_schema.threads AS threads ON threads.processlist_id = blocking_trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_history_long AS events_history ON threads.thread_id = events_history.thread_id
ORDER BY waiting_trx_id, blocking_trx_id, event_timer_start; 

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;