MySQL 'Lock wait timeout exceeded' (error 1205): find the blocking transaction and stop it happening again

#Symptoms

The application logs:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Usually several requests fail at once, all touching the same rows or table, and they each fail after the same delay (50 seconds by default). The database itself looks healthy: CPU is low and nothing is "slow" in the usual sense.

This is not a deadlock. With a deadlock (ERROR 1213) InnoDB detects a cycle and rolls back a victim straight away. With 1205 there is no cycle: one transaction holds a lock and simply doesn't let go, and everyone else queues behind it until innodb_lock_wait_timeout expires.

Warning

By default (innodb_rollback_on_timeout = OFF) InnoDB rolls back only the statement that timed out, not the whole transaction. If the application catches the error and carries on, it can commit a half-finished transaction. Make sure your code rolls back the transaction on 1205.

#1. Confirm it is a row lock wait, not a metadata lock

Two different timeouts produce similar complaints:

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';

SELECT id, user, host, db, command, time, state, LEFT(info, 120) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

If you see Waiting for table metadata lock, this is the wrong guide; look for the long transaction blocking a DDL statement instead.

#2. Find who is blocking whom

While the problem is happening, the sys schema joins the Performance Schema lock tables for you:

SELECT wait_age,
       locked_table,
       locked_index,
       locked_type,
       waiting_pid,
       LEFT(waiting_query, 100)  AS waiting_query,
       blocking_pid,
       LEFT(blocking_query, 100) AS blocking_query,
       sql_kill_blocking_connection
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;

What to read:

• Many rows with the same blocking_pid: one transaction is the root of the pile-up. That's your target.
• blocking_query is NULL: the blocker is idle. It ran a statement, did not commit, and the connection is now sitting in Sleep. This is the most common cause.
• locked_index is GEN_CLUST_INDEX or PRIMARY with many rows locked: the statement probably scanned the table because there is no usable index.

For the raw view (or if sys isn't installed), use the Performance Schema tables directly:

SELECT w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
       w.BLOCKING_ENGINE_TRANSACTION_ID   AS blocking_trx,
       l.OBJECT_SCHEMA, l.OBJECT_NAME, l.INDEX_NAME, l.LOCK_MODE, l.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks l
  ON l.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

#3. Look at the blocking transaction

Find how long the blocker has been open and how much it has changed:

SELECT trx_id,
       trx_mysql_thread_id AS pid,
       trx_state,
       trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_rows_locked,
       trx_rows_modified,
       trx_isolation_level,
       LEFT(trx_query, 100) AS current_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

When current_query is NULL, find what the session ran before it went idle. Map the processlist id to a Performance Schema thread and read its statement history:

SET @pid = 12345;   -- blocking_pid from step 2

SELECT h.EVENT_ID, LEFT(h.SQL_TEXT, 200) AS sql_text, h.ROWS_AFFECTED
FROM performance_schema.events_statements_history h
JOIN performance_schema.threads t ON t.THREAD_ID = h.THREAD_ID
WHERE t.PROCESSLIST_ID = @pid
ORDER BY h.EVENT_ID;

The events_statements_history consumer keeps the last 10 statements per thread by default. Check performance_schema.setup_consumers if it returns nothing. The user, host and db from the processlist tell you which application or person owns the session.

#4. Clear the blocker

Decide based on what the blocker is:

KILL 12345;                 -- self-managed MySQL, Azure Flexible Server
CALL mysql.rds_kill(12345); -- Amazon RDS / Aurora MySQL

Caution

Killing a connection rolls back everything its transaction changed. A transaction that modified millions of rows can take as long to roll back as it took to run, and it keeps holding its locks the whole time. Check trx_rows_modified first. After the kill, trx_state shows ROLLING BACK in information_schema.innodb_trx until it finishes. Don't restart MySQL to "speed it up": crash recovery has to finish the same rollback.

Once the blocker is gone, the waiting statements continue within a second. Confirm sys.innodb_lock_waits is empty.

#5. Find the cause, not just the culprit

To check whether a statement will scan, run EXPLAIN on it. A type of ALL on the table being updated means a full scan, and InnoDB locks every row it examines.

EXPLAIN UPDATE orders SET status = 'shipped' WHERE customer_ref = 'C-1042';

#Should you change innodb_lock_wait_timeout?

Rarely globally. A longer timeout makes pile-ups bigger; a very short one causes failures during normal short waits. Change it per session where the workload needs it:

-- Interactive or web request: fail fast
SET SESSION innodb_lock_wait_timeout = 5;

-- Nightly batch that is allowed to wait
SET SESSION innodb_lock_wait_timeout = 300;

For queue-style workloads where workers compete for rows, avoid waiting at all (MySQL 8.0 and later):

SELECT id FROM jobs WHERE status = 'ready'
ORDER BY id LIMIT 10
FOR UPDATE SKIP LOCKED;

NOWAIT instead of SKIP LOCKED returns an error immediately if a row is locked.

innodb_rollback_on_timeout = ON makes InnoDB roll back the whole transaction on 1205. It is not dynamic; it needs a restart (or a parameter group change on managed services). Test that your application expects that behaviour before you enable it.

#Prevent it next time

• Alert on long transactions. Run this every minute from your monitoring tool and alert above a threshold suited to your workload (for example, 60 seconds):

  SELECT COUNT(*) AS long_trx
  FROM information_schema.innodb_trx
  WHERE trx_started < NOW() - INTERVAL 60 SECOND;
  

• Track the Innodb_row_lock_waits and Innodb_row_lock_time_max status counters to see lock waits trend upwards before they become timeouts:

  SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
  

• Review new UPDATE and DELETE statements with EXPLAIN in code review.

• Make application code roll back on any database error, and set a connection pool check so connections are never returned with an open transaction.

• Keep batch jobs chunked and schedule them away from peak OLTP traffic.