SQLite BEGIN DEFERRED, IMMEDIATE, and EXCLUSIVE: Handling SQLITE_BUSY Mid-Transaction

A plain BEGIN in SQLite starts a DEFERRED transaction. Because of that default, an UPDATE in the middle of a transaction can fail with SQLITE_BUSY right away, even when busy_timeout is set to a generous value. This post covers what the three BEGIN forms actually do differently, what happens when a read transaction turns into a write transaction, and what you should re-run depending on where the error came from.

Everything here is based on the official SQLite documentation, mainly the BEGIN TRANSACTION page, the result codes page, the isolation page, and the busy handler page, as checked on 2026-10-15.

How the three BEGIN forms differ

According to the BEGIN documentation, the default is DEFERRED.

Form	What it does at the start	Caveats from the docs
`BEGIN` / `BEGIN DEFERRED`	Only turns off autocommit; the transaction actually starts when the database is first accessed	If the first statement is a `SELECT`, a read transaction starts; a later write either upgrades it to a write transaction or returns `SQLITE_BUSY`
`BEGIN IMMEDIATE`	Starts a write transaction right away, without waiting for a write statement	`BEGIN IMMEDIATE` itself can fail with `SQLITE_BUSY` if another connection already has a write transaction
`BEGIN EXCLUSIVE`	Starts a write transaction right away, like `IMMEDIATE`	Same as `IMMEDIATE` in WAL mode; in other journal modes it also keeps other connections from reading during the transaction

If the first statement in a DEFERRED transaction is a write, it starts as a write transaction from the beginning. The trouble comes from the common “read first, then write based on what you read” pattern.

Why the read-to-write upgrade fails

The BEGIN page says that a write inside a read transaction upgrades it to a write transaction “if possible”. If another connection has already modified the database or is in the middle of modifying it, the upgrade is not possible and the write statement fails with SQLITE_BUSY. The underlying cause differs a little by journal mode.

WAL mode: the snapshot is stale

The isolation page explains that a read transaction in WAL mode keeps seeing the snapshot from when it started. If another connection commits a write in the meantime, this connection’s snapshot is no longer the latest. An attempt to upgrade to a write then fails with SQLITE_BUSY_SNAPSHOT (extended code 517). The docs say that allowing the write would fork the history of the database file, which SQLite does not support, and that the connection must first release its snapshot (for example with ROLLBACK) and then start a new transaction with a fresh BEGIN.

So this is not a lock wait that clears if you are patient. Even after the other writer finishes, your snapshot still points to the past, so instead of holding on inside the same transaction you need to end it and run it again from the top.

Rollback journal mode: deadlock avoidance

The lock states for non-WAL mode are described on the locking page. A reader holds SHARED, a connection preparing to write holds RESERVED (only one at a time), and actually writing to the file requires EXCLUSIVE, which can only be taken once all other SHARED locks are gone.

The busy handler page uses exactly this situation as its example. One process holds SHARED and wants RESERVED; another holds RESERVED and wants EXCLUSIVE. Each blocks the other. If both waited in their busy handlers, neither would make progress, so SQLite skips the busy handler and returns SQLITE_BUSY to the first process right away. In the docs’ words, the hope is that this induces that process to release its read lock.

busy_timeout is built on top of the busy handler (sqlite3_busy_timeout, PRAGMA busy_timeout). When the busy handler is not invoked, the error comes back immediately no matter what timeout you configured. The fix is the same as in WAL mode: roll back the transaction that holds the read lock and run it again from the start.

What BEGIN IMMEDIATE changes

The SQLITE_BUSY entry on the result codes page says SQLITE_BUSY can occur when a transaction starts, during a write, or at commit, and recommends BEGIN IMMEDIATE instead of plain BEGIN to avoid hitting it mid-transaction. BEGIN IMMEDIATE can itself return SQLITE_BUSY, but if it succeeds, SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY. The isolation page gives the same advice for avoiding SQLITE_BUSY_SNAPSHOT in a transaction that you know will eventually write.

In practical terms, the contention point moves from the middle of the transaction to the single BEGIN line. If BEGIN IMMEDIATE fails, nothing has been read or written yet, so the retry is simple, and waiting at that point is ordinary writer contention, where busy_timeout has a chance to do its job.

There is a cost. Only one write transaction can be active at a time, so if you do slow work after BEGIN IMMEDIATE, other writers wait or get SQLITE_BUSY for that whole period. This follows from the one-writer rule; the docs do not quantify it. Keep read-only transactions on plain BEGIN (DEFERRED), switch only the transactions that may write to IMMEDIATE, and keep slow work such as network calls out of them.

BEGIN EXCLUSIVE is identical to IMMEDIATE in WAL mode, so there is no reason to pick it there. In rollback journal mode it also blocks readers on other connections, so unless you really need to stop reads, IMMEDIATE is the default choice.

What to retry, by failure point

