Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Jean-Christophe Deschamps]

Simon,

Consider a bank which takes an audit every day at 
close-of-business.  This might be declared to be 5pm.  However, 
accounts are continued to be debited and credited all night, due to 
interest being added, ATM transactions, etc..  Nevertheless, the audit 
needs to see a snapshot as of 5pm.


Of course, no bank would be using SQLite for this purpose, because a 
bank would be using a server/client DBMS.  But you get the idea.


I don't believe this can be any close to a real-world scenario, 
client/server architecture set aside.


Being able to issue and process a BEGIN SHARED IMMEDIATE for the read 
lock be in place at exactly 5pm, without ever missing a transaction 
performed from elsewhere at 04:59:59:999.99 nor including a transaction 
commited at 05:00:00:0.001 seems to be an impossible task in practice.


One may find it uncomfortable to ignore the delay between BEGIN is 
issued and when the next SELECT gets the lock set, but in fact you 
would never know either the delay between your program issuing BEGIN 
SHARED IMMEDIATE and the precise moment the lock is actually setup, 
unless under a low-load real-time OS providing explicit garantees on 
various exec times.  And even there, I'm not that sure.


From my remote/naive viewpoint, this is a misuse of a RDBMS relying on 
DIY.  When you want/need to be sure what you are going to read is ante 
 the only serious way is to include a precise enough 
timestamp in data rows and limit the select using it.


Indeed if you would want to do that and if you need to be just on time, 
you would rather use rock-solid:


select  from  ... where timestamp between 
 and 


I still fail to imagine a useful use case for such feature.

As Igor shown, A==B and A!=B are indiscernable.

Further in the thread the argument of "symetry" between BEGIN SHARED 
IMMEDIATE and BEGIN IMMEDIATE is only a surface view, because the arrow 
of time is one-way.  A real symetry would be a BEGIN IMMEDIATE TO BE 
COMMITED BEFORE  but that clearly doesn't make any sense.


JcD

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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[test user]

Quote: Connection 2 just happened to write lots of data and commit before
connection 1 obtained a read transaction
Quote: if SELECT on Connection 1 just happens to beat the write on
Connection 2

- The order in the example is exact, not a guess what might happen.
- Each step runs in a single thread on an event loop and is awaited, and
assumed to return with OK.
- The read transaction at the start is definite (supposing BEGIN READ
existing).


All I am saying is on "Connection 1: Get read transaction" could be:

BEGIN READ

NOT

BEGIN; SELECT * FROM some_table;


Quote: Why again do you care how BEGIN behaves

Im just suggesting that if you can obtain a write transaction with a single
trip over the FFI and an explicit command "BEGIN IMMEDIATE" the same could
be true for "read transactions"

I care because I think its a better API design which would then allow
better higher level libraries.






On Wed, Jul 31, 2019 at 9:46 PM Igor Tandetnik  wrote:

> On 7/31/2019 12:32 PM, test user wrote:
> > In some runtimes, the scheduling of functions is unpredictable, so
> although
> > you will not have a `sleep 5` in the code, the runtime can produce this
> > effect on loaded systems or with programs with long running sync
> functions.
> >
> >
> > An example of how you might use this:
> > - Connection 1: Get a read transaction.
> >
> > - Connection 2: Get a write transaction, write a lot of data, commit.
> > - Connection 2: SELECT report summary B.
> >
> > - Connection 1: SELECT report summary A,
> >
> > - Diff A and B to see what changed.
>
> Suppose you discovered that B and A are in fact the same. How do you know
> whether that occurred because a) "get a read transaction" is "broken" in
> that it doesn't actually acquire the lock as you expected, or because b)
> Connection 2 just happened to write lots of data and commit before
> connection 1 obtained a read transaction?
>
> In other words, in your example A == B is possible even if BEGIN worked
> the way you expect it to work, and grabbed a read lock immediately.
> Similarly, A != B is possible with BEGIN working the way it does now, if
> SELECT on Connection 1 just happens to beat the write on Connection 2. It's
> a matter of timing and scheduling, which you yourself posit is
> unpredictable.
>
> So, since both A==B and A!=B are possible with either behavior of BEGIN,
> why again do you care how BEGIN behaves?
> --
> Igor Tandetnik
>
> ___
> 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] Explicit "read transaction" with journal_mode=WAL.

