Skip to content
SQLite: Handle Write-Lock Contention Explicitly

SQLite: Handle Write-Lock Contention Explicitly

November 10, 2026

A second SQLite connection cannot start a write transaction while another writer holds it. A longer timeout can wait for that writer, but it cannot make the lock disappear. Reproduce the failure locally before deciding where your application should retry.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. Use Python 3.12 or newer with SQLite support, Bash, a text editor, and mktemp, rm, and rmdir. No standalone SQLite CLI is needed. The program creates its database inside an automatically cleaned temporary directory; it never opens your existing databases.

Keep the same terminal open and stop if setup fails, before saving the source:

start_directory="$PWD"
work_directory="$(mktemp -d)" || exit 1
cd -- "$work_directory" || exit 1

Hold a write transaction on one connection

Save as sqlite-writer-contention.py:

from contextlib import closing
from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory


with TemporaryDirectory() as directory:
    path = Path(directory) / "practice.sqlite3"
    with closing(sqlite3.connect(path, autocommit=True)) as first:
        first.execute("CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT)")
        first.execute("INSERT INTO jobs VALUES (1, 'queued')")

        with closing(sqlite3.connect(path, autocommit=True, timeout=0)) as second:
            first.execute("BEGIN IMMEDIATE")
            first.execute("UPDATE jobs SET status = 'claimed' WHERE id = 1")
            try:
                try:
                    second.execute("BEGIN IMMEDIATE")
                except sqlite3.OperationalError as error:
                    if error.sqlite_errorcode != sqlite3.SQLITE_BUSY:
                        raise
                    print("second writer:", error.sqlite_errorname)
                    print("second in transaction:", second.in_transaction)
                else:
                    second.execute("ROLLBACK")
                    raise AssertionError("expected the first writer to block the second")
            finally:
                first.execute("ROLLBACK")

            print("first in transaction:", first.in_transaction)
            try:
                second.execute("BEGIN IMMEDIATE")
                second.execute("UPDATE jobs SET status = 'done' WHERE id = 1")
                second.execute("COMMIT")
            except BaseException:
                if second.in_transaction:
                    second.execute("ROLLBACK")
                raise

            row = first.execute("SELECT id, status FROM jobs").fetchone()
            print("after retry:", row)

Run:

python3 sqlite-writer-contention.py

Observed output:

second writer: SQLITE_BUSY
second in transaction: False
first in transaction: False
after retry: (1, 'done')

Place the retry at a known boundary

The two connections use one file, but no threads or timing race are needed. The first deliberately retains its transaction while the second requests write access. timeout=0 makes that request fail without a busy wait. The example recognizes the SQLite result code instead of matching an English error message.

The failed BEGIN IMMEDIATE has not started the second transaction. After the first connection rolls back, the second starts a fresh transaction and completes the update. The initial claimed change was discarded; only done is committed.

autocommit=True selects explicit SQL transaction control here. Python’s Connection.commit() and rollback() do nothing in that mode, so the example uses SQL COMMIT and ROLLBACK. closing() closes the connections before their temporary database directory is removed.

Choose a waiting policy for real work

A nonzero connection timeout, expressed in seconds, allows a busy wait before an error. In this single-thread demonstration, waiting cannot help: the first connection releases its transaction only after the second call returns.

In an application, keep write transactions short and use a bounded retry policy around a complete unit of work when appropriate. Re-read any data used to make decisions after restarting a transaction. Do not retry every OperationalError: missing tables, invalid SQL, and other failures need different handling. Do not automatically repeat external side effects such as sending an email.

This example tests contention at BEGIN IMMEDIATE with the default journal mode. A busy result during an update, read-to-write upgrade, or commit can leave different transaction state. Inspect that state and design rollback or retry deliberately. WAL mode also permits only one writer at a time.

See SQLite transaction rules and Python transaction control for the boundaries used here.

Clean up

rm -- sqlite-writer-contention.py
cd -- "$start_directory" || exit 1
rmdir -- "$work_directory"
unset start_directory work_directory

If the directory is not empty, inspect what remains before removing anything else.

Acknowledgements

This post was created with assistance from AI.

Last updated on