SQLite Forum

Process vs OS level durability (sync=NORMAL, WAL)
Login

Process vs OS level durability (sync=NORMAL, WAL)

(1) By example-user on 2020-09-06 20:55:12 [source]

https://www.sqlite.org/pragma.html#pragma_synchronous

WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode.

This question is about sync=NORMAL in WAL mode:

Why is it that durability is maintained over process crashes, but not OS crashes?

Where is the OS level state kept?

Does this mean that transactions are only durable after a checkpoint process is completed moving data from the wal file into the db file?

In the above quote, what does "might" mean? Is that a low probability?

Thanks

(2) By Richard Hipp (drh) on 2020-09-06 21:08:03 in reply to 1 [link] [source]

Why is it that durability is maintained over process crashes, but not OS crashes?

The commit record has be written to the filesystem, but fsync() was not called, so that record might not have made it to stable storage. If the OS crashes before the record gets to stable storage, the transaction will be rolled back the first time the database is opened after restart.