Will the database file be corrupted if using MEMORY journal mode ?
(1) By anonymous on 2021-12-01 05:57:34 [link] [source]
I'm using SQLite in an embedded system. The I/O speed is very slow. So, I want to use MEMORY journal mode to speed up the write performance. However, the system will frequently power off. I'd like to know it the power was turned off during a write transaction, what would happen to the database file ?
Will it be corrupted ? Or just lost the uncommitted data in last transaction ? Or the transaction will be partially committed ?
(2) By Dan Kennedy (dan) on 2021-12-01 10:55:33 in reply to 1 [source]
Pretty decent chance of corruption, unfortunately.
(3) By Ryan Smith (cuz) on 2021-12-01 12:57:58 in reply to 1 [link] [source]
The point of the journals or write-ahead logs is to avoid corruption during loss of writing the data to the storage medium, whatever that loss may be caused by, software that segfaults, or hangs, or gets powered down, or gets lied to by the operating system on whether that data has truly reached the drive surface. Possibilities are myriad.
Not using them removes this safeguard and so corruption becomes a very real possibility.
A very simplified demonstration follows (and which mimics-ish only one of the SQLite journal modes, but it is enough to make the point):
Imagine your DB has 8-bytes per page which is stored in bytes starting with the length, then the type and then the data. (In this simple DB data can roll across page boundaries). If you write to it the int 55 (0x37, length = 1, type Int = 1) and 'Hello World' (Length = 12 (0x0c), String = Type 3) it might look like this: [Using actual text in stead of hex bytes for easier reading where appropriate] Pg No | data 0 | 01 01 37 0c 03 H e l 1 | l o W o r l d Now let's say I send a query to change the 55 to 1027 (0x0403), this now requires 2 bytes for the integer so we have to move our page data up to become: Pg No | data 0 | 02 01 04 03 0c 03 H e 1 | l l o W o r l d So the first step we will do to try achieve the above is to copy the two original pages out to a journal file, then write the new two pages, then remove the journal file. However, if during the write something goes wrong and only the first 3 new bytes make it to the storage, like this: Pg No | data 0 | 02 01 04 0c 03 H e l 1 | l o W o r l d This data has become corrupt, because it looks like a2-Byte integer of 0x040c followed by something that is 3 bytes long with a type "H"... which is where the DB engine gives up and calls it corrupt. The remedy is to note that the journal file we copied away with the two pages is not deleted (we never got that far) and so the next time the DB engine starts it may simply copy those two pages back leaving us with a "rolled-back" state as before: Pg No | data 0 | 01 01 37 0c 03 H e l 1 | l o W o r l d Which leaves us without the change, but with a perfectly valid non-corrupt DB.
There are other problems too, the write can fail during making of the journal file, but that is easily caught with a checksum. Other considerations when choosing a journal mode is that the OS can lie about the journal file having been written to disk, that is why options such as forcing Flush to disk after writes can be enabled. Sometimes that is not needed since some hardware (server hardware mostly) do either not lie about data reaching the drive, or have battery backup for the drive itself to ensure the cache gets flushed at least, removing the need for strict waiting.
I hope that answers the question of why the journal modes are needed and why you can't live without them with any safety expectation. You can have any range of set-ups between maximum speed and maximum safety, but the maximums themselves are mutually exclusive.
(6) By anonymous on 2021-12-01 15:47:50 in reply to 3 [link] [source]
Thanks for your detail explain. It's clear.
(4) By Simon Slavin (slavin) on 2021-12-01 13:29:14 in reply to 1 [link] [source]
Does your device generate write single commands at a regular interval, e.g. a data-logger, or a batch of write commands in a rush, and then a pause until the next batch ?
What medium is your database stored on ? Flash memory ?
(5) By anonymous on 2021-12-01 13:47:02 in reply to 4 [link] [source]
Yes, my device will do the periodical data logging. The data includes a timestamp and a group of numeric values. Usually the logging interval is 10sec. The device has an internal nand flash and a usb port. So the database can be stored on not only nand flash but also usb disk. The performance issue happens on usb disk.
(7) By Simon Slavin (slavin) on 2021-12-02 12:38:13 in reply to 5 [link] [source]
Thanks for that information, which should make it easier for readers to figure out what changes might help you.
In your situation I would try three different journalling modes:
PRAGMA journal_mode = TRUNCATE PRAGMA journal_mode = PERSIST PRAGMA journal_mode = WAL
One of these modes may be faster than the others, or they may be the same, depending on what hardware, OS, and file system you're using. Remember, the journal mode is stored in the database so you need to issue those commands just once after database creation, not every time you open the database.
(8) By Keith Medcalf (kmedcalf) on 2021-12-02 18:27:49 in reply to 7 [link] [source]
This is incorrect.
The journal mode is not persistently saved with the database. The database saves whether the journal_mode is or is not WAL. If the journal_mode is not WAL, then the journal_mode is reset to the default (the default default is DELETE) when the database is opened.
That is, journal_mode=WAL is persistent.
None of the other journal_mode settings are persisted.
(9) By Simon Slavin (slavin) on 2021-12-02 22:23:38 in reply to 8 [link] [source]
Good grief. I had been working on that assumption for years. Yet I now see in the file format there's only a way to saw whether WAL is being used or not. Thanks for the correction.