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