SQLite has some surprising behavior regarding transactions that bites almost everyone who attempts to use SQLite with multiple concurrent connections.

the problem

If a connection is in an active write transaction, any other connection attempting to upgrade a read transaction to a write transaction will fail immediately with a surprising database is locked error. This happens regardless of what PRAGMA busy_timeout is set to!

understanding why

In SQLite, all query interactions happen in transactions of some kind, whether in a read transaction (select) or a write transaction (insert|update|delete). 1.

SQLite allows for many concurrent readers but only 1 writer at a time. This invariant is enforced with locks, which connections must acquire to peform these reads and writes. 1.

By default, if a transaction starts with a read operation and only later later attempts a write (for example, a select followed by an insert), the default behavior of SQLite is to first start a read transaction by acquiring a read lock, and then lazily attempting to upgrade that read transaction to a write transaction only when the write statement occurs.

This may be surprising, especially the upgrade behavior, but it is all well documented.

(I am simplifying for ease of explanation. More detail about locking under the default rollback journal mode can be found here. This section in particular provides good detail about SQLite’s locking strategy. Additionally, there is WAL mode, which has its own benefits and complications.)

how to fix it

The fix for this issue is twofold:

For each connection to your SQLite database, set PRAGMA busy_timeout to a nonzero value when initializing the connection. busy_timeout is the amount of time in milliseconds that a connection will wait to acquire a write lock. Some SQLite clients like Python’s set this value by default. Others do not, so I recommend you verify this in your client of choice. 1.

For any sequence of SQL statements run together in a transaction that includes at least one write, start the transaction with BEGIN IMMEDIATE rather than BEGIN. This instructs SQLite to immediately attempt to acquire a write lock rather than starting with a read lock and upgrading it to a write lock lazily. If the connection cannot immediately acquire the write lock because another connection has it, the connection attempting to acquire the write lock will busy wait for busy_timeout milliseconds before giving up and throwing the database is locked error.

Again, see SQLite’s transaction documentation for more detail.

I am by no means claiming to have discovered or solved any of this on my own. Others have discussed this topic previously.

demonstration

I’ve provided a Python script at the end of this post that demonstrates both the error as it usually occurs as well as the fix.

To see the error, run: python lock.py

And to see the fix, run: python lock.py correct

The error looks like:

at [ 18:36:38 ] ➜ python lock.py
18:36:39: connection 1: thread started
18:36:39: connection 1: begin
18:36:39: connection 1: select
18:36:39: connection 1: inserted, sleeping
18:36:39: connection 2: thread started
18:36:39: connection 2: busy_timeout=0;
18:36:39: connection 2: attempting begin
18:36:39: connection 2: begin complete
18:36:39: connection 2: select
18:36:39: connection 2: trying to insert
Exception in thread Thread-2 (try_to_insert):
Traceback (most recent call last):
File "/Users/clark/.asdf/installs/python/3.12.7/lib/python3.12/threading.py", line 1075, in _bootstrap_inner
self.run()
File "/Users/clark/.asdf/installs/python/3.12.7/lib/python3.12/threading.py", line 1012, in run
self._target(*self._args, **self._kwargs)
File "/Users/clark/code/lock.py", line 80, in try_to_insert
cur.execute("insert into people (id, name, address) values (1, 2, 3)")
sqlite3.OperationalError: database is locked
18:36:44: connection 1: sleeping over, committing

And with the fix, it looks like:

at [ 18:36:44 ] ➜ python lock.py correct
18:36:56: connection 1: thread started
18:36:56: connection 1: begin
18:36:56: connection 1: select
18:36:56: connection 1: inserted, sleeping
18:36:56: connection 2: thread started
18:36:56: connection 2: busy_timeout=5000
18:36:56: connection 2: attempting begin immediate
18:37:01: connection 1: sleeping over, committing
18:37:01: connection 2: begin immediate complete
18:37:01: connection 2: select
18:37:01: connection 2: trying to insert
18:37:01: connection 2: insert