Chapter 5: Transaction Processing, Recovery & Concurrency

To optimize performance between volatile memory and persistent storage, database systems implement sophisticated buffer pool management strategies that cache frequently accessed pages in RAM, employing intelligent eviction algorithms such as Least Recently Used policies, CLOCK algorithms, and advanced frequency-based methods like TinyLFU to determine which data remains memory-resident and which gets written to disk. The chapter extensively covers crash recovery through Write-Ahead Logging, a sequential recording mechanism that captures all database modifications before they occur, enabling complete system restoration after unexpected failures. The ARIES recovery algorithm provides a systematic three-phase approach involving analysis of the log, redo operations to restore committed changes, and undo operations to roll back incomplete transactions, all governed by steal and force policies that dictate when modified pages are written to persistent storage. Concurrency control represents another critical dimension, encompassing optimistic approaches that assume conflicts are rare, pessimistic strategies that prevent conflicts through locking, and multiversion concurrency control that maintains multiple data versions to allow concurrent reads and writes. The discussion addresses various anomalies that can occur during concurrent execution, including phantom reads and write skews, while explaining how different isolation levels from Read Committed to Serializable provide varying degrees of protection against these inconsistencies. Two-phase locking protocols ensure serializability through strict acquisition and release phases, while deadlock detection mechanisms using waits-for graphs help resolve circular dependencies. The chapter distinguishes between logical locks that manage transaction-level data access and physical latches that protect internal data structures during modifications, introducing advanced optimizations like latch crabbing and B-link trees that enable efficient concurrent access to tree-structured indexes without creating performance bottlenecks in high-concurrency environments.