Every technique studied across the last several chapters, transactions, ACID properties, concurrency control, serializability, and deadlock handling, has quietly rested on one enormous assumption: that the underlying database engine has some reliable way of undoing a transaction's changes if something goes wrong, and restoring committed changes if the system crashes right after they were saved. This chapter finally examines exactly how that assumption is fulfilled, through the recovery system, the component of a DBMS responsible for restoring the database to a consistent state after any kind of failure.
Failures in a database system are not a hypothetical concern; they are a routine, expected part of operating any real software system. Power outages happen, servers crash, disks fail, and applications occasionally terminate unexpectedly in the middle of a transaction. A recovery system's entire purpose is to guarantee that no matter when a failure strikes, the database can always be brought back to a state that respects the atomicity and durability properties studied several chapters ago, with every committed transaction's effects preserved, and every uncommitted transaction's effects completely undone.
In this tutorial, you will learn the core idea behind log-based recovery, the write-ahead logging rule that makes it reliable, how undo and redo operations are decided using a transaction log, how checkpoints make the recovery process faster, and a complete worked example tracing through a crash recovery scenario step by step.
Recall the atomicity property from the chapter on transaction management: a transaction's changes must either all take effect, or none of them should. If a system crashes midway through a transaction, after some of its writes have already reached the database but before it commits, the database is left holding a partial, incomplete change that must somehow be identified and undone once the system restarts. Similarly, durability requires that once a transaction commits, its changes survive any subsequent crash, meaning those changes must somehow be recoverable even if they had not yet been physically written to the actual database files at the moment of the crash.
Both of these requirements point to the same underlying need: the database must keep some kind of persistent record of what every transaction was doing, separate from the actual data files themselves, so that this record can be consulted after a crash to figure out exactly what needs to be undone and what needs to be redone.
The transaction log, often simply called the log, is a sequential, append-only record maintained by the database system, recording every significant event related to every transaction, including when a transaction starts, every value it changes, and when it commits or aborts. Because the log is written sequentially and stored on stable, non-volatile storage, it survives crashes that might otherwise corrupt or lose the actual database files.
| Log Record Type | Information Recorded |
|---|---|
| Start | Marks the beginning of a transaction, recording its unique transaction identifier. |
| Update | Records the transaction identifier, the data item being changed, its old value, and its new value. |
| Commit | Marks that a transaction has successfully completed and all its changes should be made permanent. |
| Abort | Marks that a transaction has been rolled back and none of its changes should be made permanent. |
<T1, Start> <T1, A, 1000, 500> <T1, B, 2000, 2500> <T1, Commit>
This short log fragment records exactly what the bank transfer transaction from the transaction management chapter did: T1 began, changed A from 1000 to 500, changed B from 2000 to 2500, and then committed successfully. Notice that both the old value and the new value are recorded for every update, which is precisely what makes it possible to reverse a change later if needed, an operation called undo, or reapply it if needed, an operation called redo.
The entire recovery system depends on one absolutely critical rule, called the write-ahead logging rule, or WAL for short: before any change is written to the actual database on disk, the corresponding log record describing that change must first be written to the log, and that log record must be safely stored on stable storage.
Write-Ahead Logging Rule: Log record for a change → must be written to stable storage BEFORE the corresponding change → is written to the actual database
This ordering might feel backward at first glance, but it is exactly what makes reliable recovery possible. If the log always arrives before the actual data change, then after any crash, the system can trust the log completely to reconstruct exactly what was happening at the moment of the crash, even if the actual database files only received some of the corresponding changes, or none of them at all.
When the database system restarts after a crash, the recovery process scans through the log and classifies every transaction that was active at some point into one of two categories, determining exactly what recovery action to take for each one.
| Category | Log Evidence | Recovery Action |
|---|---|---|
| Committed Transactions | The log contains both a Start record and a Commit record for the transaction. | Redo: reapply all of its changes to guarantee they are reflected in the database, in case they had not yet been physically saved before the crash. |
| Uncommitted (Incomplete) Transactions | The log contains a Start record but no corresponding Commit record. | Undo: reverse any of its changes that may have already reached the database, using the old values recorded in the log. |
Log at the moment of the crash: <T1, Start> <T1, A, 1000, 500> <T1, Commit> <T2, Start> <T2, C, 300, 100> <T3, Start> <T3, D, 700, 900> <T3, Commit> -- CRASH occurs here --
Scanning this log after the crash reveals three transactions: T1 and T3 both have matching Commit records, so both are classified as committed and must be redone. T2 has a Start record but no Commit record, since the crash interrupted it before it could finish, so T2 is classified as incomplete and must be undone.
Recovery Actions: Redo T1: reapply A = 500 Redo T3: reapply D = 900 Undo T2: restore C back to 300, its old value recorded in the log
After performing these actions, the database correctly reflects every committed transaction's effects while completely erasing any trace of the interrupted T2, exactly satisfying the atomicity and durability guarantees this entire recovery mechanism is designed to protect.
Scanning the entire log from the very beginning of time after every single crash would become increasingly slow as a database system runs for longer and longer, accumulating an ever-growing log. Checkpoints solve this practical problem by periodically recording a snapshot marker in the log, allowing the recovery process to skip over older log records that are known to be safely reflected in the actual database already.
1. Temporarily pause the acceptance of new transactions. 2. Write all currently buffered, modified data to the actual database on disk. 3. Write a special checkpoint record to the log, listing every transaction that is currently active at this moment. 4. Resume normal transaction processing.
Log with a checkpoint: <T1, Start> <T1, A, 1000, 500> <T1, Commit> <Checkpoint, active transactions: none> <T2, Start> <T2, C, 300, 100> <T2, Commit> <T3, Start> <T3, D, 700, 900> -- CRASH occurs here --
Since the checkpoint record confirms that no transactions were active at that point, and T1 committed before the checkpoint, the recovery process does not need to examine T1 at all, since its effects are already guaranteed to be safely reflected in the database as part of the checkpoint process itself. Recovery only needs to consider transactions starting from the checkpoint onward, T2 and T3 in this case, dramatically reducing how much of the log needs to be scanned.
Recovery Actions (starting only from the checkpoint onward): Redo T2: reapply C = 100 (T2 committed after the checkpoint) Undo T3: restore D back to 700 (T3 never committed before the crash)
This is exactly why real database systems perform checkpoints regularly, at intervals balancing the overhead of pausing transaction processing against the time recovery would otherwise take after a crash.
Database systems generally follow one of two broad strategies regarding when a transaction's changes are actually applied to the database files, which affects exactly how recovery needs to behave.
| Strategy | Description | Recovery Implication |
|---|---|---|
| Deferred Update | Changes are only written to the actual database after the transaction commits, being held in the log or buffer until then. | Recovery only ever needs to redo committed transactions; undo is never necessary, since uncommitted changes never reached the database in the first place. |
| Immediate Update | Changes may be written to the actual database before the transaction commits, as soon as they occur. | Recovery needs both undo, for uncommitted transactions whose changes may have already reached the database, and redo, for committed transactions. |
A reliable recovery system is what ultimately allows every guarantee discussed throughout this entire course on transactions to hold true in the real world, where hardware fails, power gets interrupted, and software occasionally crashes unexpectedly. Without write-ahead logging, undo and redo procedures, and periodic checkpoints working together, none of the ACID guarantees studied earlier would actually mean anything once a real failure occurred, since there would be no reliable way to restore the database to a correct, consistent state afterward.
| Mistake | Correct Understanding |
|---|---|
| Assuming the log is written after the database is updated. | The write-ahead logging rule specifically requires the log record to be written before the corresponding database change, not after. |
| Confusing undo with redo. | Undo reverses changes made by uncommitted transactions, while redo reapplies changes made by committed transactions to guarantee durability. |
| Believing checkpoints eliminate the need for undo and redo entirely. | Checkpoints only reduce how much of the log needs to be scanned during recovery; undo and redo are still applied to transactions active around the checkpoint and afterward. |
| Assuming immediate update databases never need to perform undo. | Immediate update systems can write uncommitted changes to the database before a crash, making undo essential, unlike deferred update systems. |
The recovery system is the mechanism that turns the theoretical promises of atomicity and durability, discussed since the very first chapter on transaction management, into something a real database can actually guarantee, even when faced with unpredictable crashes and failures. Through the transaction log, the write-ahead logging rule, undo and redo operations, and checkpoints, this chapter demonstrated exactly how a database reconstructs a correct, consistent state after a crash, using two fully worked recovery examples to trace the process step by step.
In this tutorial, you learned why recovery is needed to fulfill the ACID guarantees studied earlier in this course, how the transaction log and write-ahead logging rule work together, how undo and redo are decided using log evidence with a complete worked example, and how checkpoints make recovery faster in practice. With this foundation, you are ready to move on to indexing, which shifts focus toward how a database physically organizes and locates data efficiently, a concern that becomes increasingly important as the size of a database grows.