Introduction

Every time you commit a bank transfer, place an online order, or save a document, a database promises you something remarkable: that data will not vanish, even if the server loses power a millisecond after you press Submit.

How can it make that promise? Flushing every changed data page to disk on every commit would work, but a random write to disk is slow — far too slow for a busy database. Write-ahead logging (WAL) is the elegant escape hatch: instead of rewriting scattered data pages immediately, the database first appends a compact description of the change to a sequential log file and flushes that. Sequential writes are much faster than random ones, and once the log record is on stable storage, the commit is durable by definition.

The rule is simple to state: the log record must reach disk before the corresponding data-page change can be declared committed. Everything else in crash recovery flows from that one invariant. If the system crashes, the database replays the log forward (REDO) to reconstruct committed changes and rolls back uncommitted ones — restoring a consistent state with no human intervention.

WAL is not exotic research; it is the proven engineering backbone of PostgreSQL, MySQL InnoDB, SQLite, and virtually every other ACID-compliant database in production today.

Crash-Recovery Demo

This simulation shows a tiny database with two account balances. Use the buttons to build a transaction step by step: each operation is first appended to the Write-Ahead Log before touching the data pages. Then crash the system at any point and watch the recovery process replay the log — committing what was durable and discarding what was not.

<div class="wal-container">
  <div class="panels">
    <div class="panel">
      <div class="panel-title">📋 Write-Ahead Log</div>
      <div id="log-entries" class="log-area">
        <div class="log-empty">Log is empty</div>
      </div>
    </div>
    <div class="panel">
      <div class="panel-title">💾 Data Pages (in-memory)</div>
      <div class="data-area">
        <div class="account">
          <span class="acct-label">Account A</span>
          <span class="acct-value" id="mem-a">$1000</span>
        </div>
        <div class="account">
          <span class="acct-label">Account B</span>
          <span class="acct-value" id="mem-b">$500</span>
        </div>
        <div class="dirty-note" id="dirty-note" style="display:none">⚠ Dirty pages not yet flushed to disk</div>
      </div>
      <div class="panel-title mt">💽 Disk (stable state)</div>
      <div class="data-area">
        <div class="account">
          <span class="acct-label">Account A</span>
          <span class="acct-value" id="disk-a">$1000</span>
        </div>
        <div class="account">
          <span class="acct-label">Account B</span>
          <span class="acct-value" id="disk-b">$500</span>
        </div>
      </div>
    </div>
  </div>
  <div class="status-bar" id="status-bar">Ready — start a transaction below.</div>
  <div class="btns">
    <button id="btn-begin" type="button">BEGIN</button>
    <button id="btn-debit" type="button" disabled>DEBIT A –$200</button>
    <button id="btn-credit" type="button" disabled>CREDIT B +$200</button>
    <button id="btn-commit" type="button" disabled>COMMIT</button>
    <button id="btn-crash" type="button" class="danger" disabled>💥 CRASH</button>
    <button id="btn-recover" type="button" class="recover" style="display:none">♻ RECOVER</button>
    <button id="btn-reset" type="button" class="ghost">Reset</button>
  </div>
</div>

