How would you prevent double-booking or a lost-update race condition when two transactions modify the same row?
Quick Answer
The core issue is a "read, check, then write" sequence where two transactions can both read the same starting state before either writes, so both proceed as if their check passed. Fix it with either pessimistic locking (`SELECT ... FOR UPDATE` before checking availability) or an atomic conditional update (`UPDATE ... WHERE available = true` and checking the affected row count) so the check and the write happen as one indivisible database operation rather than two separate round-trips.
Detailed Answer
This is a very common system-design-flavored SQL interview question — it tests whether you understand that "check, then act" in application code is inherently racy against another concurrent request doing the same thing.
The bug, first
-- Application code, naive version:
-- Step 1: check availability
SELECT available FROM seats WHERE id = 42; -- returns true
-- (time passes — two concurrent requests can both get 'true' here)
-- Step 2: book it
UPDATE seats SET available = false WHERE id = 42;
If two users' requests both execute Step 1 before either executes Step 2, both will see available = true and both will proceed to "book" the seat — a classic lost-update/double-booking race condition. The check and the write are two separate round-trips with a gap between them where another transaction can interleave.
Fix 1: pessimistic locking — make the check-then-act atomic via a lock
BEGIN;
SELECT available FROM seats WHERE id = 42 FOR UPDATE; -- locks the row
-- Any other transaction's FOR UPDATE on row 42 now blocks here until we commit/rollback
IF available THEN
UPDATE seats SET available = false WHERE id = 42;
COMMIT; -- lock released, second transaction now proceeds and sees available=false
ELSE
ROLLBACK;
END IF;
The second transaction's SELECT ... FOR UPDATE blocks until the first commits, then correctly sees available = false and can reject the booking.
Fix 2: atomic conditional update — no explicit lock needed
Often simpler and doesn't require holding a transaction open across application logic:
UPDATE seats
SET available = false
WHERE id = 42 AND available = true;
-- Check rows affected (returned by most drivers/ORMs):
-- If 1 row affected: you got the seat.
-- If 0 rows affected: someone else already booked it (or it never existed) -- reject/retry.
This works because the check (available = true) and the write happen as a single atomic statement at the database level — the database itself guarantees no other transaction's update can interleave in the middle of one UPDATE statement's row evaluation, regardless of isolation level.
Which to prefer
The atomic conditional UPDATE (Fix 2) is usually the better default — it avoids holding open transactions/locks across application logic (which is risky if that logic is slow or fails unexpectedly), and it composes well with optimistic-concurrency patterns more broadly. Reach for explicit FOR UPDATE locking when the "check" involves more complex logic than a single column comparison that can't be expressed as a single atomic UPDATE ... WHERE clause.
The general principle
Never trust a "read, then act based on what I read" sequence to be safe under concurrency unless the read and the act are combined into a single atomic database operation, or the row is explicitly locked for the entire duration between them.