How to fix SQLITE_BUSY: a step-by-step playbook

To fix SQLITE_BUSY, apply these in order and stop when the errors stop: set a busy timeout on every connection, start every write transaction with BEGIN IMMEDIATE, switch the database to WAL mode, shorten write transactions, and finish every SELECT. If errors remain after that, route writes through one connection and retry whole transactions with backoff. Each step below says what it fixes, how to apply it, and how to check that it took effect, and there is a script at the start that reproduces the problem so you can watch each step work.

For the lock states behind these rules, read SQLITE_BUSY error: what it means and what causes it. How do I fix "database is locked" in SQLite? is the short version of the core settings.

Step 0: reproduce it and classify the failure

Lock errors are intermittent, so start with something that fails on demand. This script runs several processes that each do a read-modify-write transaction against one row. It needs only the Python standard library.

"""contend.py: reproduce SQLITE_BUSY, then turn fixes on one at a time."""
import argparse, collections, multiprocessing, os, sqlite3, time

DB = "contend.db"

def connect(args):
    # isolation_level=None: the module never issues BEGIN itself.
    conn = sqlite3.connect(DB, timeout=0, isolation_level=None)
    conn.execute(f"PRAGMA busy_timeout = {args.timeout}")
    return conn

def worker(args, results):
    conn = connect(args)
    errors = collections.Counter()
    done = 0
    for _ in range(args.txns):
        try:
            conn.execute("BEGIN IMMEDIATE" if args.immediate else "BEGIN")
            (n,) = conn.execute("SELECT n FROM counter WHERE id = 1").fetchone()
            conn.execute("UPDATE counter SET n = ? WHERE id = 1", (n + 1,))
            conn.execute("COMMIT")
            done += 1
        except sqlite3.OperationalError as exc:
            errors[getattr(exc, "sqlite_errorname", str(exc))] += 1
            if conn.in_transaction:
                conn.execute("ROLLBACK")
    conn.close()
    results.put((done, errors))

def main():
    p = argparse.ArgumentParser()
    p.add_argument("--workers", type=int, default=8)
    p.add_argument("--txns", type=int, default=200)
    p.add_argument("--timeout", type=int, default=0)   # busy_timeout, ms
    p.add_argument("--immediate", action="store_true")
    p.add_argument("--wal", action="store_true")
    args = p.parse_args()

    for suffix in ("", "-wal", "-shm", "-journal"):
        if os.path.exists(DB + suffix):
            os.remove(DB + suffix)
    setup = sqlite3.connect(DB, isolation_level=None)
    setup.execute(f"PRAGMA journal_mode = {'WAL' if args.wal else 'DELETE'}")
    setup.execute("CREATE TABLE counter (id INTEGER PRIMARY KEY, n INTEGER NOT NULL)")
    setup.execute("INSERT INTO counter VALUES (1, 0)")
    setup.close()

    results = multiprocessing.Queue()
    procs = [multiprocessing.Process(target=worker, args=(args, results))
             for _ in range(args.workers)]
    start = time.monotonic()
    for proc in procs:
        proc.start()
    totals, done = collections.Counter(), 0
    for _ in procs:
        d, errs = results.get()
        done += d
        totals.update(errs)
    for proc in procs:
        proc.join()

    check = sqlite3.connect(DB)
    (n,) = check.execute("SELECT n FROM counter WHERE id = 1").fetchone()
    check.close()
    attempted = args.workers * args.txns
    print(f"attempted={attempted} committed={done} counter={n} "
          f"errors={dict(totals)} elapsed={time.monotonic() - start:.1f}s")

if __name__ == "__main__":
    main()

The exact counts change from run to run. The pattern does not. (On Python older than 3.11 the script reports the message text, database is locked, for every failure, because the extended code is not exposed.)

Run it as python3 contend.py and add flags:

Flags	What you should see
none	Most transactions fail with SQLITE_BUSY
--timeout 5000	Far fewer failures, but not zero, and the failures come back instantly
--timeout 5000 --wal	Still many failures, now including SQLITE_BUSY_SNAPSHOT
--timeout 5000 --immediate	errors={} and counter equals attempted
--timeout 5000 --immediate --wal	errors={} again

