Three gaps in the #110544 guard, all reported in its review and reproduced:
- A writer reopened by _reopen_after_close_locked (teardown/worker race,
#94736) came back with no guard: the next stray close + foreign close
deleted its WAL again.
- _try_wal_checkpoint refreshed the guard outside self._lock; landing after
close() it pinned an OFD lock with no connection behind it, so a foreign
`PRAGMA journal_mode=DELETE` saw `database is locked` forever.
- Refcounts keyed on (fd, inode) treated a recycled fd number as a surviving
lock: A+B live, close A, C reuses A's fd, close B left C recorded as guarded
while a foreign EXCLUSIVE succeeded.
The guard now counts handles per inode, re-locks every matching descriptor on
each hold (OFD re-lock is idempotent), and unlocks on the last handle only;
the reopen path holds it; the checkpoint refresh runs under self._lock and
skips a closed handle. The macOS holder scan folds case so a case-only alias
of the sidecar path on APFS still matches.
Two SessionDB handles on one state.db in one process see the same
descriptors; the first release must not drop the range the second still
needs. Test covers the same-process sibling and the foreign-process close.
SQLite protects a WAL generation with per-PROCESS POSIX locks (SHARED on
state.db, DMS byte on -shm). Any in-process open()/close() of either file
cancels both (sqlite.org/howtocorrupt.html §2.2); the next last-connection
close in ANY process then checkpoints and unlinks -wal/-shm, and the holder
sticky-halts with DeletedWalGenerationError. #109841 removed one such
close (mode tightening) but the class is open-ended: raw header probes,
plugins, tool reads of ~/.hermes, any library that touches the files.
hermes_state_lockguard re-holds the same two ranges as OFD locks
(F_OFD_SETLK) on private descriptors for as long as a writer handle is
open. OFD locks belong to the open file description, so a stray close()
cannot cancel them, and they conflict with the EXCLUSIVE a sibling needs
for the close-time reset exactly like SQLite's own. Released before the
handle's own close so a true last close still ends the generation; the
descriptors are closed only once no connection to the path remains, so a
holder scan from another process never counts them. Works on Python 3.11
(where sqlite3 cannot arm SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE) and on macOS
(F_OFD_SETLK=90 per XNU bsd/sys/fcntl.h); no-op on Windows.
Live repro (Linux, Python 3.11.15, SQLite 3.53.1): holder = SessionDB
writer; in-process os.open/os.close of state.db and -shm; then a foreign
sqlite3.connect()+close(). Before: -wal unlinked, holder write raises
DeletedWalGenerationError. After: -wal keeps its inode, holder writes.
teknium1