🚨 MySQL Error Guide: 'ERROR 1205 (HY000)' Lock Wait Timeout Exceeded

Overview

ERROR 1205 (HY000) happens when a transaction waits too long for a row lock held by another transaction and InnoDB gives up:

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

By default a transaction waits innodb_lock_wait_timeout seconds (50) for a lock before rolling back the current statement (not the whole transaction) and returning 1205. Unlike a deadlock (ERROR 1213), there is no cycle — one transaction is simply holding a lock and not releasing it fast enough. The classic culprit is a transaction that grabbed locks and then sat idle (e.g. waiting on an app, a network call, or a forgotten COMMIT).

It appears during UPDATE, DELETE, INSERT, or SELECT ... FOR UPDATE on rows another transaction has locked.

Symptoms

• Writes intermittently fail with Lock wait timeout exceeded, often on the same hot rows.
• Errors cluster around a specific table or a batch job’s run window.
• SHOW ENGINE INNODB STATUS shows transactions in LOCK WAIT.
• A long-lived transaction appears in information_schema.innodb_trx.

SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_query
FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

+-----------+-----------+---------------------+---------------------+---------------------------------+
| trx_id    | trx_state | trx_started         | trx_wait_started    | trx_query                       |
+-----------+-----------+---------------------+---------------------+---------------------------------+
| 4212771   | LOCK WAIT | 2026-06-23 10:15:02 | 2026-06-23 10:15:02 | UPDATE orders SET status='paid' |
+-----------+-----------+---------------------+---------------------+---------------------------------+

Common Root Causes

1. An idle transaction holding locks

A transaction ran a write, never committed, and is now sitting in Sleep while holding row locks. Everyone else waiting on those rows times out.

SELECT t.trx_id, t.trx_state, t.trx_started, p.time AS idle_s, p.command
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id
ORDER BY t.trx_started;

+----------+-----------+---------------------+--------+---------+
| trx_id   | trx_state | trx_started         | idle_s | command |
+----------+-----------+---------------------+--------+---------+
| 4212760  | RUNNING   | 2026-06-23 10:10:00 | 412    | Sleep   |
+----------+-----------+---------------------+--------+---------+

RUNNING transaction whose thread is in Sleep for 412s is the blocker — it holds locks but does no work.

2. A long-running transaction or batch

A large UPDATE/DELETE (or an analytics transaction) holds many locks for a long time, blocking concurrent writers.

SELECT trx_id, trx_started, trx_rows_locked, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5;

+----------+---------------------+-----------------+--------------------------------------+
| trx_id   | trx_started         | trx_rows_locked | trx_query                            |
+----------+---------------------+-----------------+--------------------------------------+
| 4212744  | 2026-06-23 10:08:31 | 184320          | DELETE FROM events WHERE ts < ...    |
+----------+---------------------+-----------------+--------------------------------------+

A bulk DELETE locking 184k rows will time out anything touching that range.

3. Hot-row contention

Many transactions updating the same row (a counter, a balance, a queue head) serialize behind one lock and pile up past the timeout.

SELECT object_name, COUNT(*) AS waiters
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks l ON w.blocking_engine_lock_id = l.engine_lock_id
GROUP BY object_name;

+-------------+---------+
| object_name | waiters |
+-------------+---------+
| counters    | 23      |
+-------------+---------+

23 transactions queued on the counters table is hot-row contention.

4. Missing index forcing wide locking

Without an index on the WHERE column, InnoDB scans and locks far more rows than the query logically touches, expanding the lock footprint.

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

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | filtered| Extra      |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | UPDATE      | orders | ALL  | NULL          | NULL | NULL    | 98000| 10.00  | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

type: ALL with no key means a full scan; InnoDB locks rows it examines, vastly widening contention.

5. Application holding the transaction open across slow work

Code that begins a transaction, then makes an external API call or waits on user input before committing, holds locks for seconds or minutes.

SELECT trx_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_query
FROM information_schema.innodb_trx ORDER BY age_s DESC LIMIT 1;

+----------+-------+-----------+
| trx_id   | age_s | trx_query |
+----------+-------+-----------+
| 4212760  | 95    | NULL      |
+----------+-------+-----------+

trx_query = NULL with a high age means the transaction is open but running nothing — app-side stall.

6. Timeout set too low for legitimate work

If batch jobs legitimately need longer, the default 50s causes spurious 1205s.

SELECT @@innodb_lock_wait_timeout;

+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 50                         |
+----------------------------+

Diagnostic Workflow

