Hey all. I’ve been digging into this as well and I don’t think this is a FastAPI issue per se. There are a few things going on that seem to be leading to the issue.

tl;dr

Dependencies and path operations defined as functions (def only) are run in an anyio threadpool. db.execute is a blocking call, causing all the workers in the pool to block in the path operation function. This prevents dependency generators from invoking their finally block, thereby preventing SQLAlchemy connections from being released.

Workarounds?

The suggested workaround to use a context manager within your path operation is by far the easiest solution. You’re scoping the SQLAlchemy session lifecycle within the same coroutine, guaranteeing that connections can be acquired and released within that coroutine. This prevents the resource contention described in the “deep dive” section.

While I haven’t tried it yet… It may be worth using the SQLAlchemy 1.4 asyncio beta. Part of the core issue here is related to db.execute being a blocking call. If SQLAlchemy natively supports async operations, it may resolve the issue.

Deep dive

def get_db(request: Request):
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

^ First, it should be noted that db = SessionLocal() is a non-blocking operation. Prior to the deadlock, all N requests will be able to create a session object and yield it to the path operation.

@app.get('/')
def index(db: Session = Depends(get_db)):
    # Some blocking work
    _ = db.execute('select 1')

^ db.execute is a blocking operation. Per the SQLAlchemy docs, the session requests a connection from the connection pool once queries are issued.

In the example code above, the SQLAlchemy connection pool is of size 4. This means that 4 requests will be able to check out a connection, while (N-4) connections will block on this line waiting for a connection to become available. (Side note, it’s good practice to define connection timeouts on your engine object to avoid waiting interminably for a connection. Though that alone won’t solve the problem).

def get_db(request: Request):
def index(db: Session = Depends(get_db)):

^ Also keep in mind that neither the dependency function nor the path operation are defined as coroutines. Both FastAPI and Starlette seem to want everything to run asynchronously, so these functions are invoked in a threadpool. See Starlette code, FastAPI doc 1, and FastAPI doc 2 for more information.

Here is where things break. anyio manages it’s own threadpool of ~40 workers, each with it’s own job queue. There are (N-4) workers blocked waiting for a SQLAlchemy connection to be relinquished. The 4 path operations that secured a connection can complete. FastAPI will then attempt to invoke the __exit__ method of your dependency generator to clean up the session (which implicitly checks the connection back into the connection pool). However, the dependency generator is not a coroutine, so it’s passed off to the threadpool.

And here is our deadlock. All anyio worker threads are blocked waiting for SQLAlchemy connections. The code to release the connections is blocked waiting for a worker to become available.

But what about native coroutines?

While the root issue with the example code is related to the use of anyio thread pools, we have observed the deadlock when using native coroutines in both path operations and dependency generators. This case is a little more straightforward… (Note, the FastAPI documentation already recommends against this pattern)

@app.get('/')
async def index(db: Session = Depends(get_db)):
    # Some blocking work
    _ = db.execute('select 1')

^ As noted in the earlier section db.execute is a blocking operation, as it implicitly requests a connection from the SQLAlchemy connection pool. However, the connection can’t be retrieved because the connection pool has exhausted. SQLAlchemy blocks waiting for a connection to become available.

Here-in lies the issue. The event loop doesn’t have the power to interrupt this coroutine; there is no await syntax indentifying the coroutine can be interrupted to yield control back to the event loop. The event loop is now fully blocked.

I’d still argue this isn’t really a FastAPI issue. The coroutine is executing a blocking call; whether it’s SQLAlchemy or another third-party library, blocking calls pose this risk.

However, I would argue the FastAPI However, I would argue the FastAPI documentation needs to be updated. The “Dependencies with yield” documentation and the “FastAPI & SQLAlchemy” example can both lead to deadlocks.

Hi everyone,

During my locust test on my FastApi apis, I’m trying a 1000 users test with a 10 users spawn rate and after a moment, I have a QueuePool limit error. First I increase my create_engine configuration (my SQL Database allows 75 different connections) : engine = create_engine( SQLALCHEMY_DATABASE_URL, pool_recycle=3600, pool_size=16, max_overflow=48 ) But the problem persists. After a lot of investigation, I find this topic : https://github.com/tiangolo/full-stack-fastapi-postgresql/issues/104#issuecomment-586466934 (the same as @Blue9 ). And I just remove the Depends as recommanded by the following Session Manager :

