MySQL: "Lock Wait Timeout Exceeded; Try Restarting Transaction"

Your transaction waited innodb_lock_wait_timeout (default 50s) for a row another transaction holds. Find the blocker, decide whether to kill it, and fix the access pattern that serializes the rows.

What you'll see

Root causes

A long-running transaction holding row locks

A transaction (or an idle-in-transaction session) holds locks past your 50s wait: batch jobs, forgotten commits, or ORM auto-commit gaps. The data_locks/performance_schema tables show exactly who holds and who waits.

Lock escalation via gaps and secondary indexes

UPDATE ... WHERE non-indexed-column locks the whole scanned range (gap locks): broad WHEREs turn row updates into table-range serialization. Missing index = hundreds of locked rows per statement.

Fix it

  1. Catch it live: see the blocker and waiter
    SELECT * FROM performance_schema.data_lock_waits\G ; SELECT * FROM sys.innodb_lock_waits\G   # blocking_pid vs waiting_pid
  2. Kill the blocking session if it's stale
    KILL <blocking_thread_id>;   # verify with SHOW PROCESSLIST that it's not legitimate work mid-flight
  3. Fix the access pattern: commit fast, index the WHERE
    # add the missing index so UPDATEs lock only matching rows; keep transactions short; SELECT outside transactions
  4. Tune the wait if legitimate concurrent work needs longer
    SET innodb_lock_wait_timeout = 10;   # often LOWER is better: fail fast, retry, instead of piling up waiters

Field note

Lower timeouts + app-level retry beats high timeouts: waiters queuing on one blocker exhaust connections and turn a single slow transaction into an outage. Deadlock (1213) vs lock-wait (1205): 1205 = gave up waiting, retry usually succeeds; 1213 = circular wait detected, one victim auto-rolled-back. Both trace back to similar ordering bugs.

Common questions

Why does restarting the transaction not always help?

Retrying works only if the blocker released the lock. If a 10-minute batch job holds it, every retry burns another 50 seconds. Find and address the holder (KILL or fix the job), then retry.

How do I find forgotten open transactions?

SELECT * FROM information_schema.innodb_trx\G — trx_started minutes ago with an idle session is the classic leak; the KILL comes with fixing the app's missing commit.

Related fixes

Ship it right the first time

Our most-documented failures, packaged as ready-to-ship starter kits: Docker, Kubernetes, and Terraform.

Browse the template store →

One-time. Yours to modify. Instant download from the NinjaOps template store.