Hello Adam, You may enjoy reading this recent thread, which is exactly about the same topic: http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-September/086099.html
The crux of your issue is that sqlite3_snapshot_get() is not involved in transaction management at all. It does not take any lock. It does not prevent checkpointing. It won't prevent an external connection from writing and triggering an automatic checkpoint, for example. Checkpoints invalidate snapshots returned by sqlite3_snapshot_get(), making them unable to be used with sqlite3_snapshot_open(). This is a given, and I learned in the previously linked thread that it is unlikely to change. If you want a read-only long-time access to a given state of the database, then you need a dedicated transaction. The access will be guaranteed for the duration of the transaction. Checkpoints won't be able to invalidate it. It will be super robust. You can, for example: 1. open a dedicated read-only connection 2. run BEGIN DEFERRED TRANSACTION 3. perform your reads 4. run COMMIT or ROLLBACK at the end of your reads. This technique actually opens the transaction, "locks" a state of the database for unlimited future accesses, on the first read. Not on the BEGIN DEFERRED TRANSACTION statement. Sometimes this is good enough (think about it for a while). But sometimes you want to control the exact state of the snapshot. For example, you may want to take a snapshot after what you call the "next official state update". A way to achieve this with the most extreme precision and robustness is the following: 1. In a "writer" connection, COMMIT the "next official state update". Now prevent any write in the database until step 4. 2. In the read-only "snapshot" connection, BEGIN DEFERRED TRANSACTION 3. In the read-only "snapshot" connection, perform *any kind of read* in order to start the transaction for good. SELECT * FROM sqlite_master LIMIT 1 is good enough. Anything goes. 4. Now you can accept further writes in the "writer" connection. 5. And now you can read from the "snapshot" connection and access a guaranteed "official state" until the end of the "snapshot" transaction. Hope this helps, Gwendal Roué _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
