Re: [sqlite] Conflict between snapshots and checkpoints
On 9/26/19 12:13 PM, Gwendal Roué wrote: > Hello, > > The documentation for sqlite3_snapshot_open() [1] says: > >> A call to sqlite3_snapshot_open() will fail to open if the specified > snapshot has been overwritten by a checkpoint. > > And indeed I am able to create a snapshot with sqlite3_snapshot_get() [2], > then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally have > sqlite3_snapshot_open() fail. > > I have a concern, because this turns automatic checkpointing into a mortal > danger for all snapshots. As soon as a checkpoint is performed, snapshots > are invalidated, and whoever is relying on them has lost the database state > he's interested into. > > My question is: is it possible to prevent checkpoints from completing > successfully when a snapshot is alive? > > I know this is possible with a "hand-made checkpoint", made of a distinct > connection which has started a deferred transaction and has performed an > initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and > SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their > precious database state after a SQLITE_CHECKPOINT_PASSIVE or a > SQLITE_CHECKPOINT_FULL. > > The behavior of those "hand-made checkpoint" matches well the needs of > users who want to use WAL without thinking too much about it: they do not > disable automatic checkpointing, and are guaranteed with a stable access to > a given database state as long as they need it. > > I was wondering if such a behavior is possible with snapshots returned from > sqlite3_snapshot_get(). > > If it is not, then I humbly suggest that this feature would be added, and > am willing to listen to the opinion of SQLite experts on this subject. > > Regards, > Gwendal Roué > > [1] https://www.sqlite.org/c3ref/snapshot_open.html > [2] https://www.sqlite.org/c3ref/snapshot_get.html > [3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html I think you have a fundamental issue here. Snapshots are tied to a Transaction, and as long as that transaction doesn't invalidate that snapshot, nothing else can. (This is what you hand-mand checkpoint does right). The issue is that if you are reusing the connection, you are sharing the Transaction and thus not protecting your snapshot. Once someone else using that connection breaks the transaction, then the snapshot is no longer protected. The issue trying to increase the protection on a snapshot is that currently sqlite_snapshot_free() isn't defined as protecting the snapshot, but is merely a memory management tool, so many applications likely don't free all their snapshots before they allow for the possibility of them being invalidated by ending the Transaction, thus this change would break many existing programs. I suppose it could be implemented with a pragma or the like that doesn't allow that transaction to be committed or invalidate the snapshot until the snapshot is freed. What that would ultimately do is cause your program to get an error from SQLite3 farther down the road when you did some action on the connection that would potentially invalidate your precious snapshot, and you now have one more source of 'unexplained' errors returns. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conflict between snapshots and checkpoints
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 wrote: > On 9/26/19, Gwendal Roué 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
