SQLite Forum

Switching pragma synchronous to upgrade durability
Login
To note conclusions here (per offline conversations):

1. In non-WAL mode, transactions are automatically durable with pragma synchronous set to NORMAL; nothing extra needs to be done for that case.
2. For WAL mode (again, with pragma synchronous set to NORMAL), sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_FULL or above is sufficient to ensure durability of previously-committed transactions cheaper options are also available. (SQLITE_CHECKPOINT_PASSIVE would not be sufficient as it may not get that far depending on other concurrent readers/writers.)
3. A more efficient WAL-mode option is just to get the journal file to be flushed (i.e., on Windows, getting FlushFileBuffers to be called for the journal file); this approach will cause previously-committed transactions to become durable with less overhead. Further details follow.

To flush the journal file:
Use sqlite3_file_control to get SQLITE_FCNTL_JOURNAL_POINTER and then call pMethods->xSync on the sqlite3_file* with SQLITE_SYNC_NORMAL. However, in order for the sqlite3_file* to be fully populated, the journal file must be fully opened. Preparing SQL statements such as "SELECT 1 FROM sqlite_schema" would work (no need to execute, just prepare), or without needing to work with a statement object, just calling sqlite3_table_column_metadata(connection, "main", "sqlite_schema", NULL, NULL, NULL, NULL, NULL, NULL) ensures the journal file is opened enough to flush its buffers (in WAL mode; in non-WAL mode that call is not sufficient to fully populate the journal sqlite3_file*, but for non-WAL mode durability is already achieved by synchronous NORMAL mode as noted above).