SQLite Data Not Persisting Until Database Closure with Journal Mode OFF

Understanding SQLite Transaction Durability and File System Sync Behavior

Issue Overview: Data Loss Without Immediate Flushing or Journaling

When utilizing SQLite in environments where journaling is explicitly disabled (via PRAGMA journal_mode=OFF), developers may encounter scenarios where database modifications (INSERT, UPDATE, DELETE) are not persisted to the physical storage medium until the database connection is explicitly closed. This behavior becomes critical in embedded systems such as RTEMS, where abrupt system resets or power cycles can occur. The core problem arises from the interaction between SQLite’s transactional guarantees, the configured journal mode, and the underlying file system’s caching or synchronization mechanisms.

In the provided scenario, the user observes that after executing transactions (including explicit BEGIN and COMMIT statements), the database file (test.db) remains at 0 bytes until sqlite3_close() is invoked. This indicates that the file system has not written buffered data to the storage device. The absence of a rollback journal (due to journal_mode=OFF) eliminates SQLite’s ability to enforce atomic commits via the journal file, bypassing critical synchronization steps that would otherwise ensure data durability. The RTEMS VFS (Unix-dotfile) implementation may further exacerbate this by deferring writes to the storage layer unless explicitly flushed.

Root Causes: Journal Mode Configuration and VFS Sync Implementation

1. Journal Mode OFF and Transactional Integrity
   When journal_mode=OFF is set, SQLite disables the rollback journal mechanism. This has two critical consequences:

   • Atomic Commit Guarantee Removal: The rollback journal is responsible for ensuring that transactions are atomic. Without it, interrupted transactions (e.g., due to a crash or reset) leave the database in an undefined state.
   • Synchronization Bypass: In normal journal modes (e.g., DELETE, TRUNCATE), SQLite invokes fsync() or equivalent system calls during the commit phase to ensure journal and database files are durably written. With journaling disabled, these synchronization steps are omitted, relying entirely on the file system’s buffering behavior.

2. VFS Layer and xSync Method Behavior
   SQLite delegates file operations (open, read, write, sync) to the registered VFS. The xSync method of the VFS is responsible for ensuring that all data buffered in memory for a file is written to persistent storage. In environments like RTEMS, the VFS implementation (e.g., Unix-dotfile) may not fully implement xSync, or the underlying file system may defer synchronization even when xSync is called. This results in data remaining in volatile memory buffers until the file is closed or the system explicitly flushes caches.

3. File System Caching and Delayed Writes
   Many file systems employ write-back caching, where modifications are held in memory before being asynchronously written to disk. This improves performance but risks data loss if the system crashes before the cache is flushed. When journaling is disabled, SQLite does not trigger fsync() during commits, leaving the timing of cache flushes entirely to the operating system or RTEMS’s file system policies.

Resolving Data Persistence: Configuring Sync Mechanisms and Journaling

Step 1: Re-evaluate Journal Mode Configuration
Disabling journaling (journal_mode=OFF) is rarely advisable except in highly controlled environments where data loss is acceptable. To ensure transactional durability:

• Enable Journaling: Revert to the default journal mode (PRAGMA journal_mode=DELETE) or use WAL mode (PRAGMA journal_mode=WAL) if concurrent reads and writes are required. This reinstates SQLite’s use of the rollback journal or write-ahead log, which include synchronization steps during commits.
• Synchronous Settings: Combine journaling with PRAGMA synchronous=FULL to enforce that SQLite waits for the storage device to confirm data is written before completing transactions. This overrides the default synchronous=NORMAL behavior, which may not flush metadata in some configurations.

Step 2: Enforce Synchronization via VFS and File System
If journaling cannot be enabled due to system constraints, explicitly invoke synchronization methods:

• Manual Flushing with sqlite3_db_cache_flush: After critical transactions, call sqlite3_db_cache_flush(db) to force SQLite to write all dirty pages to the database file. This does not replace journaling but reduces the window for data loss.
• VFS xSync Implementation: Verify that the RTEMS Unix-dotfile VFS implements the xSync method correctly. If using a custom VFS, ensure that xSync invokes the file system’s synchronization primitive (e.g., fsync()).
• File System Mount Options: Configure the RTEMS file system to mount with synchronous write options (e.g., O_SYNC in open flags), forcing immediate writes to storage at the expense of performance.

Step 3: Transaction Management and Connection Lifecycle

• Explicit Transactions with Synchronization: When using manual transactions (BEGIN/COMMIT), follow each COMMIT with a PRAGMA schema.integrity_check or sqlite3_exec(db, "COMMIT; PRAGMA synchronous=FULL;", ...) to force synchronization.
• Connection Closure as Flush Trigger: If data persistence is only required at specific intervals, structure the application to close and reopen the database connection after critical operations. This forces SQLite to flush pending changes.

Code Modifications for Immediate Data Persistence
Given the user’s code snippet, the following adjustments ensure data durability even with journaling disabled:

1. Enable Synchronous Full Mode

rc = sqlite3_exec(db, "PRAGMA synchronous=FULL;", NULL, 0, &zErrMsg);
if (rc != SQLITE_OK) {
    // Handle error
}

2. Flush Cache After Commit

rc = sqlite3_exec(db, "COMMIT;", NULL, 0, &zErrMsg);
if (rc == SQLITE_OK) {
    sqlite3_db_cache_flush(db); // Force dirty pages to disk
}

3. Verify VFS xSync Implementation
   Inspect the RTEMS Unix-dotfile VFS source code to confirm that the xSync method invokes the file system’s sync mechanism. If missing or incomplete, modify the VFS:

static int rtems_vfs_sync(sqlite3_file *file, int flags) {
    // Invoke fsync() on the underlying file descriptor
    int fd = ((unixFile*)file)->h;
    return fsync(fd) == 0 ? SQLITE_OK : SQLITE_IOERR_FSYNC;
}

Conclusion: Balancing Durability and Performance
The observed behavior stems from the interplay between SQLite’s transactional safeguards (disabled via journal_mode=OFF) and the RTEMS file system’s deferred write strategy. Restoring journaling or enforcing rigorous synchronization at the VFS layer ensures data persistence across system resets. Developers must weigh the trade-offs between data integrity and I/O performance, particularly in embedded systems where storage latency and wear are critical considerations.