Interview questions

Interview questions (with the counter-questions you'll get)

Q1. What is a transaction, and what does ACID guarantee?

They're testing: fundamentals with depth.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** A transaction groups operations so they commit or roll back as one. ACID: atomicity (all or nothing, via an undo or write-ahead log), consistency (constraints hold), isolation (concurrent transactions don't see each other's partial work, to a configured level), and durability (committed data survives crashes, via the log written to disk before commit).
3
4**Counter-questions:**
5- *"Does ACID mean no race conditions?"* → No. Default isolation levels like Read Committed still allow lost updates, non-repeatable reads and phantoms; you add atomic updates, locks or constraints, or raise the isolation level.
6
7**Trap:** assuming the default isolation prevents all races.

Q2. How do you stop two users booking the last seat?

They're testing: the most common concurrency question.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Make the database enforce it. For a counter, an atomic conditional update (`SET left = left - 1 WHERE left > 0`) and check the affected row count. For numbered seats, a unique constraint on (event, seat) or a conditional update from free to held, so only one wins. Alternatives are `SELECT ... FOR UPDATE` on the seat row, or optimistic locking with a version column.
3
4**Counter-questions:**
5- *"What about thousands of concurrent users?"* → Avoid one hot row: lock per seat, use holds, and put a queue or waiting room in front (Chapter 43).
6
7**Trap:** "check if it's free, then insert" without a lock or constraint.

Q3. Explain the isolation levels and the anomalies they prevent.

They're testing: precise knowledge.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Read Uncommitted allows dirty reads; Read Committed prevents them but allows non-repeatable reads and phantoms; Repeatable Read prevents non-repeatable reads, and the standard allows phantoms; Serializable prevents all, making the outcome equivalent to some serial order. Real databases differ: PostgreSQL defaults to Read Committed and its Repeatable Read is snapshot isolation; MySQL InnoDB defaults to Repeatable Read.
3
4**Counter-questions:**
5- *"What's write skew?"* → Two transactions read overlapping data and write different rows, each valid alone but together breaking a rule. Snapshot isolation allows it; serializable prevents it.
6
7**Trap:** reciting levels without anomalies.

Q4. Pessimistic vs optimistic locking: when do you use each?

They're testing: trade-off thinking.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Pessimistic locks rows before changing them, so others wait; best when conflicts are frequent and transactions short. Optimistic reads a version and makes the write conditional on it, retrying if it changed; best when conflicts are rare or a user takes a long time between reading and writing, like editing a form.
3
4**Counter-questions:**
5- *"Optimistic under heavy contention?"* → Most attempts fail and retry, wasting work and adding latency. Switch to pessimistic locks, atomic updates or a queue.
6
7**Trap:** not knowing optimistic locking takes no lock at all.

Q5. What is a deadlock, and how do you handle it?

They're testing: operational understanding.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** A cycle of transactions each waiting for a lock another holds. Databases detect cycles in a wait-for graph and abort one victim. Prevent most by locking resources in a consistent order and keeping transactions short; handle the rest by retrying the aborted transaction.
3
4**Counter-questions:**
5- *"Can timeouts replace deadlock detection?"* → They can break deadlocks crudely, but they also abort slow non-deadlocked transactions and add delay. Detection is more precise.
6
7**Trap:** not mentioning retries.

Q6. How do you handle a transaction that spans multiple services?

They're testing: microservices realism.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Avoid it where possible by keeping related data in one service. Otherwise, use a saga: a sequence of local transactions, each with a compensating action, orchestrated by a coordinator or driven by events. Make each step idempotent and put irreversible actions last. Two-phase commit is an option between tightly controlled systems that support it, but it blocks if the coordinator fails and holds locks across services.
3
4**Counter-questions:**
5- *"What do sagas give up?"* → Isolation: intermediate states are visible. Design states like "pending" and handle them in the UI.
6
7**Trap:** proposing 2PC between independent microservices by default.

Q7. How does two-phase commit work, and what's its weakness?

They're testing: a classic protocol.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** The coordinator asks all participants to prepare; each makes the change durable, locks resources and votes. If all vote yes, the coordinator tells them to commit; otherwise, to abort. The weakness: participants that voted yes must wait for the decision while holding locks, so a coordinator failure can block them; and it adds round trips.
3
4**Counter-questions:**
5- *"How do modern distributed databases avoid the blocking?"* → They replicate the coordinator's state with consensus (Raft or Paxos), so a new coordinator can finish the decision.
6
7**Trap:** not knowing why 2PC is called blocking.

Q8. How would you implement a distributed lock, and what can go wrong?

They're testing: depth beyond "use Redis".

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** A key set only if absent with an expiry, holding a unique token, released only by the token's owner; or leases from ZooKeeper or etcd. What goes wrong: the lease expires while the holder is paused, so two holders exist. Use fencing tokens, increasing numbers checked by the resource, and prefer database constraints for correctness-critical cases.
3
4**Counter-questions:**
5- *"Why does the token need to be checked by the resource?"* → Because only the resource sees both writes; the old holder doesn't know it lost the lock.
6
7**Trap:** assuming a Redis lock with an expiry is always safe.

Q9. How do seat holds work in a ticketing system?

They're testing: applying concepts to a classic design.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Booking is split into hold and confirm. A hold atomically marks a seat held by a user with an expiry; payment confirms only if the hold is still theirs and unexpired; a background job or read-time check releases expired holds. Hold time balances payment time against idle seats, and holds per user are limited to stop hoarding.
3
4**Counter-questions:**
5- *"Payment succeeds one second after the hold expired. Now what?"* → Either honour it if the seat is still free, or refund automatically. Decide the rule up front and make it idempotent.
6
7**Trap:** booking directly without a hold, forcing users to race while typing card details.

Q10. You see the stock of a product go negative in production. How do you investigate and fix it?

They're testing: debugging concurrency in real life.

Think about your answer first, then open it.

reveal

1Show the strong answer, counter-questions and trap
2**Strong answer:** Negative stock means two or more decrements acted on the same value: likely a read-modify-write in code, or a check-then-act without a lock. I'd look for the decrement code path, check the isolation level, and reproduce with concurrent requests. Fix with an atomic conditional update, a CHECK constraint so stock can never go below zero, and tests that fire concurrent requests.
3
4**Counter-questions:**
5- *"How do you test for race conditions?"* → Run many concurrent requests against a real database in tests and check invariants afterwards; races rarely show up in single-threaded unit tests.
6
7**Trap:** adding a code-level lock on one app server, which does nothing across many servers.
8
9---
10
11## Practice coverage
12
13Every section in this chapter is tested at least once.
14
15| Section | Tested by |
16|---|---|
17| 17.1 Transactions | Level 5.2, Q1 |
18| 17.2 ACID in depth | Q1 |
19| 17.3 Lost update | Level 1A.1, Level 3.1, Q2, Q10 |
20| 17.4 Read anomalies | Level 1A.2, Level 1A.3, Level 4, Q3 |
21| 17.5 Isolation levels | Level 5.5, Q3 |
22| 17.6 Pessimistic locking | Level 1B, Level 2.1, Q4 |
23| 17.7 Optimistic locking | Level 1B.3, Level 2.4, Level 3.2, Q4 |
24| 17.8 Deadlocks | Level 1A.4, Level 3.3, Q5 |
25| 17.9 Distributed locks | Level 1A.5, Q8 |
26| 17.10 2PC | Level 2.3, Q7 |
27| 17.11 Sagas | Level 3.5, Level 5.2, Q6 |
28| 17.12 Seat holds | Level 2.2, Level 3.4, Level 5.1, Level 5.3, Q9 |
29
30---

17. Transactions and Locking