multiple connections and switching journal modes
(1) By aryairani on 2021-12-04 18:55:03 [source]
I have an application that archives a sqlite database at various points. We use WAL during normal operation, but want to make sure the file is totally consistent before archiving it. (To me, that means no .wal or .shm would be archived, but maybe I'm being overly conservative.) We've been forcing the journal mode back to DELETE before archiving, to let sqlite clean things up, but apparently not all connections to the db have been closed, and sometimes this causes surprises (unfortunately I don't have the list of surprises available, but some sort of "busy" or lock I suppose)
A couple of questions have come out of this:
1a.) Is there any harm in switching a database's journal mode from WAL to DELETE while there are multiple connections open to it?
1b.) Is there still harm mitigated if the multiple connections are from the same thread in the same process?
1c.) Is there a way to work around the pitfalls of 1a and 1b? Would it just be a matter of catching SQLITE_BUSY and retrying?
2.) Is there a better way to go about preparing a database for archival (not necessarily to read-only media, but archival nonetheless), and performing the archival, when there may be multiple connections to the database? Or is the answer to simply ensure that there are not multiple connections to a database being prepared for archival?
Thanks for any insights.
(2) By Richard Hipp (drh) on 2021-12-04 19:31:20 in reply to 1 [link] [source]
The better way to prepare a database for archival storage is to run "VACUUM INTO ..." and then archive not the original database, but the target of the VACUUM INTO. The VACUUM INTO command was created specifically for that purpose - to minimize and remove all traces of deleted content from a database prior to saving a copy off-device.
Changing from WAL to DELETE when there are multiple connections is harmful to the database - so much so that it is not allows. If you try it, the command will fail. But you can VACUUM INTO on a database with multiple connections.