Richard, Further experiments with snapshots reveal a behavior I did not expect.
My high-level goal is to *protect snapshots*: if a user of my SQLite wrapper GRDB.swift takes a snapshot, I want this snapshot to remain valid (usable without error) for the whole duration of its lifetime. During its lifetime, a snapshot will open transactions when it is used. But there are moments when a snapshot exists (has not been freed by sqlite3_snapshot_free()), but is not currently used by a transaction. I thus have to carefully manage checkpoints, so that they do not invalidate existing snapshots, even if they are not currently used in a transaction. I did attempt at protecting existing snapshots by opening a transaction and calling sqlite3_snapshot_open with the oldest open snapshot before calling sqlite3_wal_checkpoint_v2(). But this does not prevent snapshots from invalidation: 1. Create a snapshot with sqlite3_snapshot_get() 2. In connection A, open a deferred transaction, open the snapshot 3. In connection B, run the checkpoint (this fails with SQLITE_BUSY when checkpoint is RESTART OR TRUNCATE, as expected) 4. Close the transaction in connection A 5. In connection A, open a deferred transaction, open the snapshot, read, commit: success 6. In connection B, perform a write, commit: success 7. In connection A, open a deferred transaction, open the snapshot: SQLITE_ERROR (1), "not an error" Is the error at step 7 expected? On Fri, Sep 27, 2019 at 12:35 PM Richard Hipp <d...@sqlite.org> wrote: > On 9/26/19, Gwendal Roué <gwendal.r...@gmail.com> wrote: > > > > My question is: is it possible to prevent checkpoints from completing > > successfully when a snapshot is alive? > > > > That depends on what you mean by "alive"? > > An sqlite3_snapshot_get() simply records some numbers in private > memory of the database connection that called sqlite3_snapshot_get(). > There is no way for another process to know that those numbers have > been recorded, and hence no way to know that the snapshot exists, and > no way to prevent a checkpoint from happening. > > On the other hand, if you have run sqlite3_snapshot_open() so that > there is a transaction open on the snapshot, that takes locks on the > database which prevent checkpoints from running to completion and > erasing the snapshot. > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
