WAL-Index and WAL-Log Consistency After Power Loss or System Crashes

Understanding the Relationship Between WAL-Index Integrity, WAL-Log Synchronization, and Crash Recovery

Issue Overview
The Write-Ahead Logging (WAL) mechanism in SQLite relies on two critical components: the WAL log file (containing uncommitted database changes) and the WAL-index (a memory-mapped shared structure that accelerates access to the WAL log). A key vulnerability arises when the system crashes or loses power while these components are being modified. The WAL-index is updated directly in memory via mmap, and the operating system (OS) writes these changes back to the file asynchronously. This creates a scenario where:

1. The order of writebacks for modified memory pages is not guaranteed to match the order of application-level modifications.
2. There is no explicit synchronization (e.g., fsync) between updates to the WAL-index header, the aFrameCksum array (checksums for WAL frames), and the WAL log itself.
3. The walIndexTryHdr function validates only the WAL-index header during recovery, not the deeper structures like aFrameCksum or the alignment with the WAL log.

This can lead to a state where the WAL-index contains partially written or logically inconsistent data relative to the WAL log. For example, the WAL-index header might indicate valid data, but the associated checksums or frame offsets in aFrameCksum could mismatch the actual WAL log content. The core question is whether SQLite’s recovery logic (walIndexRecover) detects and resolves such inconsistencies or if they result in undetected database corruption.

Root Causes of WAL-Index and WAL-Log Desynchronization

1. Asynchronous Writeback of Memory-Mapped WAL-Index
The WAL-index is designed for high-performance concurrent access across processes. By using mmap, SQLite avoids explicit file I/O operations. However, the OS controls when dirty pages are flushed to disk, and the order of these flushes is undefined. For instance, a process might update the WAL-index header to mark new transactions as valid, but the corresponding aFrameCksum entries or WAL log frames might not yet be persisted. A crash at this stage leaves the WAL-index in a state that does not reflect the true state of the WAL log.

2. Insufficient Validation During Initial Recovery Checks
The walIndexTryHdr function checks only the WAL-index header’s magic number, version, and checksum. It does not verify whether the aFrameCksum array matches the checksums stored in the WAL log’s frame headers. This creates a gap: even if the header is valid, the rest of the WAL-index might reference invalid or nonexistent WAL log entries.

3. Lack of Coordinated Synchronization Between Components
SQLite does not enforce a strict write order between the WAL-index and WAL log. While the WAL log is explicitly synced at strategic points (e.g., transaction commits), the WAL-index relies on the OS’s writeback heuristics. This decoupling means that a crash can leave the WAL-index ahead of or behind the WAL log. For example, the WAL-index might reference WAL frames that were never fully written to the log.

4. Overreliance on Post-Crash Recovery Logic
The system assumes that the walIndexRecover function will reconstruct the WAL-index from the WAL log if inconsistencies are detected. However, this depends on whether the recovery logic can always detect such inconsistencies. If a corrupted WAL-index passes the walIndexTryHdr check but contains invalid data, subsequent operations might propagate the corruption.

Resolving Inconsistencies: Recovery Mechanisms and Validation Strategies

1. Forced Invalidation of the WAL-Index After a Crash
When the first database connection is established after a crash, SQLite’s OS abstraction layer (e.g., unixLockSharedMemory in os_unix.c) deliberately invalidates the WAL-index by zeroing its header. This is achieved by calling unixShmZeroLock on Unix-like systems or equivalent logic on Windows. By setting the magic number to zero, walIndexTryHdr will always fail, triggering a full recovery via walIndexRecover. This ensures that the WAL-index is rebuilt from the WAL log, regardless of its on-disk state.

2. Comprehensive Reconstruction During walIndexRecover
The walIndexRecover function scans the entire WAL log to rebuild the WAL-index from scratch. It performs the following steps:

• Header Validation: Checks the WAL log header’s magic number and version.
• Frame Checksum Verification: Computes checksums for each WAL frame and cross-references them with the values stored in the frame headers.
• Transaction Boundary Detection: Identifies the last valid commit record to determine the database’s consistent state.
• WAL-Index Reconstruction: Populates the aFrameCksum array and updates the WAL-index header with the correct size, checksum, and transaction markers.

This process guarantees that the reconstructed WAL-index matches the actual contents of the WAL log, even if the original WAL-index was partially written or corrupted.

3. Ensuring Atomicity Through Memory Barriers and File Locking
SQLite uses memory barriers to enforce write visibility ordering across processes. For example, after updating the WAL-index, a barrier ensures that changes are visible to other processes before the header is marked as valid. Combined with file locking (e.g., SHARED_LOCK), this prevents concurrent writers from interfering with recovery.

4. Testing and Edge-Case Handling
The SQLite test suite includes scenarios where the WAL-index and WAL log are intentionally corrupted to verify that walIndexRecover correctly restores consistency. Edge cases include:

• Partial WAL-index writes due to power loss.
• Mismatched aFrameCksum entries caused by out-of-order writebacks.
• WAL log files with valid headers but corrupted or truncated frames.

5. Mitigation Strategies for High-Reliability Systems
For applications requiring extra robustness, additional measures can be taken:

• Periodic Full Syncs: Use PRAGMA wal_checkpoint(FULL) to reduce the WAL log size and force synchronization of both the WAL log and WAL-index.
• Disable Memory-Mapped WAL-Index: Set PRAGMA mmap_size=0 to revert to traditional I/O for the WAL-index, though this sacrifices performance.
• Custom Recovery Hooks: Extend SQLite with application-specific checksumming or logging to detect anomalies earlier.

By combining SQLite’s built-in recovery logic with these strategies, the risk of undetected corruption due to WAL-index/WAL-log inconsistencies is effectively minimized.