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.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Checkpoints.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, checkpoints
Related Database Management Systems (DBMS) Topics