Data Loss and Corruption After Force Restart During SQLite Inserts

Issue Overview: Understanding Transaction Durability and Database Corruption in SQLite

The core issue revolves around data loss and database corruption observed when an iOS device is force-restarted during active SQLite insert operations. The user reports that after rebooting the device, committed transactions appear to be missing, and the database occasionally becomes corrupted with errors like "database disk image is malformed." Key elements of the problem include:

1. Transaction Lifecycle Mismanagement: The provided code snippet explicitly starts an EXCLUSIVE TRANSACTION but does not commit it. Transactions remain open indefinitely, violating SQLite’s requirement that transactions must be explicitly committed (COMMIT/END) or rolled back (ROLLBACK). This leads to implicit rollbacks during recovery after a crash.

2. Journal Mode and Synchronization Settings: The database is configured with journal_mode=DELETE, synchronous=FULL, and locking_mode=NORMAL. While these settings are valid, their interaction with force restarts (which bypass iOS’s normal shutdown procedures) creates edge cases where journal files may not be fully synchronized to disk before power loss.

3. Database Corruption Triggers: Corrupted databases indicate that the on-disk state violates SQLite’s file format rules. This could stem from incomplete writes to the database file, mishandled journal files, or lingering open transactions that leave the database in an inconsistent state.

Possible Causes: Transaction Handling, Journal File Dynamics, and File System Sync Guarantees

1. Uncommitted Transactions and Implicit Rollbacks

SQLite transactions are atomic: they either fully commit or fully roll back. A transaction is only durable if it reaches the COMMIT phase and the journal file is synchronized to disk. In the provided code, BEGIN EXCLUSIVE TRANSACTION is executed, but no corresponding COMMIT is issued. When the connection is closed without committing, SQLite automatically rolls back the transaction. If the device is force-restarted mid-transaction, the recovery process treats the transaction as incomplete and rolls it back, leading to data loss.

2. Journal Mode DELETE and Synchronous=FULL Misconceptions

With journal_mode=DELETE, SQLite uses a rollback journal file (-journal) to restore the database to its pre-transaction state if a crash occurs. The synchronous=FULL setting ensures that all critical file operations (e.g., flushing the journal to disk) are synchronized before acknowledging a commit. However, force-restarting an iOS device bypasses the operating system’s shutdown sequence, which may interrupt in-progress I/O operations. Even with synchronous=FULL, there is no guarantee that data buffered in the device’s hardware cache is written to persistent storage. This can leave journal files in an inconsistent state, causing recovery failures.

3. Database Corruption from Incomplete Writes or Orphaned Journals

Database corruption occurs when the database file or its journal contains invalid data structures. Force-restarting during a write operation can truncate the database file or leave partially written pages. Additionally, if a journal file is not properly deleted after a transaction (due to the abrupt restart), SQLite may misinterpret it during recovery, leading to a malformed database. The error message "database disk image is malformed" often points to checksum mismatches or page header inconsistencies caused by incomplete writes.

Troubleshooting Steps, Solutions & Fixes: Ensuring Transaction Durability and Database Integrity

Step 1: Audit Transaction Boundaries and Commit Semantics

Problem: The code starts a transaction but never commits it.
Solution:
Explicitly commit transactions after each logical unit of work. Modify the code to include COMMIT after the insert operation:

if (sqlite3_exec(database, "BEGIN EXCLUSIVE TRANSACTION", 0, 0, 0) == SQLITE_OK) {
    // Prepare and execute INSERT statement
    if (sqlite3_step(statement) == SQLITE_DONE) {
        sqlite3_exec(database, "COMMIT", 0, 0, 0); // Explicit commit
    } else {
        sqlite3_exec(database, "ROLLBACK", 0, 0, 0); // Handle errors
    }
}

Rationale: Transactions must be explicitly committed to ensure durability. Without a COMMIT, SQLite assumes the transaction is still active and rolls it back during recovery.

Step 2: Switch to Write-Ahead Logging (WAL) Journal Mode

Problem: journal_mode=DELETE is prone to "split-brain" scenarios where the database and journal are out of sync after a crash.
Solution:
Enable WAL mode, which offers better concurrency and crash recovery:

PRAGMA journal_mode=WAL;

Rationale: WAL mode uses a write-ahead log file (-wal) instead of a rollback journal. Changes are appended to the WAL file, which is more resilient to crashes. Recovery involves replaying the WAL file, reducing the risk of corruption.

Step 3: Validate Synchronization and Hardware Limits

Problem: synchronous=FULL does not guarantee writes survive hardware restarts.
Solution:

• Use PRAGMA synchronous=EXTRA; to enforce additional flushing (if supported by the OS).
• Regularly checkpoint the WAL file to reduce recovery time:

PRAGMA wal_checkpoint(TRUNCATE);

Rationale: EXTRA mode adds an extra fsync after critical operations, reducing the window for data loss. Checkpointing ensures the WAL file is periodically merged into the main database.

Step 4: Implement Robust Error Handling and Database Integrity Checks

Problem: Corrupted databases are not detected until runtime.
Solution:

• Enable automatic integrity checks using PRAGMA integrity_check; after opening the database.
• Use sqlite3_db_config(database, SQLITE_DBCONFIG_RESET_DATABASE, ...) to reset corrupted databases as a last resort.

Example Code:

if (sqlite3_open(dbpath, &database) == SQLITE_OK) {
    sqlite3_stmt *integrityStmt;
    if (sqlite3_prepare_v2(database, "PRAGMA integrity_check;", -1, &integrityStmt, NULL) == SQLITE_OK) {
        while (sqlite3_step(integrityStmt) == SQLITE_ROW) {
            const char *result = (const char *)sqlite3_column_text(integrityStmt, 0);
            if (strcmp(result, "ok") != 0) {
                // Handle corruption: restore from backup or notify the user
            }
        }
    }
}

Step 5: Simulate Crash Recovery and Validate Journal File Cleanup

Problem: Journal files may linger after crashes, causing recovery issues.
Solution:

• After reopening the database, check for the presence of -journal or -wal files.
• Use PRAGMA journal_size_limit to enforce size limits on WAL files.

Rationale: Orphaned journal files indicate incomplete transactions. SQLite automatically handles these during recovery, but manual checks can preempt corruption.

Step 6: Utilize iOS-Specific File System APIs for Data Protection

Problem: iOS may delay or reorder writes to optimize performance.
Solution:

• Enable iOS Data Protection to ensure files are encrypted and flushed to disk:

NSDictionary *fileAttributes = @{
    NSFileProtectionKey: NSFileProtectionCompleteUntilFirstUserAuthentication
};
[[NSFileManager defaultManager] setAttributes:fileAttributes ofItemAtPath:dbPath error:nil];

Rationale: Data Protection classes like NSFileProtectionCompleteUntilFirstUserAuthentication ensure file metadata and contents are preserved across reboots.

Final Recommendation: Adopt Atomic Commit Patterns and Regular Backups

• Atomic Transactions: Group related operations into single transactions to minimize the window for data loss.
• Incremental Backups: Use SQLite’s Online Backup API to create periodic snapshots of the database.
• Monitor SQLite Status: Track metrics like sqlite3_total_changes() to detect anomalies in transaction throughput.

By addressing transaction lifecycle management, journal mode configuration, and iOS-specific file handling, developers can mitigate data loss and corruption risks during force restarts.