Conflict between snapshots and checkpoints

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Conflict between snapshots and checkpoints

Gwendal Roué-2
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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
> I have a concern, because this turns automatic checkpointing into a
mortal danger for all snapshots.

This sentence may be false. Automatic snapshots are PASSIVE, and I'm not
sure passive checkpoints invalidate snapshots returned by
sqlite3_snapshot_get().

But i'm not sure, and I would appreciate a confirmation!

Thanks is advance,
Gwendal Roué


On Thu, Sep 26, 2019 at 6:13 PM Gwendal Roué <[hidden email]> 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
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Richard Hipp-3
In reply to this post by Gwendal Roué-2
On 9/26/19, Gwendal Roué <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
>> 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"?

Sorry, I meant "not finalized by sqlite3_snapshot_free".

Your precise answer lifts any doubt: only transactions can prevent
checkpoints from running to completion.

Thank you very much Richard!

Gwendal

On Fri, Sep 27, 2019 at 12:35 PM Richard Hipp <[hidden email]> wrote:

> On 9/26/19, Gwendal Roué <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
In reply to this post by Richard Hipp-3
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 <[hidden email]> wrote:

> On 9/26/19, Gwendal Roué <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Richard Damon
In reply to this post by Gwendal Roué-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
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 <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Richard Damon
On 9/29/19 3:27 AM, Gwendal Roué wrote:

> 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
I won;t say I know why they were put in, but here is one possibility
that I can think of. First, your model assumes that you have total
control and confidence over the entire application (and if you do, then
you can protect the snapshots yourself). By passing the snapshot and not
the connection with transaction with the snapshot, you can be sure that
the receiver can't disturb the session and invalidate the snapshot. By
opening the snapshot, the receiver is able to recreate the database at
that point, to read its state, but can't accidentally invalidate it.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Keith Medcalf
In reply to this post by Gwendal Roué-2

On Sunday, 29 September, 2019 01:28, Gwendal Roué <[hidden email]> wrote:

>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)?

The sqlite3_snapshot_* interfaces allow you to record information about transactions maintained by the WAL transaction manager.  The interface DOES NOT manage the WAL transaction manager nor does it manage your so-called snapshots (which are not snapshots).  It allows you to "get" information about a particular point-in-time location in the WAL transaction log and "goto" a specific point-in-time in the WAL transaction log, to "free" the memory used to record this information, and to "cmp" which of two structures point to a later point-in-time in the WAL transaction log.

They do NOT modify or control the WAL transaction system or the WAL file.  You are responsible for doing this.

https://sqlite.org/wal.html

So, *IF* you want to maintain the availability of a specific point in time in the WAL transaction history, they YOU must make sure that YOU do not erase that particular point-in-time location by checkpointing the WAL transaction log.  You have the capability of doing this by disabling the autocheckpoint mechanism and controlling when you do checkpoints.  The WAL transaction system neither knows nor cares that you happen to have recorded some information about a particular point-in-time offset in the WAL file.  

It is sort of like "No Smoking" signs.  The sign does not mean that you cannot smoke.  It means that someone posted a sign that says "No Smoking".  The fact that there is a sign and what it says is an entirely separate fact that has no bearing at all on whether or not one may smoke, nor does the sign somehow magically prevent smoking, nor will it prevent you from catching on fire and smoking as a result thereof.  It is merely a sign that has something written on it which someone stuck up on the wall (or whatever support structure it is stuck to).  And what is written on the sign is entirely independent of any other state of affairs.