[Igor Tandetnik]

On 7/31/2019 12:32 PM, test user wrote:

In some runtimes, the scheduling of functions is unpredictable, so although
you will not have a `sleep 5` in the code, the runtime can produce this
effect on loaded systems or with programs with long running sync functions.


An example of how you might use this:
- Connection 1: Get a read transaction.

- Connection 2: Get a write transaction, write a lot of data, commit.
- Connection 2: SELECT report summary B.

- Connection 1: SELECT report summary A,

- Diff A and B to see what changed.


Suppose you discovered that B and A are in fact the same. How do you know whether that occurred 
because a) "get a read transaction" is "broken" in that it doesn't actually 
acquire the lock as you expected, or because b) Connection 2 just happened to write lots of data 
and commit before connection 1 obtained a read transaction?

In other words, in your example A == B is possible even if BEGIN worked the way 
you expect it to work, and grabbed a read lock immediately. Similarly, A != B 
is possible with BEGIN working the way it does now, if SELECT on Connection 1 
just happens to beat the write on Connection 2. It's a matter of timing and 
scheduling, which you yourself posit is unpredictable.

So, since both A==B and A!=B are possible with either behavior of BEGIN, why 
again do you care how BEGIN behaves?
--
Igor Tandetnik

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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Olivier Mascia]

> Le 31 juil. 2019 à 18:53, Keith Medcalf  a écrit :
> 
> I believe the idea would be to permit syntax something like:
> 
> BEGIN IMMEDIATE [SHARED|[UPDATE]] [TRANSACTION]

Keith, I mostly share your view and I like the fact that the proposal uses 
SHARED and not READ or anything like that because the goal is not to propose 
some "read-only transaction".

Yet, I fail to understand why:

1) you wrote it in the above way with [SHARED|[UPDATE]] syntax instead of:

> BEGIN IMMEDIATE [SHARED|UPDATE] [TRANSACTION]

and 2) why it would even matter to make provision for the optional UPDATE token.

BEGIN IMMEDIATE [TRANSACTION]
is the current semantic (immediately get RESERVED lock, as a first *write* 
would do within a DEFERRED TRANSACTION)

BEGIN IMMEDIATE SHARED [TRANSACTION]
would be the new semantic (immediately get SHARED lock, as a first *read* would 
do within a DEFERRED TRANSACTION)

Forgetting the UPDATE sugar might make the syntax change simpler.
The key issue around this discussion, is probably that it's a syntactic and 
semantic change that would not be available in older versions.

Applications which have proxy code to begin transactions can very easily 
attempt some:
SELECT ROWID FROM SQLITE_MASTER LIMIT 0
right after successfully running BEGIN [DEFERRED] [TRANSACTION]
to emulate that IMMEDIATE SHARED feature.

Oh by the way, the syntax might be the following alternative, closer to the 
current syntax diagrams of SQLite.  I don't know if syntax compatibility 
provisions with other SQL implementations (and some of its 'standards') would 
dictate yours or this one.

BEGIN [DEFERRED|SHARED|IMMEDIATE|EXCLUSIVE] [TRANSACTION]

In the end, it surely is not a very necessary feature.
:)
—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten 
Grüßen,
Olivier Mascia


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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Richard Hipp]

On 7/31/19, Thomas Kurz  wrote:
> Would it be possible for you to give some feedback (just an estimation)
> whether or not a suggestion might be considered?

Low probability at this time.

The suggestion does not provide any new capability, but it is
something that we would need to test and maintain for the next 31
years.  So it has a high cost and low benefit.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Keith Medcalf]

On Wednesday, 31 July, 2019 10:21, Simon Slavin  wrote:

>But I think the BEGIN command can be retrofitted without breaking
>backward compatibility.  The words WRITE and IMMEDIATE should be seen
>as options.  WRITE means that you want a write lock as well as a read
>lock.  IMMEDIATE means that you want it now, rather than when the
>first command of the transaction requires a lock.  Use neither,
>either, or both.  And EXCLUSIVE gets parsed as IMMEDIATE WRITE.  Thus
>existing programs continue to do the same thing they always did.

