SQLITE_BUSY error: what it means and what causes it

SQLITE_BUSY (result code 5, message text database is locked) means your connection needed a lock on the database file, a different connection was holding a conflicting lock, and SQLite gave up waiting. Nothing is corrupt and the failed statement changed nothing. The surprises are in the details: the default wait is zero, one class of SQLITE_BUSY is returned instantly no matter how long a timeout you set, and the places it can happen are different in rollback-journal mode and WAL mode.

How the error shows up

The numeric code is the same everywhere. What you see depends on the layer reporting it:

Where	What you see
C API	sqlite3_step(), sqlite3_exec() or sqlite3_prepare_v2() returns SQLITE_BUSY (5); sqlite3_errmsg() returns database is locked
sqlite3 shell	An error line ending in database is locked (the prefix varies between shell versions)
Python sqlite3	sqlite3.OperationalError: database is locked
Node better-sqlite3	SqliteError with code: 'SQLITE_BUSY' and message database is locked

"Database is locked" is only the message string attached to SQLITE_BUSY. A similar-looking message, database table is locked, belongs to a different code, SQLITE_LOCKED, covered below.

The lock states in rollback-journal mode

Rollback-journal mode (journal_mode of DELETE, TRUNCATE or PERSIST) is what a new database uses unless you change it. Every connection is in one of five lock states on the database file:

State	Meaning	Who else can hold it
UNLOCKED	Not reading or writing	n/a
SHARED	Reading	Any number of connections
RESERVED	Intends to write; changes are still in its private page cache	One connection, alongside any number of SHARED
PENDING	Wants EXCLUSIVE and is waiting for readers to leave	One connection; existing SHARED locks continue, no new ones are granted
EXCLUSIVE	Writing to the database file	One connection, and nothing else

A write transaction climbs the ladder: SHARED to read, RESERVED at the first write statement, then PENDING and EXCLUSIVE when it has to put pages into the database file. That last step normally happens at COMMIT. It happens earlier if the transaction is large enough to overflow the page cache, and in that case the EXCLUSIVE lock is held from the spill until the commit.

That gives three distinct places where rollback-journal mode returns SQLITE_BUSY:

1. Starting a read while another connection holds PENDING or EXCLUSIVE. A writer that is committing locks every reader out for the duration.
2. Starting to write (the first INSERT/UPDATE/DELETE, or BEGIN IMMEDIATE) while another connection holds RESERVED or higher. Only one connection can be in a write transaction.
3. Committing while another connection still holds SHARED. The writer cannot get EXCLUSIVE until every reader has finished. The official documentation is explicit that when COMMIT fails this way the transaction stays open and the COMMIT can be retried.

Case 3 is the one people miss. A single connection that started a SELECT and never finished reading it holds a SHARED lock indefinitely, and every writer's COMMIT fails until it lets go.

The PENDING state exists to stop writer starvation: once a writer is waiting, new readers are refused so the existing ones can drain.

How WAL mode changes the picture

In WAL mode (PRAGMA journal_mode=WAL, available in SQLite 3.7.0 and later) writers append to a separate -wal file instead of overwriting database pages, and readers keep reading a consistent snapshot. Coordination moves to a set of locks in the shared-memory -shm file: one write lock, one checkpoint lock, one recovery lock and five read-mark locks. RESERVED and PENDING are not used at all.

The practical effect is that cases 1 and 3 above disappear. A reader does not block a commit, and a commit does not block a reader. What remains:

Situation in WAL mode	Result
A second connection tries to write while one write transaction is open	SQLITE_BUSY after the busy timeout
A read transaction tries to become a write transaction after another connection has committed	SQLITE_BUSY_SNAPSHOT, immediately
A FULL, RESTART or TRUNCATE checkpoint cannot get past a writer or a reader	The checkpoint reports busy; ordinary statements are unaffected
Another connection has the database open with locking_mode=EXCLUSIVE	Every statement returns SQLITE_BUSY
Another connection is running crash recovery on the WAL	SQLITE_BUSY_RECOVERY
The last connection is closing and cleaning up the -wal and -shm files while you open the database	SQLITE_BUSY, briefly
Switching the database out of WAL mode while any other connection has it open	SQLITE_BUSY

WAL still allows exactly one writer at a time. It removes reader/writer conflicts, not writer/writer conflicts.

Why a timeout sometimes does nothing

A connection has no busy handler by default, so any lock conflict fails at once. PRAGMA busy_timeout = N (or sqlite3_busy_timeout()) installs a handler that sleeps and retries until N milliseconds of sleeping have accumulated. It polls; there is no queue, so waiters are not served in order.

The handler is not always called. The documentation for sqlite3_busy_handler() says that if SQLite decides invoking it could result in a deadlock, it returns SQLITE_BUSY straight away. The scenario it describes:

• Connection A holds a SHARED lock (it has read something inside a transaction) and now wants RESERVED so it can write.
• Connection B already holds RESERVED and wants EXCLUSIVE so it can commit, which requires A's SHARED lock to go away.

