Please explain multi-access behaviour to me

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

Please explain multi-access behaviour to me

Simon Slavin-3
Can someone please explain this error message to me:

Simple database, journal mode set to 'delete', accessed by two simultaneous sessions running the SQLite command-line shell,
SQLite version 3.28.0 2019-04-15 14:49:49

Session A:

PRAGMA journal_mode;             <-- says 'delete'
CREATE TABLE Test (a TEXT);
INSERT INTO Test (a) VALUES "first";
BEGIN;
SELECT * FROM Test;             <-- first

Session B:

BEGIN;
INSERT INTO Test (a) VALUES ('second');
SELECT * FROM Test;             <-- first / second

Session A:

SELECT * FROM Test;             <-- first

Session B:

END;             <-- Error: database is locked

Is session B complaining that session A has a lock ?  If session A had a lock why was there no complaint for the INSERT ?  If session B had a lock why didn't session A get a complaint when it did the SELECT * ?
_______________________________________________
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: Please explain multi-access behaviour to me

Igor Tandetnik-2
On 6/17/2019 7:49 PM, Simon Slavin wrote:
> Is session B complaining that session A has a lock ?

Yes.

> If session A had a lock why was there no complaint for the INSERT ?

A connection doesn't need to check locks on every statement - only when it tries to spill to disk, most commonly during commit.

See https://sqlite.org/lockingv3.html . Specifically, Session A holds a SHARED lock. Session B holds a RESERVED lock up until the time you ask it to commit, at which point it tries and fails to promote it to EXCLUSIVE.
--
Igor Tandetnik

_______________________________________________
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: Please explain multi-access behaviour to me

Simon Slavin-3
On 18 Jun 2019, at 1:09am, Igor Tandetnik <[hidden email]> wrote:

> A connection doesn't need to check locks on every statement - only when it tries to spill to disk, most commonly during commit.

I think I understand what you wrote.

So the bit of my program can think that its changes were written to the database and only later might my program find that they weren't ?  By which time it might no longer know what they were ?

Should I have used BEGIN EXCLUSIVE ?
_______________________________________________
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: Please explain multi-access behaviour to me

Igor Tandetnik-2
On 6/17/2019 8:21 PM, Simon Slavin wrote:
> On 18 Jun 2019, at 1:09am, Igor Tandetnik <[hidden email]> wrote:
>
>> A connection doesn't need to check locks on every statement - only when it tries to spill to disk, most commonly during commit.
>
> I think I understand what you wrote.
>
> So the bit of my program can think that its changes were written to the database and only later might my program find that they weren't ?

Why would it think that, when it didn't successfully commit the transaction?

> Should I have used BEGIN EXCLUSIVE ?

If that's what your application's logic calls for, then yes, sure. This way, you'll get an error on BEGIN EXCLUSIVE statement. Note that, once BEGIN EXCLUSIVE succeeds, all readers will be locked out until the write transaction commits or rolls back, thus reducing the concurrency.
--
Igor Tandetnik

_______________________________________________
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: Please explain multi-access behaviour to me

Keith Medcalf
In reply to this post by Simon Slavin-3
On Monday, 17 June, 2019 17:50, Simon Slavin <[hidden email]> wrote:

>Can someone please explain this error message to me:
>
>Simple database, journal mode set to 'delete', accessed by two
>simultaneous sessions running the SQLite command-line shell,
>SQLite version 3.28.0 2019-04-15 14:49:49
>
>Session A:
>
>PRAGMA journal_mode;             <-- says 'delete'
>CREATE TABLE Test (a TEXT);
>INSERT INTO Test (a) VALUES "first";
>BEGIN;
>SELECT * FROM Test;             <-- first
>
>Session B:
>
>BEGIN;
>INSERT INTO Test (a) VALUES ('second');
>SELECT * FROM Test;             <-- first / second
>
>Session A:
>
>SELECT * FROM Test;             <-- first
>
>Session B:
>
>END;             <-- Error: database is locked
>
>Is session B complaining that session A has a lock ?  

Yes.  In order to commit the transaction it must be able to escalate EXCLUSIVE (write) lock, meaning that all readers must "be out of town".  However, session A has a lock (it is hanging about in town) thus preventing the transaction from committing.  journal_mode=delete permits multiple simultaneous readers but only ONE WRITER.

>If session A had a lock why was there no complaint for the INSERT ?

