Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

Andy Dickson
Hello.  Long time reader, first time poster here.

I have read the pertinent documentation on this issue but may have
missed some important point. I am puzzled by some results I am seeing.
Apologies in advance for not providing a small code sample that
reproduces the issue, but I think the question should be answerable
without such.

Using sqlite version 3.7.17 (which I know is old but I in my little cube
have no power to change that).

Not using WAL mode.

I have a database with one process (in one thread) writing to it, and
another process (also in a single thread) reading from it only. All
writes are done under BEGIN TRANSACTION IMMEDIATE.  Sometimes, an END
TRANSACTION fails with error 5, SQLITE_BUSY.   The documentation says
this should not happen:

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

The read-only process has busy_timeout set to 10000, if that matters.

Am I right that this should not be happening, if indeed my assertions
are correct?

thanks,
Andy
_______________________________________________
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: Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

Simon Slavin-3
On 24 Jul 2018, at 6:22pm, Andy Dickson <[hidden email]> wrote:

> I have a database with one process (in one thread) writing to it, and another process (also in a single thread) reading from it only.

Do these two processes each have their own connection to the database ?

> The read-only process has busy_timeout set to 10000, if that matters.

For testing purposes, please set that same timeout for all connections to the database.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

Richard Hipp-3
In reply to this post by Andy Dickson
On 7/24/18, Andy Dickson <[hidden email]> wrote:
>
> Using sqlite version 3.7.17 (which I know is old but I in my little cube
> have no power to change that).
>

Recent versions of SQLite consume substantially fewer CPU resources
than 3.7.17.  See the graph at https://www.sqlite.org/cpu.html
--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

David Raymond
In reply to this post by Andy Dickson
Does kind of seem like conflicting documentation. Begin immediate is basically immediately calling dibbs on being the next process with write permissions. But it doesn't stop anyone else from reading at that point, and doesn't stop any current readers. You have dibbs on writing, but you can't actually do that writing until all those readers are done with their queries in their own time.


https://www.sqlite.org/rescode.html#busy is where your quote came from.

"To avoid encountering SQLITE_BUSY errors in the middle of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN to start a transaction. 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."



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

"After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however."

"The explicit COMMIT command runs immediately, even if there are pending SELECT statements. However, if there are pending write operations, the COMMIT command will fail with an error code SQLITE_BUSY."

"An attempt to execute COMMIT might also result in an SQLITE_BUSY return code if an another thread or process has a shared lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear."

"In very old versions of SQLite (before version 3.7.11 - 2012-03-20) the ROLLBACK will fail with an error code SQLITE_BUSY if there are any pending queries. In more recent versions of SQLite, the ROLLBACK will proceed and pending statements will often be aborted, causing them to return an SQLITE_ABORT or SQLITE_ABORT_ROLLBACK error. In SQLite version 3.8.8 (2015-01-16) and later, a pending read will continue functioning after the ROLLBACK as long as the ROLLBACK does not modify the database schema."



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Andy Dickson
Sent: Tuesday, July 24, 2018 1:22 PM
To: [hidden email]
Subject: [sqlite] Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

Hello.  Long time reader, first time poster here.

I have read the pertinent documentation on this issue but may have
missed some important point. I am puzzled by some results I am seeing.
Apologies in advance for not providing a small code sample that
reproduces the issue, but I think the question should be answerable
without such.

Using sqlite version 3.7.17 (which I know is old but I in my little cube
have no power to change that).

Not using WAL mode.

I have a database with one process (in one thread) writing to it, and
another process (also in a single thread) reading from it only. All
writes are done under BEGIN TRANSACTION IMMEDIATE.  Sometimes, an END
TRANSACTION fails with error 5, SQLITE_BUSY.   The documentation says
this should not happen:

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

The read-only process has busy_timeout set to 10000, if that matters.

Am I right that this should not be happening, if indeed my assertions
are correct?

thanks,
Andy
_______________________________________________
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: Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

Keith Medcalf
In reply to this post by Andy Dickson

>I have a database with one process (in one thread) writing to it, and
>another process (also in a single thread) reading from it only. All
>writes are done under BEGIN TRANSACTION IMMEDIATE.  Sometimes, an END
>TRANSACTION fails with error 5, SQLITE_BUSY.   The documentation says
>this should not happen:



---
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 Andy Dickson
>Sent: Tuesday, 24 July, 2018 11:22
>To: [hidden email]
>Subject: [sqlite] Immediate mode transaction gets error 5,
>SQLITE_BUSY when attempting commit
>
>Hello.  Long time reader, first time poster here.
>
>I have read the pertinent documentation on this issue but may have
>missed some important point. I am puzzled by some results I am
>seeing.
>Apologies in advance for not providing a small code sample that
>reproduces the issue, but I think the question should be answerable
>without such.
>
>Using sqlite version 3.7.17 (which I know is old but I in my little
>cube
>have no power to change that).
>
>Not using WAL mode.
>
>I have a database with one process (in one thread) writing to it, and
>another process (also in a single thread) reading from it only. All
>writes are done under BEGIN TRANSACTION IMMEDIATE.  Sometimes, an END
>TRANSACTION fails with error 5, SQLITE_BUSY.   The documentation says
>this should not happen:
>
>"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."
>
>The read-only process has busy_timeout set to 10000, if that matters.
>
>Am I right that this should not be happening, if indeed my assertions
>are correct?
>
>thanks,
>Andy
>_______________________________________________
>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