* { box-sizing: border-box; }
body { font-family: system-ui, sans-serif; margin: 0; color: #1a2636; }
.wal-container { padding: .2rem 0; }
.panels { display: grid; grid-template-columns: 1fr 1fr; gap: .7rem; margin-bottom: .6rem; }
.panel { background: #f0f4f8; border: 1px solid #cdd9e3; border-radius: 8px; padding: .6rem .7rem; }
.panel-title { font-size: .75rem; font-weight: 700; letter-spacing: .04em; color: #3a5068; text-transform: uppercase; margin-bottom: .4rem; }
.panel-title.mt { margin-top: .7rem; }
.log-area { min-height: 130px; display: flex; flex-direction: column; gap: 3px; }
.log-empty { font-size: .8rem; color: #8a9aaa; font-style: italic; }
.log-entry { font-size: .78rem; font-family: ui-monospace, monospace; padding: 3px 6px; border-radius: 4px; border-left: 3px solid #5a7088; background: #e3eaf0; }
.log-entry.begin { border-color: #2a7abf; background: #ddeefa; }
.log-entry.change { border-color: #e67a00; background: #fdf0df; }
.log-entry.commit { border-color: #1a9e4a; background: #d8f5e5; font-weight: 700; }
.log-entry.replayed { opacity: .65; }
.data-area { display: flex; flex-direction: column; gap: 5px; }
.account { display: flex; justify-content: space-between; align-items: center; background: #fff; border: 1px solid #cdd9e3; border-radius: 6px; padding: 4px 8px; }
.acct-label { font-size: .82rem; font-weight: 600; color: #3a5068; }
.acct-value { font-size: .9rem; font-family: ui-monospace, monospace; font-weight: 700; color: #1a2636; }
.acct-value.changed { color: #e67a00; }
.acct-value.recovered { color: #1a9e4a; }
.dirty-note { font-size: .72rem; color: #c05a00; margin-top: 3px; }
.status-bar { font-size: .88rem; font-weight: 600; min-height: 1.4em; padding: .3rem .4rem; border-radius: 6px; margin-bottom: .5rem; background: #e8eef3; }
.status-bar.ok { background: #d8f5e5; color: #0f6e30; }
.status-bar.warn { background: #fdf0df; color: #7a4400; }
.status-bar.danger { background: #fde8e8; color: #8b0000; }
.status-bar.info { background: #ddeefa; color: #0a4a7a; }
.btns { display: flex; flex-wrap: wrap; gap: .4rem; }
button { font: 600 13px system-ui; padding: .4rem .8rem; border-radius: 7px; border: 1px solid #1d3557; background: #1d3557; color: #fff; cursor: pointer; }
button:disabled { opacity: .4; cursor: default; }
button.ghost { background: #fff; color: #1d3557; }
button.danger { background: #c0392b; border-color: #a93226; }
button.recover { background: #1a9e4a; border-color: #158040; }

const i18nFmt = (s, v) => s.replace(/%(\w+)%/g, (_, k) => String(v[k] ?? ''));
// Write-Ahead Logging demo
// State: a tiny database with two accounts and a WAL.
// The WAL is always written BEFORE the in-memory (data-page) change.
// On CRASH: in-memory changes since last COMMIT are lost; log survives.
// On RECOVER: replay log → REDO committed transactions → consistent state.

const $ = id => document.getElementById(id);

// Stable on-disk state (survives crashes)
let disk = { a: 1000, b: 500 };
// In-memory (dirty) state
let mem = { a: 1000, b: 500 };
// The write-ahead log (array of entry objects)
let wal = [];
// Current transaction id
let txId = 0;
let inTx = false;
let crashed = false;
// Track which log entries are durably flushed (all of them, by WAL invariant)
// Data pages are NOT flushed until COMMIT causes a checkpoint in this simplified model.

function fmt(v) { return '


Notice the key asymmetry. Without WAL, a crash between writing two data pages leaves the database inconsistent with no way to fix it. With WAL, the log is the ground truth: recovery reads it from the last checkpoint, REDOs every committed transaction, and ignores every incomplete one — guaranteed correct no matter when the crash happened.

 + v; }

function renderLog() {
  const area = $('log-entries');
  if (wal.length === 0) {
    area.innerHTML = '<div class="log-empty">Log is empty</div>';
    return;
  }
  area.innerHTML = wal.map(e => {
    let cls = 'log-entry';
    let text = '';
    if (e.type === 'BEGIN') { cls += ' begin'; text = `LSN ${e.lsn}: BEGIN TX${e.tx}`; }
    else if (e.type === 'CHANGE') { cls += ' change'; text = `LSN ${e.lsn}: TX${e.tx} ${e.account} ${fmt(e.before)} → ${fmt(e.after)}`; }
    else if (e.type === 'COMMIT') { cls += ' commit'; text = `LSN ${e.lsn}: COMMIT TX${e.tx} ✓`; }
    if (e.replayed) cls += ' replayed';
    return `<div class="${cls}">${text}</div>`;
  }).join('');
}

function renderData(highlight) {
  $('mem-a').textContent = fmt(mem.a);
  $('mem-b').textContent = fmt(mem.b);
  $('disk-a').textContent = fmt(disk.a);
  $('disk-b').textContent = fmt(disk.b);
  $('mem-a').className = 'acct-value' + (highlight === 'changed' && mem.a !== disk.a ? ' changed' : highlight === 'recovered' ? ' recovered' : '');
  $('mem-b').className = 'acct-value' + (highlight === 'changed' && mem.b !== disk.b ? ' changed' : highlight === 'recovered' ? ' recovered' : '');
  $('dirty-note').style.display = (mem.a !== disk.a || mem.b !== disk.b) ? '' : 'none';
}

function setStatus(msg, cls) {
  const s = $('status-bar');
  s.textContent = msg;
  s.className = 'status-bar' + (cls ? ' ' + cls : '');
}

function setButtons(state) {
  // states: idle | tx | committed | crashed | recovered
  $('btn-begin').disabled  = state !== 'idle';
  $('btn-debit').disabled  = state !== 'tx';
  $('btn-credit').disabled = state !== 'tx';
  $('btn-commit').disabled = state !== 'tx';
  $('btn-crash').disabled  = state !== 'tx';
  $('btn-recover').style.display = state === 'crashed' ? '' : 'none';
}

let lsn = 0;
function appendLog(entry) {
  entry.lsn = ++lsn;
  wal.push(entry);
  renderLog();
}

// BEGIN
$('btn-begin').addEventListener('click', () => {
  if (inTx) return;
  txId++;
  inTx = true;
  appendLog({ type: 'BEGIN', tx: txId });
  setStatus(i18nFmt('TX%txId% started. Now debit and credit the accounts.', { txId: txId }), 'info');
  setButtons('tx');
});

// DEBIT A
$('btn-debit').addEventListener('click', () => {
  if (!inTx) return;
  const before = mem.a;
  const after = before - 200;
  // WAL first: log the change before touching mem
  appendLog({ type: 'CHANGE', tx: txId, account: 'Account A', before, after });
  mem.a = after; // now apply to in-memory page
  setStatus('Logged DEBIT to WAL → applied to memory page. Disk unchanged.', 'warn');
  renderData('changed');
  $('btn-debit').disabled = true;
});

// CREDIT B
$('btn-credit').addEventListener('click', () => {
  if (!inTx) return;
  const before = mem.b;
  const after = before + 200;
  appendLog({ type: 'CHANGE', tx: txId, account: 'Account B', before, after });
  mem.b = after;
  setStatus('Logged CREDIT to WAL → applied to memory page. Disk unchanged.', 'warn');
  renderData('changed');
  $('btn-credit').disabled = true;
});

// COMMIT
$('btn-commit').addEventListener('click', () => {
  if (!inTx) return;
  // Write COMMIT record to log (log is on stable storage — WAL invariant satisfied)
  appendLog({ type: 'COMMIT', tx: txId });
  // Now flush dirty pages to disk (checkpoint)
  disk.a = mem.a;
  disk.b = mem.b;
  inTx = false;
  setStatus('COMMIT written to WAL ✓  Dirty pages flushed to disk. Transaction durable.', 'ok');
  renderData();
  setButtons('committed');
});

// CRASH (mid-transaction)
$('btn-crash').addEventListener('click', () => {
  if (!inTx) return;
  // Simulate crash: in-memory state lost; disk untouched; WAL on stable storage survives
  mem.a = disk.a;
  mem.b = disk.b;
  crashed = true;
  inTx = false;
  setStatus('💥 CRASH! In-memory state wiped. Disk = last checkpoint. WAL intact on storage.', 'danger');
  renderData();
  $('dirty-note').style.display = 'none';
  setButtons('crashed');
});

// RECOVER
$('btn-recover').addEventListener('click', () => {
  if (!crashed) return;
  // REDO recovery: scan WAL, find committed transactions, replay their changes
  // Find committed tx ids
  const committed = new Set(wal.filter(e => e.type === 'COMMIT').map(e => e.tx));
  // Reset mem to disk state (= last checkpoint before crash)
  let ra = disk.a, rb = disk.b;
  // Replay CHANGE records for committed transactions
  for (const e of wal) {
    if (e.type === 'CHANGE' && committed.has(e.tx)) {
      e.replayed = true;
      if (e.account === 'Account A') ra = e.after;
      if (e.account === 'Account B') rb = e.after;
    }
  }
  mem.a = ra; mem.b = rb;
  disk.a = ra; disk.b = rb; // after recovery, flush
  crashed = false;
  renderLog();
  renderData('recovered');
  const redone = wal.filter(e => e.replayed).length;
  setStatus(i18nFmt('Recovery complete ✓  Replayed %redone% log record(s) from %size% committed TX. Uncommitted changes discarded.', { redone: redone, size: committed.size }), 'ok');
  setButtons('recovered');
});

// RESET
$('btn-reset').addEventListener('click', () => {
  disk = { a: 1000, b: 500 };
  mem  = { a: 1000, b: 500 };
  wal  = []; lsn = 0; txId = 0; inTx = false; crashed = false;
  renderLog();
  renderData();
  setStatus('Ready — start a transaction below.');
  setButtons('idle');
});

// Init
renderLog();
renderData();
setStatus('Ready — start a transaction below.');
setButtons('idle');

// Node.js verification (runs only outside the browser sandbox)
if (typeof module !== 'undefined') {
  // Scenario 1: full commit — both accounts should reflect the transfer
  disk = { a: 1000, b: 500 }; mem = { a: 1000, b: 500 }; wal = []; lsn = 0; txId = 0;
  // BEGIN
  txId = 1; wal.push({ lsn: ++lsn, type: 'BEGIN', tx: 1 });
  // DEBIT
  wal.push({ lsn: ++lsn, type: 'CHANGE', tx: 1, account: 'Account A', before: 1000, after: 800 });
  mem.a = 800;
  // CREDIT
  wal.push({ lsn: ++lsn, type: 'CHANGE', tx: 1, account: 'Account B', before: 500, after: 700 });
  mem.b = 700;
  // COMMIT
  wal.push({ lsn: ++lsn, type: 'COMMIT', tx: 1 });
  disk.a = mem.a; disk.b = mem.b;
  console.assert(disk.a === 800, 'COMMIT: disk.a should be 800');
  console.assert(disk.b === 700, 'COMMIT: disk.b should be 700');

  // Scenario 2: crash after DEBIT but before COMMIT, then RECOVER
  disk = { a: 1000, b: 500 }; mem = { a: 1000, b: 500 }; wal = []; lsn = 0; txId = 0;
  txId = 1; wal.push({ lsn: ++lsn, type: 'BEGIN', tx: 1 });
  wal.push({ lsn: ++lsn, type: 'CHANGE', tx: 1, account: 'Account A', before: 1000, after: 800 });
  mem.a = 800;
  // CRASH before COMMIT
  mem.a = disk.a; mem.b = disk.b;
  // RECOVER
  const committed2 = new Set(wal.filter(e => e.type === 'COMMIT').map(e => e.tx));
  let ra2 = disk.a, rb2 = disk.b;
  for (const e of wal) {
    if (e.type === 'CHANGE' && committed2.has(e.tx)) {
      if (e.account === 'Account A') ra2 = e.after;
      if (e.account === 'Account B') rb2 = e.after;
    }
  }
  mem.a = ra2; mem.b = rb2; disk.a = ra2; disk.b = rb2;
  // Uncommitted TX, so disk should be UNCHANGED
  console.assert(disk.a === 1000, 'CRASH+RECOVER: disk.a should remain 1000 (uncommitted)');
  console.assert(disk.b === 500,  'CRASH+RECOVER: disk.b should remain 500 (uncommitted)');

  // Scenario 3: commit TX1, then crash mid TX2, recover — TX1 persists, TX2 rolled back
  disk = { a: 1000, b: 500 }; mem = { a: 1000, b: 500 }; wal = []; lsn = 0; txId = 0;
  // TX1 commit
  txId = 1; wal.push({ lsn: ++lsn, type: 'BEGIN', tx: 1 });
  wal.push({ lsn: ++lsn, type: 'CHANGE', tx: 1, account: 'Account A', before: 1000, after: 800 });
  wal.push({ lsn: ++lsn, type: 'CHANGE', tx: 1, account: 'Account B', before: 500, after: 700 });
  wal.push({ lsn: ++lsn, type: 'COMMIT', tx: 1 });
  disk.a = 800; disk.b = 700; mem.a = 800; mem.b = 700;
  // TX2 begin debit, crash
  txId = 2; wal.push({ lsn: ++lsn, type: 'BEGIN', tx: 2 });
  wal.push({ lsn: ++lsn, type: 'CHANGE', tx: 2, account: 'Account A', before: 800, after: 600 });
  mem.a = 600;
  // CRASH
  mem.a = disk.a; mem.b = disk.b;
  // RECOVER
  const committed3 = new Set(wal.filter(e => e.type === 'COMMIT').map(e => e.tx));
  let ra3 = disk.a, rb3 = disk.b;
  for (const e of wal) {
    if (e.type === 'CHANGE' && committed3.has(e.tx)) {
      if (e.account === 'Account A') ra3 = e.after;
      if (e.account === 'Account B') rb3 = e.after;
    }
  }
  disk.a = ra3; disk.b = rb3;
  console.assert(disk.a === 800, i18nFmt('Mixed: TX1 committed debit should be 800, got %a%', { a: disk.a }));
  console.assert(disk.b === 700, i18nFmt('Mixed: TX1 committed credit should be 700, got %b%', { b: disk.b }));

  console.log('All WAL assertions passed.');
}
Result
Console

Notice the key asymmetry. Without WAL, a crash between writing two data pages leaves the database inconsistent with no way to fix it. With WAL, the log is the ground truth: recovery reads it from the last checkpoint, REDOs every committed transaction, and ignores every incomplete one — guaranteed correct no matter when the crash happened.

How It Really Works

The performance story behind WAL comes down to one hardware fact: sequential writes are much faster than random writes on both spinning disks and SSDs. Appending to a log file is sequential; overwriting a random data page is not.

  • Log Sequence Numbers (LSNs): every log record gets a monotonically increasing LSN. Data pages store the LSN of the last record that modified them, letting recovery know exactly how far each page has been applied.
  • Checkpointing: periodically the database flushes all dirty data pages to disk and writes a checkpoint record in the log. During recovery, only the log after the last checkpoint needs to be replayed — keeping startup time bounded even with a huge log.
  • REDO-only vs UNDO/REDO: some systems (SQLite WAL mode) use a simple REDO-only approach — new versions go to the log, old versions stay in the original file. Others (InnoDB) also maintain an UNDO log so that long-running transactions can be rolled back without touching the REDO log. The WAL invariant holds in both designs.
  • Group commit: instead of flushing the log after every individual transaction, the database batches many commits into one flush. Throughput rises dramatically with no loss of durability — each transaction still has its log record on disk before the commit acknowledgement goes out.

The result is a system where durability is cheap (one sequential write per commit), recovery is deterministic (replay the log from the last checkpoint), and consistency is guaranteed by construction. This is the engineering achievement that makes ACID transactions practical at scale.

Where It Matters

The idea "write intentions to a durable sequential log before acting on them" turns out to be one of the most reusable patterns in systems engineering:

  • Relational databases: PostgreSQL, MySQL InnoDB, Oracle, and SQL Server all use WAL (or its direct equivalent) to implement ACID durability. SQLite's WAL mode trades a separate lock for higher read concurrency.
  • Distributed consensus (Raft / Paxos): a Raft leader appends log entries and waits for a majority of followers to acknowledge them before committing — the same "log first, apply second" invariant, now across a network of machines. The log is the state machine.
  • Change data capture (CDC): tools like Debezium read the database's WAL as a stream of change events, enabling real-time replication, search-index updates, and audit trails without touching application code.
  • Event-sourcing architectures: systems like Apache Kafka are essentially append-only logs at their core. Every event is a log record; current state is derived by replaying. The conceptual debt goes straight back to WAL.

See how distributed systems build on the same log idea to coordinate state across many machines — the principle scales remarkably far beyond a single database.

Conclusion

Write-ahead logging is one of those ideas that looks almost too simple to be true: write it down before you do it. Yet that single rule is what lets a database make an ironclad promise — that once it says "committed," your data will survive power failures, kernel panics, and all the other disasters that punctuate the life of a running system.

The log replaces expensive random writes with cheap sequential appends, turns crash recovery into a deterministic replay, and — in distributed form — becomes the backbone of consensus protocols that coordinate thousands of machines. Next time you see a database advertising ACID guarantees, you now know the quiet mechanism doing the heavy lifting: a humble, append-only log that always goes first.

Share this article

Pick a channel — or use your device's native share sheet.

Comments

Loading comments...

https://www.kipuhub.com/en/article/write-ahead-logging/Content licensed under CC BY-NC 4.0.