Question 1

Postgres, default READ COMMITTED. Two requests run this at the same moment for the last seat (seats_left = 1). The app computes the new value itself. BEGIN;
SELECT seats_left FROM events WHERE id = 7;
-- app: if seats_left > 0
UPDATE events SET seats_left = 0 WHERE id = 7;
INSERT INTO tickets ...;
COMMIT; What happens?

Accepted Answer

Both requests sell the seat, because both reads see 1 before either commits A transaction does not lock rows it only reads. At READ COMMITTED both SELECTs see 1, both pass the check, and the second UPDATE waits for the first, then writes 0 again: two tickets for one seat. Atomicity means all or nothing on failure, not mutual exclusion. Serialization errors only happen at REPEATABLE READ or SERIALIZABLE. Postgres never checks app-side logic at READ COMMITTED.

Question 2

Postgres, READ COMMITTED. Two requests race for the last seat (seats_left = 1) of event 7: each SELECTs seats_left, sees 1, then sets it to 0, so both sell the seat. You want the smallest correct fix with one database round trip and no new infrastructure. Which do you pick?

Accepted Answer

UPDATE events SET seats_left = seats_left - 1 WHERE id = 7 AND seats_left > 0, then check the affected row count The conditional UPDATE does the check and the write in one atomic statement on a locked row. The second writer re-evaluates the WHERE after the first commits, matches 0 rows, and you report sold out. A Redis lock adds a dependency and its own failure modes (expiry, failover) for a problem the row lock already solves. SERIALIZABLE everywhere costs retries on every transaction. A cache decrement is not the source of truth and can drift from the database.

Question 3

Postgres READ COMMITTED. T1 runs SELECT seats_left FROM events WHERE id = 7 FOR UPDATE, reads 1, sets it to 0 and has not committed yet. T2 runs the same SELECT ... FOR UPDATE. What does T2 get?

Accepted Answer

T2 waits until T1 commits, then reads seats_left = 0 FOR UPDATE takes a row lock, so T2 blocks on it. At READ COMMITTED, once T1 commits, Postgres re-checks the updated row version and returns it, so T2 sees 0. Reading the old value without waiting is what a plain SELECT does. Failing at once is FOR UPDATE NOWAIT. Blocking only at the UPDATE describes a plain SELECT followed by UPDATE, which is the original race.

Question 4

Optimistic locking on a documents table: UPDATE documents
SET body = $1, version = version + 1
WHERE id = $2 AND version = $3; The statement reports 0 rows updated. What should the API do?

Accepted Answer

Treat it as a conflict: someone changed the row since you read it, so reload and retry or return 409 The version you read is stale, so another writer won. Reload, merge or reapply the change, or surface a conflict to the user. Inserting would create a duplicate of an existing document. Databases do not queue failed conditional updates. Retrying with the same old version will match 0 rows forever.

Question 5

You are choosing between optimistic version checks and SELECT ... FOR UPDATE. In which workload is pessimistic locking clearly the better choice?

Accepted Answer

A flash sale where thousands of buyers hit the same inventory row within seconds Under heavy contention on one row, optimistic writers mostly fail and retry, so retries pile up and throughput collapses. Queueing on a row lock is cheaper. Rare conflicts (wiki, own profile) favour optimistic: no locks held, little retry cost. Offline edits cannot hold a database lock for hours at all, so they need version checks or merge logic.

Question 6

Ten workers pull jobs from a Postgres jobs table. You need each job claimed by exactly one worker and workers must not wait on each other. Which claim query fits?

Accepted Answer

SELECT id FROM jobs WHERE status = 'ready' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED SKIP LOCKED makes each worker pass over rows another worker holds and lock the next free one, so claims are exclusive and nobody blocks. Plain FOR UPDATE makes every worker queue on the same first row. NOWAIT raises an error instead of moving on. A plain SELECT then UPDATE lets two workers read the same row and both take it.

Question 7

A worker takes a Redis lock with a 10 second TTL, then hits a 15 second GC pause. The lock expires, a second worker takes it, and both write to the database. What actually prevents the corrupted write?

Accepted Answer

A fencing token: an increasing number from the lock that the database checks and rejects if older Only the resource can reject a stale holder. With a monotonically increasing token stored on the row, the paused worker's write carries an older token and the database refuses it. Any TTL can be exceeded by a pause or network delay. Redlock still relies on timing and does not stop a paused client. Checking before writing leaves a gap: the pause can happen right after the check.

Question 8

A like counter is one row updated with likes = likes + 1. A viral post drives 5,000 increments per second and requests queue on the row lock. Exact counts can lag by a few seconds. What fixes it?

Accepted Answer

Spread increments over N counter rows (or buffer them) and sum on read The bottleneck is one row lock per increment. Sharded counter rows or batched increments from a queue cut contention by N, and reads sum a few rows or a cached total. FOR UPDATE takes the same lock the atomic UPDATE already takes. Optimistic locking turns waiting into failed retries, which is worse at this contention. Higher isolation adds aborts and does not remove the single hot row.

Question 9

