Unable to read while database is locked
(1) By Alex (palu3492) on 2022-01-13 16:15:31 [link] [source]
Hello, I'm using SQLite 3.22.0 in Python 3 with sqlalchemy. My application is multithreaded and I'm attempting to read and write from the database concurrently. While one thread is writing, my other threads attempting to read are being blocked and I'm receiving the "database is locked" OperationalError. How can I configure SQLite to allow reads while writes are in-progress in other threads? Thanks.
(2) By David Raymond (dvdraymond) on 2022-01-13 16:49:22 in reply to 1 [link] [source]
SQLite can only ever have 1 writer at a time. In order to have concurrent readers while that 1 writer is active you have to use WAL mode
(3) By Rowan Worth (sqweek) on 2022-01-13 21:51:48 in reply to 2 [source]
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:
- 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.
- ... make changes to tables/indices/etc ...
- Acquire PENDING lock. This indicates that a transaction is ready to be committed. Read transactions can no longer start once PENDING is acquired.
- 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.
(4) By Alex (palu3492) on 2022-01-15 15:42:29 in reply to 3 [link] [source]
That clears things up very much. I read more of the SQLite docs and have a much better understanding of what's going with locking and the journal modes. I've enabled WAL journal mode and that has solved my read issue. I also refactored my code to remove a few long-running write transactions. Thank you!