Locking A Row That Is Not There

Seat holds are created like this, at READ COMMITTED. seat_holds has no unique constraint, and there is a permanent row in seats for every seat.

BEGIN;
SELECT 1 FROM seat_holds WHERE seat_id = 42 FOR UPDATE;
-- 0 rows
INSERT INTO seat_holds (seat_id, user_id, held_until)
VALUES (42, $1, now() + interval '10 minutes');
COMMIT;

Two requests for seat 42 arrive three milliseconds apart and both succeed. Which changes actually prevent the double hold? More than one applies.

Run both transactions at SERIALIZABLE and retry on SQLSTATE 40001.

Add NOWAIT to the SELECT ... FOR UPDATE so the loser fails fast instead of racing.

Add UNIQUE (seat_id) and switch to INSERT ... ON CONFLICT DO NOTHING RETURNING seat_id, treating an empty result as "already held".

Run SELECT 1 FROM seats WHERE id = 42 FOR UPDATE; first, so both requests contend on a row that already exists.

Raise both to REPEATABLE READ, since PostgreSQL's implementation does not allow phantom reads.