A timeout alone, or WAL alone, leaves a transaction that reads and then writes exposed to a failure that no amount of waiting resolves.

For your real application, classify a failure before changing anything:

Observation	Likely cause	Go to
Fails instantly and PRAGMA busy_timeout on that connection returns 0	No busy handler	Step 1
Fails instantly with a timeout set, on the first write of a transaction that already read	Lock upgrade refused	Step 2
Extended code is SQLITE_BUSY_SNAPSHOT (517)	Stale snapshot on upgrade, WAL mode	Steps 2 and 8
Fails at COMMIT, or readers fail while a write commits, in rollback-journal mode	Readers and the writer blocking each other	Step 3
Fails only after the full timeout	Someone holds the write lock longer than the timeout	Steps 4 to 6
database table is locked	SQLITE_LOCKED, a conflict inside one connection	Step 5

Python 3.11 and later puts the extended code on the exception as sqlite_errorname. In C, call sqlite3_extended_errcode().

Step 1: set a busy timeout on every connection

Fixes: failures caused by ordinary, brief contention. With no busy handler, SQLite returns SQLITE_BUSY the moment a lock is unavailable, and no handler is the library default.

PRAGMA busy_timeout = 5000;   -- milliseconds

The setting belongs to the connection. It is not stored in the database file, so each new connection, including every connection a pool opens later, starts without it unless the driver or your connection hook sets it. Set it first, before any other pragma: PRAGMA journal_mode = WAL can itself hit a lock when several processes start at once.

Pick a value longer than your slowest legitimate write transaction and shorter than your request timeout.

Verify: run PRAGMA busy_timeout; on a connection taken from the pool the same way application code takes it. It must return your value, not 0. Failures that take the full timeout to appear point to Step 4. Failures that are still instant point to Step 2.

Step 2: start write transactions with BEGIN IMMEDIATE

Fixes: instant failures that ignore the timeout.

A plain BEGIN is deferred. It takes a read lock at the first SELECT and tries to upgrade to the write lock at the first write. SQLite does not invoke the busy handler for an upgrade, because the connection already holds a read lock that the current writer may be waiting on. In WAL mode the upgrade is also refused outright if anyone has committed since the read began.

BEGIN IMMEDIATE takes the write lock at the start, before the connection holds anything, so the busy handler applies and the transaction waits its turn:

BEGIN IMMEDIATE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT;

Use it for every transaction that might write. Keep plain BEGIN, or no explicit transaction, for read-only work so readers do not queue behind the write lock.

A single write statement in autocommit mode does not need this. It starts with no lock held, so the busy handler applies to it already.

Verify: trace the SQL your driver sends and confirm the text is BEGIN IMMEDIATE; ORMs often issue BEGIN themselves and need a setting to change it. Afterwards, every remaining busy error should come from the BEGIN IMMEDIATE statement itself, after the full timeout.

In WAL mode, once BEGIN IMMEDIATE succeeds nothing later in the transaction fails with SQLITE_BUSY. In rollback-journal mode COMMIT still has to wait for readers, which is the next step.

Step 3: switch to WAL mode

Fixes: readers and the writer blocking each other. In rollback-journal mode a commit needs every reader out of the file, and readers are locked out while a commit is in progress. In WAL mode neither happens.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

journal_mode = WAL is stored in the database file, so it only has to succeed once. synchronous is per connection. NORMAL in WAL mode cannot corrupt the database but can lose the most recent commits on power loss or an operating system crash; keep FULL if that is not acceptable. Do not use WAL when the file is on a network filesystem.

Verify: the pragma returns the resulting mode as a row. If it returns delete, the switch did not happen. Afterwards PRAGMA journal_mode; on any connection returns wal, and -wal and -shm files sit next to the database while it is open.

Step 4: shorten write transactions

Fixes: failures that arrive only after the full timeout. Those mean one transaction held the write lock longer than everyone else was willing to wait. Raising the timeout hides this for a while; shortening the transaction fixes it.