If both waited, neither could ever proceed. So SQLite fails A immediately, in the hope that A rolls back and releases its read lock.

The rule that falls out of this: when a connection asks for the write lock, the busy handler runs only if that connection holds no read lock yet. A transaction that began by reading and then tries to write is upgrading, and an upgrade that cannot succeed fails instantly in both journal modes. (The handler does run for a writer waiting on readers at COMMIT in rollback-journal mode, because the readers there are not waiting on anything.) This is the cause of "I set a 30 second timeout and it still fails in a millisecond".

You can watch it happen with two connections in one script:

import sqlite3, time

def connect():
    # isolation_level=None: we issue BEGIN/COMMIT ourselves
    return sqlite3.connect("demo.db", timeout=2.0, isolation_level=None)

setup = connect()
setup.execute("PRAGMA journal_mode=WAL")
setup.execute("CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, v INTEGER)")
setup.execute("INSERT OR REPLACE INTO t VALUES (1, 0)")
setup.close()

def attempt(label, conn, sql):
    start = time.monotonic()
    try:
        conn.execute(sql)
        outcome = "ok"
    except sqlite3.OperationalError as exc:
        # sqlite_errorname needs Python 3.11 or later
        outcome = f"{exc} [{getattr(exc, 'sqlite_errorname', 'n/a')}]"
    print(f"{label}: {outcome} after {time.monotonic() - start:.2f}s")

a, b = connect(), connect()

# 1. Plain contention: B waits for the full timeout.
a.execute("BEGIN IMMEDIATE")
attempt("B writes while A holds the write lock", b, "UPDATE t SET v = v + 1")
a.execute("COMMIT")

# 2. Upgrade with a stale snapshot: A fails at once.
a.execute("BEGIN")
a.execute("SELECT v FROM t").fetchall()        # A now holds a read snapshot
b.execute("UPDATE t SET v = v + 1")            # B commits a change
attempt("A upgrades after B committed", a, "UPDATE t SET v = v + 1")
a.execute("ROLLBACK")

Output on Python 3.13:

B writes while A holds the write lock: database is locked [SQLITE_BUSY] after 2.10s
A upgrades after B committed: database is locked [SQLITE_BUSY_SNAPSHOT] after 0.00s

The message text is identical. Only the elapsed time and the extended code tell the two cases apart.

The extended result codes

SQLITE_BUSY is a primary result code. Three extended codes refine it; the low byte of each is 5.

Code	Value	When
SQLITE_BUSY	5	Any lock conflict with another connection that is not one of the cases below
SQLITE_BUSY_RECOVERY	261	WAL mode only. Another process is recovering the database after a crash and holds an exclusive lock while it does
SQLITE_BUSY_SNAPSHOT	517	WAL mode only. A read transaction tried to upgrade to a write transaction, but another connection has committed since the read began, so the snapshot is out of date
SQLITE_BUSY_TIMEOUT	773	A blocking file-lock request in the VFS timed out. Only possible in builds compiled with SQLITE_ENABLE_SETLK_TIMEOUT

Most applications will only ever see the first and third. SQLITE_BUSY_SNAPSHOT deserves attention because waiting cannot fix it: the transaction has read data that is no longer current, and letting it write would break isolation. The only way forward is to roll back and run the whole transaction again.

An upgrade can also fail with plain SQLITE_BUSY rather than SQLITE_BUSY_SNAPSHOT: if the other connection is still inside its write transaction and has not committed yet, the upgrading connection gets code 5, also without waiting.

To see extended codes:

• C: call sqlite3_extended_errcode() after the failure, or turn them on for all return values with sqlite3_extended_result_codes(db, 1).
• Python 3.11 and later: the exception carries sqlite_errorcode and sqlite_errorname.
• Other drivers: look for an "extended code" field on the error object. If the driver only exposes the message string, you cannot distinguish the cases and have to infer from timing.

SQLITE_BUSY vs SQLITE_LOCKED

These get confused because both messages contain the word "locked".

	SQLITE_BUSY (5)	SQLITE_LOCKED (6)
Message	database is locked	database table is locked
Conflict is with	A different connection, usually in another process or thread	The same connection, or another connection sharing its cache in shared-cache mode
Busy handler	May be invoked	Never invoked
Typical cause	Another writer, a lingering reader	Changing or dropping a table that a statement on the same connection is still reading

A reliable way to produce SQLITE_LOCKED is to start a SELECT on a table, leave it partly read, and run DROP TABLE on the same connection. Running PRAGMA wal_checkpoint(TRUNCATE) from inside an open write transaction on the same connection also returns it.

The distinction matters because the fixes are unrelated. SQLITE_LOCKED is a bug in how one connection is being used: finish or reset the pending statement first. No timeout, journal mode or retry policy helps. Shared-cache mode, the other source of SQLITE_LOCKED, is described as obsolete and discouraged in the SQLite documentation.

What state you are in after the error

SQLITE_BUSY fails the statement, not the transaction. SQLite's list of errors that can trigger an automatic rollback does not include it.

