Switching pragma synchronous to upgrade durability
(1) By David Matson (dmatson) on 2021-07-07 22:14:01 [link] [source]
If a transaction on a connection previously committed with pragma synchronous set to NORMAL (in WAL mode), but then I need to ensure the transaction is durable, can I switch pragma synchronous to FULL and commit another transaction to force durability for the previous transaction? Or is there some other way to ensure the database is in a fully-durable state?
If yes, would I need to use the same connection as the previous transaction, or would a new connection also produce the same effect?
(2) By Simon Slavin (slavin) on 2021-07-08 13:29:31 in reply to 1 [link] [source]
I think you want
PRAGMA wal_checkpoint(FULL). I'm not entirely sure I understand your question because we don't use the term 'durable' here. Check it out and post again if I got it wrong.
(3) By Richard Hipp (drh) on 2021-07-08 13:51:48 in reply to 1 [link] [source]
Yes. Doing a single transaction with PRAGMA synchronous=FULL causes an fsync() to occur on the WAL file, which makes all prior transactions durable across power failures or OS crashes.
(4) By anonymous on 2021-07-08 14:39:24 in reply to 3 [link] [source]
And to make sure the original question is answered in full, this new single transaction with PRAGMA synchronous=FULL doesn't need to originate from the same connection, or the same process even. As Richard said, it will fsync() the WAL file, thus any prior transaction originating from any connection in any process will now be persisted on disk
(5) By David Matson (dmatson) on 2021-07-08 20:53:55 in reply to 4 [link] [source]
Excellent. Thanks for the help.
And just to clarify, any transaction will work, even if it is "empty"? For example, is it good enough just to do:
pragma synchronous=FULL; BEGIN TRANSACTION; COMMIT;
Or would I need to do something that forces the transaction to acquire a write lock or something like that?
(6) By David Matson (dmatson) on 2021-07-09 17:57:11 in reply to 5 [source]
For example, would it need to be
BEGIN IMMEDIATE TRANSACTION;
We also have some places that do sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_TRUNCATE - does that already achieve this effect, regardless of the pragma synchronous mode? The docs talk about syncing the database file - I'd guess that any checkpoint mode of SQLITE_CHECKPOINT_FULL or higher also means doing a sync on the WAL file, though it would be great to confirm.
(7) By David Matson (dmatson) on 2021-08-04 22:12:36 in reply to 6 [link] [source]
To note conclusions here (per offline conversations):
- In non-WAL mode, transactions are automatically durable with pragma synchronous set to NORMAL; nothing extra needs to be done for that case.
- 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.)
- 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).