Checkpoint

 

What is a Checkpoint in DBMS?

A checkpoint in a Database Management System (DBMS) is a mechanism used to minimize the amount of work needed for recovery after a system crash.

It essentially acts like a “save point” — the system writes all modified data (dirty pages) from the buffer (RAM) to the disk and records a checkpoint in the log file.

Purpose of Checkpoint

1. Reduce recovery time — only redo or undo transactions after the last checkpoint.

2. Ensure consistency between database and log files.

3. Flush dirty pages (modified data not yet written to disk).

4. Mark a safe state from which the system can recover.

How It Works 

1. When a checkpoint occurs:

   • All committed transactions before the checkpoint are written to disk.

   • The checkpoint record is written to the log file.

   • Transactions after the checkpoint may still be in progress (these are handled by recovery if crash occurs).

2. During recovery:

• Start from the last checkpoint, not from the beginning of the log — this saves time.

Example with a Schedule

Recovery After Crash:

• From the log file:

  • Before checkpoint → All transactions are committed (no need to redo).

  • After checkpoint → Check for incomplete transactions (redo/undo as needed).

T1 committed after checkpoint → Redo T1
T3 didn’t commit → Undo T3

So recovery uses the checkpoint to start scanning logs from that point forward, not from the beginning.

Checkpoint in MySQL

In MySQL (InnoDB engine), checkpoints happen automatically and periodically.

• InnoDB maintains a redo log (ib_logfile0, ib_logfile1).

• The checkpoint ensures all changes up to a certain Log Sequence Number (LSN) are flushed to disk.

• MySQL performs fuzzy checkpoints, meaning not all dirty pages are flushed at once — it spreads out the work to avoid performance drops.

Example

We’ll create:

• A table accounts

• Some initial data (dummy records)

• Then perform transactions (like deposits and withdrawals)

• Mark a checkpoint (conceptually or through flushing logs)

• Finally simulate a crash and recovery phase

Step 1: Create Database and Table

CREATE DATABASE bankdb;
USE bankdb;
CREATE TABLE accounts (
    acc_id INT PRIMARY KEY,
    acc_name VARCHAR(50),
    balance DECIMAL(10,2)
);

Step 2: Insert Dummy Data

INSERT INTO accounts VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 1500.00),
(3, 'Charlie', 2000.00);

acc_id	acc_name	balance
1	Alice	1000.00
2	Bob	1500.00
3	Charlie	2000.00

Step 3: Start Transactions (T1, T2)

Let’s say two users perform transactions.

SET autocommit = 0;

Transaction T1 (Alice deposits ₹500)

START TRANSACTION;
UPDATE accounts SET balance = balance + 500 WHERE acc_id = 1;
-- Do not COMMIT yet (simulating ongoing transaction)

Transaction T2 (Bob withdraws ₹300)

START TRANSACTION;
UPDATE accounts SET balance = balance - 300 WHERE acc_id = 2;
COMMIT;

At this point:

• T2 is committed → safely stored

• T1 is not committed yet → only in memory (buffer)

Step 4: MySQL Checkpoint

In MySQL (InnoDB), checkpointing happens automatically, but we can manually trigger flushing to simulate it.

-- Force checkpoint-like behavior
FLUSH LOGS;
FLUSH TABLES;

This ensures:

• All committed transactions (like T2) are written to disk.

• Uncommitted ones (like T1) remain in redo/undo logs.

Checkpoint record is written internally.

Step 5: Simulate a Crash (Close MYSql)

On Windows:

1. Press Ctrl + Shift + Esc to open Task Manager.

2. Go to Details tab.

3. Find mysqld.exe.

4. Right-click → End Process Tree.

MySQL is now crashed (killed abruptly).

Imagine the system crashes right now ⚡

• T1 was not committed

• T2 was committed and logged before checkpoint

Open Services → find MySQL → Right-click → Start.

Step 6: Recovery After Restart

When MySQL restarts, InnoDB automatically performs crash recovery using redo and undo logs.

You can observe this in MySQL logs or by checking:

SHOW ENGINE INNODB STATUS\G

It will show lines like:

InnoDB: Starting crash recovery...
InnoDB: Rolling back uncommitted transactions
InnoDB: Completed crash recovery

What Happens During Recovery

Transaction	Status Before Crash	Recovery Action	Final Outcome
T1 (Alice +₹500)	Uncommitted	Undo (rollback)	Reverted to ₹1000
T2 (Bob −₹300)	Committed	Redo (reapplied)	Balance = ₹1200

Step 7: Verify After Recovery

After restart, check balances again:

SELECT * FROM accounts;

Final Table (After Recovery):

acc_id	acc_name	balance
1	Alice	1000.00
2	Bob	1200.00
3	Charlie	2000.00

The database is consistent — committed transactions are preserved, uncommitted ones rolled back.

Conceptual Timeline Summary

Time	Action	Note
t1	T1 starts (Alice deposit ₹500)	Not committed
t2	T2 starts (Bob withdraw ₹300)	Commits
t3	Checkpoint occurs	T2 changes flushed to disk
t4	Crash occurs	T1 uncommitted
t5	Recovery	T1 rolled back, T2 redone