WAL Mode

Understanding WAL Mode Transaction Visibility Across Connections

SQLite’s Write-Ahead Logging (WAL) mode fundamentally alters how database modifications are handled compared to traditional rollback journal modes. In WAL mode, all write operations are first appended to a separate WAL file instead of overwriting the main database file. Readers continue to access the original database file and the WAL file to reconstruct the most recent committed state. This design allows concurrent read and write operations without blocking, but introduces specific visibility rules that govern how and when changes become observable across connections.

When a connection writes to the database (referred to as "Connection 1" in this scenario), the changes are initially stored in the WAL file. These changes are not immediately visible to other connections ("Connection 2") because SQLite enforces snapshot isolation. Each read operation in WAL mode observes a consistent snapshot of the database as it existed at the start of the read transaction. For Connection 2 to see changes made by Connection 1, two conditions must be met:

Connection 1’s transaction must be fully committed, ensuring its changes are durably written to the WAL file.
Connection 2 must initiate its read transaction after Connection 1’s commit has completed.

The WAL file contains a sequence of "frames" representing database modifications. A reader determines which frames to process by consulting a shared-memory structure called the "WAL-index," which tracks the current end of the valid WAL data. If Connection 1’s commit has not updated the WAL-index durably (due to filesystem caching or delayed synchronization), Connection 2 might read from an outdated WAL state.

The PRAGMA wal_checkpoint command influences how aggressively SQLite transfers data from the WAL file back to the main database. A passive checkpoint (the default) occurs automatically when the WAL file reaches a certain size or when a connection closes. A RESTART checkpoint truncates the WAL file after transferring its contents, which can interfere with visibility if done prematurely.

The core issue arises when Connection 2’s read operation begins before Connection 1’s changes are fully durable in the WAL file or before the WAL-index is updated. The sleep(1) or sync() workaround likely allows sufficient time for filesystem caches to flush or for background checkpointing to occur, making Connection 1’s changes visible.

Root Causes of Delayed Visibility in Concurrent SQLite Connections

Incomplete Transaction Finalization in Connection 1
SQLite wraps every write operation in an implicit transaction if no explicit BEGIN/COMMIT is used. If Connection 1’s write operation is part of an uncommitted transaction (e.g., due to autocommit timing or nested transactions), its changes remain in the WAL file’s provisional state. Connection 2 will not see these changes until the transaction is committed. The sqlite3_txn_state() API can diagnose whether a transaction is active, committed, or rolled back.
Filesystem Caching and Synchronization Delays
The PRAGMA synchronous setting determines how aggressively SQLite flushes data to storage. When set to NORMAL (the default in WAL mode), SQLite does not wait for the operating system to confirm that writes have reached persistent storage after transaction commits. This improves performance but risks losing recent changes in a power failure. More critically for visibility, other connections might not see changes if they are still buffered in the OS’s page cache. The sync() system call forces a flush of all pending writes to disk, which explains why invoking it before Connection 2’s read operation resolves the issue.
Checkpointing Frequency and Strategy
Passive checkpoints occur automatically but only when no other connections are reading the database. If Connection 2 holds a long-running read transaction, passive checkpoints are blocked, preventing the WAL file from being truncated. This forces new readers to process a longer WAL file, increasing the chance of visibility delays. A RESTART checkpoint forcibly truncates the WAL file but does not guarantee that all readers will immediately see the latest data, especially if the checkpoint is performed before the WAL changes are fully synchronized.
Read Transaction Snapshot Isolation
Connection 2’s read operations operate on a snapshot of the database state captured at the start of its transaction. If Connection 2 uses a single long-running transaction (e.g., via explicit BEGIN without COMMIT), it will not see changes made by Connection 1 until it restarts its transaction. Autocommit mode (where each SELECT is its own transaction) avoids this but introduces overhead.
Filesystem-Specific Behavior
UBIFS (Unsorted Block Image File System), used in the reported scenario, is optimized for NAND flash storage. It employs write buffering and wear-leveling algorithms that can delay the visibility of small writes. Unlike traditional filesystems, UBIFS does not guarantee immediate metadata consistency without explicit synchronization, exacerbating visibility delays.

Resolving Update Visibility Issues in Multi-Connection WAL Mode Setups

Step 1: Enforce Full Synchronization
Configure Connection 1 with PRAGMA synchronous=FULL. This ensures that every transaction commit waits until the WAL file is fully flushed to persistent storage. While this reduces write throughput, it guarantees that Connection 2 will see changes immediately after a commit. Verify the current setting using PRAGMA synchronous; and adjust as needed.

Step 2: Explicit Transaction Boundaries
Wrap Connection 1’s write operations in explicit transactions to minimize the window between write completion and commit. For example:

BEGIN IMMEDIATE;
INSERT INTO ...;
COMMIT;

The IMMEDIATE keyword prevents other writers from acquiring locks while the transaction is open. After COMMIT, use sqlite3_txn_state() to confirm the transaction has exited.

Step 3: Monitor and Control Checkpointing
Replace one-time PRAGMA wal_checkpoint(RESTART) calls with periodic passive checkpoints. After Connection 1 commits a transaction, invoke PRAGMA wal_checkpoint(PASSIVE); to encourage WAL file truncation. Monitor the WAL file size using PRAGMA wal_checkpoint(TRUNCATE); if it grows excessively.

Step 4: Shorten Read Transactions
Ensure Connection 2 uses autocommit mode for read operations or explicitly closes transactions immediately after fetching data. For example, avoid patterns like:

BEGIN;
SELECT ...; -- Holds snapshot open
-- Application logic
SELECT ...; -- Still sees old data
COMMIT;

Instead, use self-contained transactions:

SELECT ...; -- Autocommit transaction
-- Application logic
SELECT ...; -- New autocommit transaction

Step 5: Filesystem Tuning
For UBIFS, adjust mount options to reduce write buffering. Mount with sync to disable write caching or increase the frequency of background synchronization. Test with PRAGMA temp_store=MEMORY; to isolate issues related to temporary file handling.

Step 6: Diagnostic Queries
Use the following queries to diagnose visibility issues:

PRAGMA wal_checkpoint; — Check WAL file status.
SELECT * FROM sqlite_master; — Force a schema reload if tables are missing.
PRAGMA integrity_check; — Verify database consistency.

Step 7: Connection Reinitialization
If Connection 2 is long-lived and cannot be restarted, periodically reset its snapshot by closing and reopening the connection. Alternatively, execute PRAGMA schema_version; to force a reload of the schema version counter, which indirectly refreshes the connection’s view of the database.

Step 8: Compile-Time Configuration
Revisit the -DSQLITE_OMIT_SHARED_CACHE=1 compile flag. While this disables shared cache mode, ensure that connection pooling or thread-local storage is not inadvertently causing connections to use outdated file handles.

Step 9: Application-Level Synchronization
Implement a lightweight notification mechanism (e.g., a file flag or inter-process signal) to alert Connection 2 when Connection 1 commits changes. This avoids polling and reduces latency.

Step 10: Profiling and Logging
Enable SQLite’s error and warning logging using sqlite3_config(SQLITE_CONFIG_LOG, ...). Monitor for SQLITE_BUSY or SQLITE_LOCKED errors, which indicate concurrency contention. Use OS-level tools like strace to trace filesystem calls and confirm that fsync() operations occur as expected.

By systematically addressing transaction boundaries, synchronization settings, checkpointing strategies, and filesystem behavior, developers can ensure consistent visibility of changes across SQLite connections in WAL mode. The key is to balance durability requirements with performance by tailoring pragmas and transaction logic to the specific storage environment and access patterns.