Explain transaction isolation levels and how you would test for a race condition such as double booking.

Isolation defines how much one transaction can see of other transactions running at the same time. The SQL standard defines four levels, each preventing more anomalies at the cost of more locking, aborts or reduced throughput. For testing, the important point is that most databases default to a level that does not prevent check-then-act races, so features like “book a seat if it is free” need a constraint or explicit locking, and that is something a tester can and should probe.

The phenomena

• Dirty read: reading another transaction’s uncommitted changes.
• Non-repeatable read: reading the same row twice in one transaction and getting different values because another transaction committed in between.
• Phantom read: re-running a query with a WHERE condition and getting a different set of rows.
• Lost update / write skew: two transactions read the same state, both decide based on it, and both write; one effect is lost or an invariant is broken.

The levels (standard definitions)

Level	Dirty read	Non-repeatable read	Phantom
Read Uncommitted	possible	possible	possible
Read Committed	prevented	possible	possible
Repeatable Read	prevented	prevented	possible
Serializable	prevented	prevented	prevented

Implementations differ, so always check your database:

• PostgreSQL: default is Read Committed. Read Uncommitted behaves like Read Committed. Repeatable Read is snapshot isolation and also prevents phantoms, but not write skew. Serializable (SSI) prevents all anomalies and aborts conflicting transactions with SQLSTATE 40001, which the application must retry.
• MySQL InnoDB: default is Repeatable Read, with next-key locking for locking reads.
• SQL Server: default Read Committed (with locking, or row versioning if READ_COMMITTED_SNAPSHOT is on).

The double-booking race

-- Both requests run this at the same moment under Read Committed
BEGIN;
SELECT count(*) FROM bookings WHERE seat_id = 42 AND show_id = 7;  -- both see 0
INSERT INTO bookings (seat_id, show_id, user_id) VALUES (42, 7, :user);
COMMIT;                                                               -- both succeed

Each transaction’s check is correct in isolation; together they double-book.

Fixes a tester should know about

-- 1. A constraint makes the database the arbiter (best for uniqueness)
ALTER TABLE bookings ADD CONSTRAINT uq_seat_show UNIQUE (show_id, seat_id);

-- 2. Pessimistic locking of the row being decided on
BEGIN;
SELECT status FROM seats WHERE id = 42 AND show_id = 7 FOR UPDATE;  -- second caller waits
UPDATE seats SET status = 'BOOKED', user_id = :user WHERE id = 42 AND show_id = 7 AND status = 'FREE';
COMMIT;

-- 3. Optimistic locking with a version column
UPDATE seats SET status = 'BOOKED', version = version + 1
WHERE id = 42 AND version = :version_read;  -- 0 rows updated means someone else won

Or run the transaction at Serializable and retry on serialization failures.

How I would test it

1. Fire genuinely concurrent requests at the API, not sequential ones. For example, 20 parallel booking calls for the same seat:

const results = await Promise.all(
  Array.from({ length: 20 }, (_, i) =>
    request.post('/api/bookings', { data: { showId: 7, seatId: 42, userId: `u${i}` } }),
  ),
);
const created = results.filter((r) => r.status() === 201);
expect(created).toHaveLength(1);
expect(results.filter((r) => r.status() === 409)).toHaveLength(19);

2. Assert on the database state, not only responses: SELECT count(*) FROM bookings WHERE show_id = 7 AND seat_id = 42 must be 1.
3. Repeat the test many times; races are probabilistic. A single pass proves little.
4. For deterministic reproduction at the SQL level, open two sessions (two psql windows or two connections in a test) and interleave the statements manually.
5. Check the error path: the loser gets a clear 409, not a 500 from an unhandled constraint violation.

What interviewers look for

• Correct phenomena and levels, plus awareness that implementations and defaults differ.
• Recognising check-then-act as the root cause of double booking.
• Concrete prevention options: unique constraints, FOR UPDATE, optimistic locking, Serializable with retries.
• A realistic concurrency test that asserts on final state and is run repeatedly.