Will database file of SQLite3 be damaged when suddenly power-off or OS crash?

Question

I open the database file and obtain a database connection using open() method of sqlite3 and the connection will not be closed until program exits. If there occurs an unexpected error such as computer's suddenly power-off or OS crash, will the mode of the database file be damaged, or its handle lost? More specifically, can it remain writable if I reboot my computer? BTW, I don't care about the data loss when errors occurs.

Thank you very much!

Bultman

Answer 1

SQLite is specifically designed to protect against this. From the official SQLite is Transactional page:

All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by

• a program crash,
• an operating system crash, or
• a power failure.

The claim of the previous paragraph is extensively checked in the SQLite regression test suite using a special test harness that simulates the effects on a database file of operating system crashes and power failures.

You might also be interested in the SQLite article Atomic Commit in SQLite if you need to know the specific details on how they protect against crashes such as the above.

---

Regarding writing after a crash: (from File Locking and Concurrency)

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 worst that can happen will be that you need to delete the hot journal that is left over after a crash.

Knurl

Thanks! I also want to know if the database file(*.db) will remain writable if power failure happens while there's some changes being written into the database. – Bultman

@Bultman The locking is done in the OS level, so after a sudden failure and following reboot, the fschk code should clear that up and the file will be accessible again. Locking is not done by changing file attributes. Also, from the article references in the answer, it is clear that "hot journals" are handled automagically when accessing the DB file. – Glum

You should never delete a hot journal file. See the official documentation: How To Corrupt An SQLite Database File, section 1.3. – Naughton

Answer 2

As Sqlite is ACID-compliant, a power-off shouldn't be an issue.

http://en.wikipedia.org/wiki/ACID

Backwardation

I'm afraid power-off would affect the file's properties and it won't be opened at all or I can't write any data into it. – Bultman

@quantity, why are you afraid of that? So long as you don't delete the journal file, sqlite3 should be able to repair the database in case of a sudden loss of power. – Saintmihiel

Answer 3

anything could potentially happen on sudden power off. However I'd suggest UPS to mitigate any risk.

Kedah

And anything can happen due to cosmic rays, but in practice, an sqlite3 database will generally remain consistent in case of a sudden loss of power on predictable filesystems. – Saintmihiel