In my application's setup I ran into growing WAL files, and reading the documentation (https://sqlite.org/wal.html#avoiding_excessively_large_wal_files) checkpoint starvation became an immediate suspect.

A checkpoint is only able to [..] reset the WAL file, if there are no other database connections using the WAL file. If another connection has a read transaction open, then the checkpoint cannot reset the WAL file

I quickly reproduced that something like this was going on, though not quite: the quoted lines seem to indicate that another connection having a read transaction open is the blocking condition for WAL file resets. However, in my case committing the transaction did not make it so that the WAL file was cleaned up; only once the connection of the reading process was closed did the WAL file go away.

I tried connection.commit and cursor.close on the reading side, but nothing short of connection.close seemed to enable the writing process to reset the WAL file.

Test setup: one process that repeatedly changes the DB (in a fresh transaction each time), and another that opens a transaction for reading. I'm using the Python sqlite3 library, which admittedly introduces another possible point of failure/confusion.

Another thing: the results seem to be slightly intermitted, i.e. sometimes only opening a 2nd or 3rd reader produces the problem.

Finally: how could one debug into this problem further? i.e. is it possible to expose (from the side of the writing process) which reading process has which transaction open?

Perhaps you have some prepared statements for which you have not called sqlite3_reset() that are holding the read transaction open without you realizing it. Running COMMIT does not cancel a pending read transaction - it merely downgrades a write transaction into a read transaction.

To see if you have any forgotten prepared statements holding open a read transaction, try running:

SELECT sql FROM sqlite_stmt WHERE busy;

I'm quite sure I don't have prepared statements (I'm not using those) but I do find your other remark very interesting: if running COMMIT does not cancel a pending read transaction, what is the proper way to announce that a read transaction is done (short of closing the connection)?

EDIT: s/transaction/connection/g

Unfortunately, I don't think sqlite3_reset() is available directly in the Python bindings. I guess that's "my problem", but I'll see if I can work around it somehow.

Please use the query against sqlite_stmt to see what queries are pending. That might reveal what is happening.

By default, the Python connection wrapper keeps a transaction open at all times. Check the docs for how to change this behaviour.

I've managed to deepen my understanding of what's happening in my setup, leading to 1 answer and 2 new puzzle pieces:

1. In my attempts to reduce the problem to a minimal version (which is the setup I communicated about originally), I did (in Python) a cursor.execute but no cursor.fetchall(). This was revealed to me when finally running the query as suggested by Richard below: when I tried to get that query's results on screen I did do the fetchall() which unblocked the checkpointing. My current understanding is: doing only the cursor.execute but no cursor.fetchall produces a pending prepared statement.

However, having solved that problem, I was in the situation that my "minimal reproduction" was no long reproducing anything. I went back to the original problem, which is described below.

1. In my original setup, I have a few worker threads that do some work simultaneously. All DB-touching work that the threads do is wrapped in BEGIN IMMEDIATELY transactions, which thus serve as a mutex of sorts, because the threads cannot start a transaction when another thread is in a transaction. However, it seems that this mechanism does not serve as a mutex from the perspective of checkpointing. My hypothesis/understanding is that when thread A does a COMMIT, thread B's transaction can start. It is at that point that thread A attempts its checkpointing, which it cannot do because thread B has already started a new transaction. Supporting evidence for this hypothesis is: when I wrap a Python lock around the transaction (which makes thread B wait until thread A has returned from its COMMIT), checkpointing does succeed. I will proceed with reachitecting my application around this hypothesis... but is it correct?

2. A separate source of mild surprise is the observed shrinking behavior of the WAL file. Using the mechanism in [2] above I am able to grow the WAL file to e.g. 20MB by bombarding the collection of threads with work. At some point I stop the bombardment, and close the connections from all but one thread. From the remaining thread I do occasional commits. This does not cause the WAL file to shrink/disappear. Only once the final thread closes its connection does the WAL file disappear. This appears to be somewhat at odds with the following piece of documentation "By default, SQLite will automatically checkpoint whenever a COMMIT occurs that causes the WAL file to be 1000 pages or more in size, or when the last database connection on a database file closes. " (emphasis mine) -- I would expect the COMMITs to trigger a checkpoint that causes the WAL file to shrink... or perhaps a checkpoint happens, but no shrinkage?