A transaction defines a unit of work, while isolation defines important aspects of what concurrent work can observe. Interview answers should trace a concrete schedule. Saying “use a transaction” does not automatically fix a read-then-write race or make every isolation level behave identically.

The exercise uses SQL Server. Run the setup only in an isolated exercise database. The fictional seat count is not a real booking system and must not be tested against production data.

What race are we trying to avoid?

Two callers can both read that one seat remains before either changes it. If each later writes a decision based only on that earlier observation, the workflow can accept incompatible results. The exact outcome depends on the statements, isolation settings and error handling.

One way to make this particular decision explicit is to put the availability condition in the update and check whether a row changed.

-- Setup once in a disposable exercise database.
CREATE TABLE dbo.SeatExercise (
 SeatGroupId int PRIMARY KEY,
 Available int NOT NULL CHECK (Available >= 0)
);
INSERT dbo.SeatExercise VALUES (1, 1);

What should each competing session run?

-- Run this batch in each of two sessions against that exercise database.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
UPDATE dbo.SeatExercise
SET Available = Available - 1
WHERE SeatGroupId = 1 AND Available > 0;
DECLARE @Changed int = @@ROWCOUNT;
COMMIT TRANSACTION;
SELECT @Changed AS Accepted;

With the stated ordinary read-committed exercise and successful completion, one session changes the available row and the other does not accept another seat after it is gone. Check the row count; do not treat every successfully executed SQL batch as a successful reservation.

What does the transaction add?

For a single conditional update, the statement already has important atomic behavior. An explicit transaction becomes more important when related statements must commit or roll back together. The example shows a boundary that can later include such related work, but it does not imply that wrapping arbitrary application code fixes every race.

Also define how a caller handles an exception before commit. A production workflow needs a reliable rollback/error policy and must distinguish a rejected condition from an unexpected failure.

How do isolation settings affect the discussion?

SQL Server supports different isolation choices, and database options can change read-committed read behavior. Snapshot-based reads and locking reads do not have identical visibility or conflict behavior. Check the actual settings rather than giving one universal locking explanation for every database.

Higher isolation can introduce blocking or conflicts that the application must handle. A retry should be bounded and safe for the entire operation; it must not repeat an external side effect blindly.

What should the two-session test record?

  • The initial count and exact statements in each connection.
  • The isolation level and relevant database options.
  • When each session starts, waits and commits.
  • Each Accepted result and the final available count.
  • Any error, rollback or conflict rather than hiding it as zero.

Reset and remove the fictional table only within the disposable exercise environment. Do not use a cleanup script that could target a real application's similarly named table.

How to answer a follow-up about distributed work

A database transaction does not automatically include a remote notification or payment. Explain which changes belong to the local transaction and how later work learns that it committed. Likewise, a timeout at the caller does not prove the database rolled back. Those boundaries are often the most important part of a careful answer.

References and next practice

learn.microsoft.com — sql isolation

sql question collection · Browse interview topics · Practise a question in Interview Challenge · How to Answer Technical Interview Questions Out Loud