Reads run on a thread pool
Building a FileIndex means reading every base file in a group off disk and normalising its text before the vectorizer ever sees it. That is I/O bound, so FileIndex.build hands the work to a ThreadPoolExecutor: several files read and hashed concurrently instead of one at a time.
Each of those reads checks a cache first - VectorCache, a SQLite table keyed on the SHA-256 of the file's bytes. A cache hit skips the read and the text-cleaning pass entirely. That table is the shared resource every worker thread needs to touch, and SQLite has an opinion about that.
The instinct SQLite's docs point you toward
A sqlite3.Connection object is not safe to share across threads by default - the library raises if you try to use one from a thread other than the one that created it. The documented way around that is either check_same_thread=False on a shared connection, or a separate connection per thread. A connection per thread reads as the more careful choice: no shared mutable state, no need to reason about SQLite's internal locking, each worker fully isolated.
It is also the version that shipped first, and the one that broke.
What actually failed
A connection created inside a pool worker belongs to that worker's thread. ThreadPoolExecutor reuses threads across tasks, but it does not guarantee the thread that opened a connection is still the one alive - or still assigned - when the index is done being built and something calls cache.close() from the caller's thread. Closing a SQLite connection from a thread that did not create it is undefined at best; in practice it either raises or silently fails to release the file handle.
The failure mode didn't show up as an exception. It showed up later:
# One connection guarded by a lock, NOT one per thread. FileIndex.build reads
# through a ThreadPoolExecutor, and a thread-local connection cannot be closed
# from the thread calling close() - that leaked handles and made the index
# directory undeletable on Windows (WinError 32).On Linux the leaked file descriptor is mostly invisible - the OS lets you delete a file while a process still holds it open, and the space reclaims once the last handle closes. Windows does not allow that: a file with an open handle cannot be deleted or renamed, full stop. The index directory that a test tried to clean up between runs was still "in use" by a connection nobody could close anymore, because the thread that owned it had already been reassigned to something else by the pool. Tests failed intermittently, exactly as often as the pool happened to schedule things unluckily.
The fix was to stop isolating the connection
VectorCache now opens exactly one connection, with check_same_thread=False, and every access - read or write - goes through a single threading.Lock:
class VectorCache:
def __init__(self, path):
self._conn = sqlite3.connect(str(path), check_same_thread=False)
self._conn.execute("PRAGMA journal_mode=WAL")
self._lock = threading.Lock()
def get(self, key):
with self._lock:
row = self._conn.execute(
"SELECT value FROM cache WHERE key = ?", (key,)
).fetchone()
return row[0] if row else None
def close(self):
with self._lock:
self._conn.close()One connection means one owner, opened on one thread and closed on the same logical owner every time - close() is no longer racing against which pool thread happens to still exist. The lock is what makes sharing that single connection across worker threads safe: SQLite connections can be handed off between threads with check_same_thread=False, but they still cannot be used from two threads at once. The lock is doing exactly the isolation work the per-thread design was trying to get for free, just at the call site instead of at connection-open time.
Why the lock doesn't cost anything here
A lock around every cache access sounds like it should reintroduce contention the thread pool was supposed to avoid. It doesn't, because of what is actually inside the critical section: a primary-key lookup by hash, or a single-row insert. Both are sub-millisecond against a local SQLite file. The thread pool's real win is overlapping the slow part - disk reads and text cleaning - across workers, and none of that happens while the lock is held. Contention on a lock only matters when what it guards is slow; here it guards the fastest part of the whole read.
WAL mode is what makes even that lock feel unnecessary in the common case, and it's worth being precise about why it's still there. Write-ahead logging lets SQLite serve readers from the log without blocking on a writer, so concurrent reads and a concurrent write don't fight over the same file lock the way SQLite's default rollback journal would. What WAL does not do is make one Connection object safe to call from two threads at once - that's a Python-object-level guarantee, not a file-level one, and WAL operates below it. The lock and WAL are solving different problems that happen to sit next to each other: WAL keeps the file responsive under concurrent access, the lock keeps one Python object from being used by two threads simultaneously.
What generalizes
The per-thread connection looked like the safer design because it avoided a lock. But a resource with a lifecycle - something that gets closed, not just read - has an owner, and the owner has to be a single, well-defined thing for that lifecycle to make sense. Splitting a connection across N threads doesn't remove the ownership question, it just makes the answer ambiguous: all of them own it, so none of them reliably does. A short critical section behind an explicit lock is boring, but boring is what lets you point at exactly one line that opens the connection and exactly one line that closes it, on a code path everyone can read.
The bug also only reproduced on Windows, which is its own lesson: POSIX's tolerance for deleting an open file hid the leak completely on Linux and macOS dev machines. A resource-lifecycle bug that a permissive filesystem papers over is still a bug - it just needs a stricter filesystem to go looking for it.