Notes on SQLite WAL mode concurrency

[boris-kolpackov]

SQLite in the WAL mode allows readers to proceed concurrently with (one) writer. This can be implemented (in SQLite) in two ways: using the non-blocking POSIX advisory locks (and some equivalent mechanism on Windows) where the implementation polls to see if the lock becomes available or using the blocking POSIX advisory locks, where the OS manages things. Naturally, one would expect better performance from the latter but at least at the time of this writing (and SQLite version 3.53.2), blocking advisory locks are only available as a proprietary extension.

The timeout for non-blocking locks is managed using sqlite3_busy_timeout() and related functions. The timeout for blocking locks is managed using sqlite3_setlk_timeout(). Note that while it may seem that with blocking locks calling just sqlite3_setlk_timeout() should be sufficient, after debugging through the code I have my doubts: there are quite a few places where SQLITE_BUSY is returned before even attempting to grab the filesystem lock (e.g., when trying to lock mutexes, avoiding deadlocks, etc) so it seems for blocking locks one may still need to set the polling timeout since polling may be used in certain scenarios. Also, the blocking locks appear to have two (undocumented) flavors in the forms of SQLITE_ENABLE_SETLK_TIMEOUT=1 and SQLITE_ENABLE_SETLK_TIMEOUT=2.

In theory, from the application perspective, the two locking modes should behave the same. In practice, however, there are nuances.

Based on the SQLite documentation (see this forum thread for background and references), given the WAL mode and a very large timeout, an application may still receive SQLITE_BUSY in the following situations:

1. When a read-transaction needs to be upgraded to write-transaction in the presence of another write-transaction, the read-transaction is immediately aborted. This condition is supposed to be indicated with SQLITE_BUSY_SNAPSHOT (which we translate to deadlock) but there appears to be a bug. This can be avoided by starting all potentially write-transactions with BEGIN IMMEDIATE.

2. WAL cleanup: "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 separate attempt is made 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."[1]. The SQLITE_SETLK_BLOCK_ON_CONNECT flag to sqlite3_setlk_timeout() can be used to causes SQLite to block in this case instead.[2]. There doesn't appear to be a way to achieve the same for the non-blocking locking mode.

3. WAL recovery: "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." I believe the extended error code in this case is SQLITE_BUSY_RECOVERY. According to the above-linked thread, it seems this case should respect the timeout.

One thing we can do to aid the last issue is to derive sqlite::timeout from odb::timeout and include the extended error code. This will allow the user to detect and handle SQLITE_BUSY_RECOVERY.