fastapi + aiomysql connection pool stuck after 10 calls

Question

Why aiomysql connection pool stuck after N calls? (N is the maxsize number of connection. Tried the default N=10 and N=3)

I thought the acquired connections are automatically closed on exit with async with.

Here's the minimal script to reproduce:

from fastapi import FastAPI
import aiomysql
import secret

app = FastAPI()

@app.on_event("startup")
async def _startup():
    app.state.pool = await aiomysql.create_pool(host=secret.DB_URL, port=3306, user=secret.DB_USERNAME, password=secret.DB_PASSWORD, db=secret.DB_DATABASE)
    print("startup done")

async def _get_query_with_pool(pool):
    async with await pool.acquire() as conn:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute("SELECT 1")
            return await cur.fetchall()

@app.get("/v1/get_data")
async def _get_data():
    return await _get_query_with_pool(app.state.pool)


if __name__ == "__main__":
    import uvicorn

    uvicorn.run(app, host="0.0.0.0", port=8000)

Answer 1

Old answer:

turns out the culprit is the extra await before pool.acquire()

async def _get_query_with_pool(pool):
    async with await pool.acquire() as conn:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute("SELECT 1")
            return await cur.fetchall()

remove the extra await before pool.acquire(), so:

async def _get_query_with_pool(pool):
    async with pool.acquire() as conn:
        async with conn.cursor(aiomysql.DictCursor) as cur:
            await cur.execute("SELECT 1")
            return await cur.fetchall()

and now the connection works successfully

Update Q4 2021:

Turns out connection pooling are not worth it. Connection pooling can cause mysql8 alter table to lock forever.

Just open and close db connections every time you done accessing it.