Does WAL mode's SQLITE_BUSY special circumstances invoke the busy handler?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Does WAL mode's SQLITE_BUSY special circumstances invoke the busy handler?

Donald Shepherd
The documentation on WAL databases includes a section with caveats re:
SQLITE_BUSY, included below.  Do these invoke the busy handler (if
configured) or just return SQLITE_BUSY immediately?  Making a valiant
attempt to read the code leads me to believe it returns immediately without
involving the busy handler.

"Cases where a query against a WAL-mode database can return SQLITE_BUSY
include the following:

- If another database connection has the database mode open in exclusive
locking mode then all queries against the database will return SQLITE_BUSY.
Both Chrome and Firefox open their database files in exclusive locking
mode, so attempts to read Chrome or Firefox databases while the
applications are running will run into this problem, for example.

- When the last connection to a particular database is closing, that
connection will acquire an exclusive lock for a short time while it cleans
up the WAL and shared-memory files. If a second database tries to open and
query the database while the first connection is still in the middle of its
cleanup process, the second connection might get an SQLITE_BUSY error.

- If the last connection to a database crashed, then the first new
connection to open the database will start a recovery process. An exclusive
lock is held during recovery. So if a third database connection tries to
jump in and query while the second connection is running recovery, the
third connection will get an SQLITE_BUSY error."
_______________________________________________
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: Does WAL mode's SQLITE_BUSY special circumstances invoke the busy handler?

Clemens Ladisch
Donald Shepherd wrote:
> The documentation on WAL databases includes a section with caveats re:
> SQLITE_BUSY, included below.  Do these invoke the busy handler (if
> configured) or just return SQLITE_BUSY immediately?

In general, SQLite calls the busy handler if there is a chance that
the lock will go away when waiting.

> Making a valiant attempt to read the code leads me to believe it
> returns immediately without involving the busy handler.

Search for "xBusy" or "xBusyHandler".

> - If another database connection has the database mode open in exclusive
> locking mode [...]
> - When the last connection to a particular database is closing, that
> connection will acquire an exclusive lock for a short time while it cleans
> up the WAL and shared-memory files. [...]
> - If the last connection to a database crashed, then the first new
> connection to open the database will start a recovery process. An exclusive
> lock is held during recovery.

These are not really 'special' circumstances, as far as locking algorithm
is concerned.  It's just that they happen less often when in WAL mode.

The second connection that tries to acquire the lock does not know the
reason for the existing exclusive lock.  In any case, waiting is the
right thing to do, so the busy handler is called.

The only case where the busy handler is avoided is in journal rollback
mode when neither connection has an exclusive lock yet (comment for
sqlite3BtreeBeginTrans()):

** If an initial attempt to acquire the lock fails because of lock contention
** and the database was previously unlocked, then invoke the busy handler
** if there is one.  But if there was previously a read-lock, do not
** invoke the busy handler - just return SQLITE_BUSY.  SQLITE_BUSY is
** returned when there is already a read-lock in order to avoid a deadlock.
**
** Suppose there are two processes A and B.  A has a read lock and B has
** a reserved lock.  B tries to promote to exclusive but is blocked because
** of A's read lock.  A tries to promote to reserved but is blocked by B.
** One or the other of the two processes must give way or there can be
** no progress.  By returning SQLITE_BUSY and not invoking the busy callback
** when A already has a read lock, we encourage A to give up and let B
** proceed.

(That case can be avoided by using BEGIN IMMEDIATE for transactions that
(might) write.)


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