Autocoder

DBMS Concurrency Control Interview Questions

Last updated 2026-11-19

Concurrency control is how a database lets many transactions run at once without breaking isolation. Interviews ask for lock modes, two-phase locking and its variants, deadlock prevention rules such as wait-die and wound-wait, and timestamp ordering. This page gives each with a small worked trace so you can reproduce it on a whiteboard.

Read transactions and ACID first for schedules and serializability. For a general round plan see how to crack a DBMS interview, and for distributed variants look at distributed systems questions.

ApproachesLocking, timestamps, optimistic, multiversion
Key protocols2PL, strict 2PL, wait-die, wound-wait, MVCC
LevelMedium to hard

Lock compatibility

A shared (S) lock allows reading and is compatible with other S locks. An exclusive (X) lock allows reading and writing and is compatible with nothing. A request that conflicts with a held lock waits. Lock upgrade from S to X is allowed in some systems but is a classic source of deadlock: two transactions both holding S and both asking for X wait on each other.

Which protocol gives which guarantee

DBMS Concurrency Control Interview Questions: questions with worked answers

  1. 1

    What is the two-phase locking protocol?

    easy

    In 2PL every transaction has a growing phase in which it only acquires locks and a shrinking phase in which it only releases them. Once it releases any lock it may not acquire another. The point at which it holds all its locks is the lock point, and ordering transactions by lock point yields an equivalent serial schedule, so 2PL guarantees conflict serializability. It does not prevent deadlock, and basic 2PL can allow cascading rollback because locks are released before commit.

  2. 2

    What is strict 2PL and why do real systems use it?

    medium

    Strict 2PL is 2PL where a transaction keeps all its exclusive locks until it commits or aborts. Because nobody can read or overwrite an uncommitted write, the schedules are strict and cascadeless, so rollback of one transaction never forces another to abort and recovery is simple. Most commercial lock-based engines use it, often holding shared locks until commit too (rigorous 2PL) at higher isolation levels.

  3. 3

    Trace this: T1 holds S(A) and requests X(A); T2 holds S(A) and requests X(A). What happens?

    medium

    T1 cannot upgrade because T2 holds a shared lock, and T2 cannot upgrade because T1 holds one, so both wait forever: a deadlock caused by lock upgrade. The wait-for graph has edges T1 to T2 and T2 to T1, a cycle. The DBMS detects it and aborts one as the victim. To avoid it, request an update lock (U) or SELECT ... FOR UPDATE when you already know you will write; U locks are compatible with S but not with other U, so only one transaction can hold it.

  4. 4

    How do databases detect and handle deadlocks?

    medium

    Detection: maintain a wait-for graph with a node per transaction and an edge Ti to Tj when Ti waits for a lock Tj holds; periodically look for a cycle, then abort a victim chosen by cost, such as the youngest or the one with the least work done. Prevention: impose rules such as wait-die or wound-wait, or lock resources in a fixed order, or use lock timeouts as a crude fallback. Detection is common in practice because deadlocks are rare and detection costs nothing when there is none.

  5. 5

    Explain wait-die and wound-wait with timestamps.

    hard

    Both give every transaction a timestamp at start, and an older transaction has a smaller timestamp. In wait-die, when Ti requests a lock held by Tj: if Ti is older it waits, and if Ti is younger it dies (aborts and restarts with its original timestamp). In wound-wait: if Ti is older it wounds Tj, forcing Tj to abort, and if Ti is younger it waits. Both avoid cycles because waiting only goes in one age direction. Keeping the original timestamp on restart prevents starvation. Wound-wait is preemptive, since an older transaction can force a younger holder to abort.

  6. 6

    Explain basic timestamp ordering with the read and write rules.

    hard

    Each transaction T gets TS(T), and each item Q stores R-TS(Q) and W-TS(Q), the largest timestamps of readers and writers. Read(Q): if TS(T) < W-TS(Q), a younger transaction already wrote Q, so T is aborted; otherwise it reads and sets R-TS to max(R-TS, TS(T)). Write(Q): if TS(T) < R-TS(Q), a younger transaction already read the old value, so abort; if TS(T) < W-TS(Q), the write is obsolete, so abort (Thomas write rule instead ignores it); otherwise write and set W-TS(Q) = TS(T). The result is serializable in timestamp order, with no deadlocks but possible starvation and cascading aborts.

  7. 7

    What is the Thomas write rule, and what does it change?

    hard

    When T wants to write Q but TS(T) < W-TS(Q) and TS(T) >= R-TS(Q), no younger transaction has read the value T would overwrite, and a younger transaction has already written a newer value. Basic timestamp ordering aborts T; the Thomas write rule simply skips the obsolete write and continues. This admits schedules that are view serializable but not conflict serializable and reduces aborts.

  8. 8

    What is MVCC and why do readers not block writers?

    medium

    Multiversion concurrency control keeps several versions of each row, each tagged with the transaction ids that created and deleted it. A reader sees a snapshot: versions visible as of its start time (or statement start under READ COMMITTED). A writer creates a new version instead of overwriting, so readers do not take locks that block writers and writers do not block readers. PostgreSQL stores old versions in the table and cleans them with VACUUM; InnoDB keeps them in undo logs. Write-write conflicts still block or abort.

  9. 9

    What is optimistic concurrency control? When does it work well?

    medium

    Transactions execute against private copies in a read phase, then a validation phase checks whether their read and write sets conflict with transactions that committed meanwhile, and only then a write phase applies changes. Application-level versions use a version column: UPDATE ... SET qty = ?, version = version + 1 WHERE id = ? AND version = ?, retrying if zero rows were updated. It works when conflicts are rare, as in read-mostly workloads. High contention causes repeated aborts and wasted work.

    sql
    UPDATE product
    SET stock = 7, version = version + 1
    WHERE id = 42 AND version = 5;  -- 0 rows updated means someone else won; reload and retry
  10. 10

    What is multiple granularity locking and what are intention locks?

    hard

    Locks can be taken at database, table, page or row level. To let a table-level lock coexist cheaply with row-level locks, a transaction first places intention locks on ancestors: IS (intends to take S locks below), IX (intends X below) and SIX (S on this node plus intent to take X below). Before locking a row with X, the transaction takes IX on the table. A table-level S lock conflicts with IX, so the engine detects the conflict by checking one node instead of scanning every row lock. Locks are acquired root to leaf and released leaf to root.

  11. 11

    What is the phantom problem and how do locking engines solve it?

    medium

    Row locks protect existing rows, but a range query such as WHERE age BETWEEN 20 AND 30 can be affected by new rows inserted into that range after it ran. Solutions lock the predicate or the index range instead of single rows: next-key locks in InnoDB (a record lock plus a gap lock), or serializable snapshot isolation in PostgreSQL that detects dangerous read-write dependencies and aborts one transaction.

  12. 12

    What is starvation and how is it prevented?

    easy

    Starvation means a transaction waits indefinitely while others keep getting the locks it needs, for example a writer waiting behind an endless stream of readers, or a victim repeatedly chosen for abort. Fixes: grant locks in FIFO order so a waiting X request blocks later S requests, keep the original timestamp when restarting a victim, and age priorities upward the longer a transaction has waited.

  13. 13

    Compare lock-based and timestamp-based protocols.

    medium

    Locking resolves conflicts by making transactions wait, which risks deadlock but wastes little work. Timestamp ordering resolves them by aborting the transaction that arrived out of order, so there are no deadlocks but more restarts. Locking serializes in lock-point order; timestamp ordering serializes in timestamp order. In practice engines mix them: MVCC for reads with locks for writes. The NoSQL page shows how some stores use versions instead of locks.

Frequently asked questions

Does two-phase locking prevent deadlock?

No. It guarantees serializability but transactions can still wait in a cycle. Only conservative 2PL, which locks everything up front, avoids deadlock.

What does SELECT FOR UPDATE do?

It reads rows and takes exclusive row locks that are held until the transaction ends, so concurrent writers and other FOR UPDATE readers wait.

Is MVCC the same as snapshot isolation?

MVCC is the mechanism of keeping versions. Snapshot isolation is an isolation level that MVCC enables, and it allows write skew.

Which is better, optimistic or pessimistic locking?

Optimistic suits low-contention workloads, pessimistic suits high-contention ones where retries would be wasteful.

Related guides

More in CS Fundamentals Interview Questions

See all in this section