Postgres. Bookings have room_id and a stay as a daterange. Two concurrent requests must never book overlapping stays for the same room. Which guarantee holds under concurrency without app-level locking?

Accepted Answer

An exclusion constraint: EXCLUDE USING gist (room_id WITH =, stay WITH &&) An exclusion constraint rejects any new row whose room matches and whose range overlaps an existing one, enforced by the database even for concurrent inserts. The = on room_id needs the btree_gist extension. UNIQUE on the start date only blocks identical starts, not overlaps. Check-then-insert at READ COMMITTED races because neither transaction sees the other's uncommitted row. An index speeds lookups but enforces nothing.

Question 10

Postgres READ COMMITTED. Balance is 500. Two transactions run concurrently and both commit: UPDATE accounts SET balance = balance - 100
WHERE id = 1; What is the final balance?

Accepted Answer

300 The second UPDATE waits for the first's row lock, then re-reads the committed 400 and writes 300. Doing the arithmetic in SQL avoids the lost update. 400 is the lost update you get when the app reads 500 and writes back a computed value. 500 would mean both were lost. At READ COMMITTED a simple conflicting UPDATE waits, it does not fail.

Question 11

Walk through how you prevent double booking of seats in a ticketing system, from seat hold to payment.

Accepted Answer

Hold a seat with a conditional write: UPDATE seats SET status = 'held', held_by = $u, hold_expires = now() + interval '10 minutes' WHERE id = $s AND (status = 'free' OR hold_expires < now()), and check the row count. Payment confirms with another conditional UPDATE from held by this user to sold. Expired holds free themselves through the expiry check or a sweeper.

Mention the alternatives and why: FOR UPDATE for short critical sections, optimistic version checks when contention is low, a Redis hold with TTL only as a fast path while the database stays the source of truth. Make payment idempotent so a retried confirm cannot sell twice.

Question 12

Postgres REPEATABLE READ. Rule: at least one doctor on call. Alice and Bob are both on call. Each runs at the same time: BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;
-- sees 2, so it is safe to leave
UPDATE doctors SET on_call = false
WHERE name = $me;
COMMIT; What happens?

Accepted Answer

Both commit and nobody is on call This is write skew. Each transaction reads a snapshot showing 2 doctors and updates a different row, so no write conflicts and both commit. Postgres REPEATABLE READ is snapshot isolation and allows this. A serialization error here needs SERIALIZABLE. The UPDATEs touch different rows, so neither blocks. Repeatable read keeps your reads stable, it does not check that other writers changed what you read.

Question 13

Postgres REPEATABLE READ. Rule: at least one doctor on call. Alice and Bob are both on call. Each runs at the same time:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;
-- sees 2, so it is safe to leave
UPDATE doctors SET on_call = false
WHERE name = $me;
COMMIT;

What happens?

Accepted Answer

Run SERIALIZABLE and retry on serialization failure, or lock the rows you counted with FOR UPDATE SERIALIZABLE (SSI) detects the read-write dependency and aborts one transaction. Alternatively, select the on-call rows themselves with FOR UPDATE (count(*) cannot take FOR UPDATE): the second transaction blocks on the first, then re-checks at READ COMMITTED or gets a serialization error to retry at REPEATABLE READ. READ COMMITTED is weaker. A unique name constraint has nothing to do with the count. They already run in transactions, and that alone does not help.

Question 14

A report transaction runs the same plain SELECT on one account twice, a few seconds apart. Between the two reads another transaction updates that row and commits. Each database runs at its default isolation level. What does the second SELECT return?

Accepted Answer

Postgres sees the new value, MySQL InnoDB still sees the old one Postgres defaults to READ COMMITTED, where each statement takes a fresh snapshot, so the second read sees the commit. InnoDB defaults to REPEATABLE READ, where plain reads use the snapshot from the first read, so it still sees the old value. Both old would mean both default to a snapshot level. Both new would mean both default to READ COMMITTED. The swapped pair is the common mix-up of the two defaults.

Question 15

Postgres REPEATABLE READ. T1 and T2 both read a row, then both UPDATE that same row. T1 commits first. What happens to T2?

Accepted Answer

T2 fails with could not serialize access due to concurrent update At REPEATABLE READ Postgres will not let a transaction update a row changed after its snapshot started. T2 waits for T1, then aborts, and the app must retry. Silent overwrite is the lost update this level prevents in Postgres (MySQL REPEATABLE READ does allow it with plain reads). Waiting and reapplying is READ COMMITTED. One row and one lock order is not a deadlock.

Question 16

You move a booking flow to Postgres SERIALIZABLE. What must the application add?

Accepted Answer

Retry the whole transaction when it fails with SQLSTATE 40001 SSI aborts transactions that could produce a non-serializable result. The work must be replayed from BEGIN, since the reads it was based on are stale. Aborts are how SERIALIZABLE works. Retrying one statement keeps the stale reads. Serialization failures are not lock timeouts.

Database contention and isolation interview questions

Contention and lost updates

Isolation anomalies

More database topics

Practise these on your commute