A transaction inventory that treats in_transaction=False as proof that a connection cannot be holding a read snapshot will miss this reader. If your WAL keeps growing despite an apparently empty transaction inventory, inspect unfinished cursors as well as explicit transactions.
The flag does not inventory active readers
Python documents in_transaction as reflecting SQLite’s low-level autocommit mode. It does not provide a list of statements still reading from the connection. The distinction becomes visible when a query has returned a row but still has more rows to deliver.
SQLite gives the reader a consistent view of the database. An unfinished statement can keep that view alive. Its transaction documentation explains that an implicit transaction finishes when its last active statement finishes; resetting or finalizing the statement guarantees that finish. An explicit BEGIN has a separate lifetime.
A plausible application trigger is a generator that yields database rows while waiting for a network client, or a loop that breaks early and leaves its cursor reachable. The SQL may be fast. The application can still keep the reader alive while doing other work. These are failure patterns to inspect, not incidents we claim to have observed in your application.
What the controlled test actually showed
We ran the experiment twice on Windows with Python 3.12.14 and SQLite 3.53.1. Two connections shared a disposable database containing 100 rows. Both used isolation_level=None; no implicit Python BEGIN was requested. We fixed the page size at 4,096 bytes, disabled automatic checkpoints and used a zero busy timeout to keep the sequence explicit.
Each write batch committed 20 one-row updates. We then ran PRAGMA wal_checkpoint(PASSIVE) from the writer and measured the WAL file. This probe performs checkpoint work: it is not a passive observation of an untouched database. The tuple reports busy indicator, log frames, checkpointed frames, in that order, as described in the SQLite PRAGMA reference.
With the second connection merely open and idle, the result was (0, 20, 20): every frame checkpointed. Keeping a connection open was therefore insufficient to reproduce the blockage.
We next read just one row from a 100-row SELECT. After 20 writes the result was (0, 20, 0); after another 20 it was (0, 40, 0). The reader’s Python flag was still False. Calling reader.commit() left the same result. Closing the cursor changed it to (0, 40, 40).
For the other case, we issued BEGIN, read all rows and closed the cursor before writing. The checkpoint still returned (0, 20, 0), now with in_transaction=True. Only ending that explicit transaction with reader.rollback() let the result become (0, 20, 20). Cursor closure alone had already happened.
Reproduce the result in a disposable database
This script creates its own temporary database and closes both connections before deleting it. It uses the legacy isolation_level=None path deliberately, to expose the low-level behavior without a surrounding Python-managed transaction. Run it with Python’s standard-library sqlite3; check the printed Python and SQLite versions when comparing results.
import sqlite3
import sys
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory
print(sys.version.split()[0], sqlite3.sqlite_version)
with TemporaryDirectory() as scratch:
db = Path(scratch) / "lab.db"
wal = Path(str(db) + "-wal")
with closing(sqlite3.connect(
db, isolation_level=None
)) as writer, closing(sqlite3.connect(
db, isolation_level=None
)) as reader:
writer.execute("PRAGMA page_size=4096")
writer.execute("PRAGMA journal_mode=WAL").fetchone()
writer.execute("PRAGMA wal_autocheckpoint=0")
writer.execute("PRAGMA busy_timeout=0")
writer.execute(
"CREATE TABLE t(id INTEGER PRIMARY KEY, value)"
)
writer.executemany(
"INSERT INTO t VALUES (?, 0)",
[(i,) for i in range(1, 101)]
)
def reset():
writer.execute(
"PRAGMA wal_checkpoint(TRUNCATE)"
).fetchone()
def writes():
for _ in range(20):
writer.execute(
"UPDATE t SET value=value+1 WHERE id=1"
)
def probe(label):
result = writer.execute(
"PRAGMA wal_checkpoint(PASSIVE)"
).fetchone()
print(label, reader.in_transaction,
result, wal.stat().st_size)
reset()
writes()
probe("idle")
reset()
cur = reader.execute("SELECT * FROM t ORDER BY id")
cur.fetchone() # Leave 99 rows unread.
writes()
probe("unfinished")
writes()
probe("more writes")
reader.commit()
probe("commit called")
cur.close()
probe("cursor closed")
writes()
probe("reuse")
reset()
print("truncated", wal.stat().st_size)
reader.execute("BEGIN")
cur = reader.execute("SELECT * FROM t ORDER BY id")
cur.fetchall()
cur.close()
writes()
probe("explicit, cursor closed")
reader.rollback()
probe("explicit ended")The printed columns are the label, reader flag, checkpoint tuple and WAL bytes. Our output was identical in both full runs; the published reproduction also produced the same measurements:
3.12.14 3.53.1
idle False (0, 20, 20) 82432
unfinished False (0, 20, 0) 82432
more writes False (0, 40, 0) 164832
commit called False (0, 40, 0) 164832
cursor closed False (0, 40, 40) 164832
reuse False (0, 20, 20) 164832
truncated 0
explicit, cursor closed True (0, 20, 0) 82432
explicit ended False (0, 20, 20) 82432The disabled automatic checkpoint is a laboratory choice, not a tuning recommendation. It makes every checkpoint in the sequence visible. In an application with automatic checkpoints enabled, a long-lived reader can still prevent them from completing, but the exact frame counts depend on the workload and connection activity. This experiment measures lifetime and checkpoint progress; it does not compare throughput or establish a production size threshold.
A zero first column can accompany an unfinished checkpoint
Our blocked result began with zero. That is easy to misread as “everything succeeded.” For PASSIVE, compare the second and third columns: 40 log frames and 0 checkpointed frames show that work remained. The checkpoint API documentation explicitly allows this mode to finish its attempt without finishing the checkpoint when concurrent readers or writers prevent progress.
Track both counts over comparable probes, together with the time your application keeps cursors and explicit transactions open. A widening gap is evidence to investigate. It does not identify which connection owns the reader, and a gap alone does not prove an unfinished Python cursor is the cause.
Recovery did not make the WAL file smaller
After the unfinished cursor closed, all 40 frames checkpointed, but the file stayed at 164,832 bytes. Another 20 updates produced (0, 20, 20) with the same file size. The next writes reused the allocated space. Our later successful TRUNCATE probe reduced it to zero.
SQLite normally reuses WAL space rather than truncating the file at every checkpoint. Physical file length and the current checkpoint backlog answer different questions. An unchanged byte count after closing a reader does not, by itself, mean the fix failed.
Conversely, repeatedly asking for TRUNCATE will not release the application’s reader for it. That mode adds waiting and truncation behavior; check its result and consider its effect on writers before using it for maintenance. Do not manually delete a live WAL file to clear the symptom.
Inspect the code that keeps the cursor alive
Look for an early return from row iteration, a generator paused between yields, a cursor stored on a long-lived object, or I/O performed inside the read loop. When the application is done with the result, close the cursor deliberately, preferably through a finally block or contextlib.closing. If the read is inside an explicit transaction, also finish that transaction at the boundary its owner intended; do not roll back arbitrary work merely to reduce the WAL.
For large result sets, reading everything into memory to release the cursor may create a different problem. Short bounded queries, sensible pagination or separating the read from slow downstream work can reduce the hold, but splitting a query also changes which consistent view it sees. Choose that tradeoff explicitly.
The same resource-lifetime issue appears in our slow JSON-body timeout test: a convenient outer signal can stop tracking work that is still active. If the real symptom is an overwritten edit rather than a growing log, the autosave conflict experiment addresses that separate write-concurrency problem.
For this SQLite case, verify recovery with another checkpoint probe after the intended reader finishes. The useful observation is that the frame counts catch up. Waiting for the file to shrink, or accepting a False flag as an all-clear, can send you back to the wrong part of the application.
