Re: [sqlite] Ensure a snapshot remains readable
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
Re: [sqlite] Ensure a snapshot remains readable
On Sunday, 13 October, 2019 15:23, Adam Levy wrote: >My application requires a way to build a "pending state" in the database >while allowing users to query data from both the "official state" and the >"pending state". I am achieving this using sessions and snapshots. SQLite3 does not do database snapshots. What you are calling a "snapshot" is merely "remembering" a commit mark location in the WAL journal. >When pending data first comes into the application, I take a snapshot of >the current "official" state of the database and keep a read transaction >open on one read only connection for the life of the snapshot to prevent >it being checkpointed out of the WAL, and then start a session that tracks >all changes on my one write connection so that the pending data may later be >"rolled back" by applying the inverse changeset. Then I proceed to commit >incoming pending data to the write connection. Define "open a read transaction". "BEGIN" does not commence a transaction, it merely turns off autocommit. You have to actually READ something in order to obtain a read lock (transaction) on the database. >Any user querying the application for the pending state, is reading from >a read only connection which will reflect the current latest state of the >database, which includes the pending data, if any exists. Yes, it will use the latest commit point recorded in the WAL file at the time it actually reads something (not at the time the BEGIN is executed), or if you have used sqlite3_checkpoint_open to "go to" a memorized commit mark, then all data committed before that commit mark. >Any user querying for the official state, is reading from a read only >connection that has a read transaction started from the official state >snapshot captured above, if any pending data exists. Do you mean it is using the connection that you started a read transaction on, or it turning off autocommit (BEGIN) and then going to this memorized commit marker location (sqlite3_snapshot_open)? >When the next official state update occurs, the snapshot is freed, its >associated read transaction that I set aside is closed (and the read >connection is returned to my read conn pool), and the inverse changeset >is used to roll back the pending state on a write connection before applying >the official state update. Any reader currently reading from the snapshot >should be able to proceed until they end their read transaction. First of all, you cannot "free" a snapshot, since there is no such thing. What you are doing is basically nothing more than freeing the notation of the commit point location that you made (sort of like crumpling up the piece of paper that it was written on and setting it on fire). This has no effect whatsoever on the commit marker itself or on the WAL file -- merely on the on the piece of paper on which it was written. Secondly, readers do not read from a snapshot since snapshots do not actually exist. What they are reading is based on the commit marker in the WAL file at the time they actually started to read (not BEGIN, which does not actually do anything other than turn off autocommit). Thirdly, why do you "roll back" the pending state and then re-apply it again? Or is the actual update that you are going to commit different from the updates already written? >I assumed that keeping a read transaction open on the snapshot would be >enough to prevent the WAL from being checkpointed past the snapshot, >making it unavailable for use by other read connections. However, despite >keeping one read transaction open, I am still getting SQLITE_ERROR_SNAPSHOT >from sqlite3_snapshot_open when the pending data grow large enough to >force an auto checkpoint. I am fairly confident that the snapshot getting >checkpointed out of the WAL is the cause of this error since it went >away when I disabled auto checkpoints. Did you actually read something or did you just turn off autocommit? BEGIN just turns off autocommit, it does not actually commence a transaction. That transaction and the commit marker location is set when you actually read something. If you do not actually read something, then there is not actually a transaction in process. sqlite3_snapshot_get and sqlite3_snapshot_open say that they "open a read transaction" on a connection that has autocommit turned off. From my testing it appears that they do. >Is my assumption incorrect? If so, is there any way to ensure a snapshot >remains in the WAL without going so far as to manually manage checkpoints >myself? You mean a commit marker? Yes. Do not checkpoint the transaction associated with that commit marker. There are two ways to do this: control your checkpointing or open the snapshot (commit marker) and keep it open. >Finally if I must manually manage checkpoints, is it possible for a >PASSIVE checkpoint to leave the WAL in a state that will prevent taking a >snapshot in the first place? Specifically, can a PASSIVE
Re: [sqlite] Ensure a snapshot remains readable
On 13 Oct 2019, at 10:23pm, Adam Levy wrote: > My application requires a way to build a "pending state" in the database > while allowing users to query data from both the "official state" and the > "pending state". I am achieving this using sessions and snapshots. You are depending on finicky behaviour of SQLite. You will have to learn a lot about it to use it correctly. It may change or disappear sometime in the far future. It is impossible to reproduce in any other SQL implementation. Instead of seizing on a slight resemblance between your needs and an obscure internal feature of SQLite, implement your feature properly. Maintain two databases: official and pending, and a list of SQL commands required to turn 'pending' into 'official' (as a text file, or a table in one of those two databases, or a table in another database). This is crash-proof and can be backed up. It gives a solution which is far more easy to audit, and can be easily understood by anyone who might have to take over programming your application. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Ensure a snapshot remains readable
My application requires a way to build a "pending state" in the database while allowing users to query data from both the "official state" and the "pending state". I am achieving this using sessions and snapshots. When pending data first comes into the application, I take a snapshot of the current "official" state of the database and keep a read transaction open on one read only connection for the life of the snapshot to prevent it being checkpointed out of the WAL, and then start a session that tracks all changes on my one write connection so that the pending data may later be "rolled back" by applying the inverse changeset. Then I proceed to commit incoming pending data to the write connection. Any user querying the application for the pending state, is reading from a read only connection which will reflect the current latest state of the database, which includes the pending data, if any exists. Any user querying for the official state, is reading from a read only connection that has a read transaction started from the official state snapshot captured above, if any pending data exists. When the next official state update occurs, the snapshot is freed, its associated read transaction that I set aside is closed (and the read connection is returned to my read conn pool), and the inverse changeset is used to roll back the pending state on a write connection before applying the official state update. Any reader currently reading from the snapshot should be able to proceed until they end their read transaction. I assumed that keeping a read transaction open on the snapshot would be enough to prevent the WAL from being checkpointed past the snapshot, making it unavailable for use by other read connections. However, despite keeping one read transaction open, I am still getting SQLITE_ERROR_SNAPSHOT from sqlite3_snapshot_open when the pending data grow large enough to force an auto checkpoint. I am fairly confident that the snapshot getting checkpointed out of the WAL is the cause of this error since it went away when I disabled auto checkpoints. Is my assumption incorrect? If so, is there any way to ensure a snapshot remains in the WAL without going so far as to manually manage checkpoints myself? Finally if I must manually manage checkpoints, is it possible for a PASSIVE checkpoint to leave the WAL in a state that will prevent taking a snapshot in the first place? Specifically, can a PASSIVE checkpoint cause the following requirement for sqlite3_snapshot_get to no longer hold: "One or more transactions must have been written to the current wal file since it was created on disk (by any connection). This means that a snapshot cannot be taken on a wal mode database with no wal file immediately after it is first opened. At least one transaction must be written to it first." Thank you Adam Levy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
