Switching from DELETE to WAL Mode Safely

You have an existing SQLite database already carrying production data on the default DELETE rollback journal, and you need it on Write-Ahead Logging without a maintenance window, without dropping a single committed row, and without the switch quietly failing so you only discover it during the next brownout. That exact migration — a live .db file, in place, with proof it took — is the scenario this page covers. It sits under the Journaling Modes Deep Dive guide within the broader SQLite Architecture & Production Hardening discipline, which explains why the two journal families behave differently; here the concern is narrower and operational: how to flip the mode on a database that is already in service and confirm it worked.

The difficulty is that PRAGMA journal_mode=WAL is deceptively quiet. If any other connection holds the file when you issue it, SQLite cannot take the exclusive lock the switch requires, so it does not raise — it returns the current mode and leaves the database exactly where it was. Fire-and-forget migration scripts therefore log “success,” exit cleanly, and leave a DELETE-journalled database that still serializes every reader behind the writer and still pays a full journal create/delete/fsync cycle per transaction. On eMMC, SD cards, and wear-levelled flash that cost is not academic; it is SQLITE_BUSY storms and premature media wear. The safe procedure is: prove exclusive access, issue the switch, read the result back and assert it, apply crash-safe defaults, then verify the on-disk sidecar files exist.

Diagnosis

Before migrating, confirm the database is genuinely on a rollback journal and that you are the only connection. A one-line query reports the current mode without changing it:

PRAGMA journal_mode;   -- returns 'delete' (or truncate/persist) if not yet on WAL

If this returns delete, truncate, or persist, the database is on a rollback journal and every writer takes an EXCLUSIVE lock across the whole file for the duration of the write — the root cause of reader starvation. The symptom pattern that sends people here is a log full of sqlite3.OperationalError: database is locked under otherwise modest concurrency, plus a <db>-journal file that flickers into existence on every commit. You can watch that journal churn from the shell:

# A rollback-journal database creates and deletes this file every transaction:
ls -la /var/lib/telemetry/sensor.db-journal 2>/dev/null

The second thing to establish is that no other process holds the file. A WAL switch against a database that a background writer, an idle connection pool handle, or a stray REPL still has open will silently no-op. On Linux, list open handles before you migrate:

# Any output means another process still holds the database — quiesce it first:
lsof /var/lib/telemetry/sensor.db

Only when the mode reads as a rollback journal and lsof is empty are you clear to run the switch as a single, exclusive operation.

Solution

The migration below acquires an exclusive connection, records the pre-switch mode, issues the WAL switch, and — critically — reads the result back and refuses to report success unless SQLite actually returned wal. It then applies the crash-safe defaults appropriate for edge and IoT media and forces one checkpoint so the -wal file is materialised immediately rather than on first write. Every PRAGMA is annotated with the trade-off it encodes.

Figure — The safe DELETE → WAL migration: acquire exclusive access, switch the mode, validate it took effect, then apply hardened PRAGMAs and a checkpoint.

The safe DELETE-to-WAL migration pipeline with read-back verification A top-down flow: acquire an EXCLUSIVE connection while lsof is empty, issue PRAGMA journal_mode=WAL, then branch on whether the returned mode is 'wal'. If the exclusive lock could not be taken the switch silently returns the old mode, so the NO branch aborts and logs failure while the database stays on a rollback journal. The YES branch applies hardened PRAGMAs (synchronous=NORMAL, wal_autocheckpoint, journal_size_limit), runs wal_checkpoint(TRUNCATE) to materialise the -wal and -shm files, reads every PRAGMA back and asserts, then reports migration complete. 1 · Acquire EXCLUSIVE connection lsof empty · single owner during the switch 2 · PRAGMA journal_mode=WAL returns the resulting mode — it never raises returned 'wal'? NO Abort — lock denied switch silently returned the OLD mode; DB still on a rollback journal — log & fail A racing reader denies the exclusive lock. Silence is not success — always read back. YES 3 · Apply hardened PRAGMAs synchronous=NORMAL · wal_autocheckpoint=1000 journal_size_limit=64 MB — only after mode confirmed 4 · wal_checkpoint(TRUNCATE) materialises & resets the -wal / -shm sidecars 5 · Read every PRAGMA back & assert mode · synchronous · autocheckpoint — fail on mismatch Migration complete 
import os
import logging
import sqlite3

