Open Cursors when closing connections

[Jaymon]

I just had an interesting bug that took me a bit of time to track down. I had a test that just created an iterator:

it = await orm_class.query.get()

But it never consumed the created iterator. So when the test went to cleanup the tables by dropping them and committing the transaction at the end of the test it failed with:

[E] database is locked
Traceback (most recent call last):
  File ".../prom/interface/base.py", line 560, in connection
    yield connection
  File ".../prom/interface/base.py", line 615, in transaction
    await connection.transaction_stop()
  File ".../prom/interface/base.py", line 200, in transaction_stop
    await self._transaction_stop()
  File ".../prom/interface/sql.py", line 46, in _transaction_stop
    await self.execute("COMMIT")
  File ".../python3.12/site-packages/aiosqlite/core.py", line 183, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File ".../python3.12/site-packages/aiosqlite/core.py", line 122, in _execute
    return await future
           ^^^^^^^^^^^^
  File ".../python3.12/site-packages/aiosqlite/core.py", line 105, in run
    result = function()
             ^^^^^^^^^^
sqlite3.OperationalError: database is locked

While searching:

• python 3.12 sqlite drop table in transaction fails in commit
• python 3.12 sqlite database is locked

I actually found the answer here:

When dropping a table, you get the "table is locked" message when there is still some active cursor on the table, i.e., when you did not finalize a statement (or did not close a query object in whatever language you're using).

And sure enough, when I removed the iterator query the test worked as expected. So I was able to fix the test:

it = await orm_class.query.get()
await it.close()

But that got me thinking maybe I should have connections track cursors they create? Then when the connection is closed it can make sure its open cursors are closed also.

Not sure if that is worth implementing since this is the first time this has been an actual problem in over a decade and it probably isn't a problem in real world workloads. This cropped up with what looks like changes between python 3.10 and 3.12.