Missing cleanup protection can lead DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server]Connection is busy with results for another command (0)

[claria]

We are using

Python 3.11.9
aioodbc: 0.5.0
pyodbc: 5.2.0
sqlalchemy: 2.0.41

The issue was observed in sqlalchemy but is due to the behaviour of aioodbc.

Whenever two statements are executed (with two cursors) right after another, where the result (and cursor) of the first statement goes out of scope before the next statement is executed, the issue can potentially be triggered:

rows = (await session.execute(stmt1)).mappings().all()
total_count = (await session.scalars(stmt2)).one()

The reason for this is the following:

• The cursor for the first statement is not explicitly closed but goes out of scope and waits for garbage collection where it will be closed (sync code).
• The cursor for the second statement is created in a thread to not block the event loop.
• There is no protection to ensure that the cleanup of the cursor does not run at the same time as the creation of a new cursor.
• Whenever these two operations happen to be run very close to each other (< few ms) we observe the error resulting in the "Connection is busy error"

More information can be found in this sqlalchemy question: link
I assume this is also related to this issue: #462

[claria]

Some clarifications:

• The bug only occurs if the cursor is not explicitly closed via its .close() method.
• SQLAlchemy used to rely on GC for implicit closing, but this will be changed: link