Re: [sqlite] Dealing with SQLITE_BUSY

[Igor Tandetnik]

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

Re: [sqlite] Dealing with SQLITE_BUSY

[Kees Nuyt]

On Thu, 22 Feb 2018 14:36:07 +, Simon Slavin
 wrote:

> On 22 Feb 2018, at 11:24am, Clemens Ladisch  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."


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

Exeactly.

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

Re: [sqlite] Dealing with SQLITE_BUSY

[Keith Medcalf]

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 1Connection 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-
>boun...@mailinglists.sqlite.org] On Behalf Of Nick
>Sent: Thursday, 22 February, 2018 03:54
>To: sqlite-users@mailinglists.sqlite.org
>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
>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] Dealing with SQLITE_BUSY

[Simon Slavin]

On 22 Feb 2018, at 11:24am, Clemens Ladisch  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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