Redo Logs, Undo, Doublewrite, Checkpoints, Crash Recovery, and Durability

Learning outcomes

A developer asks a deceptively simple question: “If COMMIT returned success, where is my row?” The answer is not “the final table page was already forced to disk.” InnoDB separates logical transaction durability from data-page flushing. Changes occur in buffer-pool pages, old row versions are represented through undo, and redo records describe modifications needed for crash recovery. A commit can become durable when the required log state is safely persisted even though many dirty data pages will be flushed later.

This lesson traces a write through write-ahead logging (WAL), undo, redo, checkpoints and the doublewrite mechanism. It also separates three artifacts that operators often confuse: the InnoDB redo log protects crash recovery of InnoDB pages; the MariaDB binary log records logical/row events used for replication and point-in-time recovery; a backup is a restorable copy/base from which recovery begins. None substitutes universally for the other two.

1. Establish a write-path lab and record durability settings

DROP DATABASE IF EXISTS servicehub_innodb_lab;CREATE DATABASE servicehub_innodb_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_innodb_lab;CREATE TABLE digits (n TINYINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE work_orders (  work_order_id BIGINT NOT NULL AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status VARCHAR(20) NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  summary VARCHAR(180) NOT NULL,  notes LONGTEXT NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_status (status, priority, work_order_id),  KEY ix_work_orders_customer (customer_id, opened_at)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;INSERT INTO work_orders(customer_id,status,priority,opened_at,summary,notes)SELECT 1 + (x.n % 250),       ELT(1 + (x.n % 4),'queued','open','closed','cancelled'),       1 + (x.n % 5),       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL x.n SECOND,       CONCAT('ServiceHub order ', x.n),       RPAD(CONCAT('diagnostic-note-',x.n,' '), 1200, 'x')FROM (  SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) AS xWHERE x.n BETWEEN 1 AND 8000;

SHOW VARIABLES WHERE Variable_name IN ('datadir','innodb_log_group_home_dir','innodb_log_file_size',  'innodb_flush_log_at_trx_commit','innodb_doublewrite',  'log_bin','sync_binlog','binlog_format');SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_lsn_current','Innodb_lsn_flushed','Innodb_lsn_last_checkpoint',  'Innodb_os_log_written','Innodb_dblwr_writes','Innodb_dblwr_pages_written');

Variable/status availability can evolve; if one LSN counter is absent, use SHOW ENGINE INNODB STATUS and INFORMATION_SCHEMA.INNODB_METRICS as documented alternatives. The objective is to prove the current server’s write/recovery state, not to make a monitoring query depend on one historic variable name forever.

2. Follow one UPDATE from old version to commit

Before modifying an indexed record, InnoDB must preserve enough old information to support rollback and multi-version consistent reads. Undo records form the historical chain used by active snapshots. The current clustered/secondary index pages are changed in memory, producing dirty pages. Redo records describe changes so they can be replayed after a crash. Write-ahead ordering means the redo needed to reconstruct a page must be durable before that dirty page can safely be considered persisted.

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';START TRANSACTION;UPDATE work_ordersSET summary=CONCAT(summary,' / inspected')WHERE work_order_id BETWEEN 100 AND 500;COMMIT;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_os_log_written','Innodb_buffer_pool_pages_dirty');SHOW ENGINE INNODB STATUS\G

Redo-byte growth and dirty-page counts will vary because internal logging and background flushing happen concurrently. You are verifying causal direction—not predicting an exact byte count per UPDATE. The data pages can remain dirty after COMMIT while the transaction is still durable according to the configured log-flush policy.

3. Checkpoints bound how far recovery must scan/replay

Because the redo log is circular, old log space eventually has to be reusable. A checkpoint advances the point before which dirty page state is sufficiently persisted that older redo can be overwritten. If dirty-page flushing cannot keep up with redo generation, the checkpoint age approaches available redo capacity and InnoDB must increase flushing pressure. More redo capacity can absorb longer write bursts, but it also changes recovery/space considerations; “bigger is always faster” is not a safe rule.

SHOW ENGINE INNODB STATUS\GSELECT NAME, SUBSYSTEM, COUNT, COMMENTFROM information_schema.INNODB_METRICSWHERE NAME IN ('log_lsn_current','log_lsn_last_checkpoint',               'log_lsn_checkpoint_age','log_lsn_buf_pool_oldest')ORDER BY NAME;

INNODB_METRICS requires PROCESS privilege and some counters can be disabled or differ by version. For production dashboards, verify which metrics are enabled and what reset semantics apply before alerting on them.

4. Doublewrite protects a different failure mode than redo

Redo can reconstruct changes only if the data-page base it applies to is structurally usable. A torn/partial page write caused by power or OS failure can leave half of a page from the old version and half from the new version. InnoDB’s doublewrite mechanism first writes pages to a protected intermediate area and then to their final tablespace locations, giving recovery a valid page copy when a final write is torn.

On current MariaDB, innodb_doublewrite=ON is the default. The setting also supports fast on newer versions and can be disabled, but OFF assumes the underlying storage/filesystem provides atomic writes at the InnoDB page size. Disabling the doublewrite buffer simply because a benchmark is a few percent faster can convert a power-loss event into page corruption.

SHOW VARIABLES LIKE 'innodb_doublewrite';SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_dblwr_writes','Innodb_dblwr_pages_written');

5. Redo log, binary log and backup are not synonyms

A server with healthy redo but no usable backup cannot recover from every operator error or lost storage device. A backup without binary-log retention cannot necessarily reach an arbitrary point after that backup. A binary log without a base backup is not an efficient full-dataset reconstruction mechanism. Later chapters build the complete backup/PITR chain.

6. Durability settings make risk explicit

innodb_flush_log_at_trx_commit=1 writes and flushes the redo log at each transaction commit and is MariaDB’s default full-ACID baseline. Value 0 defers both write/flush to a periodic interval; value 2 writes on commit but flushes periodically. Those alternatives can improve throughput on some systems but allow recent committed transactions to be lost in server/OS/power failure scenarios as documented. Value 3 exists for historic group-commit behavior and is not a modern tuning recommendation.

When the conventional binary log is enabled, sync_binlog=1 is the safest setting for synchronizing binlog writes, while the documented default is 0 (OS-managed flushing). For strongest ordinary InnoDB + binary-log durability and replication consistency, MariaDB documentation recommends combining innodb_flush_log_at_trx_commit=1 with sync_binlog=1. That can cost fsync latency, so measure storage and group-commit behavior rather than silently weakening durability.

SELECT @@global.innodb_flush_log_at_trx_commit AS innodb_commit_flush,       @@global.sync_binlog AS sync_binlog,       @@global.log_bin AS binary_log_enabled,       @@global.binlog_format AS binlog_format;

7. Deliberately wrong approach: “turn off fsync-like safety until benchmarks pass”

A benchmark run that changes both innodb_flush_log_at_trx_commit and sync_binlog from durable to relaxed values can show an attractive throughput gain while measuring a different correctness contract. The repaired experiment records the baseline, changes one setting only on a disposable instance, measures throughput/latency, documents the exact crash-loss window, restores the setting, and never promotes the result without explicit business approval of the durability tradeoff.

Likewise, changing redo size and buffer-pool size together destroys causal attribution. Performance engineering is an experiment: hypothesis, one controlled change, workload, counters, latency distribution, rollback, conclusion. Chapter 18 will make that discipline systematic.

8. Lab checklist, knowledge check, and bridge

1. Record redo file size/location variables, commit-flush setting, doublewrite, log_bin and sync_binlog.
2. Commit a controlled UPDATE and compare redo/dirty-page evidence before and after.
3. Inspect checkpoint/LSN information from SHOW ENGINE INNODB STATUS or INNODB_METRICS.
4. Explain why a committed transaction can still have dirty data pages.
5. Write a three-row table that distinguishes redo, binlog and backup.
6. Document the failure mode protected by doublewrite.

The next lesson follows the other half of MVCC: undo history. You will deliberately hold an old read view while another session updates rows, observe history-list growth, and see why long transactions can delay purge long after their own SQL appears idle.