• Do network calls, file reads, template rendering and any waiting on user input before BEGIN IMMEDIATE or after COMMIT. Compute first, then open the transaction, write, and commit.
• Split long batch jobs into chunks of a few hundred or thousand rows, committing between chunks so other writers can interleave.
• Do the opposite for many tiny writes from one worker: group them into one transaction, so the lock is taken once.
• Create indexes and run VACUUM or large migrations in a maintenance window. They are single long write transactions.

Verify: record the time between BEGIN IMMEDIATE returning and COMMIT returning, and log any transaction that exceeds a fraction of your busy timeout. The longest write transaction, multiplied by the number of writers that can queue behind it, has to fit inside the timeout.

Step 5: finish every statement

Fixes: locks held by transactions nobody knew were open.

A SELECT that has returned some rows but has not been read to the end, reset or finalized keeps its read transaction alive. In rollback-journal mode that blocks every commit. In WAL mode it does not block writers, but it pins a snapshot, so the next write on that same connection becomes an upgrade from a stale read and fails with SQLITE_BUSY_SNAPSHOT. It also prevents checkpoints from completing, so the -wal file grows.

• Read result sets to the end, or close the cursor or finalize the statement explicitly.
• On any error inside a transaction, roll back. SQLITE_BUSY does not roll back for you, and a connection returned to a pool mid-transaction keeps its locks.
• Close incremental BLOB handles, which count as open statements.

Verify: when the application is idle, no connection should be inside a transaction. sqlite3_get_autocommit() should return non-zero on each one (Connection.in_transaction is False in Python), and in C you can walk sqlite3_next_stmt() and test sqlite3_stmt_busy() to name the statement that is still active.

Step 6: one writer connection and a pool of readers

Fixes: contention between your own writers, and the unfairness of the busy handler. The handler polls rather than queues, so under sustained load an unlucky connection can keep losing until it times out.

SQLite permits one writer at a time regardless, so make that explicit:

• Open one connection used only for writes. Guard it with a mutex or feed it from a queue so one transaction runs at a time.
• Open a pool of connections used only for reads. In WAL mode they are never blocked by the writer.
• Never write from a read connection.

Writers now wait in your queue, in order, instead of polling a file lock. This works within one process; separate worker processes still compete through the file lock, so the busy timeout stays.

Verify: inside one process, SQLITE_BUSY should disappear completely. If it still occurs, either a read connection is writing or another process is involved.

Step 7: retry what is left, as whole transactions

After the earlier steps, the only expected failure is BEGIN IMMEDIATE timing out under a burst of writes. Retrying that is safe because nothing has happened yet. Add a short randomised delay so competing writers do not collide again in step:

import random, sqlite3, time

def run_write(conn, work, attempts=5):
    """Run work(conn) in a write transaction, retrying if the lock is busy.

    conn must not start transactions on its own (isolation_level=None).
    """
    for attempt in range(attempts):
        try:
            conn.execute("BEGIN IMMEDIATE")
        except sqlite3.OperationalError as exc:
            if "database is locked" not in str(exc) or attempt == attempts - 1:
                raise
            time.sleep(random.uniform(0, 0.05 * 2 ** attempt))
            continue
        try:
            result = work(conn)
            conn.execute("COMMIT")
            return result
        except BaseException:
            if conn.in_transaction:
                conn.execute("ROLLBACK")
            raise

conn = sqlite3.connect("app.db", timeout=5.0, isolation_level=None)
conn.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, body TEXT)")
run_write(conn, lambda c: c.execute("INSERT INTO events (body) VALUES (?)", ("hello",)))

Never retry a single statement in the middle of a transaction; rerun the transaction from its first statement. Keep the work function free of side effects outside the database, because it may run more than once.

Verify: count retries. A count that rises with load means the write path is saturated and you are back at Step 4 or Step 6.

Step 8: handle SQLITE_BUSY_SNAPSHOT

SQLITE_BUSY_SNAPSHOT (extended code 517) occurs only in WAL mode, when a transaction that has already read tries to write after another connection committed. With Step 2 applied everywhere it should not occur. When it still does, look for a framework that opens a deferred transaction per request and only sometimes writes, or a statement left open on the connection (Step 5), which turns the next write into an upgrade.

Where you cannot decide up front that a transaction will write, catch the error, roll back, and rerun the whole transaction. The rollback is required: the connection keeps its stale snapshot until the transaction ends, so retrying only the failed statement fails the same way every time.

