Design DB transaction middleware and identify the background-task pitfall

What's really being asked

This question tests whether you understand that request-scoped database transactions and FastAPI BackgroundTasks have incompatible lifecycles. The interviewer wants to see that you know middleware wraps the request-response cycle, while background tasks execute after the response has already been sent. It also checks if you can spot resource boundary violations and explain why a session closed at response time cannot be reused by deferred work.

The full answer

A good answer hits four things in order. First, describe the middleware pattern: open a session on request, attach it to state or context, commit on success or rollback on exception, then close the session as the response leaves. Second, explain the timing gap: BackgroundTasks are scheduled during the request but only executed after the response returns, so they run after middleware tears down the transaction. Third, name the failure modes: the task tries to use a session whose connection has been returned to the pool, causing closed-connection or detached-instance errors. Fourth, state the fix: background tasks must acquire their own fresh session; pass primitive IDs rather than ORM objects, and let the task open a new session and commit independently.

The mistakes people make

Common wrong answers include saying the background task can simply capture the request session and keep using it. Another red flag is suggesting the middleware should await background tasks before closing the session, because that blocks the response and defeats the purpose. Some candidates propose a global long-lived session per worker, which creates concurrency bugs and leaks. A subtler mistake is arguing the request transaction should rollback if a later background task fails, which confuses separate consistency boundaries.

What usually comes next

Interviewers often ask how to pass data to a background task without holding the session open; pass serializable IDs or DTOs, not ORM instances. They may ask what happens if the background task fails, and the correct line is that the request should not rollback; the task needs its own retry logic. They might ask whether SQLAlchemy async sessions change the pitfall, and the answer is no, because the issue is lifecycle, not the driver.

A concrete example

Imagine an endpoint that creates an order in a request transaction and then schedules a background task to update inventory. The middleware commits and closes the session as soon as the endpoint returns a 201 response. The background task starts moments later and tries to flush an inventory change using the same session object. Because the async connection has already been returned to the pool, the task raises a closed-connection or detached-instance error. The correct design is to pass the order ID to the task, have it open a brand new session, fetch the order again, update inventory, commit, and then send the email. This keeps the request transaction small and gives the background work a valid connection.