Two requests try to reserve the last available seat at the same time. How would you choose the transaction and isolation strategy?

Choose isolation for a concurrent reservation

Chooses constraints, conditional writes, optimistic conflicts, or stronger isolation to protect a limited-resource reservation without unnecessary blocking.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I start with the invariant: confirmed reservations must never exceed capacity. If one row stores remaining seats, an atomic conditional update such as decrementing only where Remaining > 0 can protect it without a read-then-write race; zero affected rows means sold out. A unique constraint also prevents the same user or reservation id from being accepted twice. I keep the transaction short and return a clear conflict when the invariant cannot be satisfied.

Full answer below

Practice focus

Lost updates, write skew, conditional updates, constraints, read committed, snapshot, serializable, retry behavior, and contention trade-offs.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Isolation is a means of protecting a specific invariant. The useful question is not which level sounds strongest, but which database operation prevents available capacity from becoming negative when requests overlap.
I start with the invariant: confirmed reservations must never exceed capacity. If one row stores remaining seats, an atomic conditional update such as decrementing only where Remaining > 0 can protect it without a read-then-write race; zero affected rows means sold out. A unique constraint also prevents the same user or reservation id from being accepted twice. I keep the transaction short and return a clear conflict when the invariant cannot be satisfied.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
If the invariant spans several rows or depends on a range query, read committed may allow another transaction to change the result between my read and write. Serializable can protect the range but may increase blocking or produce serialization failures that require retry. Snapshot-based isolation reduces reader-writer blocking, yet competing writes can still conflict and row versions have an operational cost. I choose the narrowest mechanism that protects the actual invariant, often combining a constraint or conditional write with bounded retry.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I verify the provider's exact semantics instead of mapping isolation names mechanically across databases. For SQL Server I distinguish lock-based read committed, read-committed snapshot at statement scope, and snapshot consistency for the transaction; serializable range protection has a different contention profile. The API operation carries an idempotency key so a client retry does not create another reservation after an ambiguous response. A real relational integration test synchronizes competing transactions at the vulnerable point and proves capacity, uniqueness, and response behavior. Metrics cover conflicts, lock waits, deadlocks, retries, and hot resources. If one seat or product becomes a permanent contention point, I revisit the data model or reservation workflow rather than endlessly raising isolation.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Reading availability and inserting later without a database guard.
Choosing serializable globally instead of protecting one invariant.
Assuming snapshot isolation prevents every write conflict.
Retrying the request without idempotency after an uncertain response.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain dirty reads or phantom rows.
Has no business example for isolation choice.
Ignores blocking and deadlocks.
Does not know how to test concurrency.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

How would an atomic conditional update protect capacity?
When would serializable range locking be necessary?
What changes under snapshot isolation?
How would you prove the design with two concurrent transactions?

Continue the section