I'd like to run some ALTER TABLE statements on a sqlite3 database. What happens if the user kills the process or the power is cut while the ALTER TABLE is running? Will the database be left in a corrupt intermediate state?
+1
A:
From the transactional page in the documentation:
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.
And from another page in the documentation:
Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.
Combine those, and the answer is no. Your database will not be corrupted during a power failure.
Mads Mobæk
2010-03-21 00:44:37
Exactly. All labour intensive queries (and perhaps others too) are done on a temporary copy of the database, called a journal. When the changes are complete, the temp database replaces the current one, seamlessly. If the operation is interrupted, your original database is still intact. The journal can sometimes, rarely, be left behind, but that's as bas as it gets.
MPelletier
2010-03-21 19:35:04
Thanks. My concern was whether or not ALTER statements are treated the same way. But playing around at the sqlite3 prompt suggests that they are.
Bill
2010-03-22 00:33:12