Re: [sqlite] unexpected sqlite_busy behaviour within transactions

2020-02-26 Thread Software
Dear Igor, Andy, Keith

Thank you for your patience to explain. Now very clear to me why it is not 
worth for the second process to honor the sqlite_busy handler, and instead 
returns immediately. In retrospect re-reading with your explanations in mind, I 
also understand the official documentation.

In my case I will use BEGIN IMMEDIATE TRANSACTION to circumvent the issue.

Best regards

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


Re: [sqlite] unexpected sqlite_busy behaviour within transactions

2020-02-22 Thread Keith Medcalf

On Saturday, 22 February, 2020 09:26, Andy Bennett  
wrote:

>This other process has called "BEGIN IMMEDIATE TRANSACTION" and
>https://sqlite.org/rescode.html#busy says

>"The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it
>succeeds, then SQLite guarantees that no subsequent operations on the
>same database through the next COMMIT will return SQLITE_BUSY."

>As I understand it, this means that the only way this transaction will
>fail to commit is if the statements executed within it lead to some kind of
>constraint violation. i.e. it wont abort due to the actions of other
>proceses.

An immediate transaction may indeed "fail to commit".

Any statement up to and including the final COMMIT may return SQLITE_BUSY in 
the event that it is unable to upgrade its RESERVED lock to an EXCLUSIVE lock 
so that it may modify the database contents (for locking modes other than WAL 
-- WAL precludes the possibility of failure to obtain the required lock upgrade 
to EXCLUSIVE) if another connection holds a SHARED lock.  This may indefinitely 
prevent the transaction from committing.  Statements prior to the COMMIT may 
require EXCLUSIVE access to the database if they "spill their cache pages", for 
example.

However, no statement before the commit will fail for want of a RESERVED lock.  
Statements before and including the COMMIT may fail (SQLITE_BUSY) for want of 
an EXCLUSIVE lock if they need to write to the database (COMMIT or spill pages) 
and the locking mode is not WAL.

Also note that in the original example, even though the INSERT returns 
SQLITE_BUSY immediately because not doing so is pointless (and might cause a 
deadlock) does not mean that retrying the statement will not succeed.  The 
original holder of the RESERVED lock may ROLLBACK their transaction in which 
case the upgrade of the SHARED lock to RESERVED will succeed and that 
transaction might be able to proceed without having to restart its transaction.




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


Re: [sqlite] unexpected sqlite_busy behaviour within transactions

2020-02-22 Thread Andy Bennett

Hi,

A busy_timout is honored by write commands outside of 
transactions and by single write commands inside transactions, 
but not if preceded by a read command. I did not find this 
behaviour in the documentation, thus it might be a bug.


It's documented in the description of sqlite3_busy_handler: 
https://sqlite.org/c3ref/busy_handler.html . Look for a 
paragraph mentioning "deadlock".


Here's my understanding as I initially struggled to understand the nauance 
of the supplied documentation link.




   sqlite3 test.db --init sqlitetest.init "BEGIN TRANSACTION; SELECT * FROM t; 
INSERT INTO t VALUES(1); COMMIT;"


This returns SQLITE_BUSY after the SELECT statement and before the INSERT 
statement. This is when the SHARED lock needs to be upgraded to a RESERVED 
lock.


At this time, the IMMEDIATE transaction has a RESERVED lock that it will 
need to upgrade to an EXCLUSIVE lock in order to commit successfully. 
RESERVED locks can only be held by one transaction at a time but allow 
other processes to read the database.


This other process has called "BEGIN IMMEDIATE TRANSACTION" and 
https://sqlite.org/rescode.html#busy says


"The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it 
succeeds, then SQLite guarantees that no subsequent operations on the same 
database through the next COMMIT will return SQLITE_BUSY." 

As I understand it, this means that the only way this transaction will fail 
to commit is if the statements executed within it lead to some kind of 
constraint violation. i.e. it wont abort due to the actions of other 
proceses.


So that it can commit successfully this process will need to first upgrade 
its RESERVED lock to a PENDING lock on its way to an EXCLUSIVE lock.


The PENDING lock means that no new processes will be granted SHARED locks.
When all the existing SHARED locks have disappeared, the PENDING lock can 
be upgraded to an EXCLUSIVE lock.



This is how the deadlock happens: the DEFERRED transaction cannot upgrade 
its SHARED lock to a RESERVED lock because the other transaction already 
has a RESERVED lock, so it must wait. The IMMEDIATE transaction can upgrade 
its RESERVED lock to a PENDING lock but will be unable to upgrade that to 
an EXCLUSIVE lock until the DEFERRED transaction has released its SHARED 
lock.


We have a deadlock because the two transactions are waiting for each other.

Therefore, the DEFERRED transaction recieves SQLITE_BUSY straight away so 
that the IMMEDIATE transaction does not have to wait for it's busy handler 
to expire before getting an EXCLUSIVE lock.



Of course, this is also good for the integrity of the DEFERRED transaction. 
If the IMMEDIATE transaction ends up commiting then it may change the data 
that the DEFERRED transaction has already read from the database and based 
its subsequent calculations on. Allowing this would be bad for the 
integrity of that transaction. This is how the SERIALISABLE property of 
SQLite is provided.





Further reading:

https://sqlite.org/c3ref/busy_handler.html
https://sqlite.org/rescode.html#busy
https://sqlite.org/lang_transaction.html#immediate
https://www.sqlite.org/lockingv3.html





Best wishes,
@ndy

--
andy...@ashurst.eu.org
http://www.ashurst.eu.org/
0x7EBA75FF
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] unexpected sqlite_busy behaviour within transactions

2020-02-22 Thread Igor Tandetnik

On 2/22/2020 7:50 AM, softw...@quantentunnel.de wrote:

A busy_timout is honored by write commands outside of transactions and by 
single write commands inside transactions, but not if preceded by a read 
command. I did not find this behaviour in the documentation, thus it might be a 
bug.


It's documented in the description of sqlite3_busy_handler: 
https://sqlite.org/c3ref/busy_handler.html . Look for a paragraph mentioning 
"deadlock".
--
Igor Tandetnik


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