Where it failed	State	What to do
`BEGIN IMMEDIATE`	Nothing has been done yet	Retry from `BEGIN IMMEDIATE` after a short wait
A write statement inside a `DEFERRED` transaction (`SQLITE_BUSY`, `SQLITE_BUSY_SNAPSHOT`)	Earlier reads are stale, or the upgrade was refused to avoid deadlock	`ROLLBACK`, then re-run the whole transaction, reads included
`COMMIT`	Per the BEGIN page, if it fails because of another connection’s reads, the transaction stays active	Do not roll back; retry just the `COMMIT` later
`SQLITE_LOCKED`	A conflict within the same connection (or shared cache), not with another connection	This is not a wait on another connection, so look for the cause on the same connection or in shared cache

The WAL section already explains why re-running only the failed write in the second row is wrong: the SELECT results before it predate another connection’s commit. In rollback journal mode, SQLite is effectively asking this connection to give up its read lock, so repeating the same statement while keeping the transaction open does not help.

The docs read slightly differently on SQLITE_BUSY at COMMIT. The result codes page says no SQLITE_BUSY occurs through the next COMMIT once BEGIN IMMEDIATE succeeds, while the BEGIN page says COMMIT can return SQLITE_BUSY if another thread or process has an open read connection. The locking page describes the same situation (a commit failing because another process holds a SHARED lock) for rollback journal mode, and in WAL mode readers do not block writers, so it is reasonable to treat this commit failure as mainly a rollback journal mode case. That conclusion is an interpretation drawn from reading the pages together, not a statement any single page makes. Either way, if COMMIT returns SQLITE_BUSY, it is safer to confirm that the transaction is still active before retrying only the COMMIT.

Reproducing it with the sqlite3 CLI

Open the same file in two terminals. The example below assumes WAL mode, which persists in the file once set (PRAGMA journal_mode).

Setup:

-- sqlite3 demo.db
PRAGMA journal_mode=WAL;
CREATE TABLE counters(name TEXT PRIMARY KEY, n INTEGER NOT NULL);
INSERT INTO counters VALUES ('a', 0);

Now run sqlite3 demo.db in terminals A and B and enter the following in order.

-- A
BEGIN;
SELECT n FROM counters WHERE name = 'a';   -- 0; a read transaction starts

-- B (autocommit)
UPDATE counters SET n = n + 1 WHERE name = 'a';   -- in WAL mode, A's read does not block this

-- A
UPDATE counters SET n = 1 WHERE name = 'a';   -- fails with an SQLITE_BUSY-family error
ROLLBACK;

A’s final UPDATE cannot upgrade to a write because A’s snapshot predates B’s commit. The exact error text may vary with the CLI version, but in terms of result codes this is the SQLITE_BUSY_SNAPSHOT case, an extended code of SQLITE_BUSY. That is why A has to ROLLBACK and start again from the SELECT rather than re-running only the failed statement.

With BEGIN IMMEDIATE, the same work looks like this.

-- A
BEGIN IMMEDIATE;
SELECT n FROM counters WHERE name = 'a';

-- B
SELECT n FROM counters WHERE name = 'a';          -- reads still work in WAL mode
UPDATE counters SET n = n + 1 WHERE name = 'a';   -- one writer only: SQLITE_BUSY if no wait is configured

-- A
UPDATE counters SET n = n + 1 WHERE name = 'a';
COMMIT;

This time the side that loses is B, at the start of its write. If B sets a wait such as .timeout 5000 and A commits within that window, B can proceed after waiting. The CLI help describes .timeout as “Try opening locked tables for MS milliseconds” (CLI documentation). In application code, PRAGMA busy_timeout = 5000; or sqlite3_busy_timeout() plays the same role. The 5000 is just an example, not a recommendation.

Applying this in an application

Start transactions that write based on what they read with BEGIN IMMEDIATE. If your driver or ORM issues BEGIN for you, check its documentation to see which form of BEGIN it sends.
Set busy_timeout on each connection, but do not expect it alone to eliminate SQLITE_BUSY in the middle of a transaction.
Make the retry unit a function that wraps the whole transaction, not an individual SQL statement. The documented exception is a COMMIT that fails with SQLITE_BUSY because another connection has an open read: the BEGIN page says the transaction stays active in that case, so retrying only the COMMIT is enough. For any other COMMIT failure, check whether the transaction is still open before deciding what to retry. The sqlite3_get_autocommit() page says that after errors including SQLITE_BUSY a transaction might be rolled back automatically, and that this function is the only way to tell; sqlite3_txn_state() returns whether the connection holds no transaction, a read transaction, or a write transaction. If you go through a driver, use the transaction-state check it provides. If the transaction is gone, re-run the whole thing.
WAL mode does not remove SQLITE_BUSY entirely. The WAL page lists exceptions: another connection opening the database in exclusive locking mode, the last connection briefly cleaning up as it closes, and recovery after a crash. WAL file growth and checkpoints are covered separately in the SQLite WAL checkpoint post.

busy_timeout only helps with contention that clears if you wait. A failed read-to-write upgrade is not that kind of contention, so for transactions that will write, the approach the SQLite docs give is to declare the intent up front with BEGIN IMMEDIATE.