MySQL query to identify and retrieve detailed information about queries causing locks.

mysql-locks-monitor.sql

-- Requirement: performance_schema = ON

SELECT
    lw.waiting_pid,
    lw.waiting_query,
    lw.waiting_trx_age AS 'waiting_time',

    lw.blocking_pid,
    (
        SELECT h.SQL_TEXT 
        FROM performance_schema.events_statements_history h 
        WHERE h.THREAD_ID = t.THREAD_ID 
        ORDER BY h.EVENT_ID DESC 
        LIMIT 1
    ) AS blocking_query,
    lw.blocking_trx_age AS 'blocking_time',

    t.PROCESSLIST_USER AS 'blocking_user',
    t.PROCESSLIST_HOST AS 'blocking_host',

    l.OBJECT_SCHEMA AS 'locked_database',
    l.OBJECT_NAME AS 'locked_table',
    l.INDEX_NAME AS 'locked_index',

    lw.blocking_trx_rows_locked AS 'locked_rows',
    lw.blocking_trx_rows_modified AS 'locked_rows_modified',

    CASE
        WHEN @@basedir LIKE '/rdsdbbin%' THEN CONCAT('CALL mysql.rds_kill(', blocking_pid, ');')
        ELSE CONCAT('KILL ', blocking_pid, ';')
    END AS 'fix_query'
FROM
    sys.innodb_lock_waits lw
JOIN
    performance_schema.threads t ON t.PROCESSLIST_ID = lw.blocking_pid
JOIN
    performance_schema.data_locks l ON l.thread_id = t.thread_id AND l.lock_type = 'record'
JOIN
    information_schema.innodb_trx wt ON lw.waiting_trx_id = wt.trx_id
JOIN
    information_schema.innodb_trx bt ON lw.blocking_trx_id = bt.trx_id
ORDER BY bt.trx_started;


-- Output:
-- *************************** 1. row ***************************
--          waiting_pid: 42
--        waiting_query: UPDATE test SET name = 'first12345' WHERE id = 1
--         waiting_time: 00:00:50
--         blocking_pid: 41
--       blocking_query: UPDATE test SET name = 'first123' WHERE id = 1
--        blocking_time: 00:25:40
--        blocking_user: admin
--        blocking_host: 172.31.0.231
--      locked_database: test
--         locked_table: test
--         locked_index: PRIMARY
--          locked_rows: 1
-- locked_rows_modified: 1
--            fix_query: CALL mysql.rds_kill(41);