mssql+aioodbc (pyodbc.Error) ('HY000', "[HY000] [Microsoft][ODBC Driver 17 for SQL Server]La connexion est occupée avec les résultats d'une autre commande (0) (SQLExecDirectW)")

[Flofinal]

Environnement:

• Python 3.12 / FastApi
• DB: sql server ODBC 17
• sqlalchemy with aioodbc (async engine)

Hello, sometimes when I call an API function I get this exception error : (pyodbc.Error) ('HY000', "[HY000] [Microsoft][ODBC Driver 17 for SQL Server]La connexion est occupée avec les résultats d'une autre commande (0) (SQLExecDirectW)")

The bug probably comes from a cursor not closing properly in aioodbc and sql server.
I switched to pyodbc with the sync engine and there are no more problems. So I think the problem is with aioodbc with sql server.

Function api exemple (not the real code):

@router.post("/desappairer/{doublet_id}/{sn_deleted}", tags=["doublet"])
async def desappairer(doublet_id:int,sn_deleted:str,request:Request, current_user: CurrentUserWithScopes(), session: AsyncSession = Depends(get_async_session)):
    data = await request.json()
    action = data.get("action")
    statement = select(Doublet).options(joinedload(Doublet.monopale_gauche), joinedload(Doublet.monopale_droite)).where(Doublet.id == doublet_id)
    result = await session.execute(statement)
    doublet = result.scalars().first()
     await session.delete(doublet)
     statement = select(Doublet).options(joinedload(Doublet.monopale_gauche),
                                                joinedload(Doublet.monopale_droite)).where(
                                                    and_(Doublet.caisse == num_caisse, Doublet.id != doublet_id))
     result = await session.execute(statement)
     doublets = result.scalars().unique().all()
     for doublet in doublets:
          doublet.monopale_gauche.status = "LIB"
          doublet.monopale_droite.status = "LIB"
          await session.delete(doublet)
     await session.commit()
     return {"data": True}

Usually the bug happens on the line await session.commit() or await session.delete(doublet) or await session.delete(doublet)

Thanks!

[tl24]

We recently converted our app to use async database calls, and newly introduced aioodbc and we are also having this issue. We were not having this issue with just pyodbc before. We have been able to get around the issue by enabling MARS (Mars_Connection=yes in the connection string), but that seems more like a band-aid than a solution as we are not intentionally executing multiple queries on the same cursor.