Lock wait timeout 🗃️ SQL

Lock wait timeout exceeded; try restarting transaction (MySQL 1205)

A query waited too long for a row lock held by another transaction and gave up.

Seen on: MySQL PostgreSQL

Meaning

Unlike a deadlock (detected instantly), here one transaction simply holds locks for too long — often an open transaction that was never committed, a long batch update, or a missing index making an UPDATE lock far more rows than needed. InnoDB’s default innodb_lock_wait_timeout is 50 seconds; PostgreSQL’s equivalent is lock_timeout.

Common causes

  • A transaction left open (no COMMIT/ROLLBACK), e.g. in a console or a crashed worker
  • Long-running batch UPDATE/DELETE holding many row locks
  • Missing index — UPDATE … WHERE scans and locks many rows
  • External API calls made inside a DB transaction

⚡ Quick fix

  1. Find the blocking transaction and commit/kill it
  2. Keep transactions short; never wait on network calls inside one
  3. Add indexes for UPDATE/DELETE WHERE clauses
  4. Process large updates in small batches

Detailed fix by platform

MySQL

  1. Who is blocking whom:
    sql
    SELECT * FROM sys.innodb_lock_waits\G
SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;
-- KILL <trx_mysql_thread_id>;   -- only if you are sure it is safe

PostgreSQL

  1. SELECT pid, state, query, now() - xact_start AS age FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC; and pg_blocking_pids(pid).

Code examples

Batch a large update

sql
-- instead of one huge UPDATE locking millions of rows:
UPDATE orders SET archived = 1 WHERE created_at < '2024-01-01' AND archived = 0 LIMIT 5000;
-- repeat until 0 rows affected

How to diagnose

  1. Blocker — Which transaction holds the lock, and since when?
  2. Scope — Is the WHERE clause indexed?
  3. Duration — Why is that transaction long-lived?
  4. App — Network calls inside transactions?

🧠 Still stuck? Analyze your error

Paste the full message, response headers or stack trace — we'll detect the platform and point to the most likely cause.