Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Olivier Mascia]

> Le 20 juil. 2017 à 10:23, Olivier Mascia  a écrit :
> 
>> Le 20 juil. 2017 à 07:34, Rowan Worth  a écrit :
>> 
>> IMMEDIATE would take a RESERVED lock which is clearly not desired in this
>> case -- the reader only wants a SHARED lock so as to minimise contention
>> with the writer.
> 
> This discussion revolved around WAL mode.
> 
> BEGIN IMMEDIATE by a reader will have no contention impact on writer unless 
> later in the course of that transaction the reader starts doing things 
> involving write, which would turn itself to a writer.

And I'm taking that off immediately as I'm obviously wrong here. Sorry for the 
noise. Some thoughts overlapped.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

> Le 20 juil. 2017 à 08:45, Clemens Ladisch  a écrit :
> 
> Rowan Worth wrote:
>> On 18 July 2017 at 21:43, David Raymond  wrote:
>>> 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"?
>> 
>> IMMEDIATE would take a RESERVED lock which is clearly not desired in this
>> case -- the reader only wants a SHARED lock so as to minimise contention
>> with the writer.
> 
> Then to take a SHARED lock, you have to actually read something from the
> database, e.g.:
> 
>  BEGIN DEFERRED TRANSACTION;
>  PRAGMA user_version;

Yes, that's exactly that.

See 
https://github.com/groue/GRDB.swift/blob/2526e0a2f7097acbb71e41fb55baeda9d98c441d/GRDB/Core/DatabasePool.swift#L516-L540
 for an implementation

For a description of the benefits that users can have from such precision 
snapshotting, read: 
https://github.com/groue/GRDB.swift/blob/master/README.md#advanced-databasepool

And on such robust ground, you can build very high-level constructs like 
https://github.com/RxSwiftCommunity/RxGRDB

Gwendal Roué

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Olivier Mascia]

> Le 20 juil. 2017 à 07:34, Rowan Worth  a écrit :
> 
> IMMEDIATE would take a RESERVED lock which is clearly not desired in this
> case -- the reader only wants a SHARED lock so as to minimise contention
> with the writer.

This discussion revolved around WAL mode.

BEGIN IMMEDIATE by a reader will have no contention impact on writer unless 
later in the course of that transaction the reader starts doing things 
involving write, which would turn itself to a writer.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Clemens Ladisch]

Rowan Worth wrote:
> On 18 July 2017 at 21:43, David Raymond  wrote:
>> 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"?
>
> IMMEDIATE would take a RESERVED lock which is clearly not desired in this
> case -- the reader only wants a SHARED lock so as to minimise contention
> with the writer.

Then to take a SHARED lock, you have to actually read something from the
database, e.g.:

  BEGIN DEFERRED TRANSACTION;
  PRAGMA user_version;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Rowan Worth]

On 18 July 2017 at 21:43, David Raymond  wrote:

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

IMMEDIATE would take a RESERVED lock which is clearly not desired in this
case -- the reader only wants a SHARED lock so as to minimise contention
with the writer.

 -Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Peter Da Silva]

I notice that “read_uncommitted pragma” is spelled “PRAGMA read_uncommitted” in 
one place. The links all match. This small inconsistency is probably a mistake.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

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 snap

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[David Raymond]

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 

Re: [sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gerry Snyder]

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

On Jul 18, 2017 6:10 AM, "Gwendal Roué"  wrote:

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

[sqlite] Possible inaccuracy in "Isolation In SQLite" paper

[Gwendal Roué]

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