Explicit "read transaction" with journal_mode=WAL.

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

Explicit "read transaction" with journal_mode=WAL.

test user
Hello,

How can I start a "read transaction" from BEGIN?


I am using `journal_mode=WAL`.

What I am trying to do:

From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the
same snapshot/point in time.


The issue is that its hard to tell if I reading from a read snapshot (where
any successful commit on other connections since the reads BEGIN are
ignored).

When is a read transaction started?


As an example, connection A and B:

```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");
run(B, "SELECT * FROM t1"); = 0 rows

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started
```

```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");
run(B, "SELECT 1"); = 0 rows

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
```


```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");


run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
```



https://www.sqlite.org/isolation.html

Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction"

This is the only page where I can find a mention of the idea of "read
transaction" and "write transaction".


BEGIN IMMEDIATE allows the explicit start of a "write transaction".

Does an API exist for a "read transaction"?

Thanks
_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

David Raymond
To get the read lock you're going to need to read something from the database file.

I think this page is your best bet: https://www.sqlite.org/lang_transaction.html

"Transactions can be deferred, immediate, or exclusive. The default transaction behavior is deferred. 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. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed..."

So after a "begin deferred" you have to actually do something that requires file access in order to get the shared lock/start your read snapshot on the file. So if you want to get that shared lock/read snapshot you can always do something like a select from sqlite_master. "select 1;" doesn't need to access the file to complete, so it doesn't take out the shared lock (though it should be returning 1 row, are you sure it's 0?)



-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of test user
Sent: Tuesday, July 30, 2019 1:45 PM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL.

Hello,

How can I start a "read transaction" from BEGIN?


I am using `journal_mode=WAL`.

What I am trying to do:

From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the
same snapshot/point in time.


The issue is that its hard to tell if I reading from a read snapshot (where
any successful commit on other connections since the reads BEGIN are
ignored).

When is a read transaction started?


As an example, connection A and B:

```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");
run(B, "SELECT * FROM t1"); = 0 rows

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started
```

```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");
run(B, "SELECT 1"); = 0 rows

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
```


```
run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");

run(B, "BEGIN");


run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
run(A, "COMMIT");

run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
```



https://www.sqlite.org/isolation.html

Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction"

This is the only page where I can find a mention of the idea of "read
transaction" and "write transaction".


BEGIN IMMEDIATE allows the explicit start of a "write transaction".

Does an API exist for a "read transaction"?

Thanks
_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

test user
Thanks David,

`SELECT 1` = rows 0 was a mistake in the example.

How sure are you that "any SELECT that reads from the DB file starts a read
transaction"?

Does the read transaction read from a snapshot of the entire DB, or are
only specific tables in the read snapshot?






On Tue, Jul 30, 2019 at 7:14 PM David Raymond <[hidden email]>
wrote:

> To get the read lock you're going to need to read something from the
> database file.
>
> I think this page is your best bet:
> https://www.sqlite.org/lang_transaction.html
>
> "Transactions can be deferred, immediate, or exclusive. The default
> transaction behavior is deferred. 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. Because the acquisition of locks is
> deferred until they are needed, it is possible that another thread or
> process could create a separate transaction and write to the database after
> the BEGIN on the current thread has executed..."
>
> So after a "begin deferred" you have to actually do something that
> requires file access in order to get the shared lock/start your read
> snapshot on the file. So if you want to get that shared lock/read snapshot
> you can always do something like a select from sqlite_master. "select 1;"
> doesn't need to access the file to complete, so it doesn't take out the
> shared lock (though it should be returning 1 row, are you sure it's 0?)
>
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of test user
> Sent: Tuesday, July 30, 2019 1:45 PM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL.
>
> Hello,
>
> How can I start a "read transaction" from BEGIN?
>
>
> I am using `journal_mode=WAL`.
>
> What I am trying to do:
>
> From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the
> same snapshot/point in time.
>
>
> The issue is that its hard to tell if I reading from a read snapshot (where
> any successful commit on other connections since the reads BEGIN are
> ignored).
>
> When is a read transaction started?
>
>
> As an example, connection A and B:
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
> run(B, "SELECT * FROM t1"); = 0 rows
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started
> ```
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
> run(B, "SELECT 1"); = 0 rows
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
> ```
>
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
>
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
> ```
>
>
>
> https://www.sqlite.org/isolation.html
>
> Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction"
>
> This is the only page where I can find a mention of the idea of "read
> transaction" and "write transaction".
>
>
> BEGIN IMMEDIATE allows the explicit start of a "write transaction".
>
> Does an API exist for a "read transaction"?
>
> Thanks
> _______________________________________________
> 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
>
_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

Simon Slavin-3
In reply to this post by test user
On 30 Jul 2019, at 6:44pm, test user <[hidden email]> wrote:

> I am using `journal_mode=WAL`.
>
> What I am trying to do:
>
> From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the same snapshot/point in time.

This is the effect if you use BEGIN IMMEDIATE instead of just BEGIN.  So do BEGIN IMMEDIATE, then as many SELECTs as you want, then END.  For the duration of that transaction, all your SELECTs will reflect the same snapshot of the database.

> The issue is that its hard to tell if I reading from a read snapshot (where any successful commit on other connections since the reads BEGIN are ignored).

Depending on various things, other connections trying to change the database will be blocked (locked out of making changes), or will make changes that will not be 'seen' by the above transaction.
_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

Keith Medcalf
In reply to this post by test user

On Tuesday, 30 July, 2019 13:01, test user <[hidden email]> asked:

>How sure are you that "any SELECT that reads from the DB file starts
>a read transaction"?

Well, it is not that it starts a transaction so much as it acquires a shared lock.  You cannot read data from a database file without first having acquired a shared lock.

>Does the read transaction read from a snapshot of the entire DB, or
>are only specific tables in the read snapshot?

"Snapshot" is a misnomer.  

What you get is point-in-time repeatable read isolation.  Since the database can only be updated as a whole the repeatable-read applies to the entire database.  Technically, all "read transactions" are repeatable read isolation, since you cannot update the database while a shared (read) lock is held by another connection.  WAL simply permits a writer to proceed despite concurrently held shared locks by "holding off" updating of the actual database file until after the shared lock is released by writing the data to a "differences" file.

Behaviour may, of course, be different if you are using "simulated locking" such as shared-cache, which "simulates" locking at a table level amongst connections to the same shared-cache.  The single connection from the shared-cache to the database uses the normal "whole database file" locking and update semantics.

--
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: Explicit "read transaction" with journal_mode=WAL.

David Raymond
In reply to this post by test user
https://www.sqlite.org/wal.html#concurrency

"But for any particular reader, the end mark is unchanged for the duration of the transaction, thus ensuring that a single read transaction only sees the database content as it existed at a single point in time."

Read transactions see one version of the entire database for their whole transaction. They won't see any changes made by other transactions after they start.

I refer you again to the previously linked transactions page https://www.sqlite.org/lang_transaction.html
"begin;" or "begin deferred;" doesn't do anything until you next access a file, at which point it will get a lock on the file. So if you want to control specifically when your read transaction _effectively_ starts you have to actually read something from the file to start it, at which point you'll get your lock, get your end mark in the WAL, and effectively freeze your view of the database.

And a point that this is of course with an explicitly declared transaction started with a begin statement. If you're in autocommit mode then each of your selects is its own little transaction separate from any other statements before it or after it.


-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of test user
Sent: Tuesday, July 30, 2019 3:01 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

Thanks David,

`SELECT 1` = rows 0 was a mistake in the example.

How sure are you that "any SELECT that reads from the DB file starts a read
transaction"?

Does the read transaction read from a snapshot of the entire DB, or are
only specific tables in the read snapshot?






On Tue, Jul 30, 2019 at 7:14 PM David Raymond <[hidden email]>
wrote:

> To get the read lock you're going to need to read something from the
> database file.
>
> I think this page is your best bet:
> https://www.sqlite.org/lang_transaction.html
>
> "Transactions can be deferred, immediate, or exclusive. The default
> transaction behavior is deferred. 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. Because the acquisition of locks is
> deferred until they are needed, it is possible that another thread or
> process could create a separate transaction and write to the database after
> the BEGIN on the current thread has executed..."
>
> So after a "begin deferred" you have to actually do something that
> requires file access in order to get the shared lock/start your read
> snapshot on the file. So if you want to get that shared lock/read snapshot
> you can always do something like a select from sqlite_master. "select 1;"
> doesn't need to access the file to complete, so it doesn't take out the
> shared lock (though it should be returning 1 row, are you sure it's 0?)
>
>
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of test user
> Sent: Tuesday, July 30, 2019 1:45 PM
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL.
>
> Hello,
>
> How can I start a "read transaction" from BEGIN?
>
>
> I am using `journal_mode=WAL`.
>
> What I am trying to do:
>
> From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the
> same snapshot/point in time.
>
>
> The issue is that its hard to tell if I reading from a read snapshot (where
> any successful commit on other connections since the reads BEGIN are
> ignored).
>
> When is a read transaction started?
>
>
> As an example, connection A and B:
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
> run(B, "SELECT * FROM t1"); = 0 rows
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started
> ```
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
> run(B, "SELECT 1"); = 0 rows
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
> ```
>
>
> ```
> run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");
>
> run(B, "BEGIN");
>
>
> run(A, "BEGIN");
> run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)");
> run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)");
> run(A, "COMMIT");
>
> run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started
> ```
>
>
>
> https://www.sqlite.org/isolation.html
>
> Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction"
>
> This is the only page where I can find a mention of the idea of "read
> transaction" and "write transaction".
>
>
> BEGIN IMMEDIATE allows the explicit start of a "write transaction".
>
> Does an API exist for a "read transaction"?
>
> Thanks
> _______________________________________________
> 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
>
_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

test user
In reply to this post by Simon Slavin-3
Quote: "This is the effect if you use BEGIN IMMEDIATE instead of just BEGIN"

BEGIN IMMEDIATE will start a write transaction, which will block other
writers with SQLITE_BUSY until its complete.

What I would like is something like BEGIN READ, which will not block
writers for its duration.

This "read transaction" can see all committed transactions that happened
before it, but none after it.

At the moment it seems to get this guarantee I will need to do a "BEGIN;
SELECT * from sqlite_master LIMIT 1"

On Tue, Jul 30, 2019 at 8:23 PM Simon Slavin <[hidden email]> wrote:

> On 30 Jul 2019, at 6:44pm, test user <[hidden email]> wrote:
>
> > I am using `journal_mode=WAL`.
> >
> > What I am trying to do:
> >
> > From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from
> the same snapshot/point in time.
>
> This is the effect if you use BEGIN IMMEDIATE instead of just BEGIN.  So
> do BEGIN IMMEDIATE, then as many SELECTs as you want, then END.  For the
> duration of that transaction, all your SELECTs will reflect the same
> snapshot of the database.
>
> > The issue is that its hard to tell if I reading from a read snapshot
> (where any successful commit on other connections since the reads BEGIN are
> ignored).
>
> Depending on various things, other connections trying to change the
> database will be blocked (locked out of making changes), or will make
> changes that will not be 'seen' by the above transaction.
> _______________________________________________
> 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: Explicit "read transaction" with journal_mode=WAL.

Simon Slavin-3
On 30 Jul 2019, at 9:39pm, test user <[hidden email]> wrote:

> BEGIN IMMEDIATE will start a write transaction, which will block other writers with SQLITE_BUSY until its complete.

This does not apply to WAL mode.  You wrote that you were using WAL mode.

> What I would like is something like BEGIN READ

The statement 'BEGIN READ' does not exist in SQlite.

<https://www.sqlite.org/lang_transaction.html>

Simon.
_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

test user
The docs do not mention that it does not apply in WAL mode:

https://sqlite.org/lang_transaction.html#immediate
- "After a BEGIN IMMEDIATE, no other database connection will be able to
write to the database"

I tested it out against the API with WAL mode enabled, it seems a "BEGIN
IMMEDIATE" will block other writers, so it cannot be used as a "read
transaction":

```
run(A, "PRAGMA journal_mode=WAL");
run(B, "PRAGMA journal_mode=WAL");


