TopolearnUnderstanding SQLite's single-writer lock is the key to using it reliably from more than one thread or process.
SQLite allows many readers but only one writer at a time for the whole database. Transactions start with:
BEGIN(=BEGIN DEFERRED): takes no lock until the first read or write. A read-then-write transaction must upgrade its lock, and if another connection is writing, that upgrade can fail immediately with SQLITE_BUSY ("database is locked") — waiting wouldn't help because of the risk of deadlock.BEGIN IMMEDIATE: takes the write lock right away (waiting for it per the busy timeout). Use it for any transaction that will write; it avoids the upgrade failure.BEGIN EXCLUSIVE: in rollback-journal mode also blocks readers; in WAL mode it behaves like IMMEDIATE.
Set a busy timeout so connections wait for locks instead of failing: Python's sqlite3.connect(path, timeout=5.0) (5 seconds is the default) or PRAGMA busy_timeout.
Python's transaction control:
- Legacy default: the module implicitly issues
BEGIN(deferred) before DML;isolation_level="IMMEDIATE"makes thatBEGIN IMMEDIATE, andisolation_level=Nonemeans you manageBEGIN/COMMITyourself. - Python 3.12+:
autocommit=Falsegives PEP 249 behaviour — a transaction is always open andcommit()starts the next one;autocommit=Trueleaves everything to explicit SQL.
Keep write transactions short: while one is open, every other writer waits.
Example
import sqlite3, os, tempfile
path = os.path.join(tempfile.mkdtemp(), "app.db")
setup = sqlite3.connect(path)
setup.execute("CREATE TABLE counter (id INTEGER PRIMARY KEY, n INTEGER)")
setup.execute("INSERT INTO counter VALUES (1, 0)")
setup.commit()
a = sqlite3.connect(path, isolation_level=None) # manual transactions
b = sqlite3.connect(path, isolation_level=None, timeout=0.1)
a.execute("BEGIN IMMEDIATE") # a holds the write lock
a.execute("UPDATE counter SET n = n + 1 WHERE id = 1")
try:
b.execute("BEGIN IMMEDIATE") # waits 0.1 s, then fails
except sqlite3.OperationalError as e:
print(e) # database is locked
print(b.execute("SELECT n FROM counter").fetchone()) # (0,) readers still work
a.execute("COMMIT")
b.execute("BEGIN IMMEDIATE"); b.execute("UPDATE counter SET n = n + 1"); b.execute("COMMIT")
print(b.execute("SELECT n FROM counter").fetchone()) # (2,)Remember
- One writer at a time; readers continue
- Use BEGIN IMMEDIATE for transactions that will write
- Set a busy timeout so writers wait instead of failing
Watch out for
- Starting a read-then-write transaction with plain BEGIN and getting random 'database is locked' errors under load.