SQLite Forum

Database WAL snapshot
Login
> If it doesn't and shouldn't work, is there another way to ensure c1 and c2 point at the same position? (Besides creating a "dummy commit")

My experience and testing indicates that there has to be a transaction in the WAL file that has been committed and not yet checkpointed out before you can get a snapshot location with `sqlite3_snapshot_get`.  It does not appear that there is a way to get or create a marker that says "from the main database only without the WAL file".

I would suspect this is because there would then be no way to know if that snapshot was checkpointed out of the WAL (ie, if the snapshot recorded "at the beginning of the WAL file" there would be no way to invalidate that snapshot if all the transactions in the WAL were checkpointed, and you would have a snapshot that is not a valid snapshot but you have no way to tell).

So I think the "dummy commit" is required to provide a starting point.  Note that I used the "pragma user_version" because updating this to the same value it already has is in fact a transaction, where updating some data in some table somewhere to a value it already had is not (SQLite optimizes away the change that isn't and when you commit optimizes away the transaction that isn't).  This means that this "dummy transaction" serves as an actual transaction and writes a commit record to the WAL without actually changing anything.