Because at the time session A needed the EXCLUSIVE lock in order to write to the database, session B did not yet have any locks at all.  When the INSERT command was executed in AUTOCOMMIT mode, it obtained the necessary INTENT lock immediately, escalated it to EXCLUSIVE when the implicit transaction committed at the end of the statement execution, and released all locks on the database.  Session B was merely idling outside city limits when this occurred thus not interfering with the activities of session A.

>If session B had a lock why didn't session A get a complaint when it did the
>SELECT * ?

At that particular instant in time both session A and session B held SHARED locks on the database.  That means that both sessions can READ from the database.  It is not until session B attempts to commit its transaction that all other readers need to "be out of town".

--
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: Please explain multi-access behaviour to me

Keith Medcalf
In reply to this post by Igor Tandetnik-2

If you intend to update the database (ie, write to it) during a transaction, you should begin that transaction with BEGIN IMMEDIATE;  This signals that you intend to WRITE to the database in that transaction and will prevent any other connection from obtaining an INTENT (to write) lock.

BEGIN IMMEDIATE means that this transaction intends to update the database.  If it is successful, then this transaction will (eventually) be able to commit successfully.  Other drivers are permitted to enter town for the purpose of reading the signs but they will be prevented from signaling intent to update (that is, they may drive into town but they may not change anything in town, and it they attempt to do so they will be denied).

BEGIN EXCLUSIVE means that when that statement is successful EVERYONE ELSE is parked out of town and all the traffic signals are RED prohibiting them from entering town for any reason whatsoever until you commit (or rollback) the EXCLUSIVE transaction.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Igor Tandetnik
>Sent: Monday, 17 June, 2019 18:28
>To: [hidden email]
>Subject: Re: [sqlite] Please explain multi-access behaviour to me
>
>On 6/17/2019 8:21 PM, Simon Slavin wrote:
>> On 18 Jun 2019, at 1:09am, Igor Tandetnik <[hidden email]>
>wrote:
>>
>>> A connection doesn't need to check locks on every statement - only
>when it tries to spill to disk, most commonly during commit.
>>
>> I think I understand what you wrote.
>>
>> So the bit of my program can think that its changes were written to
>the database and only later might my program find that they weren't ?
>
>Why would it think that, when it didn't successfully commit the
>transaction?
>
>> Should I have used BEGIN EXCLUSIVE ?
>
>If that's what your application's logic calls for, then yes, sure.
>This way, you'll get an error on BEGIN EXCLUSIVE statement. Note
>that, once BEGIN EXCLUSIVE succeeds, all readers will be locked out
>until the write transaction commits or rolls back, thus reducing the
>concurrency.
>--
>Igor Tandetnik
>
>_______________________________________________
>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: Please explain multi-access behaviour to me

Simon Slavin-3
I think I see my error.  I thought that the lock was promoted from read to read/write when the INSERT command was processed.  At this point, SQLite knows that it is going to need to write.

Instead, although SQLite knows that it is going to have to write, it does not try to promote the lock until COMMIT.

In some ways I can appreciate this: it allows other threads to finish their work.  But in others it seems wrong, as if SQLite is ignoring a potential problem, in a way that may make it an even bigger problem in the future.

Thanks to everyone for the responses.
_______________________________________________
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: Please explain multi-access behaviour to me

Andy Bennett
In reply to this post by Simon Slavin-3
Hi,

>> A connection doesn't need to check locks on every statement -
>> only when it tries to spill to disk, most commonly during
>> commit.
>
> I think I understand what you wrote.
>
> So the bit of my program can think that its changes were
> written to the database and only later might my program find
> that they weren't ?  By which time it might no longer know what
> they were ?
>
> Should I have used BEGIN EXCLUSIVE ?

The part of your program that performs the transaction and associated
business logic should be repeatable. That is, you should be able to call it
one or more times without side effects. After it succeeds you can perform
any side effects you require.

If I don't have time to engineer all the business logic in a functional
style I try to do all the reads in the first part of the transaction and
then all the writes in the second part. I then store all the writes in a
list and, if the transaction fails, I can retry just the statements in the
list. This has several caveats:

This assumes that the transaction does eventually succeed and that the
writes are not dependent on the reads whose data might have been modified
by other transactions. You can sometimes arrange for this property with
careful design of the database schema but unless you're very careful you
can end up corrupting the business state in the database in a way that is
difficult or impossible to detect or debug. It works best (i.e. most
safely) with plain INSERTs. i.e. when you don't overwrite any of the
existing data with UPDATE or UPSERT-style statements.


