SQLite § concurrency

Transactions and WAL

A transaction is a sequence of database operations that the engine treats as a single atomic unit: either every operation succeeds and the result is permanently recorded, or none does and the database is left as it was before the transaction began. Transactions are the principal mechanism by which SQL databases preserve consistency under failure (a power loss in the middle of a multi-statement update leaves the database in a recoverable state) and the foundation of multi-user concurrency (two clients reading and writing the same database see consistent snapshots).

SQLite implements ACID transactions — Atomicity, Consistency, Isolation, Durability — with a simple, well-documented locking model. This page covers BEGIN, COMMIT, ROLLBACK, the SAVEPOINT mechanism for nested transactions, the rollback journal and the write-ahead log (WAL) as the two persistence mechanisms, the four locking states, the busy_timeout setting, and the synchronous pragma’s trade-off between durability and write throughput.

BEGIN, COMMIT, ROLLBACK

The simplest transaction:

BEGIN;
UPDATE customer SET email = ? WHERE id = ?;
INSERT INTO email_change_log (customer_id, old_email, new_email) VALUES (?, ?, ?);
COMMIT;

BEGIN opens a transaction. Every subsequent statement is part of the transaction until COMMIT (which makes the changes permanent) or ROLLBACK (which discards them). Outside a transaction, every statement runs in its own implicit transaction — an UPDATE is a transaction of one statement, started and committed automatically.

The keyword variants BEGIN TRANSACTION and BEGIN DEFERRED TRANSACTION are accepted; the unqualified form is the conventional spelling. SQLite further supports two qualifiers that affect the locking strategy:

BEGIN DEFERRED;     -- the default
BEGIN IMMEDIATE;    -- acquire a write lock at BEGIN
BEGIN EXCLUSIVE;    -- acquire an exclusive lock at BEGIN

The qualifiers are explained under Locking below; the recommendation in most application code is BEGIN IMMEDIATE when the transaction is known to write, to avoid the surprise of a SQLITE_BUSY error half-way through.

ROLLBACK discards every change made since the BEGIN:

BEGIN;
UPDATE customer SET email = NULL;       -- whoops
ROLLBACK;                                -- changes discarded

Why transactions matter for performance

A separate, equally important reason to use transactions: each implicit transaction performs a synchronous flush to disk at commit, which is the dominant cost of a write under SQLite’s default durability settings. A loop of one thousand individual INSERT statements performs one thousand flushes; the same one thousand inserts wrapped in a single BEGIN / COMMIT performs one. The performance difference is typically one to two orders of magnitude.

# Slow: 1000 fsyncs
for row in rows:
    conn.execute("INSERT INTO event (payload) VALUES (?)", (row,))

# Fast: 1 fsync
with conn:                                  # implicit BEGIN/COMMIT in Python
    for row in rows:
        conn.execute("INSERT INTO event (payload) VALUES (?)", (row,))

The recommendation is to wrap any multi-statement write — bulk imports, batched updates, the unit-of-work in an HTTP request handler — in an explicit transaction.

SAVEPOINT

A SAVEPOINT is a marker within a transaction that can be rolled back to without rolling back the entire transaction. Savepoints are SQL’s nested-transaction mechanism.

BEGIN;
INSERT INTO customer (name) VALUES ('Ada');

SAVEPOINT s1;
UPDATE customer SET email = 'ada@example.com' WHERE id = 1;
INSERT INTO email_change_log VALUES (1, NULL, 'ada@example.com');
ROLLBACK TO s1;       -- undo the changes since SAVEPOINT s1

INSERT INTO customer (name) VALUES ('Donald');
COMMIT;

After ROLLBACK TO s1, the customer named Ada exists (the inserts before s1 are preserved) but her email update has been discarded. The transaction continues; subsequent statements add new changes that are committed at COMMIT.

RELEASE s1 discards the savepoint without rolling back; subsequent ROLLBACK TO s1 is then an error. RELEASE is the conventional ending for a successful sub-transaction:

SAVEPOINT s1;
…
RELEASE s1;

Savepoints may be nested. Each savepoint within a transaction is independently named; the engine maintains a stack and ROLLBACK TO accepts any savepoint on the stack.

Atomicity, Consistency, Isolation, Durability

The four properties of a correct transaction system, with SQLite’s implementation noted:

Atomicity: a transaction either commits in full or rolls back in full. SQLite’s rollback journal (or WAL) records the pre-transaction state; a crash mid-transaction is recoverable to the consistent pre-transaction state.
Consistency: a transaction takes the database from one consistent state to another. Constraints (CHECK, FOREIGN KEY, UNIQUE, NOT NULL) are enforced at end-of-statement (or end-of-transaction for deferred constraints); a transaction that would violate them rolls back.
Isolation: concurrent transactions do not see each other’s intermediate state. SQLite’s default isolation is serialisable: a reader sees a consistent snapshot of the database at the time the read began, regardless of subsequent writes.
Durability: a committed transaction is persistent across process and host failures. SQLite achieves this with fsync (or fdatasync) at commit time; the PRAGMA synchronous setting controls the trade-off.

The rollback journal

The default persistence mode in older SQLite versions, and still the default in some build configurations, is the rollback journal. The mechanism:

Before modifying a database page, the original content is copied to a separate journal file (<database>-journal).
The page is then modified in the database file.
At COMMIT, the journal file is deleted; the database is the authoritative state.
At ROLLBACK, the journal is replayed in reverse, restoring the original pages.
After a crash, on next open, the engine checks for an orphan journal and replays it as a rollback.

The rollback journal is simple and robust. Its limitation is that readers and writers contend: a writer holds a pending lock that prevents new readers from starting, and a reader holds a shared lock that prevents writers from committing. For workloads with many concurrent readers and occasional writers, the rollback journal serialises access in a way that limits throughput.

Write-ahead log (WAL)

The write-ahead log (WAL) was introduced in version 3.7 (2010) as an alternative to the rollback journal. It is the recommended journal mode for most workloads:

PRAGMA journal_mode = WAL;

The mechanism:

Modifications are appended to a separate WAL file (<database>-wal).
The database file itself is unchanged during the transaction.
At COMMIT, the WAL is flushed and a checkpoint pointer is updated.
Periodic checkpoints copy committed changes from the WAL back to the database file.
Readers read from the database file plus the WAL up to the checkpoint pointer; they see a consistent snapshot.

The WAL’s principal advantage is that readers do not block writers and writers do not block readers. A reader holds a snapshot of the database and the WAL up to a particular point; a writer extends the WAL without affecting the readers’ snapshots. Concurrent transactions proceed without contention, except that there can be only one writer at a time — multiple writers serialise on the WAL.

WAL mode persists in the database file: once enabled, subsequent connections see the same mode without re-issuing the pragma. The recommendation is to enable WAL during initial database creation:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;     -- typically paired with WAL

The synchronous = NORMAL pairing is explained below.

Checkpointing

The WAL grows as transactions are committed; without periodic checkpoints, it would grow without bound. SQLite checkpoints automatically when the WAL exceeds a threshold (default 1000 pages, ~4 MB), but the application can issue an explicit checkpoint:

PRAGMA wal_checkpoint(PASSIVE);     -- only commit pages that don't block writers
PRAGMA wal_checkpoint(FULL);        -- block writers if necessary; ensure full checkpoint
PRAGMA wal_checkpoint(RESTART);     -- full checkpoint plus restart the WAL from page 0
PRAGMA wal_checkpoint(TRUNCATE);    -- full checkpoint plus truncate the WAL file

The default automatic checkpoint is PASSIVE. An application that wants to ensure the WAL stays small can run PRAGMA wal_checkpoint(TRUNCATE) at quiet moments (the end of an HTTP request, on connection close).

Locking states

A connection’s interaction with the database moves through five locking states:

State	Meaning
`UNLOCKED`	No lock held. The connection has not yet read or written.
`SHARED`	A shared lock; permits concurrent readers.
`RESERVED`	A reserved lock; the connection intends to write. One reserved lock at a time.
`PENDING`	A pending lock; new readers are blocked, existing readers are draining.
`EXCLUSIVE`	An exclusive lock; the writer is committing.

In rollback-journal mode, a writer escalates SHARED → RESERVED → PENDING → EXCLUSIVE. Other connections cannot start new reads while the writer is in PENDING or EXCLUSIVE. In WAL mode, the locking is more relaxed: a writer holds an exclusive lock only on the WAL, and readers continue against their snapshot of the database file.

The BEGIN qualifiers control the timing of the lock escalation:

Qualifier	Locking
`DEFERRED` (default)	No lock at `BEGIN`; locks acquired as needed.
`IMMEDIATE`	Acquire `RESERVED` lock at `BEGIN`.
`EXCLUSIVE`	Acquire `EXCLUSIVE` lock at `BEGIN`.