>For information, my quest for snapshot protection has met some success (
>https://github.com/groue/GRDB.swift/pull/625)

This appears to be updating a wrapper interface to SQLite3 so that if you happen to have a point-in-time location recorded, then the automatic WAL checkpoint system is disabled, plus doing some prevention to protect users of the wrapper from modifying (checkpointing) the WAL transaction log if they happen to have some recorded "points-in-time" that are dependent on not modifying the WAL transaction log.  (ie, things that you should be doing yourself so as to not shoot yourself in the foot).

>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.  

No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot" taken and no snapshot exists.  You are merely not seeing data written to the WAL transaction log at a point-in-time subsequent to the point in time at which you commenced the "repeatable-read".

The sqlite3_snapshot_* interface merely provides a way to record information about this repeatable-read point-in-time position in the WAL file so that you can go back to that "repeatable-read" point-in-time location sometime in the future IF IT STILL EXISTS in the WAL transaction log.  Since YOU are in control of whether or not the WAL file is checkpointed, then YOU are in control of whether this point-in-time still exists in the WAL transaction log.  If you remove data from the WAL transaction log by performing a checkpoint (which moves transactions into the main database file) then this recorded "point-in-time" may no longer exist in the WAL transaction log.

>At the end of this transaction, the reader connection becomes
>available for another read.

Yes.  And by default when the transaction starts it gets a repeatable-read isolation "point-in-time" stamp corresponding to the last transaction written to the WAL transaction log which has been committed, or gets the current point-in-time representing the "current database" if there are no committed transactions in the WAL transaction log.

>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.

There is the error.  There is no "snapshot".  You do not "create a snapshot".  You record a point-in-time marker location within the WAL transaction log.  Think of it as taking a picture of the state of the moving van after it has been half filled.  You can go back to the point-in-time represented by this picture right up until the moving van drives to the new location and unloads those boxes at your new house (does a checkpoint).  When this happens, you still have the picture, but now a new van has arrived to be loaded up and the old one is gone forever.  While you still might have the picture, it is just a bunch of data that is not useful for anything except entertainment.

>In order to protect unused snapshots, checkpoints are prevented as long
>as there exists snapshots, with sqlite3_wal_hook().

There are no "snapshots".  There are merely recorded point-in-time markers from the WAL transaction log.  If you modify the WAL transaction log by performing a checkpoint operation, your "point-in-time" may no longer be accessible because that "point-in-time marker" may no longer exist in the WAL transaction log.

>I **really** hope this protects snapshots for good. Of course, a
>confirmation from knowledgeable people would be appreciated :-)

Yes.  If you do not remove transactions from the WAL transaction log then all the point-in-time markers before each of these transactions will still exist.  As soon as you move a committed transaction from the WAL file to the main database (this is called a checkpoint), the "point-in-time" marker prior to that transaction no longer exists in the WAL file, so you "cannot get there from here" (you cannot go to that point-in-time location because it is no longer recorded in the transaction log).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
Thank you very much Keith.

Apologies for my imprecise vocabulary, and the use of the same "snapshot"
word with different meanings.

I have used the term "snapshot isolation" as used in
https://www.sqlite.org/isolation.html; But I'll remember about
REPEATABLE-READ isolation.

I also thank you very much for confirming that preventing checkpoints
allows the WAL markers to remain valid.

My goal is to provide a set of concurrency primitives that are useful for
GUI app developers. I'm learning a lot about SQLite in the process, of
course.

On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf <[hidden email]> wrote:

