Batch Atomic Writes with Synchronous=OFF Cause Database Corruption in SQLite

Interaction Between SQLITE_IOCAP_BATCH_ATOMIC and PRAGMA Synchronous=OFF

Issue Overview

This guide addresses a critical database integrity problem arising from the combination of SQLite’s batch atomic write capability (SQLITE_IOCAP_BATCH_ATOMIC) and the PRAGMA synchronous=OFF setting. When these features are used together under specific transaction patterns, SQLite may produce a malformed database file resulting in SQLITE_CORRUPT errors – even without system crashes or power failures. The corruption manifests as inconsistencies between database header information, page allocations, and index structures, particularly evident in complex operations involving virtual tables like FTS5 or large blob handling.

The core conflict stems from SQLite’s internal optimization logic that disables batch atomic write grouping when synchronous mode is turned off, while simultaneously failing to activate proper journaling mechanisms. This creates a dangerous gap in transaction atomicity guarantees. A detailed analysis of VFS method call logs reveals two key anomalies:

1. Missing Rollback Journal Activation:
   When PRAGMA synchronous=OFF is set, SQLite bypasses both write-ahead logging (WAL) and traditional rollback journals, relying instead on memory journals. However, memory journals don’t provide crash protection, and in this specific configuration, SQLite fails to enforce proper write ordering constraints required for atomic transactions.

2. Batch Atomic Write Grouping Inconsistencies:
   The SQLite pager module disables batch atomic writes when synchronous mode is off via conditional checks in pagerOpenWal(). However, residual logic in journal buffer sizing (jrnlBufferSize()) and page spill management creates conflicting requirements. This results in partial batch writes where critical database pages (including header information) get updated outside atomic transaction boundaries.

A concrete example from the test case shows a 4KB write occurring at offset 73728 (line 22885) after committing an atomic write group (lines 22874-22882). This write updates the database size without atomic protection. If interrupted, it leaves the database header’s page count inconsistent with actual file contents. The FTS5 virtual table exacerbates this issue due to its internal index maintenance patterns that trigger append-heavy write operations.

SQLite’s Journal Mode Selection and Batch Atomic Conflict

The corruption occurs due to three intersecting factors in SQLite’s transaction handling:

1. Incorrect Journal Mode Selection with Batch Atomic Disabled
When a VFS advertises SQLITE_IOCAP_BATCH_ATOMIC, SQLite prioritizes batch writes over journaling. However, when PRAGMA synchronous=OFF forces batch atomic to be disabled (via pagerUseWal() logic), the journal mode selection fails to revert to a safe fallback. The jrnlBufferSize() calculation sets nSpill to zero based on residual batch atomic assumptions, forcing a memory journal instead of a disk-based rollback journal. Memory journals lack durability guarantees, making transactions non-atomic when writes aren’t properly grouped.

2. Write Reordering in Non-Journaled Transactions
Without a durable journal, SQLite relies on the filesystem’s write ordering – but synchronous=OFF explicitly disables flush operations. The pager’s attempt to combine batch atomic optimizations with async writes creates dangling page updates. In the test case, the FTS5 insertion sequence generates multiple page updates that include:

• Index page modifications in the main database file
• Freelist management pages
• Append operations extending the file size

These updates get split across batch atomic and non-batch write operations due to miscalculations in transaction page accounting.

3. Header Page Update Timing Vulnerabilities
SQLite’s database header (page 1) contains critical metadata like:

• Schema version (offset 40)
• Freelist head page (offset 32)
• Page count (offset 28)
• WAL mode flag (offset 18)

When batch atomic is disabled but journaling isn’t properly activated, header updates may occur mid-transaction without atomic protection. The test case’s final xWrite at offset 73728 extends the database file size without updating the header’s page count atomically. Subsequent operations then read an inconsistent page count, leading to SQLITE_CORRUPT errors during verification.

Resolving Atomic Write and Journaling Conflicts

Step 1: Validate VFS Batch Atomic Implementation
First, confirm whether the VFS correctly implements batch atomic writes. Even though the corruption occurs with synchronous=OFF, an improper VFS implementation could exacerbate SQLite’s internal inconsistencies. Use this checklist:

// VFS must handle these xFileControl operations:
SQLITE_FCNTL_BEGIN_ATOMIC_WRITE  // Start atomic group
SQLITE_FCNTL_COMMIT_ATOMIC_WRITE // Commit group
SQLITE_FCNTL_ROLLBACK_ATOMIC_WRITE

