DBMS Topics
Checkpoints
Last Updated : 21 May, 2026
A checkpoint is a snapshot mechanism where the DBMS periodically writes all dirty modified buffer pages to disk and records the state of active transactions in the log.
What is a Checkpoint?
A checkpoint is a snapshot mechanism where the DBMS periodically writes all dirty (modified) buffer pages to disk and records the state of active transactions in the log.
Checkpoints reduce recovery time after a crash by limiting how far back the recovery process needs to scan the log.
Why Checkpoints are Needed
Without checkpoints, every time the system crashes:
With checkpoints:
Types of Checkpoints
1. Simple (Quiescent) Checkpoint
2. Fuzzy (Non-Quiescent) Checkpoint (Used in Practice)
Checkpoint Record Format
Simple checkpoint
<checkpoint, [T1, T2, T3]>
← list of transactions active at checkpoint time
Fuzzy checkpoint
<begin_checkpoint, [T1, T2, T3], {dirty_page_table}>
... (system runs normally here) ...
<end_checkpoint>
Recovery with Checkpoints
Analysis Phase
Redo and Undo
Checkpoint Frequency Trade-off
More frequent checkpoints
+ Faster recovery after crash
− More overhead during normal operation (disk I/O for dirty pages)
Less frequent checkpoints
+ Less overhead during normal operation
− Slower recovery (longer log to scan)
Typical: Checkpoint every few minutes or every N log records.
Checkpoint Example Walkthrough
Log Timeline
──────────────────────────────────────────────────────────►
[T1 start]
[T1: write A]
[T2 start]
[T2: write B]
[T1 commit]
[BEGIN CHECKPOINT: {T2}] ← T2 is active at checkpoint
[T3 start]
[T3: write C]
[T2: write D]
[T2 commit]
[END CHECKPOINT]
[T3: write E]
*** CRASH ***
Recovery
Start from BEGIN CHECKPOINT.
Active at checkpoint: {T2}
New transactions: {T3}
T2: has <commit> → REDO set
T3: no <commit> → UNDO set
REDO T2: write D again
UNDO T3: undo write E, undo write C
Write <T3, abort> to log.
T1 is before checkpoint and committed → no action needed ✓
Checkpoint in SQL
Most DBMS checkpoint automatically, but can also be triggered manually:
-- MySQL / MariaDB
FLUSH TABLES WITH READ LOCK; -- quiescent checkpoint
SHOW ENGINE INNODB STATUS; -- shows checkpoint info
-- PostgreSQL
CHECKPOINT; -- manual checkpoint
-- SQL Server
CHECKPOINT; -- manual checkpointSummary
Checkpoint Purpose
→ Bound the amount of log to scan during recovery
→ Flush dirty pages to disk periodically
Types
→ Simple (quiescent): stops system; simple recovery
→ Fuzzy (non-quiescent): runs concurrently; used in practice
Recovery with Checkpoint
→ Only scan log from last checkpoint
→ REDO committed transactions since checkpoint
→ UNDO incomplete transactions since checkpoint
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 DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, checkpoints
Related DBMS Topics