>
> On Sunday, 29 September, 2019 01:28, Gwendal Roué <[hidden email]>
> wrote:
>
> >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)?
>
> The sqlite3_snapshot_* interfaces allow you to record information about
> transactions maintained by the WAL transaction manager.  The interface DOES
> NOT manage the WAL transaction manager nor does it manage your so-called
> snapshots (which are not snapshots).  It allows you to "get" information
> about a particular point-in-time location in the WAL transaction log and
> "goto" a specific point-in-time in the WAL transaction log, to "free" the
> memory used to record this information, and to "cmp" which of two
> structures point to a later point-in-time in the WAL transaction log.
>
> They do NOT modify or control the WAL transaction system or the WAL file.
> You are responsible for doing this.
>
> https://sqlite.org/wal.html
>
> So, *IF* you want to maintain the availability of a specific point in time
> in the WAL transaction history, they YOU must make sure that YOU do not
> erase that particular point-in-time location by checkpointing the WAL
> transaction log.  You have the capability of doing this by disabling the
> autocheckpoint mechanism and controlling when you do checkpoints.  The WAL
> transaction system neither knows nor cares that you happen to have recorded
> some information about a particular point-in-time offset in the WAL file.
>
> It is sort of like "No Smoking" signs.  The sign does not mean that you
> cannot smoke.  It means that someone posted a sign that says "No Smoking".
> The fact that there is a sign and what it says is an entirely separate fact
> that has no bearing at all on whether or not one may smoke, nor does the
> sign somehow magically prevent smoking, nor will it prevent you from
> catching on fire and smoking as a result thereof.  It is merely a sign that
> has something written on it which someone stuck up on the wall (or whatever
> support structure it is stuck to).  And what is written on the sign is
> entirely independent of any other state of affairs.
>
> >For information, my quest for snapshot protection has met some success (
> >https://github.com/groue/GRDB.swift/pull/625)
>
> This appears to be updating a wrapper interface to SQLite3 so that if you
> happen to have a point-in-time location recorded, then the automatic WAL
> checkpoint system is disabled, plus doing some prevention to protect users
> of the wrapper from modifying (checkpointing) the WAL transaction log if
> they happen to have some recorded "points-in-time" that are dependent on
> not modifying the WAL transaction log.  (ie, things that you should be
> doing yourself so as to not shoot yourself in the foot).
>
> >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.
>
> No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot"
> taken and no snapshot exists.  You are merely not seeing data written to
> the WAL transaction log at a point-in-time subsequent to the point in time
> at which you commenced the "repeatable-read".
>
> The sqlite3_snapshot_* interface merely provides a way to record
> information about this repeatable-read point-in-time position in the WAL
> file so that you can go back to that "repeatable-read" point-in-time
> location sometime in the future IF IT STILL EXISTS in the WAL transaction
> log.  Since YOU are in control of whether or not the WAL file is
> checkpointed, then YOU are in control of whether this point-in-time still
> exists in the WAL transaction log.  If you remove data from the WAL
> transaction log by performing a checkpoint (which moves transactions into
> the main database file) then this recorded "point-in-time" may no longer
> exist in the WAL transaction log.
>
> >At the end of this transaction, the reader connection becomes
> >available for another read.
>
> Yes.  And by default when the transaction starts it gets a repeatable-read
> isolation "point-in-time" stamp corresponding to the last transaction
> written to the WAL transaction log which has been committed, or gets the
> current point-in-time representing the "current database" if there are no
> committed transactions in the WAL transaction log.
>
> >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.
>
> There is the error.  There is no "snapshot".  You do not "create a
> snapshot".  You record a point-in-time marker location within the WAL
> transaction log.  Think of it as taking a picture of the state of the
> moving van after it has been half filled.  You can go back to the
> point-in-time represented by this picture right up until the moving van
> drives to the new location and unloads those boxes at your new house (does
> a checkpoint).  When this happens, you still have the picture, but now a
> new van has arrived to be loaded up and the old one is gone forever.  While
> you still might have the picture, it is just a bunch of data that is not
> useful for anything except entertainment.
>
> >In order to protect unused snapshots, checkpoints are prevented as long
> >as there exists snapshots, with sqlite3_wal_hook().
>
> There are no "snapshots".  There are merely recorded point-in-time markers
> from the WAL transaction log.  If you modify the WAL transaction log by
> performing a checkpoint operation, your "point-in-time" may no longer be
> accessible because that "point-in-time marker" may no longer exist in the
> WAL transaction log.
>
> >I **really** hope this protects snapshots for good. Of course, a
> >confirmation from knowledgeable people would be appreciated :-)
>
> Yes.  If you do not remove transactions from the WAL transaction log then
> all the point-in-time markers before each of these transactions will still
> exist.  As soon as you move a committed transaction from the WAL file to
> the main database (this is called a checkpoint), the "point-in-time" marker
> prior to that transaction no longer exists in the WAL file, so you "cannot
> get there from here" (you cannot go to that point-in-time location because
> it is no longer recorded in the transaction log).
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Richard Damon
On 9/29/19 11:40 AM, Gwendal Roué wrote:

> Thank you very much Keith.
>
> Apologies for my imprecise vocabulary, and the use of the same "snapshot"
> word with different meanings.
>
> I have used the term "snapshot isolation" as used in
> https://www.sqlite.org/isolation.html; But I'll remember about
> REPEATABLE-READ isolation.
>
> I also thank you very much for confirming that preventing checkpoints
> allows the WAL markers to remain valid.
>
> My goal is to provide a set of concurrency primitives that are useful for
> GUI app developers. I'm learning a lot about SQLite in the process, of
> course.