If this is too much trouble then you can, as you say, use BEGIN EXCLUSIVE
and fix it when you get to the point where more concurrency is required.





Best wishes,
@ndy

--
[hidden email]
http://www.ashurst.eu.org/
0x7EBA75FF
_______________________________________________
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: Please explain multi-access behaviour to me

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

On Monday, 17 June, 2019 18:46, Simon Slavin <[hidden email]> wrote:

>I think I see my error.  I thought that the lock was promoted from
>read to read/write when the INSERT command was processed.  At this
>point, SQLite knows that it is going to need to write.

>Instead, although SQLite knows that it is going to have to write, it
>does not try to promote the lock until COMMIT.

That is correct, because no one needs to know until the transaction is COMMITted.  Maybe it will be ROLLedBACK instead, in which case no one else ever even needs to know that it was contemplated that the database might be updated.

>In some ways I can appreciate this: it allows other threads to finish
>their work.  But in others it seems wrong, as if SQLite is ignoring a
>potential problem, in a way that may make it an even bigger problem
>in the future.

No, it prevents other users of the database from being impacted by mayhaps yes and mayhaps no (that is, other connections should not know nor care about an update to the database until it is committed -- that is the purpose of transactions).  If you know that you will be updating the database in this transaction then you should start the transaction appropriately (with BEGIN IMMEDATE).

This is the ATOMICITY and ISOLATION of ACID

https://en.wikipedia.org/wiki/ACID_(computer_science)

---
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: Please explain multi-access behaviour to me

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

On Monday, 17 June, 2019 18:46, Simon Slavin <[hidden email]> wrote:

>I think I see my error.  I thought that the lock was promoted from
>read to read/write when the INSERT command was processed.  At this
>point, SQLite knows that it is going to need to write.

>Instead, although SQLite knows that it is going to have to write, it
>does not try to promote the lock until COMMIT.

>In some ways I can appreciate this: it allows other threads to finish
>their work.  But in others it seems wrong, as if SQLite is ignoring a
>potential problem, in a way that may make it an even bigger problem
>in the future.

See https://sqlite.org/lockingv3.html
for how transactions and locks work in journal_mode=delete|persist|truncate (ie, not WAL).  
There is a link on that page to how transactions work when WAL is in effect.

Note that the default BEGIN [DEFERRED] [TRANSACTION] obtains a SHARED or RESERVED lock only when the next statement is executed on that connection.  This means that an update (UPDATE/INSERT/DELETE) statement (on that connection) may fail because it might not be able to acquire a lock (either SHARED or RESERVED) that it requires.

BEGIN IMMEDIATE [TRANSACTION] causes a RESERVED lock to be obtained immediately on that connection.  Subsequent statements executed on that connection cannot fail to obtain the requisite RESERVED lock.  However, it is theoretically possible for a COMMIT on that connection to fail if there are outstanding SHARED locks from other connections.  Other connections will not be able to obtain a RESERVED lock and therefore cannot update the database while the RESERVED lock is held.

BEGIN EXCLUSIVE [TRANSACTION] causes an EXCLUSIVE lock to be obtained immediately.  Subsequent statements on the same connection cannot fail to obtain locks and the COMMIT will succeed without error or delay.  No other connection may obtain any kind of lock on the database and are therefore unable to read or write 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: Please explain multi-access behaviour to me

Simon Slavin-3
On 18 Jun 2019, at 4:19am, Keith Medcalf <[hidden email]> wrote:

> See https://sqlite.org/lockingv3.html
> for how transactions and locks work in journal_mode=delete|persist|truncate (ie, not WAL).  
> There is a link on that page to how transactions work when WAL is in effect.
>
> Note that the default BEGIN [DEFERRED] [TRANSACTION] obtains a SHARED or RESERVED lock only when the next statement is executed on that connection.  This means that an update (UPDATE/INSERT/DELETE) statement (on that connection) may fail because it might not be able to acquire a lock (either SHARED or RESERVED) that it requires.

I understand about the RESERVED lock.  I read the documentation.  My surprise was at this, from further down the same page:

" No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. "

and that's what I was asking about.  I was surprised that by default SQLite waits until the last possible moment to gain an exclusive lock, risking failure after the program may have forgotten which changes were made.  I suspect that a lot of software is using BEGIN when it should be using BEGIN EXCLUSIVE.
_______________________________________________
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: Please explain multi-access behaviour to me

