Transaction Processing Systems

Transactions protect a logical unit of work

A transaction groups operations that must succeed together. For a transfer, decreasing one account and increasing another should not leave only half the transfer committed. Atomicity concerns this all-or-nothing effect; durability concerns survival of committed effects after failure. Isolation controls interference between concurrent transactions. Consistency describes preservation of application invariants when correctly designed transactions execute under suitable guarantees. A database cannot infer every business rule merely because the code uses BEGIN and COMMIT.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Schedules and conflict serializability

A schedule interleaves actions from several transactions. Two actions conflict when they belong to different transactions, access the same item and at least one writes. Build a precedence graph with a directed edge for each ordered conflict. If the graph is acyclic, a topological ordering gives a conflict-equivalent serial schedule. This is a sufficient mechanical test for conflict serializability; it is not a universal test for every possible notion of equivalent execution.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Two-phase locking and deadlocks

Two-phase locking requires a transaction to acquire locks before its first release and acquire no new lock afterwards. Suitable locking yields conflict-serializable schedules. Strict variants retain write locks until completion, simplifying recovery. Deadlock is still possible: one transaction holds A and waits for B while another holds B and waits for A. Prevent cycles with a consistent lock order, or detect a wait-for cycle and abort a victim transaction that can be retried.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Snapshots do not automatically mean serializability

Multi-version concurrency control lets readers use an appropriate committed version rather than waiting for every writer. The visibility rule depends on the isolation level. Snapshot isolation prevents many read anomalies but can allow write skew when transactions read overlapping data and write different rows. Suppose two on-call clinicians each observe the other on duty, then each independently goes off duty. Row-level write conflicts need not occur, although the shared staffing invariant is violated.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Logging, checkpoints and crash recovery

A write-ahead log records recovery information before corresponding data pages reach durable storage. Recovery distinguishes committed work that must survive from incomplete work that must not appear committed. A checkpoint bounds how much history needs examination but is not simply a replacement for the log. Failure tests should interrupt execution around log flush, data flush and commit acknowledgement. Also separate database durability from an application side effect, such as an email sent outside the transaction.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Coordinating transactions across services

A distributed transaction needs participants to agree on an outcome. In two-phase commit, a coordinator first asks participants to prepare, then communicates commit or abort. A prepared participant may have to wait when the coordinator is unavailable; agreement does not imply uninterrupted progress. For workflows that tolerate compensation, a saga records steps and compensating actions, but compensation is a new operation and may itself fail. Neither approach removes the need for stable operation identifiers and recovery state.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Worked precedence graph and engineering consequences

Consider r1(A), w1(A), r2(A), w2(B), r1(B). The write by transaction 1 before transaction 2 reads A gives edge 1 -> 2. Transaction 2 writes B before transaction 1 reads B, giving edge 2 -> 1. The cycle proves the schedule is not conflict-serializable. In application code, keep transactions short, enforce constraints at the database boundary and treat a serialization failure as an expected retryable outcome rather than silent data loss.

References: 2026–27 calendar alignment · PostgreSQL documentation: transaction isolation

Practice & worked answers

Original exercises for this guide.

Public notes & references

Course sources establish the scope above. Reference materials support the explanations; linked lecture notes and books remain with their authors.

• 2026–27 calendar alignment ↗Course scope · 2026–27
• PostgreSQL documentation: transaction isolation ↗Supplementary primary reference