Step 1: Find transactions in LOCK WAIT

SELECT trx_id, trx_state, trx_wait_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

These are the victims; note their thread IDs.

Step 2: Identify the blocking transaction

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,
  b.trx_query AS blocking_query
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;

+-------------+----------------+--------------+-----------------+----------------+
| waiting_trx | waiting_thread | blocking_trx | blocking_thread | blocking_query |
+-------------+----------------+--------------+-----------------+----------------+
| 4212771     | 88             | 4212760      | 71              | NULL           |
+-------------+----------------+--------------+-----------------+----------------+

The blocker is thread 71, idle (NULL query).

Step 3: Inspect the blocker’s age and state

SELECT id, user, host, command, time, state, info
FROM information_schema.processlist WHERE id = 71;

A command = Sleep with a high time confirms an idle, uncommitted transaction.

Step 4: Free the lock (mitigation)

KILL 71;

Killing the blocking thread rolls back its transaction and releases the locks. The waiters then proceed.

Step 5: Inspect full lock detail if needed

SHOW ENGINE INNODB STATUS\G

The TRANSACTIONS section lists each transaction, what it is waiting for, and which locks it holds — useful when performance_schema lock tables are insufficient.

Example Root Cause Analysis

A checkout service intermittently fails with Lock wait timeout exceeded during peak hours. The DBA finds the blocker:

SELECT b.trx_id, b.trx_started, b.trx_query, p.command, p.time
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.processlist p ON p.id = b.trx_mysql_thread_id;

+----------+---------------------+-----------+---------+------+
| trx_id   | trx_started         | trx_query | command | time |
+----------+---------------------+-----------+---------+------+
| 4212760  | 2026-06-23 10:10:00 | NULL      | Sleep   | 187  |
+----------+---------------------+-----------+---------+------+

The blocker started a transaction, updated an inventory row, then made a slow third-party payment API call before committing — holding the row lock for 187 seconds. Every concurrent purchase of the same SKU queues behind it and times out at 50s.

Fix (code): move the external payment call outside the database transaction. Reserve inventory in a short transaction, commit, then call the payment API, then update the order in a second short transaction. After the change, lock hold times drop to milliseconds and the 1205 errors disappear. Killing the blocker only mitigates the symptom for the moment.

Prevention Best Practices

• Keep transactions short: never hold a transaction open across network calls, external APIs, or user think-time. Acquire locks, do the minimum, commit.
• Index the columns used in WHERE clauses of UPDATE/DELETE so InnoDB locks only the rows it changes, not whole scans.
• Break large batch DELETE/UPDATE jobs into small chunked transactions (e.g. a few thousand rows, commit, repeat) so they never hold long-lived locks.
• Reduce hot-row contention by sharding counters, using queue tables with claim semantics, or aggregating writes.
• Monitor for long-lived transactions (information_schema.innodb_trx ordered by trx_started) and alert when any exceeds a threshold; an idle uncommitted transaction is almost always a bug.
• For quick triage of a 1205 spike, the free incident assistant can correlate the blocker/waiter chain into a likely cause. More in MySQL guides.

Quick Command Reference

-- Who is waiting?
SELECT trx_id, trx_state, trx_wait_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

-- Blocker vs waiter chain
SELECT r.trx_id AS waiting, b.trx_id AS blocking,
       b.trx_mysql_thread_id AS blocking_thread, b.trx_query
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;

-- Inspect the blocking thread
SELECT id, user, command, time, state, info
FROM information_schema.processlist WHERE id = <BLOCKING_THREAD>;

-- Oldest open transactions
SELECT trx_id, trx_started, trx_rows_locked, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5;

-- Release the lock
KILL <BLOCKING_THREAD>;

-- Full lock detail / current timeout
SHOW ENGINE INNODB STATUS\G
SELECT @@innodb_lock_wait_timeout;

Conclusion

ERROR 1205 (HY000) means a transaction waited past innodb_lock_wait_timeout for a row lock another transaction held. The usual root causes:

1. An idle, uncommitted transaction sitting in Sleep while holding locks.
2. A long-running batch UPDATE/DELETE locking many rows.
3. Hot-row contention serializing many writers behind one lock.
4. A missing index forcing InnoDB to lock far more rows than necessary.
5. Application code holding a transaction open across slow external work.
6. A innodb_lock_wait_timeout set too low for legitimate long operations.

Identify the blocker via data_lock_waits and innodb_trx, kill it to recover, then fix the real cause — usually shortening transactions and adding the right index.