And since you reused the connection (otherwise you just need to keep the
transaction open and the snapshot was preserved) you don't have isolation.

Quote:


    No Isolation Between Operations On The Same Database Connection

SQLite provides isolation between operations in separate database
connections. However, there is no isolation between operations that
occur within the same database connection.



--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
Yes, this is exactly as you say. Isolation is very important, and I
carefully make sure that it is guaranteed to users who need it. This has
been working well for a few years now, GRDB.swift is very robust. My recent
focus was on SQLite snapshots, and you and other members of this mailing
list have been very helpful by completing the existing documentation :-)

Gwendal

On Sun, Sep 29, 2019 at 7:41 PM Richard Damon <[hidden email]>
wrote:

> On 9/29/19 11:40 AM, Gwendal Roué wrote:
> > Thank you very much Keith.
> >
> > Apologies for my imprecise vocabulary, and the use of the same "snapshot"
> > word with different meanings.
> >
> > I have used the term "snapshot isolation" as used in
> > https://www.sqlite.org/isolation.html; But I'll remember about
> > REPEATABLE-READ isolation.
> >
> > I also thank you very much for confirming that preventing checkpoints
> > allows the WAL markers to remain valid.
> >
> > My goal is to provide a set of concurrency primitives that are useful for
> > GUI app developers. I'm learning a lot about SQLite in the process, of
> > course.
>
> And since you reused the connection (otherwise you just need to keep the
> transaction open and the snapshot was preserved) you don't have isolation.
>
> Quote:
>
>
>     No Isolation Between Operations On The Same Database Connection
>
> SQLite provides isolation between operations in separate database
> connections. However, there is no isolation between operations that
> occur within the same database connection.
>
>
>
> --
> Richard Damon
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Dominique Devienne
In reply to this post by Keith Medcalf
On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf <[hidden email]> wrote:

> On Sunday, 29 September, 2019 01:28, Gwendal Roué <[hidden email]>
> wrote:
> >Those N reader connections allow concurrent database reads. Those "reads"
> are
> >generally wrapped in a deferred transaction which provides snapshot
> >isolation.
>
> No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot"
> taken and no snapshot exists.

You are merely not seeing data written to the WAL transaction log at a
> point-in-time subsequent to the point in time at which you commenced the
> "repeatable-read".
>

I don't see where you are going with this Keith.
Repeatable-reads "in the past" *are* snapshots IMHO.

The WAL file *does* contain enough information combined with the main
database file pages,
to logically *and* physically represent a "snapshot" of the DB at that
point-in-time. So not calling
it a "snapshot" is a stretch at the very least. What is a "snapshot"
according to you, if that's not it?

And also why do you think Richard, who knows a thing or two about
databases, called these API *snaphot* then?

