database is locked

What it means

Many connections can read an SQLite database at once, but only one can write. When your connection needs a lock that another connection holds, SQLite returns SQLITE_BUSY (code 5) and the message “database is locked”.

By default SQLite doesn’t wait: the busy timeout is 0, so the statement fails the instant it meets the lock. The other connection can be another process (a second copy of your app, a worker, a database browser, the sqlite3 shell) or another connection in your own process. A conflict inside the same connection is a different error, “database table is locked” (SQLITE_LOCKED).

Common causes

1. No busy timeout. Two writers meet and the second fails at once, instead of waiting the few milliseconds the first one needs.
2. A long write transaction. Something began a transaction, wrote, and hasn’t committed: a batch job, a migration, a script waiting on the network, or a tool with uncommitted edits. Every other writer fails until it commits or rolls back.
3. Readers blocking the writer. In the default rollback-journal mode, a write can’t commit while any other connection is reading. A long SELECT, or a read left open (a cursor never read to the end or closed), is enough.
4. A read that turns into a write. In WAL mode, a transaction that starts by reading and later writes fails if another connection wrote in between. No timeout helps; it fails straight away.
5. The file is on a network share. SQLite relies on the file system’s locks, and on SMB or NFS they can be slow or wrong. WAL mode doesn’t work over a network file system at all.

How to fix it

Set a busy timeout

Tell SQLite to retry for a while before giving up:

PRAGMA busy_timeout = 5000;  -- milliseconds

The setting belongs to the connection, so set it every time you open one. In the sqlite3 shell it’s .timeout 5000. Python’s sqlite3 module already waits 5 seconds by default (sqlite3.connect(path, timeout=5.0)); most other drivers have a similar option. A timeout turns short collisions (causes 1 and 3) into a brief wait. It can’t help when the other transaction stays open longer than the timeout.

Switch to WAL

PRAGMA journal_mode = WAL;

It prints wal, and the setting stays with the file, so you run it once. In WAL mode readers don’t block the writer and the writer doesn’t block readers, which removes cause 3. There is still only one writer at a time. WAL adds two files next to the database, <name>-wal and <name>-shm: keep them with it when you copy or move the database, and don’t delete them.

Keep write transactions short, and start them with BEGIN IMMEDIATE

Do slow work (network calls, reading files, waiting for a user) before BEGIN, not inside the transaction, and commit large imports in batches.

If a transaction will write, start it with BEGIN IMMEDIATE instead of BEGIN. It takes the write lock at the start, where the busy timeout applies, instead of discovering halfway through that another connection wrote first (cause 4).

Find who holds the file

On a Mac or Linux, lsof lists the processes with the file open:

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

COMMAND   PID USER   FD   TYPE DEVICE SIZE/OFF      NODE NAME
sqlite3 29047   wm    3u   REG   1,17     8192 138179038 lock.db
sqlite3 29047   wm    4u   REG   1,17        0 138179084 lock.db-wal

Commit, roll back or close the connection in that program. Don’t delete the -journal or -wal file to “unlock” the database: that can leave it corrupt.

Reproduce it

Two sqlite3 processes on one file (macOS /usr/bin/sqlite3, SQLite 3.51.0). The first holds a write transaction open for six seconds:

{ echo "BEGIN IMMEDIATE;"; echo "INSERT INTO orders(total) VALUES (20);"; sleep 6; echo "COMMIT;"; } | sqlite3 lock.db &

A second writer, with no timeout:

sqlite3 lock.db "INSERT INTO orders(total) VALUES (30);"

Error: stepping, database is locked (5)

The shell adds “Error: stepping,” and the result code; SQLite’s own message is database is locked. With .timeout 2000 the same insert failed after 2 seconds. When the first process committed after 3 seconds, a writer with .timeout 5000 waited about 2 seconds and succeeded.

In rollback-journal mode, a reader holding BEGIN; SELECT count(*) FROM orders; open made a writer fail the same way. After PRAGMA journal_mode = WAL, the same insert succeeded while the reader was still open. Two writers still collided in WAL mode.

The read-then-write case in WAL mode: connection A ran .timeout 5000, BEGIN; and a SELECT; connection B inserted a row and committed; then A tried to insert:

Runtime error near line 5: database is locked (5)

It failed within 10 milliseconds, ignoring the 5-second timeout. Its extended code is SQLITE_BUSY_SNAPSHOT (517). Starting A with BEGIN IMMEDIATE made both connections succeed.

In Inlet

Inlet opens SQLite files directly, so it’s one more connection to the file. If you write inside a transaction with manual commit in the query editor, commit or roll back when you’re done: until then, other programs can’t write to the file and get this error. Grid edits are staged until you press ⌘S and then go in as one transaction.