Yes.  See

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 

Connection 1                    Connection 2
SELECT ...                 BEGIN
                           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-
>] On Behalf Of Nick
>Sent: Thursday, 22 February, 2018 03:54
>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,
>sqlite3_exec(db1, "COMMIT", 0, 0, &zErrMsg);
>I got that SQLITE_BUSY as connection 2 was writing the db at the same
>I have called sqlite3_busy_timeout() but I find that it does not work
>INSERT runs after a SELECT within BEGIN and COMMIT.
>Is it expected?
>Sent from:
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to