Gerry, David, you are both right. The possibility of a bug is thus eliminated for good. And https://www.sqlite.org/isolation.html <https://www.sqlite.org/isolation.html> is, strictly speaking, accurate, even if it another document is needed to avoid any interpretation doubt (http://www.sqlite.org/lang_transaction.html <http://www.sqlite.org/lang_transaction.html>).
Fact is, I spent a few days clearing things up, until I could eventually reach a correct program despite fuzzy premises ;-) Documentation is hard, and one never stops learning SQLite. Thanks for your quick and precise answers! Gwendal > Le 18 juil. 2017 à 15:43, David Raymond <david.raym...@tomtom.com> a écrit : > > I think the documentation's good. I think you're missing the whole point of a > deferred transaction: that it doesn't start a "transaction" until it needs > to. You can run "begin deferred transaction" then walk away for 3 months > without upsetting anything. If you need the precise timing then why not just > use "begin immediate"? > > http://www.sqlite.org/lang_transaction.html > > A deferred transaction doesn't do anything until it first accesses the file. > Once it does then it will lock out any writers. > > "Deferred means that no locks are acquired on the database until the database > is first accessed. Thus with a deferred transaction, the BEGIN statement > itself does nothing to the filesystem. Locks are not acquired until the first > read or write operation. The first read operation against a database creates > a SHARED lock and the first write operation creates a RESERVED lock." > > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Gwendal Roué > Sent: Tuesday, July 18, 2017 9:10 AM > To: SQLite mailing list > Subject: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper > > Hello all, > > The following sentence in https://www.sqlite.org/isolation.html does not > exactly describe the behavior of SQLite (since many versions): > >> In WAL mode, SQLite exhibits "snapshot isolation". When a read transaction >> starts, that reader continues to see an unchanging "snapshot" of the >> database file as it existed at the moment in time when the read transaction >> started. Any write transactions that commit while the read transaction is >> active are still invisible to the read transaction, because the reader is >> seeing a snapshot of database file from a prior moment in time. > > > I'll exhibit the inaccuracy below. > > Meanwhile, "snapshot isolation" is a tremendous feature of SQLite. Not only > does it grant any read-only connection that opens a deferred transaction an > immutable and consistent view of the database. But when you can guarantee > that there is a single writer connection, snapshot isolation allows *precise > scheduling*, such as blocking the writer connection until a reader has > established snapshot isolation. With such precision, one can exactly control > what's available to a reader, while not blocking the writer longer than > necessary. > > And this is where the documentation paragraph starts becoming inaccurate. For > the simplicity of the argument, I'll execute statements sequentially from two > connections W and R. To reproduce, just open two shells, and execute > statements in the following order: > > $ sqlite3 /tmp/snapshotisolation.sqlite > SQLite version 3.16.0 2016-11-04 19:09:39 > W> PRAGMA journal_mode=wal; > W> CREATE TABLE t(a); > R> BEGIN DEFERRED TRANSACTION; > W> INSERT INTO t DEFAULT VALUES; > R> SELECT COUNT(*) FROM t; > 1 > > This is unexpected. After connection R has started a deferred transaction, it > should continue to see an "unchanging snapshot of the database file as it > existed at the moment in time when the read transaction started". Obviously, > this is not the case, since the insertion performed by W is visible from R > even though it has been performed *after* R has started its deferred > transaction. The "Any write transactions that commit while the read > transaction is active are still invisible to the read transaction" is also > flat wrong here. > > If we continue, things behave as expected: > > W> INSERT INTO t DEFAULT VALUES; > R> SELECT COUNT(*) FROM t; > 1 > > R does not see the new insertion, which means that it indeed lives in an > unchanging snapshot. It just happens that the snapshot was not established > when the transaction has started, as documented, but *later*. But when? > > After a few experiments, it looks like the snapshot is established on the > first select: > > W> PRAGMA journal_mode=wal; > W> CREATE TABLE t(a); > R> BEGIN DEFERRED TRANSACTION; > R> SELECT * FROM sqlite_master LIMIT 1; -- good enough to start the snapshot > W> INSERT INTO t DEFAULT VALUES; > R> SELECT COUNT(*) FROM t; > 0 -- as expected > > If now we stop entering commands by hand in the CLI, and start working with > threads, the *precise scheduling* I mention at the beginning of the mail > requires to block the writer connection W until the reader connection R has > started a deferred transaction, and sqlite3_step() has been any executed once > from any select statement. Only then can the writer connection be released, > with a absolute control of the content of the reader's snapshot. > > I'm not sure the behavior I've just described can be called a bug. The > snapshot is unchanging indeed. When there are several processes connected to > the database, a reader can't know whether a change has been performed before > its deferred transaction has started, or between the start of the deferred > transaction and its first select statement, and therefore should not care at > all. With this interpretation, there is nothing wrong in the current behavior > of SQLite. > > However, when an application developper is sure that the database has a > single writer connection, the question of the content of the snapshot > suddenly becomes relevant. And the documentation becomes inaccurate. > > What do SQLite concurrency masters of this mailing list think? Besides that > threads are evil, I mean ;-) Shouldn't the documentation be updated? > > Cheers to all, > Gwendal > PS: For the record, I've been talking about "precise scheduling" and > "absolute control of the content of the snapshot" because they are the > necessary conditions for some database observation features such as reloading > fresh values from a request as soon as a transaction has modified its > content. See https://github.com/RxSwiftCommunity/RxGRDB for some high-level > implementations of such feature. > > > > _______________________________________________ > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
