Isolation levels constrain which concurrent changes a transaction observes. Pick guarantees from the invariant rather than the level’s name alone.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Two concurrent bookings can both see apparent capacity unless the invariant is protected. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: State the invariant
A seat must belong to at most one booking.
Step 2: Trace concurrent transactions
Both may observe apparent availability before either commits.
Step 3: Enforce with database mechanisms
Use an appropriate unique constraint or locking strategy and handle conflicts.
Worked scenario
Two concurrent bookings can both see apparent capacity unless the invariant is protected.
Transactions A and B both read that seat 12 is free. A transaction wrapper alone does not prevent both attempting insertion. A unique seat allocation constraint makes one attempt conflict; the application maps that conflict to unavailable rather than pretending the pre-check guaranteed success.
Common mistake
A transaction wrapper alone does not prevent every race.
Verify the behavior
Coordinate simultaneous bookings against the actual engine and verify exactly one accepted allocation.
Interview exercise
Guarantee unique seat allocation.
Answer and reasoning
Use a unique constraint or suitable locking and handle conflicts; test the intended engine’s actual isolation behavior.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.