Igor Tandetnik-2
On 6/17/2019 11:35 PM, Simon Slavin wrote:
> I suspect that a lot of software is using BEGIN when it should be using BEGIN EXCLUSIVE.

A lot of software doesn't have persistent, long-lived reader transactions. If your readers come in, get their data, and quickly get out, then a writer has sufficient opportunity to squeeze through, with a simple timeout. See also: sqlite3_busy_timeout ( https://www.sqlite.org/c3ref/busy_timeout.html ), PRAGMA busy_timeout ( https://www.sqlite.org/pragma.html#pragma_busy_timeout )
--
Igor Tandetnik


_______________________________________________
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: Please explain multi-access behaviour to me

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

On Monday, 17 June, 2019 21:36, Simon Slavin <[hidden email]> wrote:

>I understand about the RESERVED lock.  I read the documentation.  My
>surprise was at this, from further down the same page:

>" No EXCLUSIVE lock is acquired until either the memory cache fills
>up and must be spilled to disk or until the transaction commits. "

>and that's what I was asking about.  I was surprised that by default
>SQLite waits until the last possible moment to gain an exclusive
>lock, risking failure after the program may have forgotten which
>changes were made.  I suspect that a lot of software is using BEGIN
>when it should be using BEGIN EXCLUSIVE.

Generally speaking you should leave your READS (SELECTs) in AUTOCOMMIT mode.  Unless you require REPEATABLE READ, or data stability between multiple select statements, in which case you should put them in a BEGIN DEFERRED transaction.  Transactions in which you intend (or might) do updates should be BEGIN IMMEDIATE transactions.  This applies generally for all DBMS systems -- and they all support some method of signifying intent to update.  And you should NEVER hold a transaction of any kind open for longer than absolutely necessary and especially never during "user interaction" (you never know when the user might need to go for a pee and get hit by a bus and killed in the process leaving you with a farked up database to unfark).

So for applications which interact with a user, the general process is:

1) read the data for the user in autocommit or inside a deferred transaction if you need more than one select statement to collect the data the user needs to see and that you need to verify that the users' update is valid, and commit or rollback that transaction BEFORE INTERACTING WITH THE USER OR UPDATING THE DISPLAY TO THE USER.

2) display the data for the user to fiddle with

3) start a transaction with BEGIN IMMEDIATE and repeat step (1) making sure the data has not changed while the very slow user diddled about with the user interface and then if and only if the data is unchanged and the update is valid, make the updates and commit the transaction.  If the commit fails because there are other active readers, then try committing again (and commit again (and commit again) ... until success is obtained).  If the data does not verify (ie, some other connection updated the necessary data while the user piddled about, or the update is otherwise invalid, rollback the transaction and use the data collected in this step to go back to step 2).

4) Lather rinse repeat until the user bails out or the heat death of the universe, whichever comes first.

In other words, never hold a transaction longer than is absolutely necessary.

The uses for a BEGIN EXCLUSIVE transaction are very rare special cases.  I think I may have used an exclusive transaction once or twice in the past three decades ...

---
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: Please explain multi-access behaviour to me

Thomas Kurz
This has been a very informative and helpful discussion. Thank you.

So have I understood correctly, that in an application, this kind of SQLITE_BUSY handling is sufficient:

BEGIN
UPDATE #1
SELECT #2
UPDATE #3
COMMIT <----- check for busy here and retry only the commit on failure

And second, what is the best place for busy-handling when only reading? Examle:

BEGIN <---- sufficient here?
SELECT #1
SELECT #2
SELECT #3
ROLLBACK

Can I assume that if the "begin" succeeds, I have the right to perform all following selects without having to fear getting an SQLITE_BUSY? Or do I have to repeat the whole block?

_______________________________________________
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: Please explain multi-access behaviour to me

Keith Medcalf

On Tuesday, 18 June, 2019 07:12, Thomas Kurz <[hidden email]> wrote:

>This has been a very informative and helpful discussion. Thank you.

>So have I understood correctly, that in an application, this kind of
>SQLITE_BUSY handling is sufficient:

>BEGIN
>UPDATE #1
>SELECT #2
>UPDATE #3
>COMMIT <----- check for busy here and retry only the commit on failure

