Debugging Blocking Sessions in MySQL

When a MySQL query is blocked, find the session holding the lock, inspect the transaction that owns it, and then decide whether to wait, end the connection, or change the code path. A lock controls access to data while a transaction works with it. A transaction groups database changes that MySQL commits or rolls back together.

A blocked statement is waiting for another transaction to release a lock that prevents it from accessing the data it needs. That wait does not mean the transaction has failed. A more concerning case is an open transaction whose connection is idle: the statement that acquired the lock has finished, but the transaction has not committed or rolled back. MySQL’s InnoDB lock-wait views help distinguish these situations and identify the sessions involved.

Find the waiting and blocking sessions

Start with the sys.innodb_lock_waits view, which summarizes InnoDB lock waits. It lists the waiting and blocking connection IDs and the affected table. It shows how long the wait has lasted and how long the blocking transaction has been open. It also provides suggested KILL statements. Sort by wait age to see the longest delays first. The following query uses the MySQL 8.4 view:

SELECT
    wait_started,
    wait_age,
    locked_table_schema,
    locked_table_name,
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_query,
    blocking_trx_started,
    blocking_trx_age,
    sql_kill_blocking_query,
    sql_kill_blocking_connection
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

The blocking connection ID is the process-list ID of the session holding up the request. Check both sessions because a waiting transaction can hold locks that block other sessions, and a statement can wait on multiple locks. For the underlying lock relationships, inspect Performance Schema’s data_lock_waits and data_locks tables, which show requested and held locks. See MySQL’s InnoDB lock and lock-wait details.

Inspect the transaction behind the blocker

Check the blocking transaction’s age, state, and rows locked or modified. The INNODB_TRX table reports the transaction state and start time, plus its current statement when available. The state shows whether the transaction is RUNNING, waiting on a lock (LOCK WAIT), ROLLING BACK, or COMMITTING. For example, replace <blocking_pid> with the blocking connection ID:

SELECT
    trx_id,
    trx_state,
    trx_started,
    trx_wait_started,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = ;

A NULL blocking_query does not mean the session holds no locks. The session may have finished the statement that took the locks and then stayed idle while its transaction remained open. A process list can show that connection as Sleep, but that status alone does not establish that its transaction has ended. SHOW FULL PROCESSLIST gives a useful snapshot of current session activity; the process-list view also provides session details through Performance Schema.

To investigate an idle blocker, find its Performance Schema thread ID using its process-list ID, then inspect its current and recent statements. Replace the placeholder with the ID from the lock-wait view:

SELECT THREAD_ID
FROM performance_schema.threads
WHERE PROCESSLIST_ID = ;

SELECT THREAD_ID, SQL_TEXT
FROM performance_schema.events_statements_current
WHERE THREAD_ID = ;

SELECT THREAD_ID, SQL_TEXT
FROM performance_schema.events_statements_history
WHERE THREAD_ID = 
ORDER BY EVENT_ID;

Statement history can reveal what the connection did before it became idle. Treat it as a clue: recent history does not prove which exact statement acquired every lock. Compare it with the application’s transaction flow and the current lock data. MySQL also cautions that transaction, lock, and process-list views show live snapshots. Their results may not agree perfectly, so recheck the session ID and wait before taking a destructive action. The transaction and locking examples show how to trace an idle blocker.

Tell a lock wait from a transaction failure

A lock wait ends when the blocking transaction releases the lock that the waiting statement needs, normally by committing or rolling back. If the wait times out, InnoDB’s default behavior rolls back only the waiting statement. The connection may still have an active transaction and locks from earlier statements, so check its state before assuming the timeout cleared everything. MySQL documents this distinction in its InnoDB error-handling rules.

A deadlock is different: two or more transactions form a cycle, with each waiting for a lock held by another. When InnoDB detects a deadlock, it rolls back one transaction as the victim. The application should handle that error by retrying the whole transaction, rather than assuming only the last statement failed. Use SHOW ENGINE INNODB STATUS\G to inspect the latest detected deadlock and its transaction details:

SHOW ENGINE INNODB STATUS\G

This status output helps explain a deadlock, while the live lock-wait views are better for finding a current blocker. MySQL limits the output returned by this command to 1 MB. To log all detected deadlocks, MySQL documents the innodb_print_all_deadlocks setting in its deadlock guidance.

Decide whether to wait, end the connection, or change the code

Wait when the blocker is making progress and the delay is acceptable. If the transaction belongs to a service you control, ask that service to commit or roll back when it is safe to do so. If the blocker is idle, old, or causing unacceptable impact, verify its identity and transaction state before terminating it.

KILL QUERY <blocking_pid> stops the connection’s current statement but leaves the connection open. If that connection is idle in an open transaction, there is no current statement to stop, so KILL QUERY does not end the transaction. KILL CONNECTION <blocking_pid> ends the connection and causes its uncommitted InnoDB work to roll back. Rollback and cleanup take time; the KILL command returning does not confirm that locks have already disappeared. Check the lock-wait and transaction views again after acting. MySQL describes these effects in the KILL statement documentation.

KILL QUERY ;
-- Use this when ending the session is intended:
KILL CONNECTION ;

If the same blocking pattern keeps returning, change the code path that holds locks. Keep write transactions short, commit after the related changes, and access rows in a consistent order. Use suitable indexes for the conditions in UPDATE and SELECT ... FOR UPDATE so the statements do not lock more rows than intended. Deadlocks can still occur, so make transaction retries safe and retry the full transaction after a deadlock victim error. MySQL’s deadlock recommendations cover reducing deadlocks and handling the ones that remain.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com