SQL transaction isolation interview questions

What does read committed guarantee?

It prevents reading another transaction’s uncommitted changes. It does not generally promise that two statements in the same transaction see identical data.

In PostgreSQL, read committed is the default and each statement gets a new snapshot. A second query can therefore see changes committed since the first query began. In SQL Server, read committed can use locking or statement-level row versioning depending on READ_COMMITTED_SNAPSHOT. The same isolation label does not imply identical blocking behavior.

Does repeatable read prevent every concurrency anomaly?

No. PostgreSQL repeatable read provides a transaction snapshot and prevents phantom reads, but serialization anomalies can still occur. SQL Server’s lock-based repeatable read protects rows already read while allowing matching new rows to appear. Discuss the implementation rather than memorizing a universal table of names.

When would you choose serializable?

Choose it when correctness needs committed transactions to have an outcome equivalent to some serial execution. It does not require physically running all transactions one at a time.

PostgreSQL may abort a transaction when it detects a dangerous dependency pattern. Applications must be prepared to retry the whole transaction. SQL Server can use key-range locks to prevent changes to the ranges read, increasing contention. Keep transactions short and consider external side effects before adding retries.

How would you prevent overselling one remaining item?

Avoid treating a separate read followed by an unconditional write as sufficient. Express the stock condition in the update itself and check whether it affected a row:

UPDATE inventory
SET available = available - 1
WHERE product_id = 42
  AND available > 0;

An affected-row count of zero means no reservation was made. Save the associated order in the same transaction and roll back if the complete operation fails. Handle database errors, including serialization failures, explicitly. For a cart spanning several rows or warehouses, identify the larger invariant before deciding which locks or isolation level it needs.

Try explaining this scenario

Two administrators each see two active on-call engineers and remove a different engineer. Each update touches a different row, yet together they leave nobody on call. Explain why row-level write conflict detection alone may miss this invariant, and how serializable execution or a shared locking protocol can protect it.

Technical references

• PostgreSQL: transaction isolation
• Microsoft: SQL Server transaction isolation levels