SQLite Forum

Unable to read while database is locked
It's more nuanced than that. In rollback journal mode (ie. the default) there can only be one writer at any time, but concurrent reads are still allowed _until_ the writer is ready to commit its changes.

The write-path escalates through these phases:

1. Acquire RESERVED lock. This indicates intent to write to the database and will fail if a concurrent write transaction is already in progress. RESERVED does not affect read transactions.
2. ... make changes to tables/indices/etc ...
3. Acquire PENDING lock. This indicates that a transaction is ready to be committed. Read transactions can no longer start once PENDING is acquired.
4. Acquire EXCLUSIVE lock. This will fail if any read transactions are still active, so often a writer has to repeatedly retry at this point. But it must have the EXCLUSIVE lock to safely update the main database file.

Full details are here: <https://sqlite.org/lockingv3.html>

A consequence of the locking protocol is that _any_ long-running transaction performed by an app has the potential to cause read-transactions to fail with "Database is locked", because if a write-transaction comes in during the long-running transaction, everything else using the DB will ultimately have to wait for the long-running transaction to finish (so the write-transaction can commit its changes). The application itself is in control of this delay though, as it chooses how persistent it should be in acquiring the EXCLUSIVE lock vs. giving up and aborting the write transaction.

So if you want things to work smoothly, focusing on short transactions is a good rule of thumb.

Other mechanisms to consider in this area are:

* sqlite3_busy_timeout() which controls how long the library should transparently retry when encountering an SQLITE_BUSY error (aka "database is locked")
    * note this does not apply to all lock acquisitions - in particular escalating from SHARED to PENDING (ie. starting a write transaction) will fail immediately if PENDING is already held, because there is no point waiting in this scenario. BEGIN IMMEDIATE can be useful to avoid wasted effort arising from contention here.
* PRAGMAs cache_spill and cache_size -- in the case of a large write transaction, the changes made to the database may exceed sqlite's configured memory cache. If this happens the cache "spills" and the writer will perform the PENDING -> EXCLUSIVE transition long before the transaction is ready to commit. This can result in a much larger window where read-transactions will fail with SQLITE_BUSY.