Concurrency control
DBMS · 10 interview questions
Concurrency control keeps simultaneous transactions from corrupting each other's work. The pessimistic approach is locking: a shared lock permits concurrent readers, an exclusive lock permits neither other readers nor writers.
Two-phase locking gives serializability by splitting a transaction into a growing phase, where it may acquire locks but not release any, and a shrinking phase, where the reverse holds. Strict two-phase locking holds every lock until commit, which also prevents other transactions reading uncommitted data.
The optimistic alternative is multiversion concurrency control. Writers create new row versions rather than overwriting, so readers see a consistent older version and never block. PostgreSQL and InnoDB both use MVCC, which is why a long analytical read doesn't stop writes.
Concurrency control interview questions
- What's the difference between a shared and an exclusive lock?
- Several transactions can hold a shared lock on the same item simultaneously, so concurrent reads are fine. An exclusive lock is held by one transaction alone and excludes both readers and writers.
- What is two-phase locking?
- A protocol with a growing phase where a transaction only acquires locks, and a shrinking phase where it only releases them. Once it releases anything it may acquire nothing more. This ordering guarantees serializability.
- What does strict two-phase locking add?
- All locks are held until commit or rollback rather than released during execution. This prevents cascading aborts, since no transaction can read data another might still roll back.
- What four conditions must all hold for a deadlock?
- Mutual exclusion, hold and wait, no preemption, and circular wait. Breaking any one prevents deadlock.
- Why they ask: Shared with the OS track — it's the same theory, so it's worth being crisp on it.
- How do databases usually handle deadlock?
- Detection rather than prevention. The engine maintains a wait-for graph, looks for a cycle, and aborts one transaction as the victim — usually the one with least work done. The application is expected to retry.
- What's the simplest way to reduce deadlocks in application code?
- Always acquire locks in a consistent order across the codebase. Most deadlocks come from two paths touching the same two rows in opposite orders. Keeping transactions short helps too.
- What is MVCC?
- Multiversion concurrency control: writes create new versions of rows instead of overwriting, and each transaction reads the version consistent with its snapshot. Readers never block writers and writers never block readers.
- What does MVCC cost?
- Storage and cleanup. Old versions must be retained until no transaction can still need them, then reclaimed — PostgreSQL's VACUUM. A long-running transaction pins old versions and causes bloat.
- What is optimistic concurrency control?
- Proceed without locking, then check at commit whether anything you read has changed — typically via a version column. If it has, abort and retry. Efficient when conflicts are rare, wasteful when they aren't.
- What does SELECT ... FOR UPDATE do?
- Takes an exclusive lock on the selected rows for the rest of the transaction, so nobody else can modify them. It's how you make a read-then-write sequence safe against a lost update.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days