what happens when a sqlite3 db write is interupted?

Question

Question: When does the actual writing to the sqlite3 db file take place and what happens if it is interrupted?

---

Info:

I have this program in python that I've been working on for a few weeks that uses sqlite3 to store large amounts of data from the simulation it is running. But there are two situations I'm worried about.

We've been having a lot of thunderstorms recently and this has knocked out power a few times, also I'm updating the file that writes to the db with some frequency, and to do so I have to kill the current running thread of the simulation. in both of these cases, especially the former, I worry about what happens when this thread gets interrupted. what if I happen to interrupt it or lose power while it is writing from the log file into the db? will the information just not get there? will it get there but corrupt? will it corrupt the whole sqlite3 db file?

Basically I want to know when does the data actually get writen to the file and not just the log file. and if this writing process does not finish for any reason, what happens to the log and the db file?

~n

Answer 1

SQLite uses atomic commits, so either everything or nothing is committed.

If you're concerned about the database being left in an invalid state, you need to make sure you wrap the entire "transitional" state in a BEGIN TRANSACTION ... COMMIT block.

The fine details of writing to the journal files, etc. (including through failures) are in the document "File Locking and Concurrency in SQLite Version 3".