UPDATE #1 could fail with SQLITE_BUSY -- it may be unable to obtain a RESERVED lock.  Assuming that the UPDATE #1 completes then the transaction holds a RESERVED lock and the rest of the statements will not get an SQLITE_BUSY.  The COMMIT needs to obtain an EXCLUSIVE lock to write the changes, so it may return SQLITE_BUSY if there are other connections having SHARED locks which might need to clear before the transaction can commit.

Since you know that the transaction will require a RESERVED lock to complete, you should use BEGIN IMMEDIATE to acquire that lock immediately.  Then if the BEGIN IMMEDIATE succeeds you only need to worry about lock escalation again when you commit (and not on the interim statements which do not need to acquire any additional locks).


>And second, what is the best place for busy-handling when only
>reading? Examle:

>BEGIN <---- sufficient here?
>SELECT #1
>SELECT #2
>SELECT #3
>ROLLBACK
>
>Can I assume that if the "begin" succeeds, I have the right to
>perform all following selects without having to fear getting an
>SQLITE_BUSY? Or do I have to repeat the whole block?

Again, you need to check for SQLITE_BUSY on SELECT #1 since that is where the SHARED lock is obtained.  Once SELECT #1 has completed the transaction holds a shared lock and the rest of the statements will proceed without requiring additional locks.

---
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: Please explain multi-access behaviour to me

Keith Medcalf

"UPDATE #1 completes" and "SELECT #1 completes" should be read as the first sqlite3_step of that statement returns some sort of success indicator ...

Note that the sequence:

BEGIN;
SELECT #1;
UPDATE #1

may result in a deadlock in that although SELECT #1 may be able to obtain a SHARED lock, UPDATE #1 may not be able to upgrade to a RESERVED lock.  The only remedy is to abort the transaction entirely and start over again from the very beginning (this is because the principle that all required locks should be acquired at once and in the same order was violated).  Using BEGIN IMMEDIATE will eliminate this possibility since it will (correctly) acquire all required locks at once ... (though you still need to get an EXCLUSIVE lock for the commit, you may still have to wait for SHARED locks to clear before the transaction can commit, although you may starve if you designed your application badly, you cannot deadlock).  I suppose you could use BEGING EXCLUSIVE to make sure that you have the required exclusive lock as well, but this will lock all other readers out of the database and defeats concurrency.

Note that in WAL mode the deadlock condition may not be detected until commit time since the transaction (not being started with BEGIN IMMEDIATE) may not be the "top" snapshot and therefore cannot commit (ever).

Also note that it is possible for ANY STATEMENT which updates the database, even if a RESERVED lock is held by the transaction, to return SQLITE_BUSY.  For example an EXCLUSIVE lock is required to spill pages to disk from the cache and this may occur before COMMIT time.

So generally, you need to always check the return codes and do the needful ... (and what is needful depends on what you were doing and what the return code is and why you got that return code -- and the application programmer knows that with certainty, or at least should).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Keith Medcalf
>Sent: Tuesday, 18 June, 2019 08:25
>To: SQLite mailing list
>Subject: Re: [sqlite] Please explain multi-access behaviour to me
>
>
>On Tuesday, 18 June, 2019 07:12, Thomas Kurz <[hidden email]>
>wrote:
>
>>This has been a very informative and helpful discussion. Thank you.
>
>>So have I understood correctly, that in an application, this kind of
>>SQLITE_BUSY handling is sufficient:
>
>>BEGIN
>>UPDATE #1
>>SELECT #2
>>UPDATE #3
>>COMMIT <----- check for busy here and retry only the commit on
>failure
>
>UPDATE #1 could fail with SQLITE_BUSY -- it may be unable to obtain a
>RESERVED lock.  Assuming that the UPDATE #1 completes then the
>transaction holds a RESERVED lock and the rest of the statements will
>not get an SQLITE_BUSY.  The COMMIT needs to obtain an EXCLUSIVE lock
>to write the changes, so it may return SQLITE_BUSY if there are other
>connections having SHARED locks which might need to clear before the
>transaction can commit.
>
>Since you know that the transaction will require a RESERVED lock to
>complete, you should use BEGIN IMMEDIATE to acquire that lock
>immediately.  Then if the BEGIN IMMEDIATE succeeds you only need to
>worry about lock escalation again when you commit (and not on the
>interim statements which do not need to acquire any additional
>locks).
>
>
>>And second, what is the best place for busy-handling when only
>>reading? Examle:
>
>>BEGIN <---- sufficient here?
>>SELECT #1
>>SELECT #2
>>SELECT #3
>>ROLLBACK
>>
>>Can I assume that if the "begin" succeeds, I have the right to
>>perform all following selects without having to fear getting an
>>SQLITE_BUSY? Or do I have to repeat the whole block?
>
>Again, you need to check for SQLITE_BUSY on SELECT #1 since that is
>where the SHARED lock is obtained.  Once SELECT #1 has completed the
>transaction holds a shared lock and the rest of the statements will
>proceed without requiring additional locks.
>
>---
>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



