SQLite Forum

Take control over the database for checkpointing and reading it
Login

Take control over the database for checkpointing and reading it

(1) By anonymous on 2021-11-22 15:41:44 [link] [source]

Hello,

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:

  1. The database must be fully contained in the main file, that is, the WAL file must be in an after full checkpoint state.
  2. 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:

  1. Call sqlite3_open(zDb, &db)
  2. Take control over the database with sqlite3_exec(db, "BEGIN IMMEDIATE", 0, 0, 0)
  3. Call sqlite3_wal_checkpoint_v2(db, zDb, SQLITE_CHECKPOINT_FULL, 0, 0)
  4. Do snapshot creation work.
  5. 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.

(2) By anonymous on 2021-11-22 16:43:19 in reply to 1 [source]

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;")

(3) By anonymous on 2021-11-22 23:51:45 in reply to 2 [link] [source]

I see. Thanks for the feedback!