I'm genuinely curious here. I think I disagree with you, but most time I
do, I'm wrong, so I'd like to understand, really. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Gwendal Roué-2
According to
http://dbmsmusings.blogspot.com/2019/06/correctness-anomalies-under.html,
SNAPSHOT ISOLATION is stronger than REPEATABLE READ, in that it prevents
"phantom reads" (
http://dbmsmusings.blogspot.com/2019/05/introduction-to-transaction-isolation.html).
I think SQLite prevents phantom reads, and so we actually get actual
SNAPSHOT ISOLATION (as written in https://www.sqlite.org/isolation.html).

On Mon, Sep 30, 2019 at 10:06 AM Dominique Devienne <[hidden email]>
wrote:

> On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf <[hidden email]> wrote:
>
> > On Sunday, 29 September, 2019 01:28, Gwendal Roué <
> [hidden email]>
> > wrote:
> > >Those N reader connections allow concurrent database reads. Those
> "reads"
> > are
> > >generally wrapped in a deferred transaction which provides snapshot
> > >isolation.
> >
> > No, it provides REPEATABLE-READ isolation.  There is no actual "snapshot"
> > taken and no snapshot exists.
>
> You are merely not seeing data written to the WAL transaction log at a
> > point-in-time subsequent to the point in time at which you commenced the
> > "repeatable-read".
> >
>
> I don't see where you are going with this Keith.
> Repeatable-reads "in the past" *are* snapshots IMHO.
>
> The WAL file *does* contain enough information combined with the main
> database file pages,
> to logically *and* physically represent a "snapshot" of the DB at that
> point-in-time. So not calling
> it a "snapshot" is a stretch at the very least. What is a "snapshot"
> according to you, if that's not it?
>
> And also why do you think Richard, who knows a thing or two about
> databases, called these API *snaphot* then?
>
> I'm genuinely curious here. I think I disagree with you, but most time I
> do, I'm wrong, so I'd like to understand, really. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Richard Damon
On 9/30/19 4:28 AM, Gwendal Roué wrote:
> According to
> http://dbmsmusings.blogspot.com/2019/06/correctness-anomalies-under.html,
> SNAPSHOT ISOLATION is stronger than REPEATABLE READ, in that it prevents
> "phantom reads" (
> http://dbmsmusings.blogspot.com/2019/05/introduction-to-transaction-isolation.html).
> I think SQLite prevents phantom reads, and so we actually get actual
> SNAPSHOT ISOLATION (as written in https://www.sqlite.org/isolation.html).

Based on a bit of research with things like
https://en.wikipedia.org/wiki/Snapshot_isolation

Snapshot Isolation appears to be a Term of Art, not a Standardized Term
so it can be forgiven if it isn't used in the documentation. Also, it
seems to imply non-serialized writes, which SQLite does NOT provide, so
isn't even really applicable.

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Keith Medcalf
In reply to this post by Dominique Devienne
On Monday, 30 September, 2019 02:06, Dominique Devienne <[hidden email]> wrote:

>On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf <[hidden email]> wrote:

>> On Sunday, 29 September, 2019 01:28, Gwendal Roué <[hidden email]> wrote:

>> >Those N reader connections allow concurrent database reads. Those
>> "reads" are generally wrapped in a deferred transaction which provides
>> snapshot isolation.

>> No, it provides REPEATABLE-READ isolation.  There is no actual
>> "snapshot" taken and no snapshot exists.

> You are merely not seeing data written to the WAL transaction log at a
> point-in-time subsequent to the point in time at which you commenced
> the "repeatable-read".

>I don't see where you are going with this Keith.
>Repeatable-reads "in the past" *are* snapshots IMHO.

Generally no.  "Snapshot Isolation" does not really exist, though some RDBMS have created it to permit more opportunistic updates.  Reads are at the Repeatable-Read isolation level (both for WAL and DELETE journal modes), and writes are Serialized.  Snapshot Isolation is an invention of the MVCC folks to theoretically permit greater update concurrency at the expense of serializable isolation and introduces anomalies into the database read and write processing that cannot occur when the updates are serialized, and which generally requires the application programmer to take extra steps to ensure database consistency.

>The WAL file *does* contain enough information combined with the main
>database file pages, to logically *and* physically represent a "snapshot"
>of the DB at that point-in-time.

For the purposes of reading only yes, I suppose you could call it a "snapshot", except that it isn't.  It is just a point-in-time repeatable-read.  You can only upgrade a transaction from read to write if you are holding the "top" snapshot (that is, you must be seeing the entire database, not a point-in-time version of it).  

>So not calling it a "snapshot" is a stretch at the very least. What is a "snapshot"
>according to you, if that's not it?

Snapshot Isolation is implemented by a bunch of different databases that do not conform to the SQL Standard and it introduces anomalies into the update process that cannot be introduced when using Serializable Isolation.  

https://en.wikipedia.org/wiki/Isolation_(database_systems)
https://en.wikipedia.org/wiki/Snapshot_isolation

>And also why do you think Richard, who knows a thing or two about
>databases, called these API *snaphot* then?

Because it is a convenient descriptor, perhaps?  They may in fact be considered to be a snapshot of the database as it existed at some point in the past (without full view of all committed transactions) however only the "top" snapshot, the one that has a view of all committed transactions is permitted to update/write to the database.

>I'm genuinely curious here. I think I disagree with you, but most time I
>do, I'm wrong, so I'd like to understand, really. --DD

The isolation is either repeatable-read for read transactions, or serializable for writes.  It is not Snapshot Isolation.

So although one may consider that what you are looking at is a "snapshot" of the database that existed at a particular point-in-time, it should not be confused with "snapshot isolation" which is an entirely different beast altogether.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conflict between snapshots and checkpoints

Dominique Devienne
On Mon, Sep 30, 2019 at 2:07 PM Keith Medcalf <[hidden email]> wrote:

> On Monday, 30 September, 2019 02:06, Dominique Devienne <
> [hidden email]> wrote:
> >On Sun, Sep 29, 2019 at 2:13 PM Keith Medcalf <[hidden email]>
> wrote:
> >> On Sunday, 29 September, 2019 01:28, Gwendal Roué <
> [hidden email]> wrote:
>
> >> >Those N reader connections allow concurrent database reads. Those
> >> "reads" are generally wrapped in a deferred transaction which provides
> >> snapshot isolation.
>
> >> No, it provides REPEATABLE-READ isolation.  There is no actual
> >> "snapshot" taken and no snapshot exists.
>
> > You are merely not seeing data written to the WAL transaction log at a
> > point-in-time subsequent to the point in time at which you commenced
> > the "repeatable-read".
>
> >I don't see where you are going with this Keith.
> >Repeatable-reads "in the past" *are* snapshots IMHO.
>
> Generally no.  "Snapshot Isolation" does not really exist, though some
> RDBMS have created it to permit more opportunistic updates.  Reads are at
> the Repeatable-Read isolation level (both for WAL and DELETE journal
> modes), and writes are Serialized.  Snapshot Isolation is an invention of
> the MVCC folks to theoretically permit greater update concurrency at the
> expense of serializable isolation and introduces anomalies into the
> database read and write processing that cannot occur when the updates are
> serialized, and which generally requires the application programmer to take
> extra steps to ensure database consistency.
>
> >The WAL file *does* contain enough information combined with the main
> >database file pages, to logically *and* physically represent a "snapshot"
> >of the DB at that point-in-time.
>
> For the purposes of reading only yes, I suppose you could call it a
> "snapshot", except that it isn't.  It is just a point-in-time
> repeatable-read.  You can only upgrade a transaction from read to write if
> you are holding the "top" snapshot (that is, you must be seeing the entire
> database, not a point-in-time version of it).
>
> >So not calling it a "snapshot" is a stretch at the very least. What is a
> "snapshot"
> >according to you, if that's not it?
>
> Snapshot Isolation is implemented by a bunch of different databases that
> do not conform to the SQL Standard and it introduces anomalies into the
> update process that cannot be introduced when using Serializable
> Isolation.
>
> https://en.wikipedia.org/wiki/Isolation_(database_systems)
> https://en.wikipedia.org/wiki/Snapshot_isolation
>
> >And also why do you think Richard, who knows a thing or two about
> >databases, called these API *snaphot* then?
>
> Because it is a convenient descriptor, perhaps?  They may in fact be
> considered to be a snapshot of the database as it existed at some point in
> the past (without full view of all committed transactions) however only the
> "top" snapshot, the one that has a view of all committed transactions is
> permitted to update/write to the database.
>
> >I'm genuinely curious here. I think I disagree with you, but most time I
> >do, I'm wrong, so I'd like to understand, really. --DD
>
> The isolation is either repeatable-read for read transactions, or
> serializable for writes.  It is not Snapshot Isolation.
> So although one may consider that what you are looking at is a "snapshot"
> of the database that existed at a particular point-in-time, it should not
> be confused with "snapshot isolation" which is an entirely different beast
> altogether.
>

So I guess our main difference here, is that I have no qualms at all with
point-in-time *read-only* repeatable-read transaction being called a
"Snapshot".
MVCC (which to me is synonymous with snapshots) is more about
read-consistency across statements (i.e. a read transaction) that does
*not* prevent writes.
Using snapshots, I can parallelize access to several tables across
connections (and threads), ensuring read-consistency in several separate
transactions and connections.
I used to do that in Oracle, and I'm glad that I can try to do it in SQLite
too now. There are caveats of course, like controlling checkpointing, but I
can live with that. --DD

PS: Note that I never used "Snapshot *Isolation*" myself. For me, Snapshot
= point-in-time read-consistency.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users