Thank you, Richard But now I fail to understand the indented use case of sqlite3 snapshots.. Why allow to reuse snapshots with several calls to open()? Why do they exist at all, since we can already profit from snapshot isolation with one transaction (at the cost of keeping it open)?
For information, my quest for snapshot protection has met some success ( https://github.com/groue/GRDB.swift/pull/625) Given I control the connections to a given database file, I am able to open one writer connection, and, on demand, up to N read-only connections. Those N reader connections allow concurrent database reads. Those "reads" are generally wrapped in a deferred transaction which provides snapshot isolation. At the end of this transaction, the reader connection becomes available for another read. One can now create a "snapshot". Those snapshots use the same pool of N readers: snapshot reads are then wrapped in a deferred transaction and sqlite3_snapshot_open. At the end of this transaction, the reader connection becomes available for another regular read or for another snapshot read. In order to protect unused snapshots, checkpoints are prevented as long as there exists snapshots, with sqlite3_wal_hook(). I **really** hope this protects snapshots for good. Of course, a confirmation from knowledgeable people would be appreciated :-) Gwendal On Sat, Sep 28, 2019 at 4:06 PM Richard Damon <rich...@damon-family.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
