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