@contextmanager
def get_db():
    session = SessionLocal()
     try:
        yield session
    finally:
        session.close()

@app.get("/users/get-mail/{mail}", response_model=schemas.User)
   def check_user_by_email(mail: str):
     with get_db() as db:
          db_user = crud.get_user_by_email(db, email=mail)
          return db_user

I think there is a critical problem with Depends that @tiangolo needs to investigate because in their documentation, they advise to use Depends, but Depends is not ready for a production use ! Thanks to the community for the Session Manager solution that saves my production deployment !

We’re seeing this issue too.

I’m working on going in and figuring out the bug, but @Blue9 seems to be correct in the problem stemming from the operation code being run in a different thread than the dependency seems to be the locus.

Temporary work arounds are to use a context manager, or use an async function with sqlalchemy.ext.asyncio, both of which ensure it all happens in the same thread. But you do not have to do both.

I would highly suggest to the FastAPI maintainers that they add a note in the documentation and on https://github.com/tiangolo/full-stack-fastapi-postgresql that there is a potential issue, as right now it is not production ready for a load greater than 30 or so concurrent users.

@happy-toro Declaring get_session() as async with SQLAlchemy sync mode did not change the behaviour for me.

Also if it helps, I have compiled results from a list of load tests I performed with different implementations for get_db() with SQLAlchemy sync mode here: https://github.com/harsh8398/fastapi-dependency-issue.

Thanks for the report @Blue9 and thanks everyone for the discussion! ☕

This was most probably solved by @adriangb in https://github.com/tiangolo/fastapi/pull/5122, released as part of FastAPI 0.82.0 🎉

If this solves it for you, you could close the issue. 🤓

We have this same issue when making use of a resource pool which acquires a resource in a generator.

The example below illustrates the issue, this can be run using apache bench to simulate some actvity.

ab -n 100 -c 20 http://localhost:8000/test

import itertools
import asyncio
from concurrent.futures import ThreadPoolExecutor
from queue import Queue
from threading import currentThread

from fastapi.applications import FastAPI
from fastapi import Depends, Request


app = FastAPI()
pool: Queue[int] = Queue()


counter = itertools.count()


def get_data():
    trace_id = next(counter)
    ct = currentThread()
    print(f"get trace-id-{trace_id}", ct.name, ct.native_id)

    value = pool.get()
    yield value

    print(f"got trace-id-{trace_id}", ct.name, ct.native_id)
    pool.put_nowait(value)
    print(f"release trace-id-{trace_id}", ct.name, ct.native_id)


@app.get("/test")
def index(
    req: Request,
    data: dict[str, str] = Depends(get_data),
) -> dict:
    return data


@app.on_event("startup")
def on_startup() -> None:
    pool.put_nowait(1)
    pool.put_nowait(2)

    loop = asyncio.get_running_loop()
    executor = ThreadPoolExecutor(1)
    loop.set_default_executor(executor)

From the output in the logs it seems like the same thread is acquiring resources from the pool and creating a deadlock. This seems to be caused by the cm = contextmanager_in_threadpool(contextmanager(call)(**sub_values)) call in solve_generator. If we decorate our dependency function with @contextlib.contextmanager the issue does not occur.

get - resource trace-0 ThreadPoolExecutor-0_0 13479
got - resource trace-0 ThreadPoolExecutor-0_0
release - resource trace-0 ThreadPoolExecutor-0_0 13479
get - resource trace-1 ThreadPoolExecutor-0_0 13479
get - resource trace-2 ThreadPoolExecutor-0_1 13480
got - resource trace-1 ThreadPoolExecutor-0_0
got - resource trace-2 ThreadPoolExecutor-0_1
get - resource trace-3 ThreadPoolExecutor-0_0 13479
get - resource trace-4 ThreadPoolExecutor-0_1 13480

Comment on it saying it fixed your issue (if you tested it and it did)

@happy-toro Declaring get_session() as async with SQLAlchemy sync mode did not change the behaviour for me.

Also if it helps, I have compiled results from a list of load tests I performed with different implementations for get_db() with SQLAlchemy sync mode here: https://github.com/harsh8398/fastapi-dependency-issue.

I’ve also been facing this issue on a production server, this seems like a major problem as FastAPI Dependencies with sync database connections are currently causing deadlocks @tiangolo

I solved this problem by using this method - https://github.com/tiangolo/full-stack-fastapi-postgresql/issues/104#issuecomment-775858005.