BEGIN [DEFERRED] [TRANSACTION] is the current default and means defer acquiring 
locks until they are required.
BEGIN IMMEDIATE [TRANSACTION] is currently implemented and acquires a SHARED 
and an INTENT lock immediately.
BEGIN EXCLUSIVE [TRANSACTION] is currently implemented and acquires an 
EXCLUSIVE lock immediately.

I believe the idea would be to permit syntax something like:

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

which would allow the specification of the type of lock to immediately acquire, 
either a simple shared lock, or the shared and intent locks as is currently 
done.  The only change would then be that BEGIN IMMEDIATE SHARED [TRANSACTION] 
would immediately acquire a shared lock.  All else would remain unchanged.

-- 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[test user]

Quote: Why would that distinction matter to it in the first place?

So its clear in the API what lock you have at what time.

This would make predicting what happens in concurrent scenarios much easier.

An explicit "read transaction" is a single line of a program.

With an implicit "read transaction", you must know the semantics of SQLite
locking, and look to see where your first SELECT returns SQLITE_OK.


With read transaction:
```
c1.startRead((tx)=>{ // Issues a BEGIN READ
// X.
sleep(5 seconds);
// SELECT... still the same snapshot from point X being read.
});
```


*No* explicit read transaction:
```
c1.startRead((tx)=>{ // Just issues a normal BEGIN
// X.
sleep(5 seconds);
// SELECT What ever was written in the last 5 seconds will be in
the result set, which is unexpected.
});
```


With a explicit "read transaction" the programmer can assume that whenever
`startRead` returns to the runtime the snapshot is guaranteed (it does not
matter how long it takes to issue the first SELECT).


In some runtimes, the scheduling of functions is unpredictable, so although
you will not have a `sleep 5` in the code, the runtime can produce this
effect on loaded systems or with programs with long running sync functions.


An example of how you might use this:
- Connection 1: Get a read transaction.

- Connection 2: Get a write transaction, write a lot of data, commit.
- Connection 2: SELECT report summary B.

- Connection 1: SELECT report summary A,

- Diff A and B to see what changed.

On Wed, Jul 31, 2019 at 3:24 PM Igor Tandetnik  wrote:

> On 7/31/2019 6:36 AM, test user wrote:
> > 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.
>
> In the current world, that client would execute BEGIN, then wait a bit,
> then start reading and discover the data written by another process. How
> does it know that those writes occurred between BEGIN and SELECT, and not
> before BEGIN? Why would that distinction matter to it in the first place?
>
> Do you envision some other channel of communication and synchronization
> between these two processes, outside the SQLite database, that would help
> establish that writes occurred after BEGIN? With SQLite alone, it's
> impossible to tell whether the sequence of events was A-B-C or B-A-C - both
> sequences produce the exact same observable behavior. So guarding against B
> squeezing between A and C seems rather pointless.
> --
> Igor Tandetnik
>
> ___
> 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] Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 31 Jul 2019, at 5:04pm, Larry Brasfield  wrote:

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

I can imagine two programs, or two processes, might be communicating in some 
other way in addition to passing data through a SQLite database.  So they need 
synchrony.

Consider a bank which takes an audit every day at close-of-business.  This 
might be declared to be 5pm.  However, accounts are continued to be debited and 
credited all night, due to interest being added, ATM transactions, etc..  
Nevertheless, the audit needs to see a snapshot as of 5pm.

Of course, no bank would be using SQLite for this purpose, because a bank would 
be using a server/client DBMS.  But you get the idea.

But I think the BEGIN command can be retrofitted without breaking backward 
compatibility.  The words WRITE and IMMEDIATE should be seen as options.  WRITE 
means that you want a write lock as well as a read lock.  IMMEDIATE means that 
you want it now, rather than when the first command of the transaction requires 
a lock.  Use neither, either, or both.  And EXCLUSIVE gets parsed as IMMEDIATE 
WRITE.  Thus existing programs continue to do the same thing they always did.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Larry Brasfield]

Keith writes:

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

