Implementing SKIP LOCKED and NOWAIT for Seat Reservation in MySQL InnoDB

Seat reservation is a common scenario (e.g., train tickets, cinema seats) that can be implemented entirely with a database. This article demonstrates a pure‑SQL approach using MySQL InnoDB.

First, a simple seats table is created:

CREATE TABLE seats (
  seat_no INT PRIMARY KEY,
  booked ENUM('YES','NO') DEFAULT 'NO'
) ENGINE=InnoDB;

To reserve seats 2 and 3, a transaction is started and the rows are locked with SELECT ... FOR UPDATE :

START TRANSACTION;
SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO' FOR UPDATE;

The SELECT … FOR UPDATE can produce three situations:

Success with both rows returned – the lock is acquired, the application can wait for payment, then update the rows and commit: UPDATE seats SET booked = 'YES' WHERE seat_no IN (2,3); COMMIT;

Success but an empty or partial result set – the lock could not be obtained.

Timeout after waiting (default 50 s), producing an error such as: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction This indicates the rows are held by another transaction.

To avoid waiting, MySQL 8.0 provides the SKIP LOCKED and NOWAIT options. SKIP LOCKED skips rows that are already locked, returning only the rows that could be locked; NOWAIT returns an error immediately if any row is locked.

SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO' FOR UPDATE SKIP LOCKED;

SELECT * FROM seats WHERE seat_no IN (2,3) AND booked = 'NO' FOR UPDATE NOWAIT;

If NOWAIT encounters a locked row, MySQL returns: ERROR 3572 (HY000): Do not wait for lock.

When locks are successfully acquired, the lock status can be inspected via the performance schema:

SELECT thread_id, object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;

SKIP LOCKED is also useful for random seat allocation, e.g. selecting any two free seats:

SELECT * FROM seats WHERE booked = 'NO' LIMIT 2 FOR UPDATE SKIP LOCKED;

These clauses affect only row‑level locks; table‑level or metadata locks can still cause blocking.

In the InnoDB source code, the implementation adds a new select_mode enum (ordinary, skip‑locked, no‑wait), sets the mode before query execution, and adjusts the lock‑acquisition logic to return DB_SKIP_LOCKED or DB_LOCK_NOWAIT as appropriate. The handling of secondary indexes is also described.

The author, a contributor to WL#8919 (InnoDB: Implement NOWAIT and SKIP LOCKED), encourages readers to share additional use cases.