BEGIN DEFERRED postpones lock acquisition. The first SELECT acquires SHARED; the first INSERT, UPDATE, or DELETE acquires RESERVED. The lazy acquisition can fail mid-transaction with SQLITE_BUSY if another writer has the database, leading to a deadlock-like condition where two transactions, each holding SHARED and trying to upgrade to RESERVED, each fail.

BEGIN IMMEDIATE avoids the issue by acquiring RESERVED at the start. If another writer holds the lock, the BEGIN itself blocks (or fails after busy_timeout), but once it succeeds, the transaction is guaranteed not to fail with SQLITE_BUSY later. The recommendation in any code that writes is BEGIN IMMEDIATE.

busy_timeout

When a connection encounters a lock held by another connection, the default behaviour is to fail immediately with SQLITE_BUSY. The PRAGMA busy_timeout setting causes the engine to retry for the specified milliseconds before giving up:

PRAGMA busy_timeout = 5000;       -- wait up to 5 seconds for a lock

The recommendation is to set a generous busy_timeout — five to thirty seconds — at the start of every connection. A short timeout produces transient errors under contention; a long timeout converts contention into latency, which is typically the more graceful failure mode for an application.

SQLITE_BUSY is non-recoverable within a transaction in some cases: the connection’s transaction is rolled back automatically, and the application must retry from the beginning. Production code wraps writes in a retry loop:

def write_with_retry(conn, fn, max_attempts=5):
    for attempt in range(max_attempts):
        try:
            with conn:           # BEGIN/COMMIT
                return fn(conn)
        except sqlite3.OperationalError as e:
            if 'locked' in str(e) or 'busy' in str(e):
                time.sleep(0.05 * (2 ** attempt))    # exponential backoff
                continue
            raise
    raise RuntimeError('Could not acquire lock after retries')

synchronous

The PRAGMA synchronous setting controls how aggressively the engine flushes to disk at commit:

Value	Behaviour
`OFF` (0)	No flush. Fastest, least durable.
`NORMAL` (1)	Flush at WAL checkpoint; recommended in WAL mode.
`FULL` (2)	Flush at every commit. Default in rollback-journal mode.
`EXTRA` (3)	Flush plus extra fsync; durability against rare hardware races.

The trade-off is durability against write throughput. OFF permits transactions to be lost on a power failure; the database remains internally consistent (no torn writes), but recently committed transactions may not be on disk. FULL guarantees that committed transactions survive any failure short of disk corruption.

The recommendation:

For databases storing primary application data: synchronous = FULL.
For databases storing data that can be rebuilt (caches, indexes, computed reports): synchronous = NORMAL is acceptable.
For ephemeral databases (in-memory, scratch): synchronous = OFF.

NORMAL is the canonical choice with WAL mode; FULL with the rollback journal.

DCL is absent

SQL’s data-control language — GRANT, REVOKE, the user-and-role privilege model — is absent in SQLite. SQLite has no concept of a database user; access control is the responsibility of the host application. The conventional mechanisms:

File-system permissions on the database file.
Connection strings with read-only flags (mode=ro in the URI form).
Application-level access control before the SQL is constructed.

The PRAGMA query_only = ON setting forbids writes for the connection, which is a useful application-level lock-down for connections that should not modify the database. The setting is connection-local and does not survive close.

A summary discipline

The recommendations applicable to a typical application:

Set PRAGMA journal_mode = WAL at database creation.
Set PRAGMA synchronous = NORMAL (with WAL) or FULL (with rollback journal).
Set PRAGMA busy_timeout = 5000 at the start of every connection.
Set PRAGMA foreign_keys = ON at the start of every connection.
Wrap every multi-statement write in BEGIN IMMEDIATE / COMMIT.
Wrap writes in a retry loop that handles SQLITE_BUSY with exponential backoff.
Use SAVEPOINT for sub-transactions that may need to be rolled back independently.
Run periodic VACUUM (or PRAGMA incremental_vacuum) to reclaim space.
Run PRAGMA optimize on connection close to refresh the planner’s statistics.
For long-running applications, periodic PRAGMA wal_checkpoint(TRUNCATE) keeps the WAL from growing unbounded under sustained write load.

The settings together produce a database connection that is fast, durable, and resilient under contention. They are the canonical configuration for an SQLite-backed application.