Take control over the database for checkpointing and reading it
I'm working on an incremental backup tool for sqlite databases. The details of how it works are not very important for my question, all that matters is that there are a few requirements for it to work:
- The database must be fully contained in the main file, that is, the WAL file must be in an after full checkpoint state.
- While the tool creates the snapshot, no writers can write to it, but readers may be able to read it.
The tool has been working fine in my tests where it is the only database user. Now I want to make it work when it is not possible to assume that it is the only database user. I have a plan in mind and I would appreciate your feedback on it, and what should I be concerned about. The steps taken by the tool will be this:
- Take control over the database with
sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, 0)
sqlite3_wal_checkpoint_v2(db, zDb, SQLITE_CHECKPOINT_FULL, 0, 0)
- Do snapshot creation work.
- Release control over the database with
sqlite3_exec(db, "END", 0, 0, 0)
Do you think there are flaws with this overall plan?
I note that there is a recent thread similar to my question. Me and the author of that thread are trying to do basically the same thing, except that I need to perform a checkpoint before doing my incremental backup work.
That won't work because checkpointing can't be done inside a transaction. You could retry in a loop until you win the race (pseudocode):
dosql("pragma wal_checkpoint(truncate); begin immediate;") while (WAL file exists and is nonempty) dosql("rollback; pragma wal_checkpoint(truncate); begin immediate;")