How to Fix MySQL Error 1205: Lock Wait Timeout Exceeded

Q: Why was only my last statement rolled back, not the whole transaction?

That's the default: innodb_rollback_on_timeout=OFF . Set it ON if you want a timeout to abort the entire transaction, which is usually what application code expects.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction is raised by InnoDB when a transaction waits longer than the value of innodb_lock_wait_timeout (default 50 seconds) for a row or gap lock that another transaction is holding. The current statement is rolled back; the full transaction is rolled back only if innodb_rollback_on_timeout=ON.

Impact

A lock-wait timeout aborts the current statement, leaves the transaction in an open but partially-rolled-back state, and surfaces as an application error. Under load, repeated timeouts compound: clients retry, hold connections, and the server's active-transaction count climbs until the symptom looks like "the database is hung" even though the underlying problem is one long-running blocker.

Unlike a deadlock (error 1213), which InnoDB detects and resolves automatically by killing the cheaper victim, a lock-wait timeout simply expires — InnoDB does not pick a victim or break the deadlock-free wait chain on its own.

Common Causes

A long-running transaction (often an idle Sleep connection that started a transaction and never committed)
Autocommit disabled in the application driver and a missing COMMIT / ROLLBACK
Large batch UPDATE / DELETE over many rows holding row locks for minutes
Gap and next-key locks taken by REPEATABLE READ isolation on range scans without good indexes
Foreign-key locks acquired implicitly on parent rows during child-row writes
ALTER TABLE or other DDL that takes metadata locks while a long query runs
Connection pool that hands out a connection mid-transaction to another request
Auto-increment lock contention (innodb_autoinc_lock_mode=0, "traditional") on bulk inserts
Application retry loops that re-enter the same hot row without backoff
innodb_lock_wait_timeout set too low for legitimate batch jobs

Troubleshooting and Resolution Steps

Find the current setting:

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

List active transactions and how long they've been running:

SELECT
    trx_id,
    trx_mysql_thread_id AS thread,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
    trx_rows_locked,
    trx_rows_modified,
    LEFT(trx_query, 200) AS query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

See who is blocking whom (MySQL 8.0+):

SELECT
    waiting_pid,
    waiting_query,
    blocking_pid,
    blocking_query,
    wait_age,
    locked_table,
    locked_index,
    locked_type
FROM sys.innodb_lock_waits;

On MySQL 5.7 the equivalent comes from information_schema.INNODB_LOCK_WAITS joined with INNODB_LOCKS and INNODB_TRX.

Inspect the latest lock-wait detail:
```
SHOW ENGINE INNODB STATUS\G
```
The TRANSACTIONS section lists each transaction with its lock structs, undo-log size, and the row it is waiting on. The line ------- TRX HAS BEEN WAITING <N> SEC FOR THIS LOCK TO BE GRANTED identifies the blocker.

Kill a blocker once identified:

-- Get the MySQL thread id from INNODB_TRX or SHOW PROCESSLIST
KILL 123;          -- kill the connection (also kills the transaction)
KILL QUERY 123;    -- cancel the running statement only

Tune the timeout for the current session, transaction, or globally:

-- Session-level
SET SESSION innodb_lock_wait_timeout = 10;

-- Global (does not affect existing connections)
SET GLOBAL innodb_lock_wait_timeout = 30;

-- Persisted across restarts (MySQL 8.0+)
SET PERSIST innodb_lock_wait_timeout = 30;

Lower values fail fast (better for OLTP); higher values are appropriate for batch and migration jobs.

Force full-transaction rollback on timeout instead of just the statement:
```
SET GLOBAL innodb_rollback_on_timeout = ON;  -- requires restart in some versions
```
This avoids the "transaction is open but the last statement was rolled back" trap that confuses many application drivers.

Skip locked rows instead of waiting (queue / worker patterns):

-- MySQL 8.0+
SELECT id, payload FROM job_queue
WHERE status = 'pending'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- Fail immediately if any target row is locked
SELECT * FROM accounts WHERE id = 42 FOR UPDATE NOWAIT;

Shrink transaction scope. The single biggest fix in practice:

-- Bad: external work inside the transaction
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- ... call payment provider, wait 800ms ...
UPDATE accounts SET balance = balance - 100 WHERE id = 42;
COMMIT;

-- Better: do all I/O outside, lock briefly
-- (idempotency key + reconciliation)

