The sqlite3 close trap

 2 min Python’s sqlite3 context manager ends a transaction but leaves the connection open. A finally block owns the close.

Python’s sqlite3.Connection context manager handles a transaction: it commits on success and rolls back on failure. It leaves the connection open. When the caller retains that connection, its file descriptor stays open until explicit close or finalization.

The fix, from the Spice harness:

@contextmanager
def sqlite_connection(path, *, busy_timeout_ms=None, wal=False,
                      ensure_parent=False):
    """Yield a connection that commits on success and always closes."""
    if ensure_parent:
        Path(path).parent.mkdir(parents=True, exist_ok=True)
    connection = sqlite3.connect(path)
    try:
        if busy_timeout_ms is not None:
            connection.execute(f"PRAGMA busy_timeout = {int(busy_timeout_ms)}")
        if wal:
            connection.execute("PRAGMA journal_mode = WAL")
        yield connection
        connection.commit()
    except BaseException:
        connection.rollback()
        raise
    finally:
        connection.close()

Commit on success, attempt rollback on failure, and close in finally. BaseException rather than Exception includes a KeyboardInterrupt mid-transaction. A failed commit also enters the rollback path. If rollback itself raises, finally still attempts the close; if close raises, that error propagates. The helper guarantees the cleanup path, not successful cleanup or preservation of the original exception as the one the caller catches.

The same module avoids repeating schema setup on every open. ensure_sqlite_schema_once keeps retrying the schema initializer until the first successful commit, then skips it for that path for the rest of the process. That cache assumes one schema owner per path; it does not identify the file currently at that path. A double-checked lock keeps the cached path to one set-membership test when validate is absent. Callers that supply a validation callback deliberately open the database and run that check even on a cached path, catching a file replaced or advanced by another writer. The helper opens WAL so a reader and the single writer can proceed together. The path is recorded only after the DDL commits, so an initializer that raises leaves the next caller to try again rather than marking a database that was never built.