logger = logging.getLogger("sqlite_wal_migration")


def migrate_to_wal(db_path: str, timeout_s: float = 5.0) -> bool:
    """
    Atomically switch a live SQLite database from a rollback journal to WAL,
    with mandatory read-back verification. Returns True only if the switch and
    every hardened PRAGMA are confirmed applied.
    """
    if not os.path.exists(db_path):
        raise FileNotFoundError(f"Database not found: {db_path}")

    # connect() timeout is in SECONDS; the busy_timeout PRAGMA below is in ms.
    conn = sqlite3.connect(db_path, timeout=timeout_s)
    try:
        # busy_timeout makes a briefly-contended switch retry instead of
        # failing instantly with SQLITE_BUSY during the migration window.
        conn.execute("PRAGMA busy_timeout=5000;")          # wait up to 5000 ms for the lock
        # EXCLUSIVE locking forces this connection to hold the file alone, so a
        # racing reader cannot cause the switch below to silently no-op.
        conn.execute("PRAGMA locking_mode=EXCLUSIVE;")     # single owner during migration

        pre = conn.execute("PRAGMA journal_mode;").fetchone()[0]
        logger.info("pre-migration journal_mode=%s", pre)  # expect 'delete'/'truncate'/'persist'

        # The switch itself. In WAL, journal_mode is PERSISTENT: it is written to
        # the DB header and survives reopen. It RETURNS the resulting mode.
        new = conn.execute("PRAGMA journal_mode=WAL;").fetchone()[0]
        if new.lower() != "wal":
            # Switch was refused (lock not granted) — DB is still on rollback.
            logger.error("switch refused; journal_mode is still %r", new)
            return False

        # Crash-safe defaults, applied only after the mode is confirmed WAL.
        conn.execute("PRAGMA synchronous=NORMAL;")         # fsync at checkpoint, not per commit; ~40-60% faster, never corrupts WAL
        conn.execute("PRAGMA wal_autocheckpoint=1000;")    # merge WAL->DB every ~1000 pages (~4 MB at 4 KiB); lower on constrained flash
        conn.execute("PRAGMA journal_size_limit=67108864;")# truncate WAL back toward 64 MB after checkpoints; bounds disk use

        # TRUNCATE checkpoint materialises -wal/-shm now and resets the WAL to
        # zero length, giving a clean, verifiable starting point.
        conn.execute("PRAGMA wal_checkpoint(TRUNCATE);")   # (busy, log_frames, checkpointed)

        # --- Mandatory read-back verification --------------------------------
        mode = conn.execute("PRAGMA journal_mode;").fetchone()[0]
        sync = conn.execute("PRAGMA synchronous;").fetchone()[0]        # 1 == NORMAL
        acp  = conn.execute("PRAGMA wal_autocheckpoint;").fetchone()[0]
        if mode.lower() != "wal":
            raise RuntimeError(f"post-switch journal_mode is {mode!r}, expected 'wal'")
        if sync != 1:
            raise RuntimeError(f"synchronous is {sync}, expected 1 (NORMAL)")
        if acp != 1000:
            raise RuntimeError(f"wal_autocheckpoint is {acp}, expected 1000")

        logger.info("migrated to WAL: mode=%s synchronous=%s autocheckpoint=%s", mode, sync, acp)
        return True
    except sqlite3.DatabaseError:
        logger.exception("WAL migration failed")
        raise
    finally:
        conn.close()

