Chapter 10 · Transaction Boundaries and Consistency by Design
Invariants, Isolation, and Concurrent Changes
Reason about invariants under concurrent access, understand isolation anomalies, and choose appropriate isolation guarantees for critical workflows.
Learning outcomes
Concurrency bugs are dangerous because each transaction can look correct in isolation while the combined execution violates an invariant. Isolation levels define which interleavings are allowed and which anomalies the database prevents.
Understand dirty reads, non-repeatable reads, phantoms, lost updates, and write skew.
Connect isolation level to business risk rather than choosing it by habit.
Recognize when uniqueness or locking is stronger than “check then write.”
Protect WorkshopHub invariants under concurrent transactions.
Concurrent execution
Suppose two dispatchers assign a primary technician at the same time. Each performs:
SELECT ... WHERE no primary assignment exists;INSERT primary assignment;Without a database constraint or sufficient isolation, both may succeed.
Lost update
Two transactions read the same inventory quantity 5:
A reads 5B reads 5A writes 4B writes 4Two units were consumed, but inventory decreased by only one.
Avoid read-modify-write when an atomic update works
UPDATE inventorySET available_qty = available_qty - 1WHERE part_id = ? AND available_qty >= 1;Then verify one row was updated. This moves the invariant into an atomic statement.
Dirty read
Transaction B reads a value written by A before A commits. If A rolls back, B observed a state that never became durable.
Non-repeatable read
A transaction reads the same row twice and sees different committed values because another transaction updated the row between reads.
Phantom
A transaction repeats a query such as:
SELECT * FROM assignmentWHERE work_order_id = 100 AND ended_at IS NULL;and sees additional matching rows inserted by another transaction.
Write skew
Two transactions read overlapping data, make different writes, and together violate an invariant even though they do not update the same row.
Example: at least one dispatcher on duty
Two dispatchers are both on duty. Each sees the other active and independently goes off duty. If each transaction updates only its own row, the final state has zero dispatchers on duty.
Row-level locking on only the row being modified may not protect invariants that depend on a set of rows.
Isolation levels conceptually
| Level | Typical intent |
|---|---|
| Read Committed | Each statement reads committed data; common default. |
| Repeatable Read | Stable snapshot/row view across a transaction, DBMS semantics vary. |
| Serializable | Concurrent result behaves as if transactions executed serially. |
Exact guarantees differ by DBMS implementation, so consult primary documentation for the engine you use.
Serializable is strongest, not always cheapest
Serializable execution can require blocking or transaction retries when the DBMS detects a serialization conflict.
Use constraints whenever possible
If “one active primary assignment per WorkOrder” can be represented by a unique partial index, that is often safer and simpler than relying solely on application isolation logic.
Locking a set
Sometimes the correct approach is to lock a parent row that represents the invariant boundary, such as locking WorkOrder before modifying its active assignments.
Optimistic conflict detection
A version column can detect concurrent modification:
UPDATE work_orderSET status_code = ?, version = version + 1WHERE work_order_id = ? AND version = ?;If zero rows update, another transaction changed the row first.
Isolation and reporting
Long reports may require a consistent snapshot so totals are not computed from mixed moments in time. Analytical consistency requirements differ from OLTP command requirements.
WorkshopHub invariant examples
| Invariant | Concurrency concern |
|---|---|
| One active primary assignment | Concurrent inserts. |
| No negative inventory | Concurrent decrements. |
| No overlapping technician intervals | Concurrent interval inserts. |
| Close only with no active assignments | Assignment inserted while close transaction runs. |
Practice: find the race
Capacity booking
A room has capacity 10. Two concurrent transactions each read current reservations = 6 and then add 3 seats. Both checks pass. Final reservations become 12. How would you protect the invariant?
Review answer
Use an atomic capacity update, locking/serialization around the capacity row, or another database constraint pattern that makes the check and update one protected operation. A simple application “read then check then insert” is not sufficient under concurrency.
Summary and next lesson
Isolation is about preserving invariants under concurrent interleavings. Lost updates, phantoms, and write skew are not abstract anomalies—they map directly to business failures. The next lesson compares optimistic and pessimistic concurrency strategies for controlling these conflicts.
References
- Martin Kleppmann, Designing Data-Intensive Applications.
- Berenson et al., “A Critique of ANSI SQL Isolation Levels.”
- PostgreSQL documentation on transaction isolation and explicit locking.