SQLite: 'database is locked' and how to fix it

SQLite keeps a whole database in one file and lets many connections read at once but only one write at a time. When a second connection tries to write while another holds the write lock, it waits for a timeout and then fails with database is locked.

Reproduce it

Connection a starts writing and does not commit. Connection b tries to write to the same file.

import os
import sqlite3
import tempfile

path = os.path.join(tempfile.mkdtemp(), "t.db")
a = sqlite3.connect(path, timeout=0.2)
a.execute("CREATE TABLE t (x INTEGER)")
a.commit()

a.execute("BEGIN IMMEDIATE")
a.execute("INSERT INTO t VALUES (1)")   # a holds the write lock and has not committed

b = sqlite3.connect(path, timeout=0.2)
try:
    b.execute("INSERT INTO t VALUES (2)")
except sqlite3.OperationalError as error:
    print("b:", error)

a.commit()                              # a lets go
b.execute("INSERT INTO t VALUES (2)")
b.commit()
print("after a commits:", b.execute("SELECT COUNT(*) FROM t").fetchone())

b: database is locked
after a commits: (2,)

Nothing is broken. b simply gave up waiting before a finished. As soon as a committed, b succeeded. So the cure comes down to one of two things: make the holder let go sooner, or make the waiter wait longer or less often.

Fix 1: keep write transactions short

This is the real fix. Do slow work (network calls, parsing, user input) before you begin a transaction, then write and commit straight away. In Python's sqlite3, a transaction starts at your first INSERT, UPDATE or DELETE and lasts until commit(). A with conn: block commits for you.

rows = fetch_and_parse_everything()   # slow work first, no transaction open
with conn:                            # then a short write
    conn.executemany("INSERT INTO t VALUES (?)", rows)

Fix 2: set a busy timeout

A timeout tells SQLite how long to retry while another connection holds the lock, instead of failing at once. Python's default is 5 seconds. Raise it if you have occasional short collisions:

conn = sqlite3.connect("app.db", timeout=10)
conn.execute("PRAGMA busy_timeout = 10000")  # the same idea, in milliseconds

Fix 3: use write-ahead logging (WAL)

In the default journal mode a writer blocks readers. In WAL mode readers and the writer proceed together. Turn it on once; it persists in the database file.

conn = sqlite3.connect("app.db")
print(conn.execute("PRAGMA journal_mode=WAL").fetchone())   # ('wal',)

WAL needs the database to be on a normal local disk. It is not reliable on some network file systems, and it adds two small side files (-wal and -shm) next to the database, so include them when you copy or back up the database while it is open.

Fix 4: stop holding a read open while you write

A pattern that surprises people: looping over a SELECT cursor and writing to the same database inside the loop. Fetch first, then write.

ids = [row[0] for row in conn.execute("SELECT id FROM jobs WHERE done = 0")]   # read finished
with conn:
    conn.executemany("UPDATE jobs SET done = 1 WHERE id = ?", [(i,) for i in ids])

If it keeps happening

If several processes write constantly (a web application with many workers, plus scheduled jobs), you are asking SQLite to do something it was designed not to do. Either route all writes through one process or queue, or move to a server database such as PostgreSQL, where concurrent writers are the normal case.