The TRUNCATE checkpoint on a fresh WAL is cheap and buys certainty: it forces the shared-memory index and log file into existence so the verification step has something concrete to inspect. Note the ordering — the hardened synchronous, wal_autocheckpoint, and journal_size_limit values are set only after the mode is confirmed, so a refused switch never leaves you with half-applied tuning on a database that is still serialising readers.

Verification

A migration that returns True in code is necessary but not sufficient; confirm the switch on disk and from a fresh connection, because journal_mode=WAL is persisted in the header and any new handle should now see it. First, the sidecar files: a live WAL database keeps a -wal (append-only frame log) and a -shm (shared-memory index) file next to the main database whenever a connection is attached.

# After migration, with a connection open, both sidecar files exist:
ls -la /var/lib/telemetry/sensor.db*
#   sensor.db        <- main database
#   sensor.db-wal    <- append-only WAL frame log
#   sensor.db-shm    <- shared-memory WAL index

Then confirm from an independent connection that the header truly carries the mode — this proves the switch was durable, not just an in-session setting:

import sqlite3

with sqlite3.connect("/var/lib/telemetry/sensor.db") as check:
    mode = check.execute("PRAGMA journal_mode;").fetchone()[0]
    assert mode.lower() == "wal", f"header did not persist WAL; got {mode!r}"
    # integrity_check must return exactly 'ok' before you accept new writes.
    integrity = check.execute("PRAGMA integrity_check;").fetchone()[0]
    assert integrity == "ok", f"integrity_check returned {integrity!r}"
    print("verified: WAL mode persisted, integrity ok")

If you enabled PRAGMA locking_mode=EXCLUSIVE during migration, remember it is per-connection and does not persist; the verification connection above opens in the default NORMAL locking mode and should still report wal. Watching the WAL grow and reset under real traffic — the mechanics of checkpoint frequency tuning and WAL autocheckpoint — is the final confirmation the mode is not just set but healthy.

Failure Modes & Gotchas

Silent revert when the switch races a reader. This is the defining failure of this migration. If a pool handle, a supervisor’s health-check connection, or an idle REPL holds the file, the exclusive lock is denied and PRAGMA journal_mode=WAL returns the old mode with no error. The mitigation is exactly the read-back assertion in the solution above: never trust the switch’s silence, always compare the returned value against wal and fail loudly. In a fleet, quiesce writers first (lsof empty) or run the migration as the very first thing the provisioning process does, before any pool warms up. The tuned-mode read-back also caught this class of bug on the parent journaling modes page.

The target filesystem cannot back WAL. WAL depends on POSIX advisory locking and a shared-memory -shm mapping. Network mounts (NFS, SMB) and some FUSE overlays implement these incompletely, so the switch either fails to create -shm (SQLITE_CANTOPEN) or, worse, appears to succeed while cross-process visibility is broken. This is also where the FAT32 versus ext4 distinction bites — see managing file locks on FAT32 vs ext4. If the database lives on such a volume, do not force WAL; degrade deterministically to a PERSIST/TRUNCATE rollback journal with synchronous=FULL per the fallback routing strategies, and confirm the choice against the synchronous PRAGMA crash-safety trade-off.

Deleting -wal/-shm to “clean up,” or a snapshot that copies only the .db. Once on WAL, the -wal file holds committed transactions that have not yet been checkpointed into the main file. Removing it while a connection is open — or taking a backup that captures the .db alone — discards those transactions and can surface as SQLITE_CORRUPT or a silently stale restore. Never delete the sidecars against a live database; if you must remove them for recovery, close every connection first, then delete both -wal and -shm together, and re-run PRAGMA integrity_check before resuming. For backups, checkpoint with wal_checkpoint(TRUNCATE) immediately before the snapshot or use SQLite’s online backup API so the copy spans the main file and WAL consistently. Keep the sidecars under the same strict filesystem permissions and security boundaries as the main database, and pair the migration with a sensible busy_timeout so post-switch write contention retries instead of failing.