DBMS Notes
A checkpoint is a recovery mechanism where the DBMS periodically forces all modified (dirty) buffer pages to disk and records the state of active...
What is a Checkpoint?
A checkpoint is a recovery mechanism where the DBMS periodically forces all modified (dirty) buffer pages to disk and records the state of active transactions in the log. Checkpoints create a known-good reference point — during recovery after a crash, the system only needs to examine the log from the most recent checkpoint onward, rather than scanning from the beginning.
Think of a checkpoint like saving your progress in a video game. If the game crashes, you restart from your last save point — not from the very beginning.
Types of Checkpoints
1. Simple (Quiescent) Checkpoint
The database completely pauses activity during the checkpoint:
Steps
1. STOP accepting new transactions
2. Wait for ALL active transactions to either COMMIT or ABORT
3. Flush ALL dirty buffer pages to disk
4. Write a <CHECKPOINT> record to the log
5. Flush the log to disk
6. RESUME accepting new transactions
Timeline
---[normal activity]---[PAUSE]---[flush all]---[RESUME]---
^ ^
no transactions activity resumes
allowed here
Advantage: Simple implementation; after checkpoint, guaranteed that all committed data is on disk.
Disadvantage: System is completely unavailable during the checkpoint — unacceptable for 24/7 applications.
2. Non-Quiescent (Fuzzy) Checkpoint
Used in practice by real DBMS systems. Activity continues during the checkpoint:
Steps
1. Write <CHECKPOINT START, active_list> to log
(active_list = {T5, T8, T12} currently running transactions)
2. Flush dirty buffer pages to disk (while new transactions continue)
3. Write <CHECKPOINT END> to log
Timeline
---[T5,T8 running]---[CHKPT START]---[flush pages, T8 commits, T13 starts]---[CHKPT END]---
^ ^
record active list checkpoint complete
transactions continue!
Advantage: No interruption to normal operations.
Disadvantage: More complex recovery logic (must handle transactions that were active during checkpoint).
Recovery Using Checkpoints
After a crash, the recovery manager uses the checkpoint to determine which transactions need redo and which need undo.
Classification of Transactions
| T1 | |----commit----| | -> IGNORE (committed before checkpoint) |
| T2 | |----------|-----commit----| -> REDO (committed after checkpoint, before crash) |
| T3 | |----------|---------------|---- -> UNDO (active at crash, never committed) |
| T4 | |----commit----| -> REDO (started after checkpoint, committed) |
| T5 | |---------------|---- -> UNDO (started after checkpoint, still active) |
| Transaction | Status at Crash | Action |
|---|---|---|
| T1 | Committed before checkpoint | No action needed (already on disk) |
| T2 | Started before checkpoint, committed after | REDO |
| T3 | Started before checkpoint, still active | UNDO |
| T4 | Started after checkpoint, committed | REDO |
| T5 | Started after checkpoint, still active | UNDO |
Recovery Algorithm
| REDO list | Transactions that committed after the checkpoint |
| UNDO list | Transactions that were active at crash (no COMMIT record) |
| For each transaction in REDO list | re-apply all their write operations |
| For each transaction in UNDO list | reverse all their write operations |
Checkpoint Frequency — Trade-offs
| Frequent Checkpoints | Infrequent Checkpoints |
|---|---|
| Faster recovery (less log to scan) | Slower recovery (more log to scan) |
| More I/O overhead during normal operation | Less overhead during normal operation |
| Shorter log retention needed | Longer log retention needed |
| Slight performance hit | Better runtime performance |
Typical configuration: Every 5-15 minutes, or after a configurable number of log records (e.g., every 100,000 entries).
Checkpoint in Practice — Real DBMS Systems
-- MySQL/InnoDB: Fuzzy checkpointing happens automatically
-- Can force a checkpoint:
FLUSH TABLES;
-- PostgreSQL: Checkpoint configuration
-- postgresql.conf:
-- checkpoint_timeout = 5min
-- checkpoint_completion_target = 0.9
-- max_wal_size = 1GB
CHECKPOINT; -- Manual checkpoint command
-- Oracle: Automatic checkpoints + manual
ALTER SYSTEM CHECKPOINT;Write-Ahead Logging (WAL) and Checkpoints
Checkpoints work hand-in-hand with WAL (Write-Ahead Logging):
Summary
| Aspect | Detail |
|---|---|
| Purpose | Limit recovery scanning to recent log entries |
| Types | Simple (quiescent) and Fuzzy (non-quiescent) |
| Recovery benefit | Only process transactions after last checkpoint |
| Trade-off | I/O overhead vs. recovery speed |
| Real systems | All production DBMS use fuzzy checkpoints |
| Configuration | Frequency tuned based on recovery time objectives |
Checkpoints are a critical component of database reliability — they ensure that even after unexpected crashes, the system can recover quickly to a consistent state without replaying the entire history of operations.
Continue the lesson path