MySQL Useful DB Lock Queries

When a query hangs and nothing obvious is wrong, it is usually a lock. These are the queries I keep going back to, in the order I tend to run them.

All of these are for Aurora MySQL 3, which is MySQL 8.x. The lock tables moved to performance_schema in 8.0, so the older information_schema.innodb_lock_waits versions of these queries will not work.

1. Current lock waits

Who is blocking whom. This is the first thing to run.

SELECT
    r.trx_id             AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query          AS waiting_query,
    r.trx_wait_started    AS wait_started,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_age_sec,
    b.trx_id             AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query          AS blocking_query,
    b.trx_started        AS blocking_trx_started
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
ORDER BY wait_age_sec DESC;

2. All current locks held

What is locked, by whom, and with which lock type and mode.

SELECT
    l.ENGINE_TRANSACTION_ID AS trx_id,
    l.OBJECT_SCHEMA,
    l.OBJECT_NAME,
    l.LOCK_TYPE,
    l.LOCK_MODE,
    l.LOCK_STATUS,
    l.LOCK_DATA,
    t.trx_mysql_thread_id AS thread_id,
    t.trx_query
FROM performance_schema.data_locks l
JOIN information_schema.innodb_trx t ON t.trx_id = l.ENGINE_TRANSACTION_ID
ORDER BY l.OBJECT_NAME;

3. Long-running transactions

The common root cause of held locks. A transaction left open keeps its locks until it commits or rolls back.

SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
    trx_mysql_thread_id AS thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

4. Full process list with state

Useful for spotting anything sitting in a Waiting for... state.

SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME AS time_sec,
    STATE,
    LEFT(INFO, 200) AS query
FROM information_schema.processlist
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

5. Metadata lock waits

DDL against DML contention, which is what you want when an ALTER TABLE is stuck behind something else.

SELECT
    p.PROCESSLIST_ID,
    p.PROCESSLIST_USER,
    p.PROCESSLIST_HOST,
    m.OBJECT_TYPE,
    m.OBJECT_SCHEMA,
    m.OBJECT_NAME,
    m.LOCK_TYPE,
    m.LOCK_STATUS
FROM performance_schema.metadata_locks m
JOIN performance_schema.threads p ON p.THREAD_ID = m.OWNER_THREAD_ID
WHERE m.LOCK_STATUS = 'PENDING'
   OR m.OBJECT_SCHEMA NOT IN ('performance_schema', 'mysql');

6. InnoDB engine status

A verbose text blob, but it carries the deadlock history and a lock summary that the tables above do not give you.

SHOW ENGINE INNODB STATUS\G