NOWAIT, lock timeouts, SKIP LOCKED

MySQL 8.4 InnoDB offers different responses to row-lock contention. These controls do not bound every kind of wait a query can encounter.

NOWAIT: fail fast

The existing primary-key row is locked by A. B's locking read returns 3572, then succeeds after A commits.

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, no wait for this record lock
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

The variable contract sets an InnoDB row-lock timeout in whole seconds, with a default of 50. It does not govern metadata locks. With innodb_rollback_on_timeout=OFF (the default), 1205 rolls back the statement, not the whole explicit transaction.

A

B

UPDATE (holds row lock)

UPDATE — waits 1s← 1205 Lock wait timeout exceeded

COMMIT

retry UPDATE→ 300

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 OK

B> SELECT @@innodb_rollback_on_timeout AS rollback_on_timeout;
 rollback_on_timeout 
---------------------
                   0 
(1 row)

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = 125 WHERE id = 2;
Query OK, 1 row affected

B 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 transaction

B's earlier change survives, and C still waits for B's lock on bob.

B> SELECT balance FROM accounts WHERE id = 2;
 balance 
---------
     125 
(1 row)

C> UPDATE accounts SET balance = balance + 1 WHERE id = 2;
⏳ C is waiting for a lock…

B> ROLLBACK;
Query OK

⏵ C resumes:
Query OK, 1 row affected

C> SELECT balance FROM accounts WHERE id = 2;
 balance 
---------
     101 
(1 row)

After A commits, B can retry the failed update as a new autocommit statement.

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

The transcript checks the OFF setting and shows B's earlier update and its lock surviving 1205. B's explicit ROLLBACK undoes that update and releases C. A detected deadlock (1213) rolls back the whole transaction. Starting the server with innodb_rollback_on_timeout=ON changes the timeout rollback scope; that configuration is documented, not exercised here.

Decide whether the still-open transaction remains valid before retrying a statement. ROLLBACK and restart when its earlier decisions must be reconsidered.

SKIP LOCKED: the job-queue primitive

The workers acquire different available rows in this schedule. D initially sees no available row even though jobs still exist; after A's explicit rollback, D can select job 1. No worker crash or external job execution is tested.

A

B

C

D

grabs job 1

job 1 taken→ grabs job 2

grabs job 3

no available row→ nothing

ROLLBACK→ job 1 lock released

grabs job 1

Four sessions issue the same query in the order shown, with A, B, and C retaining their locks.

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)

D finds no available row; the table still contains the locked jobs.

D> SELECT * FROM jobs ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;
Empty set

A explicitly rolls back, releasing its job-row lock. No crash is injected.

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 OK

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

The locking-read contract limits NOWAIT and SKIP LOCKED to row-level locks. SKIP LOCKED omits locked rows and gives an inconsistent view, useful for some queue policies, not a complete view or a delivery guarantee. Both options are unsafe for statement-based replication. They can still encounter metadata-lock waits.

Further reading

• The PostgreSQL counterpart

The reconstructed support assessment records the exact manual support and execution limits for this lesson.