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

ERROR 1205 means a transaction gave up waiting for a row lock. InnoDB locks rows as transactions read and write them, and when your statement needs a lock that another open transaction already holds, it waits. If that wait passes innodb_lock_wait_timeout (default 50 seconds), MySQL aborts the waiting statement with 1205 and asks you to retry. This is a wait timeout, not a deadlock (ERROR 1213), and the distinction shapes the fix.

What this error means

Two sessions want the same rows at overlapping times. One holds a lock (from an UPDATE, DELETE, INSERT, or SELECT ... FOR UPDATE) and has not committed or rolled back. The second session queues behind it. The lock is released only when the holding transaction ends, so if the holder stays open, the waiter eventually times out. The waiting statement is rolled back, but on most drivers the rest of that transaction stays open, so your application must decide whether to retry or roll back.

Common causes

• A long-running transaction (a batch job, a large UPDATE, or a report) holds locks for a long time while others wait.
• An application opens a transaction, then makes a slow external call (HTTP request, queue publish) before committing, holding locks across the round trip.
• A forgotten open transaction: autocommit is off, or a BEGIN was never followed by COMMIT or ROLLBACK, so locks are held indefinitely.
• Missing or unsuitable indexes force InnoDB to scan and lock far more rows than the query actually changes.
• Gap locks under the default REPEATABLE READ isolation level widen the locked range beyond the matched rows.
• High contention on a hot row (a counter, a status flag, a shared parent record) that many transactions update at once.

How to fix it

Stabilize

1. Find the transaction that is blocking others. On MySQL 8.0, use performance_schema or the sys helper view:

SELECT * FROM sys.innodb_lock_waits\G

SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread,
       b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread
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;

On MySQL 5.7, the equivalent lives in INFORMATION_SCHEMA.INNODB_LOCK_WAITS joined with INNODB_TRX and INNODB_LOCKS (the INNODB_LOCKS table was removed in 8.0).

2. If a single stuck transaction is stalling production and you cannot wait for it to finish, kill its thread. Use the blocking thread id from the query above:

KILL 4821;

Killing the connection rolls its transaction back and releases the locks. Confirm you are killing the blocker, not an important in-flight write.

Diagnose

3. Read the InnoDB engine status for the full picture of transactions, held locks, and waits:

SHOW ENGINE INNODB STATUS\G

Look at the TRANSACTIONS section for entries with a high number of seconds active, LOCK WAIT state, or many undo log entries. Cross-reference the trx_query and trx_started columns from information_schema.innodb_trx to identify which statement and which application path is holding locks.

Fix

4. Shorten the transactions. Commit as soon as the write is done, and never hold a transaction open across a slow external call. Move that call outside the BEGIN and COMMIT.

5. Add indexes so writes lock only the rows they touch. Confirm with EXPLAIN that the statement seeks rather than scans:

EXPLAIN UPDATE orders SET status = 'shipped' WHERE order_ref = 'A-10293';

If type shows ALL or key is NULL, add an index on the filtered column (for example CREATE INDEX idx_orders_ref ON orders (order_ref);) so InnoDB locks one row instead of a range.

6. Add retry on 1205 in the application. On catching error 1205, roll back the transaction and retry the whole unit of work a few times with a short backoff. Retrying a single statement inside a half-aborted transaction is not safe.

How to prevent it

• Keep transactions short: read, write, commit, without waiting on network or user input in between.
• Index the columns used in WHERE, JOIN, and foreign keys so locks stay narrow.
• Never leave autocommit off in code paths that can pause; commit or roll back on every path.
• Access rows in a consistent order across the codebase to reduce contention.
• Add bounded retry with backoff for 1205 (and 1213) so transient waits self-heal.
• Monitor long-running transactions and alert on any transaction open longer than a few seconds.

When to call a senior engineer

If 1205 keeps recurring after you shorten transactions and add indexes, the cause is usually structural: a hot row every request touches, an isolation level that does not match the workload, or an ORM that opens transactions too early. Bring in a senior engineer when locking stalls checkout or payments, when you are unsure which transaction is safe to kill, or when the same rows are contended by both batch jobs and live traffic. Erzon can trace the blocking transaction, reshape the access pattern, and add the right indexes and retry logic without guesswork.