PRAGMA wal_checkpoint and sync
(1) By anonymous on 2020-06-07 03:54:05 [source]
Does calling PRAGMA wal_checkpoint guarantee a sync for the WAL file even if the connection is set to synchronous = 0 ?
If yes then great! But if not, is there a way to guarantee a sync from a connection with synchronous = 0? Other than setting it to 2, writing something and setting it back to 0?
Also, what about the WAL2 branch? Does a checkpoint guarantee a sync there?
(2) By anonymous on 2020-06-08 21:26:36 in reply to 1 [link] [source]
Bumping this, any idea where/how I could find an answer to this? Can't find it explicitly in the docs.
(3) By Keith Medcalf (kmedcalf) on 2020-06-09 09:12:50 in reply to 1 [link] [source]
It is explicitly documented here https://sqlite.org/pragma.html#pragma_synchronous
synchronous - OFF (0) means that no sync/fsync are performed. That seems pretty unambiguous to me. Why would you think that when you have specified to NEVER do a sync operation that it might be performed upon the occurrence of some condition such as pigs flying or the moon blasting from orbit ala Space 1999?
In other words, when you have told a connection to never do a sync there is no way that it will ever do a sync, and if it does, then it is broken behaviour.
Sounds like you want synchronous = NORMAL which is defined as doing a sync on checkpoint and not at each transaction COMMIT.
https://sqlite.org/wal.html in particular the 5th paragraph which reads:
Note that with PRAGMA synchronous set to NORMAL, the checkpoint is the only operation to issue an I/O barrier or sync operation (fsync() on unix or FlushFileBuffers() on windows). If an application therefore runs checkpoint in a separate thread or process, the main thread or process that is doing database queries and updates will never block on a sync operation. This helps to prevent "latch-up" in applications running on a busy disk drive. The downside to this configuration is that transactions are no longer durable and might rollback following a power failure or hard reset.
There is even a recommended compile option to automatically define this behaviour when using WAL so that you do not need to issue the pragma synchronous=1 command:
https://sqlite.org/compile.html section 2 point 4:
SQLITE_DEFAULT_WAL_SYNCHRONOUS=1. For maximum database safety following a power lose, the setting of PRAGMA synchronous=FULL is recommended. However, in WAL mode, complete database integrity is guaranteed with PRAGMA synchronous=NORMAL. With PRAGMA synchronous=NORMAL in WAL mode, recent changes to the database might be rolled back by a power loss, but the database will not be corrupted. Furthermore, transaction commit is much faster in WAL mode using synchronous=NORMAL than with the default synchronous=FULL. For these reasons, it is recommended that the synchronous setting be changed from FULL to NORMAL when switching to WAL mode. This compile-time option will accomplish that.