1205 — Lock wait timeout exceeded

InnoDB row-lock timeout raises 1205 and rolls back the waiting statement with innodb_rollback_on_timeout OFF; ON rolls back the transaction, so recovery must account for that setting and the complete operation.

Reproduce it

Two sessions want the same row. B caps how long it's willing to queue. The -- [A] and -- [B] markers say who runs each statement, top to bottom.

-- [A] lock the row and hold it
BEGIN;
UPDATE accounts SET balance = 200 WHERE id = 1;

-- [B] wait at most one second for that lock, then give up
SET SESSION innodb_lock_wait_timeout = 1;
UPDATE accounts SET balance = 300 WHERE id = 1;

In this SQL excerpt, B has no BEGIN, so its UPDATE is standalone; the excerpt does not establish an open explicit transaction. The complete Scenario instead checks innodb_rollback_on_timeout OFF, writes 125 inside BEGIN before a timeout, and proves the earlier value and lock survive. B rolls back; C then asserts balance 101 on row 2. After A commits, B retries its standalone UPDATE and asserts balance 300 on row 1. ON is a documented alternative, not executed here. For whole-operation retry, end any surviving transaction and reconsider all reads/decisions. Metadata-lock timeout also uses 1205 but is controlled by lock_wait_timeout, not innodb_lock_wait_timeout. A deadlock (1213) rolls back the InnoDB transaction.

Run it yourself

The NOWAIT, lock timeouts, SKIP LOCKED lesson renders this scenario as a verified transcript alongside the fail-fast alternatives. For the queue B is waiting in, read Lock queues and Row locks.

The PostgreSQL side

PostgreSQL 55P03 covers lock_timeout and NOWAIT conflicts. Its default lock_timeout is disabled, but other deadlines or cancellation can still end a wait. An error inside PostgreSQL BEGIN leaves a failed block. InnoDB's default row-lock timeout is 50 seconds; configured values and rollback scope must be checked separately.