Dealing with SQLITE_BUSY

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

Dealing with SQLITE_BUSY

Nick
I use sqlite3_open() to open two connections, and I have configured
journal_mode=WAL, threadsafe=2.

Connection 1 is doing:
sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg);
sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg);
sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, &zErrMsg);
//SQLITE_BUSY
sqlite3_exec(db1, "COMMIT", 0, 0, &zErrMsg);

I got that SQLITE_BUSY as connection 2 was writing the db at the same time.
I have called sqlite3_busy_timeout() but I find that it does not work if
INSERT runs after a SELECT within BEGIN and COMMIT.

Is it expected?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
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: Dealing with SQLITE_BUSY

Clemens Ladisch
Nick wrote:

> I use sqlite3_open() to open two connections, and I have configured
> journal_mode=WAL, threadsafe=2.
>
> Connection 1 is doing:
> sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg);
> sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg);
> sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, &zErrMsg);
> //SQLITE_BUSY
> sqlite3_exec(db1, "COMMIT", 0, 0, &zErrMsg);
>
> I got that SQLITE_BUSY as connection 2 was writing the db at the same time.
> I have called sqlite3_busy_timeout() but I find that it does not work if
> INSERT runs after a SELECT within BEGIN and COMMIT.

When you have two connections that upgrade from a read-only transaction
to a read-write transaction, you would get a deadlock.

Use "BEGIN IMMEDIATE" instead to tell the DB that you intend to write.


Regards,
Clemens
_______________________________________________
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: Dealing with SQLITE_BUSY

Simon Slavin-3


On 22 Feb 2018, at 11:24am, Clemens Ladisch <[hidden email]> wrote:

> Use "BEGIN IMMEDIATE" instead to tell the DB that you intend to write.

Depending on how you want your locks to work, BEGIN EXCLUSIVE may work better.

Do remember when setting your timeout, that you have to set it in both connections.

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: Dealing with SQLITE_BUSY

Keith Medcalf
In reply to this post by Nick

Yes.  See https://sqlite.org/lang_transaction.html

From that page:

"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. If the transaction is immediate, then RESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. 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. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, no other database connection except for read_uncommitted connections will be able to read the database and no other connection without exception will be able to write the database until the transaction is complete."

So, if you do a BEGIN DEFERRED, the write (reserved) lock is not obtained until you try to write (update) the database via the connection.  This means that an UPDATE within the BEGIN ... COMMIT block may get SQLITE_BUSY if the lock cannot be upgraded to a RESERVED lock.  If you *know* that you will be updating the database then the BEGIN IMMEDIATE command makes dealing with SQLITE_BUSY simpler.  In that case, the RESERVED lock is attempted to be obtained immediately and the BEGIN IMMEDIATE will have an SQLITE_BUSY if the lock cannot be obtained.  It would then not be possible for the subsequent UPDATE to fail with SQLITE_BUSY, making your transaction handling easier.

In WAL journal mode, BEGIN IMMEDIATE around write transactions will constrain the SQLITE_BUSY to the BEGIN IMMEDIATE statement.  Whether or not a SHARED lock can be upgraded to a RESERVED lock during a transaction depends on a lot of things, including whether the transaction being upgraded in the "top of the snapshot stack".  If it is not, it can never be upgraded (the following will fail):

Connection 1 Connection 2
BEGIN
SELECT ...                 BEGIN
                           UPDATE ...
                           COMMIT
UPDATE ...
...

The update on Connection 1 will NEVER work, even if connection 2 has committed.  That is because its "view" of the database is "prior" to the view created by the succeeding update transaction and it can NEVER be upgraded to RESERVED lock.  Therefore, if you are starting a transaction in which you intend to write, always indicate so by using BEGIN IMMEDIATE.

---
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 Nick
>Sent: Thursday, 22 February, 2018 03:54
>To: [hidden email]
>Subject: [sqlite] Dealing with SQLITE_BUSY
>
>I use sqlite3_open() to open two connections, and I have configured
>journal_mode=WAL, threadsafe=2.
>
>Connection 1 is doing:
>sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg);
>sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg);
>sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0,
>&zErrMsg);
>//SQLITE_BUSY
>sqlite3_exec(db1, "COMMIT", 0, 0, &zErrMsg);
>
>I got that SQLITE_BUSY as connection 2 was writing the db at the same
>time.
>I have called sqlite3_busy_timeout() but I find that it does not work
>if
>INSERT runs after a SELECT within BEGIN and COMMIT.
>
>Is it expected?
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>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: Dealing with SQLITE_BUSY

Kees Nuyt
In reply to this post by Simon Slavin-3
On Thu, 22 Feb 2018 14:36:07 +0000, Simon Slavin
<[hidden email]> wrote:

> On 22 Feb 2018, at 11:24am, Clemens Ladisch <[hidden email]> wrote:
>
>> Use "BEGIN IMMEDIATE" instead to tell the DB that you intend to write.
>
> Depending on how you want your locks to work, BEGIN EXCLUSIVE may work better.

Yes, but note:  "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.
An exclusive transaction causes EXCLUSIVE locks to be acquired
on all databases. After a BEGIN EXCLUSIVE, no other database
connection except for read_uncommitted connections will be able
to read the database and no other connection without exception
will be able to write the database until the transaction is
complete."
<https://sqlite.org/lang_transaction.html>

> Do remember when setting your timeout, that you have to set it in both connections.

Exeactly.

--
Regards,
Kees Nuyt
_______________________________________________
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: Dealing with SQLITE_BUSY

Igor Tandetnik-2
In reply to this post by Nick
On 2/22/2018 5:54 AM, Nick wrote:

> I use sqlite3_open() to open two connections, and I have configured
> journal_mode=WAL, threadsafe=2.
>
> Connection 1 is doing:
> sqlite3_exec(db1, "BEGIN", 0, 0, &zErrMsg);
> sqlite3_exec(db1, "SELECT * FROM t1;", 0, 0, &zErrMsg);
> sqlite3_exec(db1, "INSERT INTO t1 VALUES(1, 1, \"aa\”)”, 0, 0, &zErrMsg);
> //SQLITE_BUSY
> sqlite3_exec(db1, "COMMIT", 0, 0, &zErrMsg);
>
> I got that SQLITE_BUSY as connection 2 was writing the db at the same time.
> I have called sqlite3_busy_timeout() but I find that it does not work if
> INSERT runs after a SELECT within BEGIN and COMMIT.
>
> Is it expected?

Yes. See http://sqlite.org/c3ref/busy_handler.html , the part about a deadlock. See also the discussion of BEGIN IMMEDIATE and BEGIN EXCLUSIVE here: http://sqlite.org/lang_transaction.html ; either will avoid deadlocks.
--
Igor Tandetnik

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