Database Concurrency
Interleaving transactions without losing integrity. Conflict serializability is the theory.
Level: advanced
What is it?
Database concurrency control coordinates overlapping transactions so their committed effects meet the isolation guarantee. Techniques include locks, MVCC snapshots, optimistic validation, conflict detection, and serializable scheduling.
Why does it matter?
Concurrent execution improves utilization, but careless interleavings can lose updates, read inconsistent combinations, or violate cross-row invariants. The model must protect business rules, not just individual rows.
Mental model
Write each transaction as a sequence of reads and writes, then ask whether an interleaving could produce a result that no valid serial order would produce. The concurrency-control mechanism determines which schedules can commit.
How it works
- Conflict serializability considers pairs of conflicting operations on the same data item where at least one is a write.
- A precedence graph with a cycle indicates that a schedule is not conflict-serializable under the classic test.
- Snapshot isolation can still allow anomalies such as write skew when transactions update disjoint rows based on overlapping reads.
- Locks and serializable validation can block or abort conflicting transactions.
- Application-level invariants spanning several rows may need explicit constraints, locking, or serializable transactions.
Steps
- Transaction A checks that fewer than ten reservations exist.
- Transaction B checks the same condition before A commits.
- Both insert different rows, so a simple row-level conflict may not expose the shared-count invariant.
- Use a database constraint, a locking/serialization strategy, or a different schema that makes the invariant enforceable.
Real-world usage
- Preventing lost updates and write skew.
- Solving conflict-serializability graph questions.
- Designing safe booking, inventory, and quota workflows.
Common mistakes
- Assuming two transactions are safe because they update different rows.
- Confusing a serial execution with any arbitrary interleaving.
- Treating isolation-level labels as a substitute for reasoning about the actual invariant.
Interview questions
What does conflict serializability mean?
A schedule is conflict-serializable when its conflicting operation order is equivalent to some serial transaction order.
How can write skew occur under snapshot isolation?
Two transactions read a shared invariant from the same snapshot and update disjoint rows, allowing both to commit a combined state that violates the invariant.