Retry with exponential backoff in the application:

import time
import pymysql
from pymysql.err import OperationalError

def with_retry(fn, attempts=4):
    delay = 0.05
    for i in range(attempts):
        try:
            return fn()
        except OperationalError as e:
            if e.args[0] == 1205 and i < attempts - 1:
                time.sleep(delay)
                delay *= 2
                continue
            raise

Reduce gap locking by switching the session to READ COMMITTED where the application's correctness allows (no phantom-row dependency, no range-update patterns that require serializability):
```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
READ COMMITTED takes no gap locks, only row locks on rows actually scanned with a usable index — this alone often eliminates timeout symptoms on hot range-scan workloads.
Add indexes the writer can use. Without a covering index on the WHERE predicate, InnoDB locks every row it scans (not just rows it modifies). An UPDATE accounts SET ... WHERE email = ? over an unindexed email column locks the whole table; the same statement with an index on email locks one row.

Hunt down idle transactions — the silent classic cause:

SELECT
    t.trx_id,
    t.trx_mysql_thread_id AS thread,
    p.user, p.host, p.db, p.command, p.time AS thread_time_s,
    TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
    p.state, LEFT(p.info, 200) AS last_query
FROM information_schema.INNODB_TRX t
JOIN information_schema.PROCESSLIST p ON p.id = t.trx_mysql_thread_id
WHERE p.command IN ('Sleep')
ORDER BY trx_age_s DESC;

These are the worst offenders: a connection sitting Sleep with an open transaction is holding locks for as long as the pool keeps the connection alive.

Additional Information

innodb_lock_wait_timeout is independent of wait_timeout and interactive_timeout, which govern idle connection lifetime, not lock waits.
Lock-wait timeouts and deadlocks are different problems. Deadlocks fire error 1213 and are detected by innodb_deadlock_detect=ON (default). Lock-wait timeouts fire 1205 and indicate one transaction simply held a lock too long for any chain to make progress.
The default 50-second timeout is too long for most OLTP traffic; values in the 5–10 second range fail fast and make problems visible early. Batch jobs should set a higher session value rather than raising the global default.
performance_schema.data_locks and performance_schema.data_lock_waits (MySQL 8.0+) are the canonical lock introspection tables; the older INNODB_LOCKS / INNODB_LOCK_WAITS views were removed in 8.0.
Connection pools (HikariCP, pgbouncer-style proxies, ProxySQL) can mask the root cause by recycling a connection that left a transaction open. Pool implementations should either reset the session (COM_RESET_CONNECTION) or be configured to never hand out a connection inside an open transaction.

Frequently Asked Questions

Q: How is this different from a deadlock?
A: A deadlock (error 1213) is a circular wait that InnoDB detects and breaks by rolling back the smaller-cost transaction. A lock-wait timeout (error 1205) is a single transaction waiting on another that never releases — there is no cycle, just a slow or stuck holder.

Q: Why was only my last statement rolled back, not the whole transaction?
A: That's the default: innodb_rollback_on_timeout=OFF. Set it ON if you want a timeout to abort the entire transaction, which is usually what application code expects.

Q: Is it safe to lower innodb_lock_wait_timeout globally?
A: Generally yes. 10 seconds fails fast and gives clearer signal, but ensure your batch/migration code either runs with a higher session value or has retry logic.

Q: I see the timeout but SHOW ENGINE INNODB STATUS is empty.
A: The "LATEST DETECTED DEADLOCK" section only records deadlocks, not lock-wait timeouts. Look at the TRANSACTIONS section instead, or use sys.innodb_lock_waits.

Q: My application uses an ORM — where do these long transactions come from?
A: Most commonly: (a) the ORM begins a transaction on first query and only commits when the session is closed, so a slow HTTP handler holds the transaction open; (b) connection pooling with autocommit off; (c) a select_for_update() inside a long view function.

Q: Will switching to READ COMMITTED break anything?
A: It eliminates gap locks (good) but allows non-repeatable reads. Applications that rely on the same SELECT returning identical results twice inside one transaction, or that use range scans for "no row exists with X" checks before insert, may need adjustments.

Q: Can I prevent these by raising innodb_lock_wait_timeout to its maximum?
A: No — that just turns crashes into hangs. The fix is shorter transactions and proper indexes; the timeout is a symptom-level lever, not a cure.