Level 7 of 13 · about 3 weeks
Transactions and concurrency control
A precise definition of ACID, when an interleaving of concurrent transactions is actually safe, what each isolation level really prevents, lock-based and timestamp-based protocols, deadlocks, multiversion concurrency control, the anomalies snapshot isolation still allows, serializable snapshot isolation, and picking the right isolation level for a real application.
Modules
Core: everyone needs it. Common: most jobs use it. Specialist: optional depth, safe to skip on a first read.
1
ACID properties
one honest, precise definition of atomicity, consistency, isolation, and durability
Core 5 parts · about 2 hours
2
Schedules and serializability
a precise way to judge whether an interleaving of concurrent transactions is actually safe
Specialist 5 parts · about 3 hours
3
Isolation levels in practice
what read committed, repeatable read, and serializable actually prevent, one anomaly at a time
Core 5 parts · about 2 hours
4
Lock-based concurrency control
shared and exclusive locks, and the two-phase discipline that makes locking guarantee serializability
Core 5 parts · about 2 hours
5
Timestamp-ordering and validation-based protocols
two ways to guarantee serializability without ever making a transaction wait for a lock
Specialist 5 parts · about 2 hours
6
Deadlocks
how two transactions can wait on each other forever, and what the database does about it
Specialist 5 parts · about 2 hours
7
Multiversion concurrency control
keeping old versions of a row around so readers never have to wait on writers
Core 5 parts · about 2 hours
8
Write skew and phantoms
concrete anomalies snapshot isolation lets through, even though it looks airtight
Specialist 5 parts · about 2 hours
9
Serializable snapshot isolation
getting true serializability on top of MVCC, without giving up its reader-writer non-blocking benefit
Specialist 5 parts · about 2 hours
10
Choosing an isolation level for a real app
matching the guarantee to the specific bug it is meant to prevent, not reaching for the strictest option by default
Core 5 parts · about 2 hours