During power loss, SQLite rollbacks my DB to a point before BEGIN

Issue

I have an SQLite database in my app. Everytime the app modifies/adds a row in the DB, app starts an SQLite transaction explicitly by issuing the command BEING TRANSACTION. After updating/adding data in my database, I issue the command COMMIT, or ROLLBACK if the update fails. Usual stuff (https://www.sqlite.org/lang_transaction.html). Everything works perfect until I do a power loss.

The problem is everytime I do a power loss after the app executes the COMMIT, starts the device and eventually start the app, SQLITE retains the data as if BEGIN and COMMIT never happened at all! Why?

I also checked the database file in my app directory, it has the correct data. I also noticed an extra file <mydatabasefilename>-journal in my app directory. Upon checking, this is the temp file used by SQLite to support rollback an incomplete transaction especially during power loss.

Again, my COMMIT happened before the power loss. My database file (as checked in my app directory) contains the correct data. But why <mydatabasefilename>-journal contains the older data? I don't get it at all. Am I missing something here?

Solution

What settings for PRAGMA synchronous and PRAGMA journal_mode does your database use? These can be used for controlling the exact behaviour of sqlite with respect to these things.

The behaviour of the <mydatabasefilename>-journal file containing the old contents sounds like the rollback journal described here: https://www.sqlite.org/lockingv3.html#rollback

When a process wants to change a database file (and it is not in WAL mode), it first records the original unchanged database content in a rollback journal. The rollback journal is an ordinary disk file that is always located in the same directory or folder as the database file and has the same name as the database file with the addition of a -journal suffix. [...]

[...]

A rollback journal is said to be hot if it needs to be rolled back in order to restore the integrity of its database. A hot journal is created when a process is in the middle of a database update and a program or operating system crash or power failure prevents the update from completing. Hot journals are an exception condition. Hot journals exist to recover from crashes and power failures. If everything is working correctly (that is, if there are no crashes or power failures) you will never get a hot journal.

The different journal modes that can be set are described here: https://www.sqlite.org/pragma.html#pragma_journal_mode

The DELETE journaling mode is the normal behavior. In the DELETE mode, the rollback journal is deleted at the conclusion of each transaction. Indeed, the delete operation is the action that causes the transaction to commit. (See the document titled Atomic Commit In SQLite for additional detail.)

You can try setting other settings for PRAGMA synchronous, see these settings: https://www.sqlite.org/pragma.html#pragma_synchronous

• EXTRA (3)

  EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss.

• FULL (2)

  When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode.

So in your case, setting PRAGMA synchronous = EXTRA; might do what you want, but this might come at a performance cost.

You could also try experimenting with PRAGMA journal_mode = WAL;, described at https://www.sqlite.org/wal.html, in case that works better for you on power loss, with less heavy settings of PRAGMA synchronous.

Answered By - mstorsjo