How to find what's blocking in MySQL

Two errors, one cause

Two InnoDB errors bring most people to this page:

• ERROR 1205: Lock wait timeout exceeded; try restarting transaction. A statement waited longer than innodb_lock_wait_timeout (50 seconds by default) for a row lock another transaction holds.
• ERROR 1213: Deadlock found when trying to get lock. Two transactions each waited for a lock the other held. InnoDB detected the cycle and rolled one of them back.

Both come from transactions holding row locks while others need the same rows. For a lock wait you want three things: who is waiting, which transaction is at the root, and what that transaction did. For a deadlock you want the two statements that collided, so you can change the order or the index.

The queries below are for MySQL 8.0 and later, where Performance Schema and the sys schema are on by default. The old information_schema.innodb_locks and innodb_lock_waits tables were removed in 8.0; their replacements are performance_schema.data_locks and data_lock_waits.

1. See who is waiting

sys.innodb_lock_waits joins the lock tables and transactions into one row per wait, oldest first.

SELECT wait_age_secs,
       locked_table_schema,
       locked_table_name,
       locked_index,
       waiting_pid,
       LEFT(waiting_query, 80)  AS waiting_query,
       blocking_pid,
       LEFT(blocking_query, 80) AS blocking_query,
       blocking_trx_age,
       blocking_trx_rows_modified
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

• waiting_pid and blocking_pid are processlist IDs, the same numbers SHOW PROCESSLIST shows and KILL takes.
• locked_index tells you which index the lock is on. PRIMARY means the lock was taken on the primary key; a secondary index name means it was taken through that index; GEN_CLUST_INDEX means the table has no primary key.
• blocking_query = NULL means the blocker is not running anything right now. It ran its statements and left the transaction open. Step 3 covers this case.

The view also has sql_kill_blocking_query and sql_kill_blocking_connection columns that print ready-made KILL statements. Read step 5 before using them.

2. Find the root blocker

In a chain, session A blocks B and B blocks C. Ending B fixes nothing. The session that matters blocks others but is not waiting itself.

SELECT blocking_pid                 AS root_blocker,
       COUNT(*)                     AS sessions_waiting,
       MAX(wait_age_secs)           AS longest_wait_s,
       MAX(blocking_trx_age)        AS trx_age,
       MAX(blocking_trx_rows_modified) AS rows_modified
FROM sys.innodb_lock_waits
WHERE blocking_pid NOT IN (SELECT waiting_pid FROM sys.innodb_lock_waits)
GROUP BY blocking_pid
ORDER BY sessions_waiting DESC;

rows_modified is a rough measure of how much work a rollback will undo. A transaction that changed a million rows can take minutes to roll back, and it keeps its locks until it finishes.

3. See what the blocker did

Replace 42 with the root blocker's processlist ID. First, who owns the connection and how long its transaction has been open:

SELECT p.id, p.user, p.host, p.db, p.command, p.time AS idle_s,
       t.trx_started,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
       t.trx_rows_locked,
       t.trx_rows_modified
FROM information_schema.processlist AS p
JOIN information_schema.innodb_trx AS t ON t.trx_mysql_thread_id = p.id
WHERE p.id = 42;

command = Sleep with an open transaction is the usual culprit: an application that began a transaction, ran an update, then went off to do something else, or a developer's console with autocommit off. user and host tell you which application or machine it belongs to.

To see the statements it ran, map the processlist ID to a Performance Schema thread and read its statement history:

SELECT h.event_id,
       LEFT(h.sql_text, 120)               AS sql_text,
       ROUND(h.timer_wait / 1e9, 1)         AS ms,
       h.rows_affected
FROM performance_schema.events_statements_history AS h
JOIN performance_schema.threads AS th ON th.thread_id = h.thread_id
WHERE th.processlist_id = 42
ORDER BY h.event_id;

The history keeps the last few statements per thread (typically 10). The UPDATE or SELECT ... FOR UPDATE that took the lock is often still there. If the transaction ran many statements since, it may have scrolled out.

4. Waiting for table metadata lock

If SHOW PROCESSLIST shows Waiting for table metadata lock, the wait is not a row lock and sys.innodb_lock_waits will be empty. Metadata locks protect table definitions. The classic case is an ALTER TABLE waiting behind an open transaction that read the table; every later query on that table then queues behind the ALTER.

SELECT object_schema,
       object_name,
       waiting_pid,
       LEFT(waiting_query, 80) AS waiting_query,
       waiting_query_secs,
       blocking_pid,
       blocking_lock_type,
       blocking_lock_duration
FROM sys.schema_table_lock_waits;

blocking_lock_duration = TRANSACTION means the lock is held until that session commits or rolls back. The fix is the same as for row locks: find the session, then commit it from the application or end it.

5. End it safely

Before ending anything, decide whether you should:

• A batch job that is nearly done: waiting may be cheaper than a long rollback.
• An idle session with an open transaction: contact the owner, or end it. Its uncommitted work is rolled back.
• The same blocker keeps returning: fix the source. Commit sooner, keep transactions short, and do not call external services while holding locks.

-- stop only the running statement; the session and its transaction stay
KILL QUERY 42;

-- end the session; its open transaction is rolled back
KILL 42;

-- watch the rollback: trx_state shows ROLLING BACK until it finishes
SELECT trx_state, trx_rows_modified
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = 42;

Then run step 1 again. The waits should be gone. Confirm from the application side that requests complete; a successful KILL is not proof the problem is solved.

6. Read a deadlock

A deadlock resolves itself: InnoDB rolls back one transaction, usually the smaller one by rows changed, and returns error 1213 to it. Nothing is left to kill. What you want is the evidence, so you can stop it happening again.

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section. It lists each transaction, the statement it was running, the lock it held, the lock it was waiting for (index and record), and which transaction was rolled back. Only the last deadlock is kept, so record every one when they are frequent:

SET PERSIST innodb_print_all_deadlocks = ON;

Common fixes, in the order to try them:

• Touch rows in the same order in every transaction, for example always by primary key ascending.
• Add the index the UPDATE ... WHERE or SELECT ... FOR UPDATE needs, so it locks the rows it changes instead of every row it scans.
• Keep transactions short, and retry the transaction that received error 1213. It was rolled back cleanly, so a retry is safe.

7. Timeouts that limit the damage

-- row locks: give up after 10 seconds instead of the default 50
SET SESSION innodb_lock_wait_timeout = 10;

-- metadata locks: set this before an ALTER TABLE (default is one year)
SET SESSION lock_wait_timeout = 30;

-- what happens to the transaction after error 1205
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';

With innodb_rollback_on_timeout = OFF (the default), error 1205 rolls back only the statement that timed out. The transaction stays open with its earlier changes and locks. An application that catches 1205 and carries on, or commits, keeps partial work. Handle 1205 by issuing ROLLBACK and retrying the whole transaction. innodb_rollback_on_timeout is read only at startup.

For migrations, set a short lock_wait_timeout before the ALTER TABLE and retry if it fails. An ALTER that waits behind a long transaction blocks every query that arrives after it.

The same steps in Quorum

Quorum reads Performance Schema and draws who waits for whom, with the root blocker identified, its transaction age and the program that owns it. Observed facts are kept apart from likely explanations.

• Blocking and deadlocks, drawn: the root blocker, transaction age and owning program, instead of four separate queries.
• Long-running transactions listed before they turn into a chain.
• Review before you act: the exact KILL, the target server and its side effects, previewed and confirmed once; a changed statement or target needs a new confirmation.
• Honest outcomes: confirmed, failed or unknown, never a silent retry. Save the steps as a runbook for next time.