How to Fix SQLite “database is locked” Errors in Python With WAL Mode, BEGIN IMMEDIATE, and Checkpoints
Reproduce SQLite’s “database is locked” error in Python, then fix it with WAL mode, a busy timeout, BEGIN IMMEDIATE and checkpoint hygiene, with every number measured and 14 tests pinning the...
What you will build, and why “database is locked” happens
Picture a small Python service that keeps its data in a SQLite file. One thread records events as they arrive and four others answer dashboard queries over the same file. It runs well on your laptop. Then traffic picks up and somebody sees sqlite3.OperationalError: database is locked. Nothing is corrupt and nothing is down. SQLite is telling you that two connections wanted conflicting locks at the same moment and one of them ran out of patience.
Table Of Content
- What you will build, and why “database is locked” happens
- Eight terms to know before we start
- Prerequisites
- Create the project
- Step 1: Check your SQLite version before you start
- Step 2: Build a test bench and reproduce the error in the default mode
- 2.1 The seeded database
- 2.2 The load generator
- 2.3 Run it in the default mode
- 2.4 Why the default mode behaves like this
- Step 3: Switch on WAL mode
- 3.1 What the first half shows
- 3.2 What the second half shows
- 3.3 What WAL costs
- 3.4 The cold-start surprise: readers can still be refused
- Step 4: Learn what the busy timeout does, because writers still take turns
- Step 5: Avoid the read-then-write trap with BEGIN IMMEDIATE
- 5.1 Reading the four results
- 5.2 Why waiting cannot fix the stale-read error
- 5.3 Which connection setting should you use?
- Step 6: Keep the WAL file from growing without bound
- 6.1 Two small helpers
- 6.2 One forgotten transaction
- 6.3 The file does not shrink by itself
- 6.4 Rules that keep the log small
- Step 7: Decide what synchronous=NORMAL buys, and what it costs
- 7.1 Measure it
- 7.2 What you give up
- 7.3 Kill a writer and see what survives
- Step 8: Back up and copy a live database without losing commits
- Step 9: Package the lessons into store.py and test them
- Step 10: Confirm everything works end to end
- Common mistakes and how to spot them
- Sharing one connection between threads
- Treating the with block as a close
- Leaving a read transaction open
- Putting the file where processes cannot share memory
- Copying or deleting only some of the files
- Expecting WAL to remove every busy error
- Where to go from here
- Sources
In this tutorial you will make that error happen on purpose, measure it, and remove it step by step. You will switch the database to write-ahead logging (WAL mode) and see what that changes and what it does not. You will meet a second, sneakier kind of “database is locked” that no timeout can cure, and fix it with BEGIN IMMEDIATE. You will watch a WAL file grow to 43 MiB because of one forgotten transaction and learn how to stop that. You will weigh what synchronous=NORMAL gives up, kill a writer in the middle of a transaction to see what survives, and back up a live database without losing recent commits. At the end you will package the lessons into a small module with 14 tests.
Everything uses Python’s standard library plus pytest. Every figure below came from running the code in this article, run on Windows 11 with Python 3.13.14 and its bundled SQLite 3.50.4. I also re-ran every step on Python 3.15.0, which bundles SQLite 3.53.4, and the behaviors matched. Timings depend on your disk and CPU, so your numbers will differ; the patterns are what to look for.
Eight terms to know before we start
Transaction. A group of statements that either all take effect or none do.
Lock. SQLite’s way of stopping two connections from changing the same file at the same time. A connection that cannot get the lock it needs either waits or gives up.
Rollback journal. The default way SQLite makes a transaction safe. Before changing the database file it saves the original content in a side file, so it can undo the change after a crash.
Write-ahead log (WAL). The alternative. Changes are appended to a separate file ending in -wal and the main file is left alone until later.
Checkpoint. Copying finished changes from the WAL back into the main database file.
Snapshot. The version of the database that a read transaction sees. It stays the same until that transaction ends, even if other connections commit changes meanwhile.
Busy timeout. How long a connection waits for a lock before it gives up with “database is locked”. Python’s timeout argument sets it, and the default is five seconds.
Autocommit mode. A mode in which every statement is its own transaction unless you start one yourself with BEGIN.
The SQLite documentation states the promise of WAL like this: “WAL provides more concurrency as readers do not block writers and a writer does not block readers.” That is mostly true, and the interesting part of this tutorial is the “mostly”.
Prerequisites
- Python 3.12 or newer. The
autocommitargument ofsqlite3.connect()was added in 3.12, and the code uses it. Everything here ran on Python 3.13.14 on Windows 11, and the experiments were repeated on Python 3.15.0. - pytest 9.1.1 for the test suite in Step 9. The experiments need nothing beyond the standard library.
- A local disk. WAL does not work over a network file system, as Step 3 explains.
- Comfort with Python threads and basic SQL. You do not need to know anything about SQLite internals; the parts that matter are explained as they appear.
- A terminal and an empty folder.
Create the project
Make a folder, create a virtual environment inside it, activate it, and install pytest:
mkdir walbook
cd walbook
python -m venv .venv
.venv\Scripts\activate
python -m pip install pytest==9.1.1On macOS and Linux the activation line is source .venv/bin/activate. Every file in the rest of this tutorial goes in the walbook folder, and every command is run from there.
Step 1: Check your SQLite version before you start
Python’s sqlite3 module is a thin wrapper around the SQLite C library, and the library has its own version number, separate from Python’s. That matters here because SQLite’s documentation describes a rare data-race bug in WAL mode, which the developers call the WAL-reset bug. They say when it can bite: “The bug only affects databases in WAL mode when there are two or more database connections open on the same file, in separate threads or processes, and when those two connections attempt to write or checkpoint at the same instant.” The page continues: “It is fixed in version 3.51.3 (2026-03-13) and later. Backports of the fix are available for some earlier releases: 3.44.6 and 3.50.7.”
It also keeps the risk in proportion. The developers could not reproduce the bug organically at first, and the page says: “However, this is not an emergency.” In the same passage it adds that “application developers should upgrade to a version of SQLite that fixes the problem.”
This first script prints the library version and checks it against the three release lines the page names. Save it as step00_versions.py. It also prints a few settings of a brand-new database so you can see the defaults we are about to change.
# step00_versions.py
import os
import platform
import sqlite3
import sys
import tempfile
def has_wal_reset_fix(version):
"""True when this SQLite release has the March 2026 WAL-reset fix: 3.44.6 and later 3.44.x, 3.50.7 and later 3.50.x, 3.51.3 and later."""
v = tuple(int(part) for part in version.split("."))
return v >= (3, 51, 3) or (3, 50, 7) <= v < (3, 51, 0) or (3, 44, 6) <= v < (3, 45, 0)
print("Python", sys.version.split()[0], "on", platform.system(), platform.release())
print("SQLite", sqlite3.sqlite_version, "| has the WAL-reset fix:", has_wal_reset_fix(sqlite3.sqlite_version))
with tempfile.TemporaryDirectory() as folder:
conn = sqlite3.connect(os.path.join(folder, "probe.db"))
for name in ("journal_mode", "synchronous", "page_size", "wal_autocheckpoint", "busy_timeout"):
print(f"PRAGMA {name:<19}->", conn.execute(f"PRAGMA {name}").fetchone()[0])
conn.close()Run it with python step00_versions.py. Output on the machine used for this article:
Python 3.13.14 on Windows 11
SQLite 3.50.4 | has the WAL-reset fix: False
PRAGMA journal_mode -> delete
PRAGMA synchronous -> 2
PRAGMA page_size -> 4096
PRAGMA wal_autocheckpoint -> 1000
PRAGMA busy_timeout -> 5000Two things to take from this. First, the Python 3.13.14 build used here bundles SQLite 3.50.4, which is older than the 3.50.7 backport, so the check prints False. When I ran the same script on Python 3.15.0 it printed SQLite 3.53.4 and True. If yours prints False, plan to upgrade Python or the SQLite library it uses before you run several writers on data you care about. A False can also be a false alarm if your operating system vendor patched its own copy without changing the version number, and only the vendor’s changelog can tell you that. For the throwaway databases in this lab I carried on with 3.50.4. The final experiment ends with an integrity check that passed, but that says little about a race the developers could only trigger with special test code at first, so treat it as a reason to upgrade, not as reassurance.
Second, the defaults. journal_mode is delete, which is the rollback journal. synchronous is 2, which means FULL. page_size is 4096 bytes. wal_autocheckpoint is 1000 pages. busy_timeout is 5000 milliseconds, which is Python’s timeout argument at work: the documentation describes it as “How many seconds the connection should wait before raising an OperationalError when a table is locked.” We will come back to every one of these numbers.
Step 2: Build a test bench and reproduce the error in the default mode
Before fixing anything you need to see the problem happen, and you need a way to measure it. This step builds a seeded database and a load generator.
2.1 The seeded database
Save the next block as harness.py. build_seed() creates work/seed.db once: 50,000 rows in an events table, about 11 MB, in the default journal mode. fresh_copy() copies the seed to a new file for each experiment, so one experiment cannot leave anything behind for the next, and can switch the copy to WAL. pragma() opens a brand-new connection, reads one setting and closes the connection again, which is how we will check what a newcomer to the database sees.
# harness.py
# A seeded events database and a small reader/writer load generator (threads, one connection each).
import shutil
import sqlite3
import threading
import time
from pathlib import Path
HERE = Path(__file__).resolve().parent
WORK = HERE / "work"
SEED = WORK / "seed.db"
SEED_ROWS = 50_000
def build_seed():
"""Create work/seed.db once: 50,000 events in the default (rollback journal) mode."""
WORK.mkdir(exist_ok=True)
if SEED.exists():
return
conn = sqlite3.connect(SEED)
conn.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, source TEXT NOT NULL, payload TEXT NOT NULL)")
conn.executemany(
"INSERT INTO events (source, payload) VALUES (?, ?)",
((f"sensor-{i % 50}", "x" * 200) for i in range(SEED_ROWS)),
)
conn.commit()
conn.close()
def remove_db_files(path):
for suffix in ("", "-wal", "-shm", "-journal"):
Path(f"{path}{suffix}").unlink(missing_ok=True)
def fresh_copy(name, journal_mode="delete"):
"""Copy the seed to work/<name> (removing leftovers first); optionally switch the copy to WAL."""
build_seed()
path = WORK / name
remove_db_files(path)
shutil.copyfile(SEED, path)
if journal_mode == "wal":
conn = sqlite3.connect(path)
conn.execute("PRAGMA journal_mode=WAL")
conn.close()
return path
def pragma(path, name):
"""Open a brand-new connection, read one PRAGMA, close it."""
conn = sqlite3.connect(path)
try:
return conn.execute(f"PRAGMA {name}").fetchone()[0]
finally:
conn.close()2.2 The load generator
Now the part that makes the trouble. run_load() starts four reader threads and one writer thread against the same file for a few seconds. Each thread opens its own connection; a SQLite connection belongs to the thread that created it, and Python enforces that by default. Readers run a query that scans the table, then pause for a millisecond. The writer commits a batch of 200 rows every few milliseconds, inside BEGIN IMMEDIATE and COMMIT.
The two knobs that matter are reader_timeout and writer_timeout, the busy timeouts of the two kinds of connection in seconds. A reader timeout of 0.0 means “give up at once”, so every collision shows up as an error we can count. A read counts as locked only if it raised an OperationalError whose message contains “locked”; any other error is re-raised, so a real bug can never hide among the lock counts. At the end the function reports totals and the 99th percentile time of reads and commits, meaning the time that 99 out of every 100 were faster than. The keeper connection at the bottom stands in for the long-lived connection a running application has; Step 3 explains why it matters.
# harness.py (continued)
def run_load(path, *, seconds=3.0, readers=4, reader_timeout=0.0, writer_timeout=5.0, rows_per_commit=200):
"""Run `readers` reading threads and one writing thread for `seconds`; return counts and p99 times."""
stop = threading.Event()
lock = threading.Lock()
totals = {"reads": 0, "read_locked": 0, "commits": 0, "commit_locked": 0}
read_ms, commit_ms = [], []
def reader():
conn = sqlite3.connect(path, timeout=reader_timeout)
done = locked = 0
mine = []
while not stop.is_set():
started = time.perf_counter()
try:
conn.execute("SELECT count(*) FROM events WHERE source = 'sensor-7'").fetchone()
done += 1
except sqlite3.OperationalError as exc:
if "locked" not in str(exc):
raise
locked += 1
mine.append((time.perf_counter() - started) * 1000)
time.sleep(0.001)
conn.close()
with lock:
totals["reads"] += done
totals["read_locked"] += locked
read_ms.extend(mine)
def writer():
conn = sqlite3.connect(path, timeout=writer_timeout, autocommit=True)
done = locked = 0
mine = []
while not stop.is_set():
started = time.perf_counter()
try:
conn.execute("BEGIN IMMEDIATE")
conn.executemany(
"INSERT INTO events (source, payload) VALUES (?, ?)",
((f"sensor-{i % 50}", "y" * 200) for i in range(rows_per_commit)),
)
conn.execute("COMMIT")
done += 1
except sqlite3.OperationalError as exc:
if "locked" not in str(exc):
raise
locked += 1
if conn.in_transaction:
conn.execute("ROLLBACK")
mine.append((time.perf_counter() - started) * 1000)
time.sleep(0.005)
conn.close()
with lock:
totals["commits"] += done
totals["commit_locked"] += locked
commit_ms.extend(mine)
# A long-lived "keeper" connection, like the one a running application process has. It also means the
# threads do not all race to open a cold database at the same instant (step 3 comes back to that race).
keeper = sqlite3.connect(path)
keeper.execute("SELECT count(*) FROM sqlite_master").fetchone()
threads = [threading.Thread(target=reader) for _ in range(readers)] + [threading.Thread(target=writer)]
for thread in threads:
thread.start()
time.sleep(seconds)
stop.set()
for thread in threads:
thread.join()
keeper.close()
return {**totals, "p99_read_ms": percentile(read_ms, 0.99), "p99_commit_ms": percentile(commit_ms, 0.99)}
def percentile(values, q):
ordered = sorted(values)
return ordered[min(len(ordered) - 1, int(len(ordered) * q))]
HEADER = (f"{'scenario':<34}{'reads ok':>9}{'reads locked':>13}{'p99 read':>12}"
f"{'commits':>9}{'commit errors':>14}{'p99 commit':>14}")
def row(label, r):
return (f"{label:<34}{r['reads']:>9}{r['read_locked']:>13}{r['p99_read_ms']:>9.1f} ms"
f"{r['commits']:>9}{r['commit_locked']:>14}{r['p99_commit_ms']:>11.1f} ms")2.3 Run it in the default mode
This script copies a fresh seed, confirms its journal mode, and runs the load twice: once with readers that give up at once, and once with readers that wait up to five seconds, which is Python’s default behavior.
# step01_default_mode.py
from harness import HEADER, fresh_copy, pragma, row, run_load
path = fresh_copy("default_mode.db")
print("journal mode:", pragma(path, "journal_mode"))
print(HEADER)
print(row("delete, readers give up at once", run_load(path, reader_timeout=0.0)))
path = fresh_copy("default_mode.db") # a fresh copy, so both runs start from the same 50,000 rows
print(row("delete, readers wait up to 5 s", run_load(path, reader_timeout=5.0)))Run python step01_default_mode.py. The first run builds the seed, so it takes a moment longer. Output from my run:
journal mode: delete
scenario reads ok reads locked p99 read commits commit errors p99 commit
delete, readers give up at once 121 203 108.4 ms 70 0 510.9 ms
delete, readers wait up to 5 s 136 0 243.0 ms 128 0 58.5 msRead the columns like this. reads ok is how many read attempts succeeded, and reads locked is how many failed with “database is locked”. commits is how many write transactions finished, and commit errors is how many writes gave up. The two p99 columns are the 99th percentile times.
In the first row, 203 of 324 read attempts failed. The data was fine; the readers simply arrived while the writer was committing and they were turned away. In the second row nobody failed, because the readers were willing to wait, but the tail got ugly: one read in a hundred took 243 ms or more, and the writer managed 128 commits in three seconds. I ran this step six times and kept the numbers, four runs on Python 3.13 and two on Python 3.15. Between about a third and almost two thirds of the fail-fast reads failed (35 percent to 63 percent), and the writer committed between 70 and 128 times. Your run will land somewhere in the same neighborhood.
2.4 Why the default mode behaves like this
The default journal works like this, in the words of the SQLite documentation: “The traditional rollback journal works by writing a copy of the original unchanged database content into a separate rollback journal file and then writing changes directly into the database file.” Because the writer changes the main file itself, readers have to be kept out of the way while it does.
SQLite does that with a ladder of locks, described on its file locking page. A reader holds a SHARED lock. A writer that has finished preparing its change asks for a PENDING lock, and the page explains it this way: “A PENDING lock means that the process holding the lock wants to write to the database as soon as possible and is just waiting on all current SHARED locks to clear so that it can get an EXCLUSIVE lock. No new SHARED locks are permitted against the database if a PENDING lock is active, though existing SHARED locks are allowed to continue.” So every commit opens a window in which new readers are refused. With timeout=0.0 they fail in that window. With a five-second timeout they queue up behind it. Either way the reader pays, and the writer pays too, because its commit has to wait for every reader already inside to finish.
Step 3: Switch on WAL mode
The change is one line of SQL: PRAGMA journal_mode=WAL. The script below runs it on a fresh copy, lists the files that appear, closes the connection, and asks a brand-new connection what it sees. Then it repeats the load test from Step 2 on WAL databases. Save it as step02_wal_mode.py.
# step02_wal_mode.py
import sqlite3
from harness import HEADER, WORK, fresh_copy, pragma, row, run_load
def listing(name):
return sorted(p.name for p in WORK.glob(name + "*"))
path = fresh_copy("wal_probe.db")
conn = sqlite3.connect(path)
print("journal mode before:", conn.execute("PRAGMA journal_mode").fetchone()[0])
print("journal mode after: ", conn.execute("PRAGMA journal_mode=WAL").fetchone()[0])
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("INSERT INTO events (source, payload) VALUES ('probe', 'first write in wal mode')")
conn.commit()
print("files while the connection is open: ", listing("wal_probe.db"))
conn.close()
print("files after the last connection closes:", listing("wal_probe.db"))
print("a brand-new connection reports journal_mode:", pragma(path, "journal_mode"), "and synchronous:", pragma(path, "synchronous"))
print()
print(HEADER)
path = fresh_copy("wal_mode.db", "wal")
print(row("wal, readers give up at once", run_load(path, reader_timeout=0.0)))
path = fresh_copy("wal_mode.db", "wal")
print(row("wal, readers wait up to 5 s", run_load(path, reader_timeout=5.0)))Output from my run:
journal mode before: delete
journal mode after: wal
files while the connection is open: ['wal_probe.db', 'wal_probe.db-shm', 'wal_probe.db-wal']
files after the last connection closes: ['wal_probe.db']
a brand-new connection reports journal_mode: wal and synchronous: 2
scenario reads ok reads locked p99 read commits commit errors p99 commit
wal, readers give up at once 414 0 52.2 ms 403 0 7.2 ms
wal, readers wait up to 5 s 341 0 54.7 ms 408 0 5.0 ms3.1 What the first half shows
The statement returns the new mode as a string, wal. While the connection is open there are two extra files next to the database: wal_probe.db-wal, the log itself, and wal_probe.db-shm, a small shared-memory index that lets several connections find pages in the log quickly. When the last connection closes, both files disappear.
The mode is stored in the database file, so it sticks. The documentation says it plainly: “Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent.” That is why the brand-new connection reported wal without being told to. Other settings do not stick. I set synchronous=NORMAL on the first connection, and the new connection reported 2, the default FULL, because synchronous belongs to a connection and not to the file. Anything that is per connection has to be set on every connection you open; that is the main reason the module in Step 9 has a connect() function.
One more detail worth knowing: the pragma can fail quietly. If SQLite cannot switch, for example because the database is in memory or the file system cannot share memory, it does not raise an error. The documentation says “the journaling mode will be unchanged and the string returned from the primitive will be the prior journaling mode”. Always check the string it returns.
3.2 What the second half shows
With WAL on, the same load produced 0 locked reads in both rows. The writer committed 403 and 408 times instead of 70 and 128, and the 99th percentile commit time fell from 510.9 ms and 58.5 ms to 7.2 ms and 5.0 ms. In those six runs, WAL committed between 350 and 425 times in three seconds, and never refused a read.
The reason is the one in the documentation: “The WAL approach inverts this. The original content is preserved in the database file and the changes are appended into a separate WAL file.” A commit is a record appended to the log, so it never touches the file the readers are using. Each reader remembers where the log ended when its transaction began, and the documentation spells out what follows from that: “for any particular reader, the end mark is unchanged for the duration of the transaction, thus ensuring that a single read transaction only sees the database content as it existed at a single point in time.” That frozen view is the snapshot from our list of terms, and it comes back in Steps 5 and 6.
3.3 What WAL costs
WAL is not free. The documentation lists the disadvantages honestly. “All processes using a database must be on the same host computer; WAL does not work over a network filesystem.” The log and the index rely on memory shared between processes, so a database on a network share is a poor fit. There are two extra files, the -wal file and the -shm shared-memory file, which the documentation says can make SQLite less appealing as an application file format. WAL “might be very slightly slower (perhaps 1% or 2% slower) than the traditional rollback-journal approach in applications that do mostly reads and seldom write.” And checkpointing, which Step 6 covers, is “still something that application developers need to be mindful of.” None of that is a reason to avoid WAL for a service with concurrent readers and a writer, but it is worth knowing before you convert a database that is shared in unusual ways.
3.4 The cold-start surprise: readers can still be refused
I said WAL readers never failed, and in the table above that was true. The documentation is more careful than I was: “But there are some obscure cases where a query against a WAL-mode database can return SQLITE_BUSY, so applications should be prepared for that happenstance.” Two of the cases it lists involve a connection holding an exclusive lock for a moment: “When the last connection to a particular database is closing, that connection will acquire an exclusive lock for a short time while it cleans up the WAL and shared-memory files”, and “If the last connection to a database crashed, then the first new connection to open the database will start a recovery process. An exclusive lock is held during recovery.”
The keeper connection in the load generator was there to keep those cases out of the measurement. This next script shows what happens without one. It starts eight threads at the same instant against a WAL database that no process has open. Each thread connects and runs a query. The last thread to close removes the log files, so every one of the 100 trials is a cold start. Save it as step02b_cold_start.py.
# step02b_cold_start.py
# Eight threads open a WAL database that no process has open at that moment, and read it with a given busy timeout.
import sqlite3
import threading
from harness import fresh_copy
THREADS, TRIALS = 8, 100
def cold_start_trial(path, timeout):
barrier = threading.Barrier(THREADS)
errors = []
def open_and_read():
barrier.wait() # all threads start at the same instant
conn = sqlite3.connect(path, timeout=timeout)
try:
conn.execute("SELECT count(*) FROM events WHERE source = 'sensor-7'").fetchone()
except sqlite3.OperationalError as exc:
errors.append(str(exc))
finally:
conn.close()
threads = [threading.Thread(target=open_and_read) for _ in range(THREADS)]
for thread in threads:
thread.start()
for thread in threads:
thread.join()
return errors
for timeout in (0.0, 5.0):
path = fresh_copy("cold.db", "wal")
trials_with_error = failed_reads = 0
for _ in range(TRIALS):
errors = cold_start_trial(path, timeout) # the last connection to close deletes the -wal file, so every trial starts cold
trials_with_error += bool(errors)
failed_reads += len(errors)
print(f"busy timeout {timeout} s: {trials_with_error} of {TRIALS} cold starts had a failed read ({failed_reads} failed reads in total)")Output from my run:
busy timeout 0.0 s: 85 of 100 cold starts had a failed read (302 failed reads in total)
busy timeout 5.0 s: 0 of 100 cold starts had a failed read (0 failed reads in total)With a busy timeout of zero, 85 of the 100 cold starts had at least one failed read; in the five runs where I kept the numbers the count ranged from 79 to 94. With the default five seconds there were none. I did not instrument which of the documented cases each failure fell into, but the lesson does not depend on it: set a busy timeout on every connection, readers included, even in WAL mode. Python’s default of five seconds already does this. The danger is code, copied from somewhere, that passes timeout=0 to “make it fail fast”.
Step 4: Learn what the busy timeout does, because writers still take turns
WAL lets readers and a writer work together. It does not let two writers work together. The documentation says why: “since there is only one WAL file, there can only be one writer at a time.” When a second writer arrives it has to wait, and how long it waits is the busy timeout.
The next script makes that visible. One connection grabs the write lock with BEGIN IMMEDIATE and holds it for exactly one second. Another connection tries to get the lock with three different timeouts. At the end a plain reader checks whether it is held up by the writer. Save it as step03_writers_queue.py.
# step03_writers_queue.py
import sqlite3
import threading
import time
from harness import fresh_copy
path = fresh_copy("writers.db", "wal")
def hold_write_lock(ready, seconds):
conn = sqlite3.connect(path, autocommit=True)
conn.execute("BEGIN IMMEDIATE") # take the one write lock and keep it
ready.set()
time.sleep(seconds)
conn.execute("COMMIT")
conn.close()
def start_holder(seconds=1.0):
ready = threading.Event()
holder = threading.Thread(target=hold_write_lock, args=(ready, seconds))
holder.start()
ready.wait()
return holder
for wait in (0.0, 0.3, 5.0):
holder = start_holder()
conn = sqlite3.connect(path, timeout=wait, autocommit=True)
started = time.perf_counter()
try:
conn.execute("BEGIN IMMEDIATE")
conn.execute("COMMIT")
result = "got the write lock"
except sqlite3.OperationalError as exc:
result = f"OperationalError: {exc} ({exc.sqlite_errorname})"
print(f"timeout={wait:<4} {result} after {time.perf_counter() - started:.2f} s")
conn.close()
holder.join()
holder = start_holder()
reader = sqlite3.connect(path, timeout=0.0)
started = time.perf_counter()
count = reader.execute("SELECT count(*) FROM events").fetchone()[0]
print(f"a reader counted {count} rows in {time.perf_counter() - started:.3f} s while the write lock was held")
reader.close()
holder.join()Output from my run:
timeout=0.0 OperationalError: database is locked (SQLITE_BUSY) after 0.00 s
timeout=0.3 OperationalError: database is locked (SQLITE_BUSY) after 0.42 s
timeout=5.0 got the write lock after 1.01 s
a reader counted 50000 rows in 0.025 s while the write lock was heldWith timeout=0.0 the second connection failed immediately. With timeout=0.3 it waited and then failed after 0.42 seconds. That is a little more than 0.3 because, as the documentation puts it, the busy handler sleeps repeatedly until at least the requested time has accumulated, so the last sleep can overshoot. With timeout=5.0 it waited about a second, until the first connection committed, and then got the lock. The reader was not held up at all: it counted 50,000 rows in 25 milliseconds while the write lock was held.
Notice the error name in parentheses. Python 3.11 and newer put SQLite’s result code name on the exception as sqlite_errorname. A lock that is simply taken by someone else is SQLITE_BUSY. Remember that name, because Step 5 shows an error with the same message and a different name.
What should you do about the timeout? Pick a value longer than your longest normal write transaction, and keep write transactions short, because everyone who writes waits behind them. When the timeout expires, treat it as a signal of overload rather than something to retry in a tight loop. Our tutorials on backpressure and load shedding and on retrying with exponential backoff and jitter cover what to do next.
Step 5: Avoid the read-then-write trap with BEGIN IMMEDIATE
There is a second way to see “database is locked”, and no timeout will fix it. It happens when a transaction reads something, another connection changes it, and then the first transaction tries to write. The SQLite documentation describes the rule: “If a write statement occurs while a read transaction is active, then the read transaction is upgraded to a write transaction if possible. If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY.” And the earlier sentence about snapshots: while a read transaction is active, changes made by other connections “will not be seen by the database connection that started the read transaction.”
The error has its own name in WAL mode. The result code page says: “The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY that occurs on WAL mode databases when a database connection tries to promote a read transaction into a write transaction but finds that another database connection has already written to the database and thus invalidated prior reads.”
We will provoke it with the classic example, a balance. The account starts at 100. Our connection reads the balance, and while it is thinking another connection adds 5. Our connection then writes “balance plus 10” using the value it read. The correct final balance is 115. The script runs the same story under four connection settings, so you can compare. It sends COMMIT itself in every case. Save it as step04_stale_read.py.
# step04_stale_read.py
import sqlite3
import threading
import time
from harness import WORK, remove_db_files
def fresh_accounts():
path = WORK / "accounts.db"
remove_db_files(path)
conn = sqlite3.connect(path)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)")
conn.execute("INSERT INTO accounts VALUES (1, 100)")
conn.commit()
conn.close()
return path
def other_writer(path, box):
conn = sqlite3.connect(path, timeout=5.0, autocommit=True)
started = time.perf_counter()
conn.execute("UPDATE accounts SET balance = balance + 5 WHERE id = 1")
box["waited"] = time.perf_counter() - started
conn.close()
def attempt(label, begin=None, **options):
"""Read the balance, let another connection add 5, then write balance + 10 from the value we read."""
path = fresh_accounts()
mine = sqlite3.connect(path, timeout=5.0, **options)
if begin:
mine.execute(begin)
balance = mine.execute("SELECT balance FROM accounts WHERE id = 1").fetchone()[0]
box = {}
other = threading.Thread(target=other_writer, args=(path, box))
other.start()
other.join(0.5) # a writer that is not blocked finishes at once; a blocked one is still waiting
started = time.perf_counter()
try:
mine.execute("UPDATE accounts SET balance = ? WHERE id = 1", (balance + 10,))
mine.execute("COMMIT") # we send COMMIT ourselves in every case, so the four runs are comparable
outcome = "committed"
except sqlite3.OperationalError as exc:
outcome = f"{exc} ({exc.sqlite_errorname})"
if mine.in_transaction:
mine.execute("ROLLBACK")
elapsed = time.perf_counter() - started
other.join()
mine.close()
check = sqlite3.connect(path)
final = check.execute("SELECT balance FROM accounts").fetchone()[0]
check.close()
print(label)
print(f" {outcome} after {elapsed:.2f} s; the other writer waited {box['waited']:.2f} s; final balance {final}")
print("start: balance 100; correct final balance after +5 and +10: 115")
attempt("default connection settings")
attempt("autocommit=False", autocommit=False)
attempt("autocommit=True + BEGIN", begin="BEGIN", autocommit=True)
attempt("autocommit=True + BEGIN IMMEDIATE", begin="BEGIN IMMEDIATE", autocommit=True)Output from my run:
start: balance 100; correct final balance after +5 and +10: 115
default connection settings
committed after 0.00 s; the other writer waited 0.00 s; final balance 110
autocommit=False
database is locked (SQLITE_BUSY_SNAPSHOT) after 0.00 s; the other writer waited 0.00 s; final balance 105
autocommit=True + BEGIN
database is locked (SQLITE_BUSY_SNAPSHOT) after 0.00 s; the other writer waited 0.00 s; final balance 105
autocommit=True + BEGIN IMMEDIATE
committed after 0.01 s; the other writer waited 0.55 s; final balance 1155.1 Reading the four results
Default connection settings: no error, wrong answer. The write went through and the final balance is 110. The other connection’s +5 vanished, and nothing told us. In Python’s default (“legacy”) mode the module does not open a transaction for a SELECT; it opens one just before the UPDATE. So the read happened outside any transaction, the UPDATE started a fresh one with no snapshot to go stale, and it overwrote the other writer’s change with a value computed from old data. A test in Step 9 pins down that the SELECT leaves in_transaction false and the UPDATE makes it true. This is the silent lost update that our tutorial on ETag and If-Match fixes at the HTTP layer, and it is the most dangerous outcome of the four precisely because it is quiet.
autocommit=False: an immediate error. This mode follows the Python database standard (PEP 249). The documentation says it means “sqlite3 ensures that a transaction is always open” and that “sqlite3 uses BEGIN DEFERRED statements when opening transactions.” So our SELECT ran inside a transaction that started a snapshot, the other writer committed, and our UPDATE failed at once with SQLITE_BUSY_SNAPSHOT, even though the busy timeout was five seconds. Nothing was lost this time; the final balance is 105 and our +10 was refused.
autocommit=True with BEGIN: the same error. Writing the plain BEGIN yourself is the same thing as the previous case, since SQLite’s default transaction type is deferred.
autocommit=True with BEGIN IMMEDIATE: correct. The other writer waited about half a second, until our transaction committed, and then added its 5. Both changes applied and the final balance is 115. The documentation explains the difference: “IMMEDIATE causes the database connection to start a new write immediately, without waiting for a write statement. The BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.” By taking the write lock before the read, we guarantee that nobody can change the data between our read and our write.
5.2 Why waiting cannot fix the stale-read error
You might expect the busy timeout to help, since it helped in Step 4. It cannot, and SQLite does not even try. The reason is that waiting does not make your old read fresh: whatever you decided based on the old snapshot is now out of date. The documentation for the busy handler adds a safeguard against deadlocks: “If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY to the application instead of invoking the busy handler.” If you must use deferred transactions, the only correct response to this error is to roll back and run the whole transaction again, starting from the read. The better habit is simply to start every transaction that might write with BEGIN IMMEDIATE. Transactions that only read can stay deferred.
5.3 Which connection setting should you use?
The Python documentation suggests autocommit=False for most code: “This is the recommended value of autocommit.” That advice is about portability across databases. For a concurrent SQLite service, the case above shows the cost, because its transactions start deferred. I use autocommit=True and send BEGIN IMMEDIATE, COMMIT and ROLLBACK myself. Two details come with that choice. First, in this mode “commit() and rollback() have no effect”, so you must send COMMIT as SQL; Step 9 has a test for that trap. Second, the older spelling isolation_level=None does the same job, since “If set to None, transactions are never implicitly opened”, but the documentation also says “Using autocommit to control transaction handling is recommended over using isolation_level”, and it warns that “The default will change to False in a future Python release.” Passing autocommit explicitly keeps your code behaving the same when that default changes.
Step 6: Keep the WAL file from growing without bound
In WAL mode, a commit appends to the log, so something has to copy the log’s content back into the main file eventually. That job is the checkpoint, and SQLite does it for you: “By default, SQLite does a checkpoint automatically when the WAL file reaches a threshold size of 1000 pages.” With 4096-byte pages that is about 3.9 MiB, which is the size you will see in the first experiment below.
A checkpoint has one hard limit. The documentation says: “A checkpoint operation takes content from the WAL file and transfers it back into the original database file. A checkpoint can run concurrently with readers, however the checkpoint must stop when it reaches a page in the WAL that is past the end mark of any current reader.” Remember the snapshot from Step 3: a reader sees the log up to its end mark, so the pages it might still need cannot be recycled. The consequence follows directly: “Thus a long-running read transaction can prevent a checkpointer from making progress.”
6.1 Two small helpers
The experiment needs two helpers: one that reports the size of the -wal file in MiB, and one that commits a batch of 100 rows. Save them as waltools.py.
# waltools.py
# Two helpers for the checkpoint demos: the size of the -wal file and a small write transaction.
import os
def wal_mib(path):
"""Size of the -wal file next to path, in MiB (0.0 when there is no such file)."""
try:
return os.path.getsize(f"{path}-wal") / (1024 * 1024)
except FileNotFoundError:
return 0.0
def commit_batch(conn, rows=100):
"""One write transaction that appends `rows` events (conn must be in autocommit mode)."""
conn.execute("BEGIN IMMEDIATE")
conn.executemany(
"INSERT INTO events (source, payload) VALUES (?, ?)",
((f"sensor-{i % 50}", "z" * 200) for i in range(rows)),
)
conn.execute("COMMIT")6.2 One forgotten transaction
The script below writes 1,200 batches while nobody holds a read transaction open (part A). Then a reader begins a transaction, reads once, and never finishes (part B), while the writer commits 1,200 more batches. Finally the reader ends its transaction (part C). Along the way we ask SQLite to checkpoint with PRAGMA wal_checkpoint and print what it returns. Save it as step05_checkpoints.py.
# step05_checkpoints.py
import sqlite3
from harness import fresh_copy
from waltools import commit_batch, wal_mib
def write_many(conn, path, commits, every=300):
for i in range(1, commits + 1):
commit_batch(conn)
if i % every == 0:
print(f" after {i:>4} commits the -wal file is {wal_mib(path):5.1f} MiB")
path = fresh_copy("ckpt.db", "wal")
writer = sqlite3.connect(path, autocommit=True)
print("page_size:", writer.execute("PRAGMA page_size").fetchone()[0], "bytes")
print("wal_autocheckpoint:", writer.execute("PRAGMA wal_autocheckpoint").fetchone()[0], "pages")
print("A. nobody holds a read transaction open")
write_many(writer, path, 1200)
print("B. one reader begins a transaction and never finishes it")
reader = sqlite3.connect(path, autocommit=True)
reader.execute("BEGIN")
seen = reader.execute("SELECT count(*) FROM events").fetchone()[0]
write_many(writer, path, 1200)
now = writer.execute("SELECT count(*) FROM events").fetchone()[0]
print(f" the reader still sees {seen} rows, the writer sees {now}")
writer.execute("PRAGMA busy_timeout=100") # do not wait the default 5 s for a checkpoint that cannot finish
print(" PASSIVE checkpoint (busy, frames in wal, frames checkpointed):", writer.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone())
print(" TRUNCATE checkpoint (busy, frames in wal, frames checkpointed):", writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())
print(f" the -wal file is still {wal_mib(path):.1f} MiB")
print("C. the reader finishes its transaction")
reader.execute("COMMIT")
print(" TRUNCATE checkpoint (busy, frames in wal, frames checkpointed):", writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())
print(f" the -wal file is {wal_mib(path):.1f} MiB")
reader.close()
writer.close()Output from my run. The sizes are deterministic and came out identical on both Python versions:
page_size: 4096 bytes
wal_autocheckpoint: 1000 pages
A. nobody holds a read transaction open
after 300 commits the -wal file is 3.9 MiB
after 600 commits the -wal file is 3.9 MiB
after 900 commits the -wal file is 3.9 MiB
after 1200 commits the -wal file is 3.9 MiB
B. one reader begins a transaction and never finishes it
after 300 commits the -wal file is 12.1 MiB
after 600 commits the -wal file is 22.4 MiB
after 900 commits the -wal file is 32.7 MiB
after 1200 commits the -wal file is 43.0 MiB
the reader still sees 170000 rows, the writer sees 290000
PASSIVE checkpoint (busy, frames in wal, frames checkpointed): (0, 10932, 462)
TRUNCATE checkpoint (busy, frames in wal, frames checkpointed): (1, 10932, 462)
the -wal file is still 43.0 MiB
C. the reader finishes its transaction
TRUNCATE checkpoint (busy, frames in wal, frames checkpointed): (0, 0, 0)
the -wal file is 0.0 MiBPart A is the healthy picture: the file sits at 3.9 MiB through 1,200 commits, because each automatic checkpoint finishes and the log starts again from the beginning. Part B is the problem. One transaction that began and never ended pinned the log, and the file grew by about 10 MiB for every 300 commits, to 43.0 MiB. Notice that the reader still sees 170,000 rows, the count at the moment its snapshot began, while the writer sees 290,000. That is the snapshot at work, and it is also the reason the log cannot be recycled.
The checkpoint pragma returns three numbers. The documentation defines them like this: “The first column is usually 0 but will be 1 if a RESTART or FULL or TRUNCATE checkpoint was blocked from completing, for example because another thread or process was actively using the database.” Then: “The second column is the number of modified pages that have been written to the write-ahead log file. The third column is the number of pages in the write-ahead log file that have been successfully moved back into the database file at the conclusion of the checkpoint.” So (0, 10932, 462) means the PASSIVE checkpoint, which is defined as “Checkpoint as many frames as possible without waiting for any database readers or writers to finish”, found 10,932 pages in the log and could move only 462 of them, consistent with the rule that it must stop at the end mark of the oldest reader. The TRUNCATE checkpoint, defined as working “the same way as RESTART with the addition that the WAL file is truncated to zero bytes upon successful completion”, reported (1, 10932, 462): the first number 1 means it was blocked, and the file stayed at 43.0 MiB. Once the reader ended its transaction, the same call returned (0, 0, 0) and the file went to 0.0 MiB.
The documentation names this failure “checkpoint starvation” and describes the worse version: “However, if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound.” A big log is more than a disk-space problem, too. The same page warns that “read performance deteriorates as the WAL file grows in size since each reader must check the WAL file for the content”.
6.3 The file does not shrink by itself
There is a second surprise. Even after the reader finishes and a checkpoint completes, the log file keeps its size. The pragma documentation says: “the write-ahead log file is not truncated following a checkpoint. Instead, SQLite reuses the existing file for subsequent WAL entries since overwriting is faster than appending.” You can cap what is left behind with PRAGMA journal_size_limit: “Each time a transaction is committed or a WAL file resets, SQLite compares the size of the rollback journal file or WAL file left in the file-system to the size limit set by this pragma and if the journal or WAL file is larger it is truncated to the limit.” The default is no limit. The next script grows a log to about 41 MiB behind a held snapshot, releases the reader, then commits three more times, once without a limit and once with a limit of 4 MiB. Save it as step05b_size_limit.py.
# step05b_size_limit.py
import sqlite3
from harness import fresh_copy
from waltools import commit_batch, wal_mib
def grow_then_release(limit):
path = fresh_copy("limit.db", "wal")
writer = sqlite3.connect(path, autocommit=True)
if limit:
writer.execute(f"PRAGMA journal_size_limit={limit}")
reader = sqlite3.connect(path, autocommit=True)
reader.execute("BEGIN")
reader.execute("SELECT count(*) FROM events").fetchone()
for _ in range(1200):
commit_batch(writer)
print(f" while a reader holds its snapshot: {wal_mib(path):5.1f} MiB")
reader.execute("COMMIT")
for i in range(1, 4):
commit_batch(writer)
print(f" after the reader finished, commit {i}: {wal_mib(path):5.1f} MiB")
reader.close()
writer.close()
print("journal_size_limit left at its default (-1, no limit)")
grow_then_release(None)
print("journal_size_limit = 4 MiB")
grow_then_release(4 * 1024 * 1024)Output from my run:
journal_size_limit left at its default (-1, no limit)
while a reader holds its snapshot: 41.2 MiB
after the reader finished, commit 1: 41.2 MiB
after the reader finished, commit 2: 41.2 MiB
after the reader finished, commit 3: 41.2 MiB
journal_size_limit = 4 MiB
while a reader holds its snapshot: 41.2 MiB
after the reader finished, commit 1: 41.2 MiB
after the reader finished, commit 2: 4.0 MiB
after the reader finished, commit 3: 4.0 MiBWithout a limit, the file stayed at 41.2 MiB after the reader was gone and after three more commits. With the limit it stayed at 41.2 MiB after the first commit and dropped to 4.0 MiB after the second. The first commit triggered the automatic checkpoint, which could now finish. The second commit was the first write after that, and the documentation explains what writers do then: “the writer will rewind the WAL back to the beginning and start putting new transactions at the beginning of the WAL”, which is the moment the limit is applied.
6.4 Rules that keep the log small
Keep read transactions short. A read transaction is not only an explicit BEGIN. SQLite’s transaction documentation says: “An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes.” So a SELECT whose cursor you stop reading early keeps its snapshot alive. A test in Step 9 proves it: one row read from a cursor blocks a truncating checkpoint until the cursor is exhausted. Read what you need, fetch everything or close the cursor, and do not hold a cursor open while waiting for a user or a slow network call.
Leave automatic checkpoints on unless you replace them with your own. Consider setting journal_size_limit so that a rare spike does not leave a huge file behind forever. Run a TRUNCATE checkpoint during quiet moments if you want the file small, with a short busy timeout, because FULL, RESTART and TRUNCATE wait for other connections. And treat the size of the -wal file as a metric. It is the first thing to grow when a reader goes missing.
Step 7: Decide what synchronous=NORMAL buys, and what it costs
Every commit has to decide how sure it wants to be that the data reached the disk. The synchronous setting controls that. FULL, the default, waits for the operating system to confirm the write. NORMAL waits less often. In WAL mode the difference is large, because of one sentence in the documentation: “Writers sync the WAL on every transaction commit if PRAGMA synchronous is set to FULL but omit this sync if PRAGMA synchronous is set to NORMAL.” With NORMAL, the page adds, “the checkpoint is the only operation to issue an I/O barrier or sync operation (fsync() on unix or FlushFileBuffers() on windows)”.
7.1 Measure it
This script times 300 single-row transactions on one connection under three settings. There is no contention here; it measures only the cost of waiting for the disk. Save it as step06_sync_levels.py.
# step06_sync_levels.py
import sqlite3
import time
from harness import WORK, remove_db_files
def commits_per_second(journal_mode, synchronous, commits=300):
path = WORK / "sync.db"
remove_db_files(path)
conn = sqlite3.connect(path, autocommit=True)
conn.execute(f"PRAGMA journal_mode={journal_mode}")
conn.execute(f"PRAGMA synchronous={synchronous}")
conn.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)")
started = time.perf_counter()
for _ in range(commits):
conn.execute("INSERT INTO t (v) VALUES ('x')") # autocommit: each statement is its own transaction
elapsed = time.perf_counter() - started
conn.close()
return commits / elapsed
print(f"{'journal_mode':<14}{'synchronous':<13}{'commits per second':>20}")
for journal_mode, synchronous in (("delete", "FULL"), ("wal", "FULL"), ("wal", "NORMAL")):
print(f"{journal_mode:<14}{synchronous:<13}{commits_per_second(journal_mode, synchronous):>20,.0f}")Output from my run:
journal_mode synchronous commits per second
delete FULL 192
wal FULL 1,329
wal NORMAL 63,195The rollback journal with FULL managed 192 commits per second. WAL with FULL managed 1,329, about seven times as many, because, as the WAL page puts it, “WAL uses many fewer fsync() operations”. WAL with NORMAL managed 63,195, about 48 times more again, because it does not sync at commit at all. The absolute numbers belong to this disk; in every run I made the three figures stayed near 200, 1,300 and 63,000 on this machine, and you should expect a different scale on yours. The ratios are the lesson.
7.2 What you give up
The price is stated plainly in the documentation: “WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode.” It also gives its recommendation: “The synchronous=NORMAL setting provides the best balance between performance and safety for most applications running in WAL mode.” In plain terms: if the whole machine loses power, you may lose the last few commits, but the database will not be corrupted. If only your program dies, you lose nothing that was committed.
7.3 Kill a writer and see what survives
I can test the second half of that sentence on this machine, and I will say clearly what I cannot test. The child program below inserts 1,000 rows per transaction and prints two kinds of line: inserted N, not committed yet while transaction N is still open, and committed N after it commits. It stalls at a transaction chosen by the parent, so the kill lands at a known moment. Save it as crash_child.py.
# crash_child.py
# Insert 1,000 rows per transaction, reporting each step, and stall at the transaction the parent picked.
import sqlite3
import sys
import time
path, target, phase = sys.argv[1], int(sys.argv[2]), sys.argv[3] # phase: "inside" or "after"
conn = sqlite3.connect(path, autocommit=True)
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("CREATE TABLE IF NOT EXISTS ledger (id INTEGER PRIMARY KEY, note TEXT NOT NULL)")
n = 0
while True:
n += 1
conn.execute("BEGIN IMMEDIATE")
conn.executemany("INSERT INTO ledger (note) VALUES (?)", [("row",)] * 1000)
print(f"inserted {n}, not committed yet", flush=True)
if phase == "inside" and n == target:
time.sleep(60) # the parent kills this process here, with 1,000 uncommitted rows
conn.execute("COMMIT")
print(f"committed {n}", flush=True)
if phase == "after" and n == target:
time.sleep(60) # the parent kills this process here, just after a commitThe parent starts the child with synchronous=NORMAL in WAL mode, waits for the line it asked for, and then ends the whole process tree without letting it clean up: no close(), no exit handlers. On Windows, a virtual environment’s python.exe launches the real interpreter as a child, so the parent uses taskkill /T to end both. Then it reopens the database and counts rows. Save it as step07_crash.py.
# step07_crash.py
import os
import sqlite3
import subprocess
import sys
from pathlib import Path
from harness import WORK, remove_db_files
CHILD = Path(__file__).with_name("crash_child.py")
WORK.mkdir(exist_ok=True)
def hard_kill(process):
"""End the process without giving it any chance to clean up (no atexit, no connection.close())."""
if sys.platform == "win32":
# a virtual environment's python.exe starts the real interpreter as a child, so kill the whole tree
subprocess.run(["taskkill", "/PID", str(process.pid), "/T", "/F"], capture_output=True)
else:
process.kill()
process.wait()
trials = [("inside", 20, "inserted 20, not committed yet"),
("inside", 80, "inserted 80, not committed yet"),
("after", 50, "committed 50")]
for trial, (phase, target, wanted) in enumerate(trials, start=1):
path = WORK / f"crash{trial}.db"
remove_db_files(path)
child = subprocess.Popen([sys.executable, str(CHILD), str(path), str(target), phase],
stdout=subprocess.PIPE, text=True)
for line in child.stdout:
if line.strip() == wanted:
break
hard_kill(child) # the child is stalled at this exact point, so the kill lands where we want it
wal_bytes = os.path.getsize(f"{path}-wal") if os.path.exists(f"{path}-wal") else 0
conn = sqlite3.connect(path)
rows = conn.execute("SELECT count(*) FROM ledger").fetchone()[0]
verdict = conn.execute("PRAGMA integrity_check").fetchone()[0]
conn.close()
left = sorted(p.name for p in WORK.glob(f"crash{trial}.db*"))
print(f"trial {trial}: killed the writer right after it printed '{wanted}'")
print(f" -wal held {wal_bytes:,} bytes; reopened: {rows:,} rows = {rows // 1000} whole transactions "
f"+ {rows % 1000} stray rows; integrity_check: {verdict}")
print(f" files after the reopen: {left}")Output from my run, identical on both Python versions:
trial 1: killed the writer right after it printed 'inserted 20, not committed yet'
-wal held 453,232 bytes; reopened: 19,000 rows = 19 whole transactions + 0 stray rows; integrity_check: ok
files after the reopen: ['crash1.db']
trial 2: killed the writer right after it printed 'inserted 80, not committed yet'
-wal held 1,911,712 bytes; reopened: 79,000 rows = 79 whole transactions + 0 stray rows; integrity_check: ok
files after the reopen: ['crash2.db']
trial 3: killed the writer right after it printed 'committed 50'
-wal held 1,207,192 bytes; reopened: 50,000 rows = 50 whole transactions + 0 stray rows; integrity_check: ok
files after the reopen: ['crash3.db']In trial 1 the writer was killed with transaction 20 open and 1,000 inserted but uncommitted rows. The reopened database holds 19 whole transactions and no stray rows: the open transaction simply never happened. Trial 2 is the same story at transaction 80. In trial 3 the writer was killed immediately after transaction 50 committed, and all 50 transactions are there, even though synchronous=NORMAL never forced the log to disk. The -wal file still held data when the parent looked at it, and after the reopen only the main file remains, because SQLite replayed the log when the database was opened and cleaned up when the connection closed. integrity_check said ok every time.
What this does not show is a power cut. A killed process leaves the operating system and its file cache running, and the cache still holds what the process wrote. A real power loss or system crash is the case in which NORMAL may lose recent commits, and this test cannot reproduce it. So the decision rule is yours to make: choose NORMAL if you can live with losing the last few commits after a power failure, and keep FULL for data where every acknowledged commit must survive one, such as payments or an audit trail.
Step 8: Back up and copy a live database without losing commits
In WAL mode a database is not one file any more. The documentation is direct about it: “The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved. If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted.” The SQLite page on corruption adds that it is safe to copy the file “as long as there are no transactions in progress while the copy is taking place”, and lists the safe alternatives that work on a live database: the backup API and VACUUM INTO.
This script commits 100 rows into a WAL database whose automatic checkpoints are switched off for the demo, so the new rows exist only in the log. Then it makes three copies: a plain file copy, a copy with Connection.backup, and a copy with VACUUM INTO. Save it as step08_backup.py.
# step08_backup.py
import shutil
import sqlite3
from harness import WORK, fresh_copy, remove_db_files
def count(path):
conn = sqlite3.connect(path)
try:
return conn.execute("SELECT count(*) FROM events").fetchone()[0]
finally:
conn.close()
path = fresh_copy("live.db", "wal")
live = sqlite3.connect(path, autocommit=True)
live.execute("PRAGMA wal_autocheckpoint=0") # for this demo, never move WAL content into the main file on its own
live.execute("BEGIN IMMEDIATE")
live.executemany(
"INSERT INTO events (source, payload) VALUES (?, ?)",
[("late", "committed after the last checkpoint")] * 100,
)
live.execute("COMMIT")
print("rows the live database reports:", live.execute("SELECT count(*) FROM events").fetchone()[0])
print("file sizes in bytes:", {p.name: p.stat().st_size for p in sorted(WORK.glob("live.db*"))})
plain, safe, vacuumed = (WORK / name for name in ("plain_copy.db", "backup_api.db", "vacuum_into.db"))
for target in (plain, safe, vacuumed):
remove_db_files(target)
shutil.copyfile(path, plain)
print("rows in a plain file copy of live.db: ", count(plain))
destination = sqlite3.connect(safe)
live.backup(destination) # the sqlite3 module's wrapper around SQLite's online backup API
destination.close()
print("rows in a copy made with Connection.backup:", count(safe))
live.execute(f"VACUUM INTO '{vacuumed.as_posix()}'")
print("rows in a copy made with VACUUM INTO: ", count(vacuumed))
live.close()Output from my run:
rows the live database reports: 50100
file sizes in bytes: {'live.db': 11415552, 'live.db-shm': 32768, 'live.db-wal': 16512}
rows in a plain file copy of live.db: 50000
rows in a copy made with Connection.backup: 50100
rows in a copy made with VACUUM INTO: 50100The live database holds 50,100 rows, but 100 of them live in a 16,512-byte -wal file. A plain copy of live.db copied only the main file and has 50,000 rows. It opens without any error, which is the dangerous part: nothing tells you that 100 committed rows are missing. The two safe copies have all 50,100. The Python documentation says of Connection.backup that it “Works even if the database is being accessed by other clients or concurrently by the same connection.” The SQLite documentation says of VACUUM INTO that “the generated output database is a consistent snapshot of the original database.” Use one of them for any backup of a database that is open. And never delete the -wal or -shm files by hand to “clean up”; they are part of the database while any connection is open or after a crash.
Step 9: Package the lessons into store.py and test them
You now know enough to write down the rules once, so that every part of the service follows them. Save this as store.py.
# store.py
# SQLite helpers for a small multi-threaded service: WAL, a busy timeout, short read and write transactions.
import contextlib
import os
import sqlite3
class StoreBusy(Exception):
"""The write lock could not be taken within the busy timeout."""
def connect(path, *, busy_ms=5000):
"""One connection per thread. autocommit=True: we send BEGIN, COMMIT and ROLLBACK ourselves (commit() would do nothing)."""
conn = sqlite3.connect(path, timeout=busy_ms / 1000, autocommit=True)
mode = conn.execute("PRAGMA journal_mode=WAL").fetchone()[0]
if mode != "wal": # in-memory databases and some network file systems quietly stay in another mode
conn.close()
raise RuntimeError(f"journal_mode=WAL was refused, the database is in {mode!r} mode")
conn.execute("PRAGMA synchronous=NORMAL")
conn.execute("PRAGMA foreign_keys=ON")
return conn
@contextlib.contextmanager
def write_tx(conn):
"""Take the write lock first (BEGIN IMMEDIATE), so no stale read can reach the first write."""
try:
conn.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as exc:
if "locked" in str(exc):
raise StoreBusy("another connection is holding the write lock") from exc
raise
try:
yield conn
conn.execute("COMMIT")
except BaseException:
if conn.in_transaction:
conn.execute("ROLLBACK")
raise
@contextlib.contextmanager
def read_tx(conn):
"""One consistent snapshot for several SELECTs. Always ended quickly, so checkpoints can move on."""
conn.execute("BEGIN")
try:
yield conn
finally:
conn.execute("ROLLBACK")
def checkpoint(conn, mode="PASSIVE", wait_ms=None):
"""PRAGMA wal_checkpoint: returns (busy, frames in the wal, frames checkpointed)."""
if mode not in ("PASSIVE", "FULL", "RESTART", "TRUNCATE"):
raise ValueError(mode)
old = conn.execute("PRAGMA busy_timeout").fetchone()[0]
if wait_ms is not None:
conn.execute(f"PRAGMA busy_timeout={int(wait_ms)}")
try:
return tuple(conn.execute(f"PRAGMA wal_checkpoint({mode})").fetchone())
finally:
conn.execute(f"PRAGMA busy_timeout={old}")
def wal_bytes(path):
try:
return os.path.getsize(f"{path}-wal")
except FileNotFoundError:
return 0
def backup(conn, destination):
"""A consistent copy of a live database, including commits that are still only in the -wal file."""
target = sqlite3.connect(destination)
try:
conn.backup(target)
finally:
target.close()Here is what each piece does and which step it comes from.
connect() opens one connection with autocommit=True and a busy timeout (Steps 3 to 5), switches the file to WAL and refuses to continue if SQLite answers with any other mode (Step 3), sets synchronous=NORMAL on every connection (Steps 3 and 7), and turns on foreign keys, another per-connection setting that is off by default. StoreBusy is the exception your service catches when the write lock could not be had within the timeout, so you can answer with a 503 and a Retry-After header instead of a stack trace (Step 4).
write_tx() is the heart of it. It sends BEGIN IMMEDIATE, so the write lock comes first and there is no stale snapshot to trip over (Step 5), commits when the block ends, and rolls back if the block raises. read_tx() gives you one consistent snapshot for several SELECT statements and always ends it, so a forgotten reader cannot pin the log (Step 6). checkpoint() wraps the pragma and its three-number result, wal_bytes() is the metric to watch, and backup() is the safe copy from Step 8.
Now the tests. Each one pins a behavior from an earlier step, so a future change cannot quietly undo it. Save this as test_store.py.
# test_store.py
import shutil
import sqlite3
import threading
import time
import pytest
import store
@pytest.fixture
def db(tmp_path):
path = tmp_path / "t.db"
conn = store.connect(path)
conn.execute("CREATE TABLE counters (id INTEGER PRIMARY KEY, n INTEGER NOT NULL)")
conn.execute("INSERT INTO counters VALUES (1, 0)")
yield path, conn
conn.close()
def value(conn):
return conn.execute("SELECT n FROM counters WHERE id = 1").fetchone()[0]
def test_wal_is_switched_on_and_persists(db):
path, conn = db
assert conn.execute("PRAGMA journal_mode").fetchone()[0] == "wal"
other = sqlite3.connect(path)
try:
assert other.execute("PRAGMA journal_mode").fetchone()[0] == "wal"
finally:
other.close()
def test_memory_database_refuses_wal():
with pytest.raises(RuntimeError, match="memory"):
store.connect(":memory:")
def test_reader_is_not_blocked_by_an_open_write_transaction(db):
path, writer = db
reader = store.connect(path, busy_ms=0)
try:
with store.write_tx(writer):
writer.execute("UPDATE counters SET n = 99")
assert value(reader) == 0 # the last committed value, with no waiting
assert value(reader) == 99
finally:
reader.close()
def test_second_writer_gets_storebusy_after_the_timeout(db):
path, first = db
second = store.connect(path, busy_ms=150)
try:
with store.write_tx(first):
started = time.perf_counter()
with pytest.raises(store.StoreBusy):
with store.write_tx(second):
pass
assert 0.1 < time.perf_counter() - started < 2.0
finally:
second.close()
def test_write_tx_rolls_back_when_the_body_raises(db):
path, conn = db
with pytest.raises(ZeroDivisionError):
with store.write_tx(conn):
conn.execute("UPDATE counters SET n = 5")
1 / 0
assert value(conn) == 0
assert not conn.in_transaction
def test_concurrent_increments_lose_nothing(db):
path, _ = db
def work():
conn = store.connect(path)
for _ in range(50):
with store.write_tx(conn):
n = value(conn)
conn.execute("UPDATE counters SET n = ?", (n + 1,))
conn.close()
threads = [threading.Thread(target=work) for _ in range(8)]
for thread in threads:
thread.start()
for thread in threads:
thread.join()
check = store.connect(path)
try:
assert value(check) == 8 * 50
finally:
check.close()
def test_a_deferred_transaction_with_a_stale_read_fails_at_once(db):
path, a = db
b = store.connect(path) # busy_ms is 5000 on both connections
try:
a.execute("BEGIN")
value(a) # the read snapshot starts here
b.execute("UPDATE counters SET n = n + 1") # autocommit: commits immediately
started = time.perf_counter()
with pytest.raises(sqlite3.OperationalError, match="database is locked"):
a.execute("UPDATE counters SET n = 10")
assert time.perf_counter() - started < 1.0 # waiting would not help, so SQLite does not wait
a.execute("ROLLBACK")
finally:
b.close()
def test_read_tx_keeps_one_snapshot(db):
path, writer = db
reader = store.connect(path)
try:
with store.read_tx(reader):
first = value(reader)
with store.write_tx(writer):
writer.execute("UPDATE counters SET n = 7")
assert value(reader) == first
assert value(reader) == 7
finally:
reader.close()
def test_a_long_reader_blocks_wal_truncation(db):
path, writer = db
reader = store.connect(path)
try:
with store.read_tx(reader):
value(reader)
with store.write_tx(writer):
writer.execute("UPDATE counters SET n = n + 1")
busy, frames, done = store.checkpoint(writer, "TRUNCATE", wait_ms=50)
assert busy == 1 and done < frames
assert store.checkpoint(writer, "TRUNCATE") == (0, 0, 0)
assert store.wal_bytes(path) == 0
finally:
reader.close()
def rows(path):
conn = sqlite3.connect(path)
try:
return conn.execute("SELECT count(*) FROM notes").fetchone()[0]
finally:
conn.close()
def test_plain_copy_misses_wal_only_commits_but_backup_keeps_them(db, tmp_path):
path, conn = db
conn.execute("PRAGMA wal_autocheckpoint=0")
conn.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
store.checkpoint(conn, "TRUNCATE")
with store.write_tx(conn):
conn.executemany("INSERT INTO notes (body) VALUES (?)", [("late",)] * 20)
plain, safe = tmp_path / "plain.db", tmp_path / "safe.db"
shutil.copyfile(path, plain)
store.backup(conn, safe)
assert rows(plain) == 0
assert rows(safe) == 20
def test_a_connection_cannot_be_used_from_another_thread(db):
path, conn = db
errors = []
def use_it():
try:
conn.execute("SELECT 1")
except sqlite3.ProgrammingError as exc:
errors.append(str(exc))
thread = threading.Thread(target=use_it)
thread.start()
thread.join()
assert errors and "thread" in errors[0]
def test_commit_method_does_nothing_in_autocommit_mode(db):
path, conn = db
conn.execute("BEGIN IMMEDIATE")
conn.execute("UPDATE counters SET n = 5")
conn.commit()
assert conn.in_transaction # commit() did nothing, the transaction is still open
conn.execute("ROLLBACK")
assert value(conn) == 0
def test_legacy_mode_opens_its_transaction_only_for_writes(db):
path, _ = db
legacy = sqlite3.connect(path) # default settings: autocommit is LEGACY_TRANSACTION_CONTROL
try:
legacy.execute("SELECT n FROM counters").fetchone()
assert not legacy.in_transaction # the SELECT ran outside any transaction
legacy.execute("UPDATE counters SET n = n")
assert legacy.in_transaction # the UPDATE opened one
legacy.rollback()
finally:
legacy.close()
def test_an_unfinished_select_blocks_wal_truncation(db):
path, writer = db
with store.write_tx(writer):
writer.executemany("INSERT INTO counters (n) VALUES (?)", [(i,) for i in range(1, 5)])
reader = store.connect(path)
try:
cursor = reader.execute("SELECT id FROM counters ORDER BY id")
cursor.fetchone() # one row read, the statement is still active and so is its snapshot
with store.write_tx(writer):
writer.execute("UPDATE counters SET n = n + 1")
busy, frames, done = store.checkpoint(writer, "TRUNCATE", wait_ms=50)
assert busy == 1
cursor.fetchall() # the statement finishes, and the snapshot ends with it
assert store.checkpoint(writer, "TRUNCATE") == (0, 0, 0)
finally:
reader.close()Run the suite with python -m pytest -q. Output from my run:
.............. [100%]
14 passed in 1.02sThree of the tests deserve a comment. test_concurrent_increments_lose_nothing starts eight threads that each read a counter, add one and write it back 50 times, which is the read-then-write pattern from Step 5; with write_tx the total is exactly 400. test_a_deferred_transaction_with_a_stale_read_fails_at_once documents the trap on purpose: it uses a plain BEGIN, shows that the error arrives in well under a second even though the busy timeout is five, and so fails the day someone changes the behavior. And test_commit_method_does_nothing_in_autocommit_mode confirms the Python documentation’s statement that commit() and rollback() “have no effect” in this mode: if you call conn.commit() out of habit, the transaction stays open.
The Python documentation also explains the thread test. check_same_thread is on by default: “If True (default), ProgrammingError will be raised if the database connection is used by a thread other than the one that created it.” Open one connection per thread, as the harness does.
Step 10: Confirm everything works end to end
The last script puts the finished module under the original kind of load: two writers and four readers on one WAL file for three seconds. Writers use write_tx. Readers use read_tx with a busy timeout of zero, so any refusal would show up as an error. At the end it checks that every committed row is accounted for and runs integrity_check. Note the two lines near the top: the file is switched to WAL once at startup, before the threads open their own connections. Save it as step09_end_to_end.py.
# step09_end_to_end.py
# Two writers and four readers on the finished helpers. Every committed row must be accounted for.
import threading
import time
import store
from harness import SEED_ROWS, fresh_copy
ROWS_PER_COMMIT = 50
path = fresh_copy("final.db") # starts in the default mode
store.connect(path).close() # switch to WAL once, at startup, before the threads open their own connections
stop = threading.Event()
lock = threading.Lock()
result = {"reads": 0, "read_errors": 0, "busy": 0, "commits": 0}
def writer():
conn = store.connect(path)
while not stop.is_set():
try:
with store.write_tx(conn):
conn.executemany(
"INSERT INTO events (source, payload) VALUES (?, ?)",
((f"sensor-{i % 50}", "w" * 200) for i in range(ROWS_PER_COMMIT)),
)
except store.StoreBusy:
with lock:
result["busy"] += 1
continue
with lock:
result["commits"] += 1
time.sleep(0.002)
conn.close()
def reader():
conn = store.connect(path, busy_ms=0) # readers in WAL mode should never need to wait
while not stop.is_set():
try:
with store.read_tx(conn):
conn.execute("SELECT count(*) FROM events WHERE source = 'sensor-7'").fetchone()
with lock:
result["reads"] += 1
except Exception:
with lock:
result["read_errors"] += 1
time.sleep(0.001)
conn.close()
threads = [threading.Thread(target=writer) for _ in range(2)] + [threading.Thread(target=reader) for _ in range(4)]
for thread in threads:
thread.start()
time.sleep(3.0)
stop.set()
for thread in threads:
thread.join()
check = store.connect(path)
found = check.execute("SELECT count(*) FROM events").fetchone()[0]
verdict = check.execute("PRAGMA integrity_check").fetchone()[0]
check.close()
expected = SEED_ROWS + result["commits"] * ROWS_PER_COMMIT
print(f"reads completed: {result['reads']}, read errors: {result['read_errors']}")
print(f"commits: {result['commits']}, writers that gave up (StoreBusy): {result['busy']}")
print(f"rows expected {expected}, rows found {found}, match: {expected == found}")
print("integrity_check:", verdict)Output from my run:
reads completed: 376, read errors: 0
commits: 1497, writers that gave up (StoreBusy): 0
rows expected 124850, rows found 124850, match: True
integrity_check: okThere were no read errors, no writer gave up, and the row count matches exactly: 50,000 seeded rows plus 1,497 commits of 50 rows is 124,850. When I ran the same script on Python 3.15.0 with SQLite 3.53.4 it also reported zero read errors, an exact row match and an ok integrity check. The deterministic results from Steps 5 to 8 were identical on both versions, and only the timing figures differed. (The pytest suite ran on Python 3.13 only.)
Common mistakes and how to spot them
Sharing one connection between threads
Python refuses by default, with a ProgrammingError about the thread that created the connection. The tempting fix is check_same_thread=False, but the documentation warns that then “write operations may need to be serialized by the user to avoid data corruption.” One connection per thread is simpler and is what every experiment here does.
Treating the with block as a close
The Python documentation is explicit: “The context manager neither implicitly opens a new transaction nor closes the connection.” An unclosed connection also keeps the file open. While writing this tutorial, one of my own first drafts opened a connection, hit an error and never closed it, and the next experiment died with PermissionError: [WinError 32] when it tried to delete the file. Wrap connections in try and finally, or in contextlib.closing().
Leaving a read transaction open
It is the 43 MiB log from Step 6. Look for a BEGIN with no matching end, a cursor that is never exhausted, and web handlers that hold a connection while they wait for something else.
Putting the file where processes cannot share memory
The requirement again: “All processes using a database must be on the same host computer; WAL does not work over a network filesystem.” A database on a network share is not a candidate for WAL, and the failure may be quiet, because Step 3 showed that the pragma can answer with the old mode instead of raising an error. That is why connect() checks the string.
Copying or deleting only some of the files
Use the backup API or VACUUM INTO for live databases, as in Step 8, and do not remove the log files by hand.
Expecting WAL to remove every busy error
It removes the collisions between readers and the writer. Writers still queue (Step 4), a stale read inside a deferred transaction still fails (Step 5), and cold starts can still surprise a zero-timeout reader (Step 3). The remedy for all three is the same small set of habits in store.py.
Where to go from here
You now have a connection helper that follows the rules, a way to measure the effect of each rule, and a test suite that keeps them in place. A few directions for further learning:
- Handle
StoreBusyproperly in a web service. Our tutorial on backpressure and load shedding covers answering with a clear refusal, and the one on retries with exponential backoff and jitter covers the client side. - See the lost-update problem from the HTTP side with ETag and If-Match, and look at keyset pagination on SQLite and reading EXPLAIN QUERY PLAN to keep read queries short, which keeps snapshots short.
- Learn the idea behind the log by building one: How to Build a Write-Ahead Log in Python. For patterns that keep two systems consistent, see the transactional outbox and two-phase commit tutorials.
- Upgrade SQLite if Step 1 printed
False, then read the SQLite WAL page end to end. It is short and it is the source for most of the claims above.
Sources
- SQLite: Write-Ahead Logging (how WAL works, checkpoint starvation, the busy cases and the WAL-reset bug)
- SQLite: PRAGMA statements (journal_mode, synchronous, wal_checkpoint, journal_size_limit)
- SQLite: Transactions, result codes, busy handler, file locking, how to corrupt a database and VACUUM
- Python documentation: sqlite3
No Comment! Be the first one.