// xDeviceCharacteristics must include:
SQLITE_IOCAP_BATCH_ATOMIC

// Verify write grouping in VFS logs:
xFileControl(BEGIN_ATOMIC_WRITE)
xWrite (multiple pages)
xFileControl(COMMIT_ATOMIC_WRITE)

Step 2: Enforce Journal Mode Consistency
When detecting PRAGMA synchronous=OFF with a batch atomic-capable VFS, force SQLite into a defined journal mode. Add these checks to application initialization:

-- After setting synchronous=OFF:
PRAGMA journal_mode = PERSIST;  -- Or other disk-based journal

If SQLite resists changing the journal mode (due to internal nSpill settings), modify the VFS to artificially limit batch atomic capabilities when synchronous is off:

// In xDeviceCharacteristics:
if( synchronous_mode == OFF ) {
  return capabilities & ~SQLITE_IOCAP_BATCH_ATOMIC;
}
else {
  return capabilities | SQLITE_IOCAP_BATCH_ATOMIC;
}

Step 3: Apply SQLite Trunk Fix for Page Count Updates
The core SQLite issue (now fixed in trunk) involved improper sequencing of file size updates relative to batch atomic groups. Apply this patch to critical sections in pager.c:

// Before (vulnerable code):
if( pPager->doNotSpill==0 ){
  rc = sqlite3OsWrite(pPager->fd, pPg->pData, pPager->pageSize, pPg->offn);
}

// After (fixed code):
if( pPager->doNotSpill==0 && pPager->journalMode!=PAGER_JOURNALMODE_MEMORY ){
  rc = sqlite3OsWrite(pPager->fd, pPg->pData, pPager->pageSize, pPg->offn);
}

This modification prevents out-of-group writes when a memory journal is active. Rebuild SQLite with this change and verify through test cases that all file size updates occur within atomic write groups.

Step 4: Implement Transaction Boundary Monitoring
Add logging to track transaction commit phases and write grouping:

// Wrap critical pager operations with logging:
sqlite3_log(SQLITE_NOTICE, "BEGIN_ATOMIC_WRITE");
sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_BEGIN_ATOMIC_WRITE, 0);
...
sqlite3_log(SQLITE_NOTICE, "COMMIT_ATOMIC_WRITE");
sqlite3OsFileControl(pPager->fd, SQLITE_FCNTL_COMMIT_ATOMIC_WRITE, 0);

Analyze logs to ensure:

• No writes occur between COMMIT_ATOMIC_WRITE and the next transaction start
• File size changes (xFileSize, xTruncate) are grouped with data writes
• All FTS5 auxiliary table updates are contained within atomic groups

Step 5: Modify FTS5 Transaction Handling
Virtual tables like FTS5 often bypass standard pager controls. Add explicit transaction management around FTS5 operations:

sqlite3_exec(db, "BEGIN;", 0, 0, 0);
for(int i=0; i<num_rows; i++){
  // FTS5 insertions
}
sqlite3_exec(db, "COMMIT;", 0, 0, 0);

// Force WAL journal mode for FTS5 tables:
sqlite3_exec(db, "PRAGMA journal_mode=WAL;", 0, 0, 0);

This containment ensures FTS5’s internal page allocations respect atomic write boundaries.

Step 6: Database Validation Routines
Implement proactive corruption checks using PRAGMA integrity_check and PRAGMA quick_check after critical operations:

void check_integrity(sqlite3 *db) {
  sqlite3_exec(db, "PRAGMA quick_check;", callback, 0, &zErrMsg);
  if( strstr(zErrMsg, "malformed") ){
    // Handle corruption recovery
  }
}

Combine this with automatic backup routines that snapshot the database before large batch operations.

Final Solution
The definitive resolution involves updating to a SQLite version containing the trunk fix that properly sequences file size updates within atomic write groups. Until that release is available, apply these mitigations:

1. Avoid PRAGMA synchronous=OFF with batch atomic VFS implementations
2. Force journal_mode=WAL when using virtual tables like FTS5
3. Patch SQLite’s pager to honor journal mode transitions when batch atomic is disabled
4. Validate all VFS xFileControl implementations for proper atomic write grouping

By aligning SQLite’s batch atomic logic with journal mode selection and ensuring all file modifications occur within defined transaction boundaries, developers can eliminate this class of database corruption errors.