sqlite3 - file stays opened even after connection is closed (still opened by the cursor?)

[Andrej730]

Bug report

Bug description:

Python 3.11.9

Consider the snippet below.
It creates a new sqlite database, then closing the connection to it and trying to delete the file. On Windows it fails since sqlite file is still used by Python.

It seems the created cursor is still using the file, though all connections to it are closed.
Which is kind of unexpected, since when you close the connection to the database, connection object and all cursors are effectively become dead and they cannot be reopened, so nothing should be still using the file after connection is closed.

What helps is to close cursor explicitly (c.close() or just delete it del c).

Another detail - if we create a table, but don't insert anything to it, issue doesn't occur.

from pathlib import Path
import sqlite3

db_path = Path("new_db.sqlite")
if db_path.exists():
    db_path.unlink()

db = sqlite3.connect(db_path)
c = db.cursor()
c.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)")

# Commenting out resolves the issue.
c.executemany("INSERT INTO test (value) VALUES (?)", [(f"value_{i}",) for i in range(1000)])

db.commit()
# c.close() # Required to avoid the error.
db.close()

# PermissionError:
# [WinError 32] The process cannot access the file because it is being used by another process: 'new_db.sqlite'
db_path.unlink()

CPython versions tested on:

3.11

Operating systems tested on:

Windows

[VPradoB]

could not replicate this on arch linux. python 3.11.9 (no venv)

[erlend-aasland]

Which is kind of unexpected, since when you close the connection to the database, connection object and all cursors are effectively become dead and they cannot be reopened, so nothing should be still using the file after connection is closed.

This is not true. Closing a connection does not implicitly cause cursors to become "dead"¹; it only decrefs the cursors. Clinging on to a reference may defer the actual closing of the SQLite database.

It is good resource management to always explicitly close your cursors when you are done using them.

Perhaps we could explain this better in the docs.
Perhaps we should also emit a ResourceWarning if there are dangling cursors.

Footnotes

1. I'm not sure what "dead" means here; I assume either "deleted" and/or "closed" ↩

[erlend-aasland]

Clinging on to a reference may defer the actual closing of the SQLite database.

Expanding on this:

We use the SQLite C API sqlite3_close_v2. Quoting from its documentation:

If sqlite3_close_v2() is called with unfinalized prepared statements, unclosed BLOB handlers, and/or unfinished sqlite3_backups, it returns SQLITE_OK regardless, but instead of deallocating the database connection immediately, it marks the database connection as an unusable "zombie" and makes arrangements to automatically deallocate the database connection after all prepared statements are finalized, all BLOB handles are closed, and all backups have finished. The sqlite3_close_v2() interface is intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary.

Explicitly (calling .close()) or implicitly (for example deleting) closing a cursor will cause its "attached" sqlite3_stmt * to be reset, thus is will not defer connection close.

[erlend-aasland]

I suggest we start with a docs update. We might also want to emit a resource warning.

[Andrej730]

1. I'm not sure what "dead" means here; I assume either "deleted" and/or "closed" ↩

Sorry, if it's kind of ambigious. I meant it as in "the cursor becomes unusable", basically "closed". Since all cursors become unusable once connection is closed, so maybe it would made sense for them to close their connection to the file too.

I don't know how it's internally structured and whether it's possible to close all cursors on connection.close() or connection doesn't hold any references to cursors, so it wouldn't be possible, and creating a such container of weakrefs to cursors and maintaining it probably wouldn't worth it just to make .close() more convenient.

So, some documentation note will probably do - it seems closing cursor explicitly or implicitly (by deleting it) before reaccessing the file could be crucial on Windows. Not sure if there's a similar angle on closing cursor for Unix, but for anyone using sqlite, it's a small caveat to keep in mind in case they want their code to run cross-platform, though there won't be errors on Unix.

[vstinner]

Perhaps we should also emit a ResourceWarning if there are dangling cursors.

It sounds like an interesting idea.