NOWAIT, lock timeouts, SKIP LOCKED
Waiting in a lock queue is the default, not the law. MySQL gives you three ways out: fail instantly, give up after a deadline, or pretend locked rows don't exist.
NOWAIT: fail fast
A> BEGIN;
Query OK
A> SELECT id FROM accounts WHERE id = 1 FOR UPDATE;
id
----
1
(1 row)
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT; -- ER_LOCK_NOWAIT — instantly, no waiting
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.Once A is done, the same statement succeeds.
A> COMMIT;
Query OK
B> SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
id
----
1
(1 row)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
innodb_lock_wait_timeout: wait, but not forever
Every InnoDB lock wait is already bounded by innodb_lock_wait_timeout — 50 seconds by default, settable per session (whole seconds only). When it fires you get errno 1205, and — easy to miss — it rolls back only the statement, not the transaction, which stays open and keeps every lock it already holds:
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
B> SET SESSION innodb_lock_wait_timeout = 1;
Query OKB queues for the row lock like anyone else — but gives up after a second.
B> UPDATE accounts SET balance = 300 WHERE id = 1; -- ER_LOCK_WAIT_TIMEOUT, raised after the timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionThe failure canceled only B's statement — a retry after A commits works.
A> COMMIT;
Query OK
B> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
300
(1 row)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
1205 ≠ 1213
A deadlock (1213) rolls back your whole transaction; a lock timeout (1205) rolls back one statement. After 1205 your transaction is alive and still holding locks — either retry the statement or ROLLBACK, but don't assume you're back at a clean slate. (Set innodb_rollback_on_timeout=ON server-wide if you want 1205 to roll back the whole transaction.)
SKIP LOCKED: the job-queue primitive
Four workers run the exact same query at the same time.
A> BEGIN;
Query OK
A> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> BEGIN;
Query OK
B> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; -- job 1 is locked by A — skipped, no waiting
id | task
----+--------------
2 | resize image
(1 row)
C> BEGIN;
Query OK
C> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+--------------
3 | build report
(1 row)Worker D finds the queue empty — an instant answer, not a wait.
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty setA worker crash (rollback) puts its job straight back on the queue.
A> ROLLBACK;
Query OK
D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
id | task
----+------------
1 | send email
(1 row)
B> COMMIT;
Query OK
C> COMMIT;
Query OKVerified against MySQL 8.4.10 · Run it yourself · Scenario source
Three exits from the queue, three shapes: FOR UPDATE NOWAIT fails instantly with errno 3572, innodb_lock_wait_timeout gives up after its deadline with 1205, and FOR UPDATE SKIP LOCKED pretends the locked rows aren't there. That last one reads a deliberately inconsistent view — perfect for grabbing any free job, wrong for anything that must see every row. PostgreSQL offers the same NOWAIT and SKIP LOCKED syntax but a millisecond-granular lock_timeout and SQLSTATE 55P03 (compare). Row locks have been the whole story so far; the lock that takes down migrations is a table-level one.