9 lessons
Transactions and the ACID properties
What a transaction actually is, transaction states, and a deep dive into Atomicity, Consistency, Isolation, and Durability — the four guarantees the rest of this module exists to deliver.
Schedules and serializability
How to represent interleaved concurrent transactions as a schedule, and why serial and serializable schedules are the gold standard for correctness.
Conflict serializability and view serializability
The precise definition of a conflicting operation pair, the precedence graph test for conflict serializability, and how view serializability captures a few extra safe schedules that conflict serializability misses.
Lock-based concurrency control
Shared and exclusive locks, the two-phase locking protocol and why it guarantees conflict serializability, plus strict 2PL and the anomalies plain locking still allows.
SQL isolation levels and multiple granularity locking
The four standard SQL isolation levels and the specific anomalies each one allows, plus intention locks for efficiently locking at the table, page, or row level.
Timestamp ordering and optimistic concurrency control
How transactions can be serialized by assigned timestamps instead of locks, and how optimistic concurrency control bets on conflicts being rare and validates only at the end.
Multiversion Concurrency Control (MVCC)
How keeping multiple versions of each row lets readers and writers avoid blocking each other entirely — the strategy behind PostgreSQL, MySQL/InnoDB, and most modern production databases.
Deadlock handling in databases
Wait-for graphs for deadlock detection, prevention schemes using timestamps, and the difference between deadlock and starvation.
Crash recovery and write-ahead logging
How the write-ahead log delivers atomicity and durability in practice, undo/redo logging, checkpoints, and the full recovery procedure after a crash.