# MySQL error 1205: Lock wait timeout exceeded; try restarting transaction

Lock wait timeout exceeded; try restarting transaction

Codes: 1205ER_LOCK_WAIT_TIMEOUTHY000

Also seen as: SQLSTATE HY000 1205 · lock wait timeout exceeded mysql · Deadlock found when trying to get lock · innodb_lock_wait_timeout

A write fails after a long pause rather than immediately, and retrying often succeeds. It appears under load and never in isolation. The statement in the error is the one that waited, which sends people to optimise it — while the actual problem is a different transaction that held a lock and did not release it.

1. 01

   Another transaction holds the lock and has not committed

   primary

   A transaction opened, wrote a row, and then did something slow — an HTTP call, a queue publish, waiting on user input — before committing. Its locks are held for the whole duration, and everything touching those rows queues behind it.

   → how to tell: information_schema.innodb_trx shows a transaction in a running state with a trx_started timestamp many seconds old and few rows modified

2. 02

   A transaction was left open by the application

   primary

   Autocommit disabled and no explicit commit, or an error path that returns without rollback. The connection sits idle holding locks until it is reused or closed — indefinitely, from the database's point of view.

   → how to tell: The blocking thread's state is idle or sleeping while its transaction is still active in innodb_trx

3. 03

   The statement locks far more rows than it changes

   common

   Without a usable index, InnoDB locks index records across the range it has to scan, not just the rows that match. A poorly indexed UPDATE serialises writers that would otherwise not conflict at all.

   → how to tell: EXPLAIN on the blocking statement shows a full scan or a large rows estimate, and adding an index reduces both the scan and the contention

4. 04

   Genuine contention on the same rows

   common

   A counter row, a job queue head, or a single settings row updated by every request. Every writer is correct and short; there are simply too many of them for one row.

   → how to tell: The waiting and blocking statements target the same primary key, and wait time scales with request rate rather than with statement duration

5. 05

   The timeout is shorter than the workload needs

   edge

   innodb_lock_wait_timeout defaults to 50 seconds. A batch job that legitimately holds locks for longer will time out other work regardless of how well written it is.

   → how to tell: The wait fails at a consistent boundary matching the configured timeout, and the blocking transaction is a known long-running job

6. 06

   A schema change is holding a metadata lock

   edge

   DDL takes metadata locks that block writes to the table. An ALTER waiting behind an open transaction blocks everything behind itself in turn.

   → how to tell: SHOW PROCESSLIST includes a thread in 'Waiting for table metadata lock', and a DDL statement is present

1. 01Find who is blocking whom while it is happening. This is the whole diagnosis — the error names the victim, this names the culprit.

   $ mysql -e "SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age FROM sys.innodb_lock_waits\G"

   note: On MySQL without the sys schema, use information_schema.innodb_trx joined against performance_schema.data_lock_waits.

2. 02List long-running transactions, including ones whose connection looks idle. An idle connection with an open transaction is the classic cause.

   $ mysql -e "SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_rows_locked, trx_query FROM information_schema.innodb_trx ORDER BY trx_started\G"

3. 03Shorten the transaction so locks are held for as little time as possible. Do slow work before opening it, not inside it.
   javascript

   // 🔴 the lock is held across a network call
   await db.begin();
   await db.query("UPDATE orders SET status='paid' WHERE id=?", [id]);
   await paymentProvider.capture(id);   // seconds, holding the row lock
   await db.commit();
   
   // ✅ external work first, transaction last and brief
   const receipt = await paymentProvider.capture(id);
   await db.begin();
   await db.query("UPDATE orders SET status='paid', receipt=? WHERE id=?", [receipt, id]);
   await db.commit();
   

4. 04Make sure every path commits or rolls back, including error paths. A returned connection with an open transaction keeps its locks.
   javascript

   const conn = await pool.getConnection();
   try {
     await conn.beginTransaction();
     await work(conn);
     await conn.commit();
   } catch (err) {
     await conn.rollback();
     throw err;
   } finally {
     conn.release();
   }
   

5. 05Index the columns the blocking statement filters on, so it locks the rows it changes rather than everything it scans.

   $ mysql -e "EXPLAIN UPDATE orders SET status='x' WHERE customer_id=42;"

6. 06Retry the victim with backoff. A lock wait timeout is transient by nature, and the statement is safe to re-run after a rollback.
   javascript

   for (let attempt = 0; attempt < 3; attempt++) {
     try { return await runTransaction(); }
     catch (e) {
       if (e.errno !== 1205 || attempt === 2) throw e;
       await sleep(100 * 2 ** attempt * (0.5 + Math.random()));
     }
   }
   

7. 07Adjust innodb_lock_wait_timeout only after the above. Raising it makes callers wait longer rather than reducing contention; lowering it fails faster, which is sometimes the better trade for interactive requests.

verify · Under the load that produced them, sys.innodb_lock_waits stays empty and no 1205 reaches the application, with any residual occurrences retried transparently.

if that fails · To clear an incident already in progress, kill the blocking transaction rather than the waiters — KILL on the blocking_pid from innodb_lock_waits releases its locks and lets the queue drain immediately.

MySQL

5.7 and later— innodb_lock_wait_timeout defaults to 50 seconds and is settable per session.

InnoDB

all versions— Locks are taken on index records, so a statement without a usable index locks more rows than it modifies.

Platforms

self-hosted, managed MySQL

• ✗ MySQL error 1213 deadlock found, which is a detected cycle aborted immediately rather than a wait that expired
• ✗ PostgreSQL 40P01, the equivalent deadlock condition in a different engine
• ✗ Connection pool acquisition timeouts, which occur before any database lock is requested
• ✗ Query timeouts such as max_execution_time, where the statement is slow rather than blocked

1. 01official-docsMySQL Server Error Reference — ER_LOCK_WAIT_TIMEOUT

   https://dev.mysql.com/doc/mysql-errors/8.4/en/server-error-reference.html

   Oracle · read 2026-08-08

   supports: That error 1205 is ER_LOCK_WAIT_TIMEOUT with SQLSTATE HY000, reported by InnoDB when a lock wait expires — distinct from the deadlock error, which is 1213.

   “Lock wait timeout exceeded; try restarting transaction”

2. 02official-docsMySQL — InnoDB Locking

   https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

   Oracle · read 2026-08-08

   supports: That InnoDB locks index records rather than rows in the abstract, which is why a statement without a usable index locks far more than it changes and serialises writers unnecessarily.

   “A record lock is a lock on an index record.”

mediumThe error's identity and SQLSTATE, and the fact that InnoDB locks index records, are quoted from MySQL's own reference — and the index-scope claim is the least obvious of the causes. Confidence is medium rather than high because it rests on two sources: the 50-second default and the diagnostic queries against sys and information_schema are standard practice rather than statements quoted here.

• → PostgreSQL error 40P01: deadlock detected