Delayed Visibility of WAL-Mode Commits Across SQLite Connections

Understanding Transaction Isolation and Commit Timing in SQLite WAL Mode

The core challenge revolves around ensuring that changes committed by a writer connection (Connection A) in SQLite’s Write-Ahead Logging (WAL) mode become visible to a reader connection (Connection B) in a timely manner. This scenario typically occurs when Connection B attaches the database as read-only and attempts to access data immediately after being notified of a commit by Connection A. The observed behavior—where Connection B does not immediately see updates—stems from SQLite’s transaction isolation mechanisms and the specific timing of commit-related operations.

In WAL mode, readers and writers can operate concurrently without blocking each other. However, readers maintain a consistent snapshot of the database state as of the moment their read transaction begins. If Connection B initiates a read transaction (explicitly or implicitly) before Connection A finalizes its commit, Connection B will continue to see the pre-commit state until it explicitly ends its transaction and starts a new one. This behavior is by design and ensures transactional consistency.

The confusion often arises from the interaction between commit hooks and the actual visibility of changes. Commit hooks in SQLite are invoked before the commit process finalizes, not after. This means notifications triggered by a commit hook may prematurely signal Connection B to read data before the commit is fully durable and visible. Additionally, Connection B might inadvertently hold open read transactions due to unfinalized prepared statements or cursors, further delaying visibility of new changes.

Root Causes of Delayed Change Visibility in WAL Mode

Active Read Transactions on Connection B
If Connection B has an open read transaction (e.g., an unfinalized SELECT statement or an uncommitted BEGIN TRANSACTION), it will continue to observe the database state as it existed when the transaction began. This is true even if Connection A has committed changes after the start of Connection B’s transaction. SQLite’s snapshot isolation ensures that readers see a consistent view of the database, which requires explicit transaction boundaries to refresh the snapshot.
Misuse of Commit Hooks for Notification
Commit hooks execute before the commit is finalized. If Connection A uses a commit hook to notify Connection B, the notification occurs prior to the commit being fully written to the WAL file. Consequently, Connection B may attempt to read the database before the changes are visible. The correct trigger for notifications should be after the commit statement (e.g., COMMIT) returns SQLITE_DONE, indicating the transaction has been successfully applied.
Unfinalized Queries Holding Transactions Open
SQLite automatically starts a read transaction when a query is executed and keeps it open until the query is finalized. If Connection B executes a query but does not call sqlite3_finalize() on the corresponding prepared statement, the read transaction remains active, blocking visibility of new commits. For example, a SELECT statement that is stepped through but not reset or finalized can retain a transaction indefinitely.
File System Caching and Immutable Database Handles
While less common, file system caching or opening the database in immutable mode (?immutable=1) can theoretically affect visibility. However, in standard configurations, these are unlikely culprits unless explicitly configured.

Resolving Delayed Visibility: Transaction Management and Monitoring

Step 1: Ensure Proper Transaction Boundaries on Connection B
Explicitly manage read transactions on Connection B to avoid long-running snapshots. After receiving a notification of a commit from Connection A, execute the following before re-reading data:

COMMIT;  -- End any pending transaction  
BEGIN TRANSACTION;  -- Start a new read transaction

This guarantees that Connection B’s new transaction starts after Connection A’s commit, ensuring access to the latest database state.

Step 2: Audit and Finalize Unfinalized Queries
Use the sqlite_stmt virtual table to identify unfinalized statements that may be holding transactions open:

SELECT sql FROM sqlite_stmt WHERE run AND sql NOT LIKE '%sqlite_stmt%';

If this query returns rows, it indicates active statements that need to be finalized with sqlite3_finalize(). Address this in your code by ensuring all prepared statements are properly cleaned up after use.

Step 3: Replace Commit Hooks with Post-Commit Notifications
Instead of relying on a commit hook, trigger notifications to Connection B after the commit operation completes. For example:

// Connection A's code (C API example)  
sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);  
if (sqlite3_get_autocommit(db)) {  
    // Only notify Connection B after commit is confirmed  
    notify_connection_b();  
}

This ensures Connection B does not attempt to read until the commit is fully durable.

Step 4: Use PRAGMA data_version for Change Detection
The data_version pragma provides a monotonically increasing integer that changes with each commit. Connection B can poll this value to detect changes without re-reading the entire dataset:

# Connection B's code (pseudo-code)  
last_version = execute("PRAGMA schema.data_version")  
while True:  
    current_version = execute("PRAGMA schema.data_version")  
    if current_version != last_version:  
        last_version = current_version  
        re_read_data()  
    sleep(poll_interval)

This approach minimizes unnecessary data reloading and decouples Connection B from Connection A’s notification mechanism.

Step 5: Verify WAL Configuration and File Handles
Ensure neither connection opens the database with immutable=1 or other restrictive modes. Confirm WAL mode is active:

PRAGMA journal_mode=WAL;

If the database is opened read-only, ensure the WAL file (-wal) and shared memory file (-shm) are accessible to Connection B.

Step 6: Implement Exponential Backoff for Polling
If polling data_version, use an adaptive sleep interval to balance responsiveness and resource usage. For example:

poll_interval = 0.1  # Start with 100ms  
while True:  
    current_version = execute("PRAGMA data_version")  
    if current_version != last_version:  
        last_version = current_version  
        re_read_data()  
        poll_interval = max(0.1, poll_interval / 2)  # Increase responsiveness  
    else:  
        poll_interval = min(1.0, poll_interval * 1.5)  # Reduce CPU usage  
    sleep(poll_interval)

Final Considerations

Transaction Isolation Levels: SQLite defaults to SERIALIZABLE isolation, but in WAL mode, readers and writers operate with SNAPSHOT isolation. This guarantees consistency but requires explicit transaction management.
Connection Pooling: If using connection pools, ensure each connection follows the same transaction discipline.
Debugging Tools: Use sqlite3_trace() or logging to monitor transaction boundaries and statement finalization in complex applications.

By systematically addressing transaction boundaries, statement finalization, and change detection mechanisms, developers can ensure timely visibility of WAL-mode commits across connections.