Re: [sqlite] Conflict between snapshots and checkpoints
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 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 > 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
Re: [sqlite] Conflict between snapshots and checkpoints
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conflict between snapshots and checkpoints
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 wrote: > > On Sunday, 29 September, 2019 01:28, Gwendal Roué > 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
Re: [sqlite] Conflict between snapshots and checkpoints
On Sunday, 29 September, 2019 01:28, Gwendal Roué 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
Re: [sqlite] Conflict between snapshots and checkpoints
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Segfault in fts3IncrmergeLoad in v3.29
Hi SQLite,
I'd like to report a bug I've encountered using SQLite v3.29, where it reliably
segfaults when trying to insert a row into a fts3 table in a database created
with a previous version of SQLite.
Please let me know if I can provide more information than what's below, as I'm
keen to get this resolved (and/or find a work around) urgently — this is
affecting hundreds of our users in production.
The SQL is `INSERT INTO thread_search(subject) VALUES ('a')`, but it seems that
any insert to this table will cause a problem. The table was created using
`CREATE VIRTUAL TABLE thread_search USING fts3(thread_id, subject, content,
from, to, cc, bcc, replyto, deliveredto, attachments, labels, list,
rfc822msgid, meta, tokenize=porter)` on a previous version of SQLite (though
I'm not sure which one).
I am happy to share a broken database privately (we have reproduced this once
internally, and are seeing it in the wild for a large number of customers, but
have not yet been able to create a shareable database that reproduces the
problem). If it makes a difference, one potentially unusual thing we do when
writing to the table in normal operations is setting the `rowid` to a value we
control (so that search results are returned in the correct order without a
post-sort).
I'm using SQLite as part of Chromium, and the stacktrace I get from Chromium
looks like this:
5 libsystem_platform.dylib 0x7fff70be7b5d _sigtramp + 29
6 libchromium_sqlite3.dylib 0x00016e5b2fe8 sqlite3DbMallocRawNN + 56
7 libchromium_sqlite3.dylib 0x00016e67053c fts3IncrmergeLoad + 1388
8 libchromium_sqlite3.dylib 0x00016e66f6d7 sqlite3Fts3Incrmerge + 1031
9 libchromium_sqlite3.dylib 0x00016e660b12 fts3SyncMethod + 210
10 libchromium_sqlite3.dylib 0x00016e5d1e70 sqlite3VtabSync + 176
11 libchromium_sqlite3.dylib 0x00016e5d0813 vdbeCommit + 67
12 libchromium_sqlite3.dylib 0x00016e5cff8d sqlite3VdbeHalt + 701
13 libchromium_sqlite3.dylib 0x00016e5d8c0b sqlite3VdbeExec + 16459
14 libchromium_sqlite3.dylib 0x00016e5a57f1 sqlite3Step + 433
15 libchromium_sqlite3.dylib 0x00016e5a54bd chrome_sqlite3_step + 125
That said, when I run the commandline version sqlite3 (built after running
fossil checkout branch-3.29 --force) inside lldb, I get a slightly
different-looking crash:
* thread #1, queue = ' com.apple.main-thread ( http://com.apple.main-thread/ )
', stop reason = EXC_BAD_ACCESS (code=1, address=0x0) frame #0:
0x00010a2b8098 sqlite3`sqlite3Fts3Incrmerge [inlined]
nodeReaderInit(aNode=0x, nNode=0) at sqlite3.c:174812:7 [opt]
174809 p->nNode = nNode; 174810 174811 /* Figure out if this is a leaf or an
internal node. */ -> 174812 if( p->aNode[0] ){ 174813 /* An internal node. */
174814 p->iOff = 1 + sqlite3Fts3GetVarint(>aNode[1], >iChild); 174815
}else{ Target 0: (sqlite3) stopped. (lldb) bt * thread #1, queue = '
com.apple.main-thread ( http://com.apple.main-thread/ ) ', stop reason =
EXC_BAD_ACCESS (code=1, address=0x0) * frame #0: 0x00010a2b8098
sqlite3`sqlite3Fts3Incrmerge [inlined] nodeReaderInit(aNode=0x,
nNode=0) at sqlite3.c:174812:7 [opt] frame #1: 0x00010a2b806b
sqlite3`sqlite3Fts3Incrmerge at sqlite3.c:175310 [opt] frame #2:
0x00010a2b7e6d sqlite3`sqlite3Fts3Incrmerge(p=,
nMerge=, nMin=) at sqlite3.c:175967 [opt] frame #3:
0x00010a2a6bf3 sqlite3`fts3SyncMethod(pVtab=0x7fcc74004ba0) at
sqlite3.c:164365:33 [opt] frame #4: 0x00010a1dbe15 sqlite3`sqlite3VdbeHalt
at sqlite3.c:135756:12 [opt] frame #5: 0x00010a1dbd8c
sqlite3`sqlite3VdbeHalt [inlined] vdbeCommit(db=, p=)
at sqlite3.c:78914 [opt] frame #6: 0x00010a1dbd8c
sqlite3`sqlite3VdbeHalt(p=) at sqlite3.c:79379 [opt] frame #7:
0x00010a1ee6ff sqlite3`sqlite3VdbeExec(p=) at
sqlite3.c:84884:8 [opt] frame #8: 0x00010a1aa02e sqlite3`sqlite3_step
[inlined] sqlite3Step(p=) at sqlite3.c:82160:10 [opt] frame #9:
0x00010a1a9e98 sqlite3`sqlite3_step(pStmt=) at sqlite3.c:82225
[opt]
I can also confirm that with sqlite v3.28, the INSERT works without failing:
$ ~/Downloads/sqlite-tools-osx-x86-328/sqlite3
~/debug/gabes-database-25-copy 'INSERT INTO thread_search (subject)
VALUES("a")' $
Interestingly, if I first run the insert first using v3.28; then it works fine
with v3.29 — I assume that this is because I no longer hitting the incremental
merge path in the newer version of the code.
It also seems to succeed in v3.29 if I first run `INSERT INTO
thread_search(thread_search) VALUES ('optimize')` before running the insert. I
don't yet know whether this is a permanent fix or whether if I continue
inserting until sqlite3Fts3Incmerge runs again, it will crash again.
Conrad
P.S. I'm happy to run more debugging here, in particular I'm not sure how to
get more information out when this breaks.
P.S. Superhuman is hiring — referral bonus for Full Stack Engineers (
https://superhuman.com/roles?gh_jid=260350 ) :
Re: [sqlite] Segfault in fts3IncrmergeLoad in v3.29
I've done some further digging here, and using git bisect, it seems like the
problem was this change: https://sqlite.org/src/info/c736c40aab071a69. I can
happily send a patch that fixes this for me, but I would like help producing a
minimal test case to add to the test suite.
In the database I have as an example, there is one section of
thread_search_segdir that has a negative size (indicative of being part-way
through a rebuild?).
level|idx|start_block|leaves_end_block|end_block|hex(root)
8|0|491439|492126|530222 -782471|02A7921E04363233660005633436623800046A396B67
Decoding the root, the left_child_id is 493863, and in fact there are four
height=1 nodes (493863, 493864, 493865, and 493866).
Node 493866 is interesting, because it is an interior node with no terms:
blockid|hex(block) 493866|01DE841E # left-child 492126
When we try and insert a new row into the table, it triggers an incremental
merge. As part of that merge, we loop down the tree looking for nodes to merge
together. We first hit node 496287 at height=2, then node 493866 at height=1
and then 492126 (a leaf node).
Before this commit, everything works ok; but after this commit, when we run
`nodeReaderInit` on node 492166 it segfaults because we pass in
`pNode->block.a` which is 0, and then try and read the memory it points to.
The reason that `pNode->block.a` is 0 on the leaf node is because we have
(erroneously) skipped the loop body on the height=1 node and the code assumes
that the previous iteration will have initialized pNode.
There are a few ways to fix the crash while keeping the test-suite passing, but
I think the correct thing to do is to move the new check earlier:
In ./ext/fts3/fts3_write.c:4926 - rc = nodeReaderInit(, pNode->block.a,
pNode->block.n); - if( reader.aNode ){ + if( pNode->block.a ){ + rc =
nodeReaderInit(, pNode->block.a, pNode->block.n);
Although it might seem like this change is equivalent, `pNode->block.a` will
exist more often than `reader.aNode`. This is because `nodeReaderInit` calls
`nodeReaderNext`, which has the side-effect of setting `reader.aNode = 0` when
there are no more terms to be read. In the case of node 493866 (above)
pNode->block.a exists (and it contains a pointer to the left-mode-child) but
calling nodeReaderNext sets reader.aNode to 0 because it contains no terms.
This change allows the loop to finish iterating correctly in the case above,
where there is no corruption but there is an interior node with no terms. This
change will also fix a potential crash on the leaf node in the case of an
actually corrupt interior node as we will skip the initialization of
`aNodeWriter[i-1]` later in the loop and so pNode->block.a will still be 0 from
when it was first created.
Please let me know if I can be further helpful in getting this fixed,
Conrad
P.S. Superhuman is hiring — referral bonus for Full Stack Engineers (
https://superhuman.com/roles?gh_jid=260350 ) : $1,947.
On Sat, Sep 28, 2019 at 10:35 PM, Conrad Irwin < con...@superhuman.com > wrote:
>
> Hi SQLite,
>
>
>
> I'd like to report a bug I've encountered using SQLite v3.29, where it
> reliably segfaults when trying to insert a row into a fts3 table in a
> database created with a previous version of SQLite.
>
>
>
> Please let me know if I can provide more information than what's below, as
> I'm keen to get this resolved (and/or find a work around) urgently — this
> is affecting hundreds of our users in production.
>
>
> The SQL is `INSERT INTO thread_search(subject) VALUES ('a')`, but it seems
> that any insert to this table will cause a problem. The table was created
> using `CREATE VIRTUAL TABLE thread_search USING fts3(thread_id, subject,
> content, from, to, cc, bcc, replyto, deliveredto, attachments, labels,
> list, rfc822msgid, meta, tokenize=porter)` on a previous version of
> SQLite (though I'm not sure which one).
>
>
>
> I am happy to share a broken database privately (we have reproduced this
> once internally, and are seeing it in the wild for a large number of
> customers, but have not yet been able to create a shareable database that
> reproduces the problem). If it makes a difference, one potentially unusual
> thing we do when writing to the table in normal operations is setting the
> `rowid` to a value we control (so that search results are returned in the
> correct order without a post-sort).
>
>
>
> I'm using SQLite as part of Chromium, and the stacktrace I get from
> Chromium looks like this:
>
> 5 libsystem_platform.dylib 0x7fff70be7b5d _sigtramp + 29
>
> 6 libchromium_sqlite3.dylib 0x00016e5b2fe8 sqlite3DbMallocRawNN + 56
>
> 7 libchromium_sqlite3.dylib 0x00016e67053c fts3IncrmergeLoad + 1388
>
> 8 libchromium_sqlite3.dylib 0x00016e66f6d7 sqlite3Fts3Incrmerge + 1031
>
>
> 9 libchromium_sqlite3.dylib 0x00016e660b12 fts3SyncMethod + 210
>
> 10 libchromium_sqlite3.dylib 0x00016e5d1e70 sqlite3VtabSync + 176
>
> 11 libchromium_sqlite3.dylib
[sqlite] PRAGMA table_info fails with "no such tokenizer"
Python 3.7.4 (default, Jul 9 2019, 16:32:37)
[GCC 9.1.1 20190503 (Red Hat 9.1.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect("/home/anatoli/.cache/tracker/meta.db")
>>> cursor = conn.cursor()
>>> cursor.execute('PRAGMA main.table_info("fts5")')
Traceback (most recent call last):
File "", line 1, in
sqlite3.OperationalError: no such tokenizer: TrackerTokenizer
The `sqlite3 meta.db .dump` works without errors. The `fts5` table
referenced in the dump.
INSERT INTO
sqlite_master(type,name,tbl_name,rootpage,sql)VALUES('table','fts5','fts5',0,'CREATE
VIRTUAL TABLE fts5 USING fts5(content="fts_view", "nfo:fileName",
"nco:title", "nfo:genre", "nmm:genre", "mtp:creator",
"nco:nameFamily", "nco:nameGiven", "nco:nameAdditional",
"nco:phoneNumber", "nao:prefLabel", "nao:description", "nco:fullname",
"nco:nickname", "nco:contactGroupName", "nfo:tableOfContents",
"ncal:comment", "ncal:location", "ncal:summary", "ncal:contact",
"ncal:description", "nmo:messageSubject", "nmm:albumTitle",
"nmm:artistName", "nco:department", "nco:role", "nco:note",
"nie:keyword", "nco:imID", "nco:imNickname", "nco:emailAddress",
"nmm:category", "nfo:fontFamily", "nco:region", "nco:country",
"nco:extendedAddress", "nco:streetAddress", "nco:postalcode",
"nco:locality", "nco:county", "nco:district", "nco:pobox",
"nie:title", "nie:subject", "nie:plainTextContent", "nie:description",
"nie:comment", "nid3:title", "nid3:albumTitle", "nid3:contentType",
tokenize=TrackerTokenizer)');
--
Anatoli Babenia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conflict between snapshots and checkpoints
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 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 >