• If the failing statement ran in autocommit mode, there is nothing to clean up.
• If BEGIN IMMEDIATE failed, no transaction was started.
• If a statement inside an explicit transaction failed, the transaction is still open and still holds whatever locks it had. For an upgrade failure that means it still holds the read lock that is blocking the other writer. Roll back promptly.
• If COMMIT failed in rollback-journal mode, the transaction is intact and the COMMIT can be retried once the readers have gone.

Code that catches the exception and carries on without rolling back turns one failure into a long-lived lock that causes many more.

Finding out who holds the lock

SQLite keeps no table of lock holders, so this is detective work. Go through the candidates in order.

Other processes. List everything that has the file open:

lsof /path/to/app.db /path/to/app.db-wal /path/to/app.db-shm

This shows which processes have the files open, not which one holds the lock, but the list is usually short. Common finds: a second copy of the application, a cron job, a backup script, an interactive sqlite3 shell someone left inside a BEGIN, or a GUI database browser with uncommitted edits. The SQLite documentation names Chrome and Firefox as programs that open their own databases in exclusive locking mode, so reading those files while the browser runs always fails.

Your own process, on other connections. Connection pools and per-thread connections mean "another connection" is often in the same process. Check each one for an open transaction: sqlite3_get_autocommit() returns zero inside a transaction, and sqlite3_txn_state() (SQLite 3.34.0 and later) says whether it is a read or a write transaction. Python exposes the first as Connection.in_transaction.

Unfinished statements. A prepared statement that has been stepped but neither run to completion nor reset keeps its read transaction open. In C you can enumerate them:

sqlite3_stmt *stmt = NULL;
while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL) {
    if (sqlite3_stmt_busy(stmt)) {
        fprintf(stderr, "still active: %s\n", sqlite3_sql(stmt));
    }
}

In higher-level languages the equivalent is a cursor or result iterator that was not read to the end and not closed. sqlite3_close() itself returns SQLITE_BUSY if unfinalized statements remain, which is a useful signal that some were leaked.

A crashed writer. If a process died mid-transaction, the next connection to open the database rolls back the hot journal or recovers the WAL, holding an exclusive lock while it does. This is short-lived and clears on its own.

The filesystem. If no process plausibly holds a lock and the file is on NFS, SMB or another network filesystem, the locks themselves may be unreliable. SQLite's documentation warns that broken lock implementations on network filesystems can cause corruption as well as spurious lock errors, and WAL mode does not work across hosts at all.

What to do about it

The short version, in the order that usually pays off:

1. Give every connection a busy timeout so ordinary contention waits instead of failing.
2. Start transactions that will write with BEGIN IMMEDIATE, so the wait happens at BEGIN where the busy handler applies and the transaction never has to upgrade.
3. Use WAL mode so readers and the writer stop blocking each other.
4. Keep transactions short and finish every SELECT.

How do I fix "database is locked" in SQLite? walks through those settings, and SQLite in production: WAL mode and knowing when it's enough covers the point at which a single-writer database stops being the right tool.

Common mistakes

• Treating the timeout as a cure. It handles lock waits. It does nothing for upgrade failures, which are a transaction-design problem.
• Assuming WAL removes SQLITE_BUSY. Two writers still collide, and WAL adds SQLITE_BUSY_SNAPSHOT.
• Looking only at other processes. The holder is frequently a second connection or a half-read cursor in the same program.
• Retrying only the failed statement after SQLITE_BUSY_SNAPSHOT. The earlier reads are stale. Roll back and rerun the transaction from its first statement.
• Ignoring the error and continuing. The transaction stays open and keeps its locks.
• Reading database table is locked as the same problem. That is SQLITE_LOCKED, a same-connection conflict.
• Setting the busy timeout once. It is a per-connection setting and is not stored in the database file. Every new connection starts with no handler unless the driver sets one.

Checklist

1. Record the extended result code, not only the message.
2. Note whether the failure was instant or came after the timeout. Instant with a timeout set means an upgrade or a same-connection conflict.
3. Note which statement failed: opening a read, the first write, or COMMIT.
4. Check the journal mode with PRAGMA journal_mode;, since the possible causes differ.
5. Confirm the failing connection really has a busy timeout: PRAGMA busy_timeout;.
6. List processes with the file open, then check your own connections for open transactions and unfinished statements.
7. Confirm the file is on a local filesystem.

References

• Result and error codes (sqlite.org), including SQLITE_BUSY, SQLITE_LOCKED and the extended codes
• File locking and concurrency in SQLite version 3
• Write-ahead logging, section "Sometimes queries return SQLITE_BUSY in WAL mode"
• WAL-mode file format, for the WAL locks
• sqlite3_busy_handler(), including the deadlock rule
• Transactions: DEFERRED, IMMEDIATE and EXCLUSIVE
• sqlite3_stmt_busy() and sqlite3_txn_state()
• How to corrupt an SQLite database file, for filesystem locking problems