Handling SQLITE_BUSY - important for v1.0

[rhashimoto]

Update 5/10/2024: The default locking behavior will remain exclusive, but it is still highly recommended for developers to understand SQLITE_BUSY and how to properly handle it.

The eventual 1.0 release of wa-sqlite might change the default locking behavior of some of the example VFS classes (that allow multiple connections) from always exclusive to allowing shared reads. This will improve concurrency with multiple connections but can also produce SQLITE_BUSY when executing queries. Applications using wa-sqlite that could ever have overlapping queries from different connections, e.g. from multiple tabs, must handle this properly.

The SQLite locking model used for non-WAL journaling modes allows shared locks for read transactions, which permits multiple connections to read from the database at once. The tradeoff for this concurrency is the possibility of deadlock, which can happen when two or more connections want to upgrade their shared read lock to an exclusive write lock which can't proceed until all other connections release their read lock.

SQLite signals this deadlock state with SQLITE_BUSY (which wa-sqlite converts to a thrown Error with property code equal to SQLITE_BUSY and message "database is locked"). Applications need to handle SQLITE_BUSY by issuing a ROLLBACK if in a multi-statement transaction (SQLite will implicitly rollback single-statement "autocommit" transactions itself) and then retrying the entire transaction. The application may also need to undo side effects that it has produced outside of SQLite during the failed transaction.

Many application developers aren't aware that it is their responsibility to handle SQLITE_BUSY properly, or aren't that vigilant about it (including me) especially if not that much database contention is expected. That risks deadlock in production code. Note that this means that even though you can submit multiple write transactions with sqlite3_exec(), you generally shouldn't unless your transactions are idempotent (retrying a successful transaction doesn't change anything) or you can determine which transaction failed.

Developer laziness and lack of awareness aren't the only drawbacks with SQLITE_BUSY. Rollback and retry solves deadlock but can still produce livelock, and the workarounds for livelock can reduce performance. Another problem is fairness, giving each connection equal access to the database. This issues can become a concern for applications with high contention.

What is the best practice here? Perhaps one of these approaches:

• Use carefully crafted transaction-aware middleware (your own or 3rd party) to submit queries. This is probably the best choice for most developers.
• Always check and handle return codes and exceptions (the wa-sqlite API wrappers convert SQLite errors to exceptions). This is a good general aspiration but is hard to live up to without a code review culture (and diligent reviewers).
• Use a VFS that only takes exclusive locks. This will eliminate SQLITE_BUSY but will also force read transactions from multiple connections to run sequentially. Most wa-sqlite example VFS classes can be easily customized to a different locking behavior via an import at the top of the source file.

You can use BEGIN IMMEDIATE to start a multi-statement transaction that includes statements that write to the database. This minimizes the window for SQLITE_BUSY, which is generally good practice, but it can still happen and being less likely also makes it harder to catch in casual testing.

It is already possible for a VFS to eliminate SQLITE_BUSY errors by not using any shared locks as noted above, though that is a bit heavy-handed. I believe a smarter, better-targeted solution is possible if the VFS can be told in advance whether the next transaction might write to the database. This signal could be a custom PRAGMA statement that developers or users would add, or maybe SQLite could send a xFileControl opcode upon seeing BEGIN IMMEDIATE, BEGIN EXCLUSIVE, or an autocommit statement that writes. I have proposed this to the SQLite team and there has been a bit of engagement (mainly pointing out, correctly, that my suggestion contained errors) so we'll see if that goes anywhere.