Driver settings

The same settings exist in every driver under different names. These come from each project's documentation.

Driver	Busy timeout	Immediate transactions	WAL
Python sqlite3	connect(path, timeout=5.0), in seconds; the default is 5	isolation_level=None and send BEGIN IMMEDIATE yourself	conn.execute("PRAGMA journal_mode=WAL")
Node better-sqlite3	new Database(path, { timeout: 5000 }), in ms; the default is 5000	db.transaction(fn).immediate(...)	db.pragma('journal_mode = WAL')
Node node:sqlite	new DatabaseSync(path, { timeout: 5000 }), in ms; the default is 0	db.exec('BEGIN IMMEDIATE')	db.exec('PRAGMA journal_mode = WAL')
Go mattn/go-sqlite3	_busy_timeout=5000 in the DSN	_txlock=immediate in the DSN	_journal_mode=WAL in the DSN
Go modernc.org/sqlite	_pragma=busy_timeout(5000) in the DSN	_txlock=immediate in the DSN	_pragma=journal_mode(WAL) in the DSN

The timeout option of node:sqlite was added in Node.js 22.16 and 24.0.

A complete better-sqlite3 setup:

const Database = require('better-sqlite3');

const db = new Database('app.db', { timeout: 5000 });
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
db.exec('CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)');
db.prepare('INSERT OR IGNORE INTO accounts VALUES (1, 100), (2, 0)').run();

const transfer = db.transaction((from, to, amount) => {
  db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, from);
  db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, to);
});

transfer.immediate(1, 2, 10);   // BEGIN IMMEDIATE ... COMMIT

The better-sqlite3 build already defaults WAL databases to synchronous = NORMAL, so the second pragma only makes the choice visible.

In Go, DSN parameters apply to every connection that database/sql opens, which is what you want for the timeout. _txlock=immediate applies to every db.Begin() on that handle, read-only transactions included, so use two handles and cap the writer at one connection:

writeDB, err := sql.Open("sqlite3",
    "file:app.db?_busy_timeout=5000&_journal_mode=WAL&_txlock=immediate")
if err != nil {
    log.Fatal(err)
}
writeDB.SetMaxOpenConns(1)

readDB, err := sql.Open("sqlite3", "file:app.db?_busy_timeout=5000")

The mattn/go-sqlite3 README also suggests cache=shared for this error. SQLite's documentation calls shared-cache mode obsolete and discourages it, and conflicts in that mode surface as SQLITE_LOCKED, which the busy timeout does not cover. Skip it.

Common mistakes

• Setting the timeout on one connection. Pools, per-thread connections and worker processes all need it.
• Raising the timeout to thirty seconds or more. Find the long transaction instead.
• Using BEGIN EXCLUSIVE in rollback-journal mode to be safe. It blocks readers for the whole transaction. IMMEDIATE is enough.
• Expecting settings to fix shared storage. Locking on NFS and SMB can be unreliable, and WAL does not work across hosts.

Checklist

1. Reproduce the failure and record the extended code, the failing statement, and whether it was instant.
2. PRAGMA busy_timeout returns a non-zero value on every connection, set before other pragmas.
3. Every transaction that can write begins with BEGIN IMMEDIATE; read-only transactions do not.
4. PRAGMA journal_mode returns wal, and the file is on local storage.
5. No network calls or slow work inside write transactions; batch jobs commit in chunks.
6. Result sets are fully read or closed; every error path rolls back.
7. Within a process, writes go through one connection.
8. Remaining BEGIN IMMEDIATE timeouts are retried as whole transactions with jittered backoff, and retries are counted.

References

• Result and error codes (sqlite.org): SQLITE_BUSY, SQLITE_BUSY_SNAPSHOT
• Transactions: DEFERRED, IMMEDIATE and EXCLUSIVE
• PRAGMA busy_timeout and sqlite3_busy_handler()
• Write-ahead logging
• Shared-cache mode, including the note that its use is discouraged
• Python sqlite3 module
• better-sqlite3 API documentation
• Node.js node:sqlite
• mattn/go-sqlite3 connection string parameters
• modernc.org/sqlite package documentation