Transactions in SQLite

A transaction groups one or more statements into a single unit that either commits as a whole or rolls back as a whole. SQLite is fully ACID, and transactions are how you get atomicity and isolation. They also matter for speed: each implicit transaction pays a durability cost on commit, so wrapping a batch of writes in one explicit transaction is the single biggest performance lever for bulk inserts.

This guide covers the transaction commands, the three locking modes you choose at BEGIN, savepoints, how WAL mode changes concurrency, and the SQLITE_BUSY error that surprises most people.

The basic commands

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN starts a transaction, COMMIT makes every change durable, and ROLLBACK discards everything since the BEGIN. If the first UPDATE succeeds but the second fails, you ROLLBACK and the balance is never deducted without the matching credit. BEGIN TRANSACTION is a synonym for BEGIN, and END is a synonym for COMMIT.

Any statement that touches the database automatically starts a transaction if one is not already open, and it commits when the statement finishes. This is autocommit mode. The reason explicit transactions speed up bulk writes is that they replace one fsync-per-statement with one fsync at COMMIT.

Locking modes: DEFERRED, IMMEDIATE, EXCLUSIVE

You can choose when SQLite acquires its write lock:

BEGIN DEFERRED;   -- the default
BEGIN IMMEDIATE;
BEGIN EXCLUSIVE;

• DEFERRED (the default) takes no lock at BEGIN. A read lock is acquired on the first read, and the write lock is acquired only when you first write. This is the most concurrent option but it is also where SQLITE_BUSY ambushes you (see below).
• IMMEDIATE acquires the write lock at BEGIN. Other connections can still read but cannot write. If you know the transaction will write, start it with IMMEDIATE to fail fast instead of partway through.
• EXCLUSIVE acquires a lock that prevents other connections from reading as well. In WAL mode, EXCLUSIVE and IMMEDIATE behave the same way, because WAL readers do not block on a writer.

The practical rule: if a transaction is going to write, use BEGIN IMMEDIATE. It converts a potential mid-transaction SQLITE_BUSY into an immediate, easy-to-retry failure.

The SQLITE_BUSY trap

This is the classic SQLite footgun. Two connections each start a DEFERRED transaction with a read. Both then try to upgrade to a write lock. One gets it; the other gets SQLITE_BUSY. Because it already holds a read lock, it cannot simply wait without risking deadlock, so SQLite returns the error rather than blocking.

Two mitigations:

PRAGMA busy_timeout = 5000;  -- wait up to 5000 ms for a lock before erroring

A busy timeout makes SQLite retry internally for the given milliseconds before returning SQLITE_BUSY. It does not fully solve the deferred-write deadlock above, which is why the second fix is structural: start any write transaction with BEGIN IMMEDIATE so the write lock is taken up front, before any read lock complicates the upgrade.

SAVEPOINT: nested rollback points

SAVEPOINT gives you named checkpoints you can roll back to without abandoning the whole transaction.

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 50);
 
SAVEPOINT before_items;
INSERT INTO order_items (order_id, sku) VALUES (1, 'A1');
INSERT INTO order_items (order_id, sku) VALUES (1, 'BAD');
ROLLBACK TO before_items;   -- undoes both item inserts, keeps the order row
 
RELEASE before_items;        -- discards the savepoint, keeps remaining work
COMMIT;

ROLLBACK TO name rewinds to the savepoint but leaves the transaction open and the savepoint in place. RELEASE name removes the savepoint (and any nested ones), merging its work into the enclosing transaction. Savepoints can nest, which is how libraries implement "nested transactions" on top of SQLite, since BEGIN inside a transaction is an error.

WAL mode and concurrency

By default SQLite uses a rollback journal, where a writer blocks readers and vice versa. Write-Ahead Logging changes that:

PRAGMA journal_mode = WAL;

In WAL mode, writes append to a separate -wal file, so readers see a consistent snapshot from the main database while a writer appends. The result: readers do not block the writer, and the writer does not block readers. There is still only one writer at a time, but the common read-heavy workload stops contending.

WAL is persistent: once set, the database stays in WAL mode across connections until you change it. A few tradeoffs to know: WAL needs the database to be on local disk (not a network filesystem), it creates -wal and -shm sidecar files, and the WAL file grows until a checkpoint folds it back into the main file. PRAGMA wal_checkpoint; runs one manually; SQLite also checkpoints automatically.

Isolation level

SQLite effectively provides serializable isolation, the strongest level, because there is only ever one writer. There are no READ COMMITTED or REPEATABLE READ knobs to set as in PostgreSQL or MySQL. A reader inside a transaction sees a stable snapshot; it will not see another connection's committed changes until it ends its own transaction and starts a new read.

Common mistakes

• Relying on the default DEFERRED for write transactions. Use BEGIN IMMEDIATE for anything that writes to avoid the upgrade-lock SQLITE_BUSY.
• No busy timeout. Without PRAGMA busy_timeout, any contention returns an error immediately. Set a timeout in every connection.
• Calling BEGIN inside an open transaction. It errors. Use SAVEPOINT for nested behavior.
• Forgetting to COMMIT or ROLLBACK. An open transaction holds locks. If a connection dies mid-transaction, the changes roll back, but until then other writers are blocked.
• Expecting WAL on a network share. WAL requires shared memory and local disk. On NFS or SMB it will not work reliably; stay on the rollback journal there.

For batching many inserts inside a single transaction, see the SQLite UPSERT guide for handling duplicate keys, and the indexes guide for keeping those writes fast.

When you are stepping through a transaction by hand to check intermediate state before you commit, Mako's AI autocomplete can draft the SAVEPOINT and ROLLBACK TO statements so you can test a rollback path without rewriting the batch.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.