Should I put my faith in SQLite transactions to avoid file corruption?

Question

Short version

If my process is terminated in the middle of a transaction, or while SQLite is committing a transaction, what are the chances that the database file will be corrupted?

Long version

My application uses an SQLite database for storage (directly, not via Core Data). I'm working on a new version of the application which will require an update to the database schema. On launch, the app will check the database and, if it needs updating, execute a series of SQL statements to do so.

Depending on the amount of data in the database, the update may be long running (on the order of seconds), so I need to consider the possibility that the process may be terminated before the update is completed. (For context, this on an iPhone, where the processor is slow and the app may be terminated by an incoming phone call.) I will, of course, wrap the upgrade SQL statements in a transaction. Will that be enough to guarantee that the database will not be corrupted?

I'm assuming that transactions work as advertised, and that if the process is terminated in the middle of the transaction, the file will be OK. But I'm also assuming there is a window of time during the COMMIT where something can go wrong.

To play it safe, I could create a backup copy of the database file before starting the update, but if the transactions are safe then that would be overkill. It would also make the update process take longer, which increases the chance it would be interrupted, and then I'd have to consider that the file copy operation might be interrupted... I'd like to keep the code as simple as possible (but no simpler).

In the course of researching this question I've started reading "Atomic Commit In SQLite", which is more detail than I probably need to know, but is giving me faith that I don't need to second-guess SQLite's ability to protect the database file. But I'd still like to hear from Stack Overflow: is a transaction good enough, or should I be more cautious?

Answer 1

Are you using CoreData with a SQLite backend? If so, I actually find that the best way to handle this problem is to create two separate NSManagedObjectContexts (a read-only and an editing). When the process completes, just save the "editing" context and then the two contexts will be in sync. If something happens during your operation, the editing context won't get saved, so you'll be fine.

Answer 2

I have read the Atomic Commit in SQLite document. It may not be overkill if you really want to understand what's going on, but in a nutshell, a transaction goes like this:

1. Lock the database file
2. Create the rollback journal
   1. Determine what portions of the database file are going to be changing
   2. Write copies of those pages to the journal file
   3. Write the journal file header
3. Write your intended changes to the database file
4. Delete the rollback journal (THIS IS THE COMMIT)

When the user is done talking to mom and re-starts your app, when it tries to open the database file, if there is a rollback journal present, it will write the original data back to the datafile using a similarly safe process. Even if you lose your transaction, and lose a rollback, it will eventually be taken care of once mom's nervous breakdown is properly thwarted and he can run the app for more than a couple seconds at a time.

If it were me, I would trust the transactions. With so many users of SQLite, even in embedded apps, I think transaction commit failures would be a very hot topic all over the net if they weren't working properly.