Phase 5Intermediate6 of 18 in Database Design

Transactions, Isolation and Concurrency

How ACID is actually implemented, isolation levels and the anomalies each allows (lost update, write skew), pessimistic and optimistic locking, SKIP LOCKED queues, deadlocks, and MVCC snapshots and visibility.

Concurrency bugs pass every test on a laptop and then corrupt money in production. This phase teaches you to spot the race, name the anomaly, and pick the cheapest mechanism that prevents it.

0/5 · 0%

5 topics ~45 min 8 code blocks & diagrams

Start with the first topic

5.1

ACID and How Each Property Is Implemented

Atomicity: all of a transaction's changes commit or none do (undo via MVCC or rollback logs). Consistency: constraints hold at commit (the database's part) and invariants hold (the application's part). Isolation: concurrent transactions don't see each other's partial work (MVCC and locks). Durability: committed data survives crashes (WAL flushed before COMMIT returns).

9 min 1 code practice

5.2

Isolation Levels and Anomalies

Isolation levels trade anomalies for concurrency: Read Committed (PostgreSQL's default) prevents dirty reads; Repeatable Read (snapshot isolation in PostgreSQL) also prevents non-repeatable reads and phantoms but allows write skew; Serializable prevents all anomalies by aborting transactions that could produce a non-serial result. Lost updates and write skew are the bugs that matter most in practice.

9 min 2 code practice

5.3

Pessimistic and Optimistic Locking, Row and Table Locks

Pessimistic locking takes locks before changing data (SELECT ... FOR UPDATE) so others wait; optimistic locking lets everyone proceed and rejects stale writes with a version check. Row locks protect individual rows; table-level lock modes mostly matter for DDL. SKIP LOCKED turns a table into a work queue, and lock_timeout stops lock waits from piling up.

9 min 1 code practice

5.4

Deadlocks: Cause, Detection and Prevention

A deadlock is a cycle of transactions each waiting for a lock the next one holds. PostgreSQL checks for cycles after deadlock_timeout (1 s) and aborts one victim with SQLSTATE 40P01. Prevent deadlocks by acquiring locks in a consistent order, keeping transactions short, and retrying the victim.

9 min 1 diagram 1 code practice

5.5

MVCC: Snapshots, Row Versions and Visibility

Multi-version concurrency control keeps several versions of each row so readers see a consistent snapshot without blocking writers. In PostgreSQL every tuple has xmin (creating transaction) and xmax (deleting or updating transaction); a snapshot decides which versions are visible. Old versions become dead tuples that VACUUM must reclaim.