Concurrency problems are difficult because each transaction can be correct in isolation while their interleaving is wrong.
ACID is a set of responsibilities
- Atomicity: all effects commit or none do.
- Consistency: a valid transaction moves the database between valid states.
- Isolation: concurrent execution behaves according to a declared contract.
- Durability: committed effects survive failures covered by the system.
Consistency is not automatically invented by the database. Constraints and transaction logic must define the valid states.
Schedules and conflicts
A schedule interleaves reads and writes from transactions. Two operations conflict when they touch the same item, belong to different transactions, and at least one is a write.
T1: read(A) write(A)
T2: read(A) write(A)Build a precedence graph with one node per transaction. Add Ti → Tj when a conflicting operation from Ti occurs before one from Tj. A cycle means the schedule is not conflict-serializable.
In plain terms: the graph asks whether transactions can be placed in one consistent “happened before” order. A cycle says each would need to be before the other.
Common anomalies
| Anomaly | What changes unexpectedly |
|---|---|
| Dirty read | A transaction sees another transaction’s uncommitted data |
| Non-repeatable read | Re-reading one row returns a different committed value |
| Phantom | Re-running a predicate returns a different set of rows |
| Lost update | One write overwrites another transaction’s work |
| Write skew | Two transactions preserve local checks but jointly break an invariant |
Product names for isolation levels are similar, but exact guarantees differ by database. Verify the target system rather than relying only on the SQL-standard table.
Locks and MVCC solve different parts
Shared and exclusive locks coordinate conflicting access. Two-phase locking acquires locks in a growing phase and releases them in a shrinking phase. Strict 2PL holds write locks until commit, preventing other transactions from reading uncommitted writes and avoiding cascading aborts.
MVCC keeps row versions so readers can often proceed without blocking writers. It reduces contention; it does not remove write conflicts or every anomaly.
Reproduce a lost-update defense
Open two PostgreSQL sessions and run:
-- Setup
CREATE TABLE inventory(product_id integer PRIMARY KEY, quantity integer NOT NULL);
INSERT INTO inventory VALUES (42, 1);Use one atomic statement from both sessions:
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0
RETURNING quantity;Only one transaction can return the decremented row. The other observes that the predicate no longer matches after lock acquisition.
Choose isolation from the invariant
Do not begin with “use serializable everywhere” or “read committed is faster.” Write the invariant, construct the dangerous interleaving, then choose an atomic statement, explicit lock, constraint, or stronger isolation that prevents it.
Review card
- Correct transactions can form an incorrect schedule.
- Conflicts require same item, different transactions, and a write.
- A cycle in the precedence graph rejects conflict serializability.
- MVCC improves reader/writer concurrency but does not erase anomalies.
- Isolation names are not perfectly portable across products.
- Start from the invariant and its dangerous interleaving.