Improved modularity would be facilitated by making it possible to actually 
begin a transaction immediately rather than having it be deferred until the 
associated DB access occurs.  Granted, when the sequence of events (BEGIN …, 
SELECT …, … TRANSACTION)  is viewed as a whole, deferring the guarantee makes 
only a slight difference to the work that must (or should) be done by the 
application.  But the deferral changes where checking must be done for actually 
obtaining the guarantee.  Presently, that checking has to be done in the code 
which makes the queries (or updates, inserts, etc.)  And that checking, which 
is needed on the first access statement only, is probably done with different 
requirements for handling the contention error than pertain to following 
statements.

Pseudo-code examples may clarify my point.

Now:
  Begin transaction;
  if ( failed( do first access ) ) {
Handle contention error or more unusual errors;
Rollback transaction;
  }
  else {
if ( failed ( do subsequent accesses ) ) {
  Handle the rare and arcane access errors;
  Rollback transaction;
}
else {
  Commit transaction;
}
  }

With guarantee moved up to BEGIN:
  If ( failed( begin transaction ) ) {
Handle contention error;
  }
  else {
if ( failed ( do must-be-grouped accesses ) ) {
  Handle the rare and arcane access errors;
  Rollback transaction;
}
else {
  Commit transaction;
}
  }

When using C++, C# or another language with object lifetime guarantees, I might 
wrap the contention checking, waiting, etc., into a Transaction class which 
bundled the transaction mechanics with (some of) the error handling a 
contention failure entails.  The client code would read something like:
  using (var t = new Transaction(dbConnection) ) {
if ( failed ( do must-be-grouped accesses ) ) {
  Handle access errors
  // t does the rollback when it goes out of scope.
}
else {
  t.Commit();
}
  }
This grouping of handling for different error categories is made much less 
convenient by the present deferral of acquiring the necessary lock(s).

Best regards,
-
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Igor Tandetnik]

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

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.


In the current world, that client would execute BEGIN, then wait a bit, then 
start reading and discover the data written by another process. How does it 
know that those writes occurred between BEGIN and SELECT, and not before BEGIN? 
Why would that distinction matter to it in the first place?

Do you envision some other channel of communication and synchronization between 
these two processes, outside the SQLite database, that would help establish 
that writes occurred after BEGIN? With SQLite alone, it's impossible to tell 
whether the sequence of events was A-B-C or B-A-C - both sequences produce the 
exact same observable behavior. So guarding against B squeezing between A and C 
seems rather pointless.
--
Igor Tandetnik

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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 31 Jul 2019, at 11:36am, test user  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[test user]

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

> > Le 31 juil. 2019 à 00:22, Keith Medcalf  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
> 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] Explicit "read transaction" with journal_mode=WAL.

[Olivier Mascia]

> Le 31 juil. 2019 à 00:22, Keith Medcalf  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Keith Medcalf]

On Tuesday, 30 July, 2019 15:40, Olivier Mascia  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Olivier Mascia]

> Le 30 juil. 2019 à 23:19, Keith Medcalf  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Olivier Mascia]

> Le 30 juil. 2019 à 22:39, test user  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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Keith Medcalf]

On Tuesday, 30 July, 2019 14:39, test user  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Keith Medcalf]

On Tuesday, 30 July, 2019 14:43, Simon Slavin  wrote:

>On 30 Jul 2019, at 9:39pm, test user  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 30 Jul 2019, at 9:39pm, test user  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.
>
> 
>
> Simon.
> ___
> 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] Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 30 Jul 2019, at 9:39pm, test user  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.



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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[test user]

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

> On 30 Jul 2019, at 6:44pm, test user  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
> 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] Explicit "read transaction" with journal_mode=WAL.

[David Raymond]

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  On Behalf Of 
test user
Sent: Tuesday, July 30, 2019 3:01 PM
To: SQLite mailing list 
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 
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  On
> Behalf Of test user
> Sent: Tuesday, July 30, 2019 1:45 PM
> To: SQLite mailing list 
> 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
> ```
>
>
> ```
&g

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Keith Medcalf]

On Tuesday, 30 July, 2019 13:01, test user  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Simon Slavin]

On 30 Jul 2019, at 6:44pm, test user  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
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  On
> Behalf Of test user
> Sent: Tuesday, July 30, 2019 1:45 PM
> To: SQLite mailing list 
> 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
> 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

Re: [sqlite] 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  On Behalf Of 
test user
Sent: Tuesday, July 30, 2019 1:45 PM
To: SQLite mailing list 
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
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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users