Transactions, locking and concurrency

BEGIN IMMEDIATE versus DEFERRED, busy_timeout, one writer at a time, connection per thread, and WAL behaviour under load.

Journal modes and lock states

pragma journal_mode;          -- delete (default), wal, truncate, memory, off
pragma journal_mode = wal;    -- persistent: keeps the setting in the file
pragma synchronous = normal;  -- WAL: safe against application crashes
pragma busy_timeout = 5000;   -- wait up to 5s for a lock instead of failing
pragma foreign_keys = on;     -- off by default, per connection

-- DEFERRED: the write lock is taken at the first write statement
begin deferred;
-- IMMEDIATE: the write lock is taken now, so a conflict fails fast
begin immediate;
-- EXCLUSIVE: also blocks readers (rarely what you want in WAL)
begin exclusive;

• SQLite allows exactly one writer at a time, regardless of journal mode. WAL removes the reader-writer conflict, not the writer-writer conflict.
• busy_timeout makes a blocked writer wait instead of returning SQLITE_BUSY immediately. Set it on every connection, because it is per connection.
• synchronous = normal in WAL mode can lose the last transactions after a power failure but not corrupt the database. full is safe at a real cost per commit.
• A DEFERRED transaction that reads and then writes can fail with SQLITE_BUSY even though it was the only transaction when it started.

Connections and concurrency

import sqlite3, threading

# a connection per thread: SQLite objects are not thread-safe across threads
local = threading.local()

def conn():
    if not getattr(local, "c", None):
        c = sqlite3.connect("app.db", timeout=5.0, isolation_level=None)
        c.execute("pragma journal_mode=wal")
        c.execute("pragma synchronous=normal")
        c.execute("pragma busy_timeout=5000")
        c.execute("pragma foreign_keys=on")
        local.c = c
    return local.c

def transfer(from_id, to_id, amount):
    c = conn()
    c.execute("begin immediate")            # acquire the write lock up front
    try:
        c.execute("update account set balance = balance - ? where id = ?", (amount, from_id))
        c.execute("update account set balance = balance + ? where id = ?", (amount, to_id))
        c.execute("commit")
    except Exception:
        c.execute("rollback")
        raise

• isolation_level=None in Python's driver disables the module's implicit transaction handling so your explicit begin immediate is honoured. Without it the driver may start a DEFERRED transaction for you.
• Batching inserts inside one transaction is orders of magnitude faster than one transaction per row, because each commit is an fsync.
• Keep transactions short. A read transaction that stays open in WAL mode prevents checkpointing from reclaiming the WAL file, which grows without bound.
• Set wal_autocheckpoint or run pragma wal_checkpoint(truncate) after a bulk load to bring the WAL file back down.

-- bulk insert: one transaction, one fsync
begin immediate;
insert into reading (sensor, ts, value) values (?, ?, ?);
-- ... thousands of rows through the same prepared statement
commit;

-- check the WAL size and checkpoint it after a large import
pragma wal_checkpoint(truncate);

Designing around a single writer

• Queue writes in the application so one thread or process owns the write path. A single writer with a queue often beats fighting for locks.
• Batch writes with a short delay: collecting events for 100 ms and inserting them in one transaction is far cheaper than one commit per event.
• Never hold a transaction open across a network call, a file write or user input. The write lock is global to the database.
• If you need multiple writers, consider a client-server database. Serialising writes in the application is a legitimate design, but it is a design decision you must make explicitly.
• Use a SEPARATE read-only connection for reporting queries so a long scan does not compete with the write path for the same connection.
• pragma wal_checkpoint(passive) is safe to call from a background thread; it does as much as it can without blocking.

-- diagnose lock contention
pragma busy_timeout;            -- is it actually set on this connection?
select * from pragma_wal_checkpoint;
pragma wal_checkpoint(passive);

-- a read-only connection for analytics
-- file:app.db?mode=ro&immutable=0
-- and in SQL code:
pragma query_only = on;         -- rejects writes on this connection

FAQ

Related

WAL mode, backups, and when not to use SQLite Testing, tooling and benchmarks for SQLite apps

Last refreshed 2026-09-18.