SQLite: database table is locked

What it means

SQLite has two lock errors. database is locked (SQLITE_BUSY, 5) is about another connection to the file, usually another process. This one, SQLITE_LOCKED (code 6), “database table is locked”, is a conflict inside your own connection, or between connections in one process that share a cache.

SQLite doesn’t wait on this error. A busy timeout applies only to SQLITE_BUSY, so the statement fails at once, however long the timeout.

With shared cache the message names the table, database table is locked: jobs, and a schema change gives database schema is locked: main.

Common causes

A statement still running on the same connection. You’re stepping through a SELECT’s rows and, before it’s finished, run DROP TABLE or DROP INDEX on that connection, for any table. A statement is still running until it has returned its last row, or been reset or finalized (in Python, until the cursor is exhausted or closed). VACUUM in the same situation gives cannot VACUUM - SQL statements in progress.
Shared-cache mode. Connections opened with cache=shared (often file::memory:?cache=shared, to share an in-memory database between connections in tests) lock individual tables. While one connection has written to a table in an open transaction, another can’t read it.
A schema change in a shared-cache transaction. Until it commits, the other connections can’t read the schema at all.

How to fix it

Finish the read before you change the schema

Read all the rows first, or close the cursor, then run the DROP:

rows = connection.execute("SELECT id FROM jobs").fetchall()   # finished
connection.execute("DROP TABLE old_jobs")

In C, call sqlite3_reset() or sqlite3_finalize() on a statement you’ve stopped reading. If you need to read and change things at the same time, use a second connection for one of them.

Stop using shared cache

SQLite’s documentation calls shared-cache mode obsolete and discourages it. Open each connection with its own cache (drop cache=shared from the URI, or don’t call sqlite3_enable_shared_cache). For a file, turn on WAL (PRAGMA journal_mode = WAL;) so readers and the writer don’t block each other, and set a busy timeout for writers that meet.

For an in-memory database that several connections in one process share, the memdb VFS (SQLite 3.36.0 and later) works without shared cache: open file:/<name>?vfs=memdb on each connection. In our test, a conflict there came back as database is locked (SQLITE_BUSY) instead, which a busy timeout can wait for.

If you must keep shared cache, let readers skip read locks

PRAGMA read_uncommitted = ON;

On a shared-cache connection, reads then don’t take table locks, so they aren’t blocked, but they can see changes another connection hasn’t committed. Writers still conflict.

Keep transactions short

On a shared cache, a table stays locked until the transaction that touched it ends. Commit promptly, and don’t hold a transaction open while waiting on anything else.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0. The shell can hold several connections (.connection), so this script, run with sqlite3 :memory: < lk.sql, opens the same file twice with shared cache:

.open "file:lk.db?cache=shared"
BEGIN;
INSERT INTO jobs (name) VALUES ('c');
.connection 1
.open "file:lk.db?cache=shared"
SELECT count(*) FROM jobs;

Runtime error near line 6: database table is locked: jobs (6)

The shell adds “Runtime error near line 6:” and the result code; SQLite’s message is database table is locked: jobs. With .timeout 3000 on the second connection, it still failed at once: the whole run took 5 milliseconds. After PRAGMA read_uncommitted = ON, the count succeeded and included the uncommitted row. Creating a table in the first connection’s transaction instead gave:

Parse error near line 6: database schema is locked: main (6)

The same script without cache=shared: the second connection’s SELECT succeeded, and its INSERT failed with database is locked (5), the SQLITE_BUSY error.

The same-connection case, in Python 3.14’s sqlite3 module (SQLite 3.53.4), with a SELECT on jobs read one row in:

DROP TABLE jobs -> OperationalError database table is locked SQLITE_LOCKED 6
DROP TABLE old_jobs -> OperationalError database table is locked SQLITE_LOCKED 6
VACUUM -> OperationalError cannot VACUUM - SQL statements in progress SQLITE_ERROR 1

A DELETE, a CREATE INDEX and an ALTER TABLE … ADD COLUMN on the same connection succeeded; DROP INDEX failed like the drops above. After closing the cursor, DROP TABLE old_jobs worked.

In Inlet

Inlet waits up to 5 seconds for a lock before reporting database is locked. That wait doesn’t apply here: SQLite returns database table is locked at once, so if you see it, look for an unfinished statement or a shared cache in the program that holds the file.

database table is locked