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.11 · 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.11 · 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.11 · 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.