_______________________________________________
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: Please explain multi-access behaviour to me

Simon Slavin-3
In reply to this post by Thomas Kurz
On 18 Jun 2019, at 2:11pm, Thomas Kurz <[hidden email]> wrote:

> So have I understood correctly, that in an application, this kind of SQLITE_BUSY handling is sufficient:
>
> BEGIN
> UPDATE #1
> SELECT #2
> UPDATE #3
> COMMIT <----- check for busy here and retry only the commit on failure

I believe that the above is correct.  Though you should be checking the return code for every SQLite3 call, just because catching an unexpected code there can save you days of debugging time.

Actually, you're better off setting a timeout rather than handling retry in your own code:

<https://sqlite.org/c3ref/busy_timeout.html>

(or use the PRAGMA).  People tend to set tiny timeouts and then waste programming time implementing their own retry code.  But what you really want is to set a timeout of a minute or more.  After all, which is better, to catch a working retry after 45 seconds or to present an error message to the user ?

The exception is when your SQLite operation is blocking the main thread of a real-time program.  But that's rare.
_______________________________________________
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: Please explain multi-access behaviour to me

Andy Bennett
Hi,

> Actually, you're better off setting a timeout rather than handling
> retry in your own code:
>
> <https://sqlite.org/c3ref/busy_timeout.html>

As I understand it, SQLITE_BUSY can occur in two situations: one where
busy looping will fix it and one where it won't. Using the busy_timeout
handles the first case automatically and means that if you get
SQLITE_BUSY in user code it's always the deadlocking variant.
To get around those deadlocks you have to abort the whole transaction
and retry from the beginning. Keith explains some of these scenarios in
more detail in a message a few moments before your own so I won't repeat
them here.

Nevertheless, you need to retry because of concurrency contention, which
can be handled automatically and reasonably efficiently, *AND* you need
to retry at the business logic layer due to ACID correctness guarantees
being provided and enforced.


Transactions can also fail for other reasons (such as a UNIQUE
constraint violation or a read-modify-write hazard that happens when
another transaction changes some data that you read and are manipulating
and writing back) and you need to be able to recover from these
scenarios.

Having said that, SQLITE is nice because its single writer model means
that read-modify-write hazards are less of a problem than other RDBMSes,
provided transactions that intend to write use BEGIN IMMEDIATE.



--
[hidden email]
http://www.ashurst.eu.org/
http://www.gonumber.com/andyjpb
0x7EBA75FF
_______________________________________________
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: Please explain multi-access behaviour to me

Jose Isaias Cabrera-4

Andy Bennett, on Tuesday, June 18, 2019 03:53 PM, wrote...

>
> > Actually, you're better off setting a timeout rather than handling
> > retry in your own code:
> >
> > <https://sqlite.org/c3ref/busy_timeout.html>
>
> As I understand it, SQLITE_BUSY can occur in two situations: one where
> busy looping will fix it and one where it won't. Using the busy_timeout
> handles the first case automatically and means that if you get
> SQLITE_BUSY in user code it's always the deadlocking variant.
> To get around those deadlocks you have to abort the whole transaction
> and retry from the beginning. Keith explains some of these scenarios in
> more detail in a message a few moments before your own so I won't repeat
> them here.

This is very true.  It would be easier to have just two writers, and one hard drive. But in my case, I have 8 writers, and a shared network drive.  So, I had to go even farther than that, which was to have the software check if someone was writing to the DB by creating a small hidden, and it it, write the name of the PM, so when a PM wanted to write, The program would prompt the PM, "Please be patien, Joe is writing to the DB."  When the writer received an OK from the SQLite writing connection, I would delete that file, and the other writer would check if someone was writing to the DB, if there was no hidden file, then, a new one would be added with the second writer... etc. etc.  This took me a while to figure out with live data and in the end, everyone is happy and love it. Just my 98 Dominican cents. ;-) (US$0.01 = DO$0.49)

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users