run(A, "CREATE TABLE t1(a PRIMARY KEY, b);");


run(B, "BEGIN IMMEDIATE");

run(A, "BEGIN");
run(A, "INSERT INTO t1 (a, b VALUES (1, 2), (3, 4)")); // SQLITE_BUSY
run(A, "INSERT INTO t1 (a, b VALUES (5, 6), (7, 8)"));
run(A, "COMMIT");

run(B, "SELECT * FROM t1");
```

I could of made a mistake though. Do you have an example/docs reference?

BEGIN READ is semantically what I was trying to describe as what I was
trying to achieve, I understand its not in the language!


On Tue, Jul 30, 2019 at 9:43 PM Simon Slavin <[hidden email]> wrote:

> On 30 Jul 2019, at 9:39pm, test user <[hidden email]> wrote:
>
> > BEGIN IMMEDIATE will start a write transaction, which will block other
> writers with SQLITE_BUSY until its complete.
>
> This does not apply to WAL mode.  You wrote that you were using WAL mode.
>
> > What I would like is something like BEGIN READ
>
> The statement 'BEGIN READ' does not exist in SQlite.
>
> <https://www.sqlite.org/lang_transaction.html>
>
> Simon.
> _______________________________________________
> 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: Explicit "read transaction" with journal_mode=WAL.

Keith Medcalf
In reply to this post by Simon Slavin-3

On Tuesday, 30 July, 2019 14:43, Simon Slavin <[hidden email]> wrote:

>On 30 Jul 2019, at 9:39pm, test user <[hidden email]> wrote:

>> BEGIN IMMEDIATE will start a write transaction, which will block
>> other writers with SQLITE_BUSY until its complete.

> This does not apply to WAL mode.  You wrote that you were using WAL
> mode.

You are incorrect Simon.  BEGIN IMMEDIATE indicates that the transaction started on the connection will update the database by acquiring an intent (write) lock immediately.  No other connection will be able to obtain an intent (write) lock on the database -- it does not matter whether the journal mode is write-ahead or rollback (write-behind).  Other connections (in write-ahead-logging mode only) will be able to obtain SHARED (read) locks, but will not be able to obtain intent (write) locks.

Using BEGIN IMMEDIATE to obtain locks for read-only transactions will effectively eliminate all concurrent access to the database.

>> What I would like is something like BEGIN READ

>The statement 'BEGIN READ' does not exist in SQlite.

I think that is the point.  There is no way to immediately acquire a shared lock on the database.

--
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: Explicit "read transaction" with journal_mode=WAL.

Keith Medcalf
In reply to this post by test user

On Tuesday, 30 July, 2019 14:39, test user <[hidden email]> wrote:

>What I would like is something like BEGIN READ, which will not block
>writers for its duration.

I would think that adding a new lock type may be confusing and would prefer something like adding a SHARED or READ keyword after IMMEDIATE

BEGIN IMMEDIATE [SHARED|[UPDATE]] [TRANSACTION]

where the default is UPDATE if not specified.  This will have the least effect on backwards compatibility but still makes it obvious that you are requesting an immediate lock, just a SHARED/READ lock rather than an intent to update lock.  This syntax is closer to the SELECT ... FOR UPDATE OF ... used in some other SQL dialects to obtain specific intent locks at prepare time rather than depending on the ability to escalate the shared lock to intent on the retrieved table[/row] ...

--
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: Explicit "read transaction" with journal_mode=WAL.

Olivier Mascia
In reply to this post by test user
> Le 30 juil. 2019 à 22:39, test user <[hidden email]> a écrit :
>
> What I would like is something like BEGIN READ, which will not block
> writers for its duration.
>
> This "read transaction" can see all committed transactions that happened
> before it, but none after it.
>
> At the moment it seems to get this guarantee I will need to do a "BEGIN;
> SELECT * from sqlite_master LIMIT 1"

I would drop the "SELECT * from sqlite_master LIMIT 1" or whatever other dummy read.  As long as your explicit transaction, started with "BEGIN" which is a shortcut for "BEGIN DEFERRED", does not actually start reading anything, what importance does it have for you that it already holds an "end-mark"? (See link below for reference to the "end mark" concept).  As soon as it reads anything, it will see the database from that point on, until ROLLBACK or COMMIT, as it was at point of that single first read.

https://www.sqlite.org/wal.html#concurrency


> Le 30 juil. 2019 à 22:42, Simon Slavin <[hidden email]> a écrit :
>
>> BEGIN IMMEDIATE will start a write transaction, which will block other writers with SQLITE_BUSY until its complete.
>
> This does not apply to WAL mode.  You wrote that you were using WAL mode.

Yes it applies to WAL mode.

BEGIN IMMEDIATE will anyway get a RESERVED lock immediately and will block other writers from that point in time. Though in WAL mode, it won't block a new reader to start reading and see the database content as it existed before the BEGIN IMMEDIATE transaction was started. That view will still be stable after the writer COMMIT.

The catch here is that a BEGIN (DEFERRED) does not restrict to read operations. Such a transaction might at some point attempt to write to the database. If it does, it will fail if:
- another writer is still busy
- another writer has already COMMITed changes

The easiest way to program using WAL mode is probably to BEGIN when you want a read transaction and drill your code not to attempt any write while inside such a virtual read transaction.  And always do a BEGIN IMMEDIATE upfront when you want to start a write transaction.  If your write transactions are short-lived, you will get excellent concurrency (readers always free to start or continue running) with minimal wait time in the busy handler when executing BEGIN IMMEDIATE.  Even PASSIVE checkpointing will run free (albeit not as completely) with readers ongoing.


Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia


_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

Olivier Mascia
In reply to this post by Keith Medcalf
> Le 30 juil. 2019 à 23:19, Keith Medcalf <[hidden email]> a écrit :
>
> I would think that adding a new lock type may be confusing and would prefer something like adding a SHARED or READ keyword after IMMEDIATE
>
> BEGIN IMMEDIATE [SHARED|[UPDATE]] [TRANSACTION]
>
> where the default is UPDATE if not specified.  This will have the least effect on backwards compatibility but still makes it obvious that you are requesting an immediate lock, just a SHARED/READ lock rather than an intent to update lock.

Keith, in the context of WAL mode, I fail to see why it would be beneficial to obtain any lock immediately, when the transaction being setup using BEGIN (DEFERRED) is intended to only read.  Until it actually has started to read (which it will always be able to do), why would it matter that a writer did write and commit between the "reader" BEGIN and its first read?  What do I miss here?

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia
https://www.integral.be


_______________________________________________
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: Explicit "read transaction" with journal_mode=WAL.

Keith Medcalf

On Tuesday, 30 July, 2019 15:40, Olivier Mascia <[hidden email]> wrote:

> Keith, in the context of WAL mode, I fail to see why it would be
> beneficial to obtain any lock immediately, when the transaction being
> setup using BEGIN (DEFERRED) is intended to only read.  Until it
> actually has started to read (which it will always be able to do),
> why would it matter that a writer did write and commit between the
> "reader" BEGIN and its first read?  What do I miss here?

BEGIN [[DEFERRED] [TRANSACTION]] does not acquire any locks when it is executed.  The locks are acquired by the first statement thereafter that requires some locks (be they a shared lock as in a SELECT statement or shared and intent locks if the statement is an UPDATE statement)  This, of course, means that any statement subsequent to the begin may fail because if cannot acquire the locks it requires -- even the first SELECT statement if some other connection currently holds an EXCLUSIVE lock (such as during a commit or after the commencement of a cache spill by another connection when not in WAL journal mode).

BEGIN IMMEDIATE [TRANSACTION] acquires the shared and intent locks immediately and if it succeeds, then the following statements will also succeed (up until the commit or an update statement which has to spill the cache since when in regular journal mode a lock escalation to exclusive is still required to write the actual database, and a concurrent shared lock will prohibit that -- though in WAL mode the commit will not fail either).

Theoretically, a BEGIN IMMEDIATE SHARED TRANSACTION would guarantee that subsequent SELECT statements cannot fail because a shared lock cannot be acquired, since that lock will already be held.  

I do not personally see the benefit of moving the repeatable read guarantee to the BEGIN point rather than the first database read after the BEGIN because only fully committed transactions will be visible anyway -- and if there are dependencies between transactions (that is it takes multiple transactions to validly mutate database state) then the problem is that the transaction design is flawed, and not that the repeatable-read is being commenced at the wrong time.

I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode though.  I will grant that there may be cases where it might be useful in WAL mode, even though I cannot think of any.

--
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: Explicit "read transaction" with journal_mode=WAL.

Olivier Mascia
> Le 31 juil. 2019 à 00:22, Keith Medcalf <[hidden email]> a écrit :
>
> I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode though.  I will grant that there may be cases where it might be useful in WAL mode, even though I cannot think of any.

Fully agree.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia

_______________________________________________
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: [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

Richard Damon
In reply to this post by Olivier Mascia
On 7/30/19 5:33 PM, Olivier Mascia wrote:

>> Le 30 juil. 2019 à 22:39, test user <[hidden email]> a écrit :
>>
>> What I would like is something like BEGIN READ, which will not block
>> writers for its duration.
>>
>> This "read transaction" can see all committed transactions that happened
>> before it, but none after it.
>>
>> At the moment it seems to get this guarantee I will need to do a "BEGIN;
>> SELECT * from sqlite_master LIMIT 1"
> I would drop the "SELECT * from sqlite_master LIMIT 1" or whatever other dummy read.  As long as your explicit transaction, started with "BEGIN" which is a shortcut for "BEGIN DEFERRED", does not actually start reading anything, what importance does it have for you that it already holds an "end-mark"? (See link below for reference to the "end mark" concept).  As soon as it reads anything, it will see the database from that point on, until ROLLBACK or COMMIT, as it was at point of that single first read.
>
> https://www.sqlite.org/wal.html#concurrency
IF the only interaction the program has with other parts is via the
database, then yes, it shouldn't be important exactly when the program
'locks' it view of the database. On the other hand, if there might be
other communication channels open, then it might be important that the
program has at a particular point before it really needs to access the
database that it establish its 'end-mark'. Doing the SELECT is one way
to do that, adding some variation of BEGIN that immediately starts a
SHARED lock would be another.

--
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: Explicit "read transaction" with journal_mode=WAL.

test user
In reply to this post by Olivier Mascia
Quote: What importance does it have for you that it already holds an
"end-mark?
Quote: Why would it matter that a writer did write and commit between the
"reader" BEGIN and its first read?

Im writing a library and would like to have an API where the "read
transaction" has a clear beginning in time.

BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart
for a "read transaction".

As an example, the client of this library could:

- A. Obtain a "read transaction", *without running any SELECTs*.
- B. Complete 20 write transactions in another process.
- C. Begin reading from the read transaction (A) at the point before the
transactions had occurred.


At the moment, a "read transaction" is only started on the first SELECT.

If a client tries to start a "read transaction" with BEGIN, and that
returns SQLITE_OK, its not clear that this has not actually begun any
transaction until the first SELECT query.

This would enable an API like:

const r = await db.startReadTx();
const w = await db.startWriteTx();

// At this point in the runtime it clear when the transactions have begun,
and how they will impact other concurrent read/write transactions.



On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia <[hidden email]> wrote:

> > Le 31 juil. 2019 à 00:22, Keith Medcalf <[hidden email]> a écrit :
> >
> > I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode
> though.  I will grant that there may be cases where it might be useful in
> WAL mode, even though I cannot think of any.
>
> Fully agree.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
> _______________________________________________
> 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: Explicit "read transaction" with journal_mode=WAL.

Simon Slavin-3
On 31 Jul 2019, at 11:36am, test user <[hidden email]> wrote:

> BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart for a "read transaction".

The difference is that the connection can write as well as read. So it is a lock for reading.
_______________________________________________
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: [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

Richard Damon
In reply to this post by test user
As has been pointed out, your function that is called can do the BEGIN
and then a SELECT that hits the database to force the obtaining of the
read lock. The fact that the BEGIN didn't get the lock is then not seen
by the users of your API.

IF at some point a new BEGIN SHARED IMMEDIATE operation becomes
available, you can change you function's internals, and the caller
doesn't know the difference except that the call got perhaps a bit faster.

On 7/31/19 6:36 AM, test user wrote:

> Quote: What importance does it have for you that it already holds an
> "end-mark?
> Quote: Why would it matter that a writer did write and commit between the
> "reader" BEGIN and its first read?
>
> Im writing a library and would like to have an API where the "read
> transaction" has a clear beginning in time.
>
> BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart
> for a "read transaction".
>
> As an example, the client of this library could:
>
> - A. Obtain a "read transaction", *without running any SELECTs*.
> - B. Complete 20 write transactions in another process.
> - C. Begin reading from the read transaction (A) at the point before the
> transactions had occurred.
>
>
> At the moment, a "read transaction" is only started on the first SELECT.
>
> If a client tries to start a "read transaction" with BEGIN, and that
> returns SQLITE_OK, its not clear that this has not actually begun any
> transaction until the first SELECT query.
>
> This would enable an API like:
>
> const r = await db.startReadTx();
> const w = await db.startWriteTx();
>
> // At this point in the runtime it clear when the transactions have begun,
> and how they will impact other concurrent read/write transactions.
>
>
>
> On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia <[hidden email]> wrote:
>
>>> Le 31 juil. 2019 à 00:22, Keith Medcalf <[hidden email]> a écrit :
>>>
>>> I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL mode
>> though.  I will grant that there may be cases where it might be useful in
>> WAL mode, even though I cannot think of any.
>>
>> Fully agree.
>>
>> —
>> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
>> Grüßen,
>> Olivier Mascia
>>
>> _______________________________________________
>> 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


--
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: [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

test user
Yeh I think this is the way to go currently. I just wanted to be sure I
understood it correctly and that it doesn’t exist already.

Is there a standard place where people can request features to be added to
SQLite? The Fossil repo perhaps?



On Wed, 31 Jul 2019 at 12:53, Richard Damon <[hidden email]>
wrote:

> As has been pointed out, your function that is called can do the BEGIN
> and then a SELECT that hits the database to force the obtaining of the
> read lock. The fact that the BEGIN didn't get the lock is then not seen
> by the users of your API.
>
> IF at some point a new BEGIN SHARED IMMEDIATE operation becomes
> available, you can change you function's internals, and the caller
> doesn't know the difference except that the call got perhaps a bit faster.
>
> On 7/31/19 6:36 AM, test user wrote:
> > Quote: What importance does it have for you that it already holds an
> > "end-mark?
> > Quote: Why would it matter that a writer did write and commit between the
> > "reader" BEGIN and its first read?
> >
> > Im writing a library and would like to have an API where the "read
> > transaction" has a clear beginning in time.
> >
> > BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart
> > for a "read transaction".
> >
> > As an example, the client of this library could:
> >
> > - A. Obtain a "read transaction", *without running any SELECTs*.
> > - B. Complete 20 write transactions in another process.
> > - C. Begin reading from the read transaction (A) at the point before the
> > transactions had occurred.
> >
> >
> > At the moment, a "read transaction" is only started on the first SELECT.
> >
> > If a client tries to start a "read transaction" with BEGIN, and that
> > returns SQLITE_OK, its not clear that this has not actually begun any
> > transaction until the first SELECT query.
> >
> > This would enable an API like:
> >
> > const r = await db.startReadTx();
> > const w = await db.startWriteTx();
> >
> > // At this point in the runtime it clear when the transactions have
> begun,
> > and how they will impact other concurrent read/write transactions.
> >
> >
> >
> > On Tue, Jul 30, 2019 at 11:40 PM Olivier Mascia <[hidden email]> wrote:
> >
> >>> Le 31 juil. 2019 à 00:22, Keith Medcalf <[hidden email]> a écrit
> :
> >>>
> >>> I can see where a BEGIN IMMEDIATE SHARED would be useful in non-WAL
> mode
> >> though.  I will grant that there may be cases where it might be useful
> in
> >> WAL mode, even though I cannot think of any.
> >>
> >> Fully agree.
> >>
> >> —
> >> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit
> besten
> >> Grüßen,
> >> Olivier Mascia
> >>
> >> _______________________________________________
> >> 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
>
>
> --
> 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
12