Flask异步视图中使用Flask-SQLAlchemy出现PostgreSQL idle in transaction问题

现有遗留Flask项目新增异步视图后，使用Flask-SQLAlchemy发起同步数据库调用时，事务会陷入idle in transaction状态，多次请求后耗尽连接池引发TimeoutError: QueuePool limit错误，导致应用500异常。

复现代码 #

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://127.0.0.1:5432/temp"
app.config["SQLALCHEMY_ECHO"] = True
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)

class SomeModel(db.Model):
    """Simplest possible model."""
    id = db.Column(db.Integer, primary_key=True)

@app.route("/async")
async def hello_async():
    """Sample asynchronous view."""
    SomeModel.query.all()
    return "Hello"

@app.route("/sync")
def hello_sync():
    """Sample synchronous view."""
    SomeModel.query.all()
    return "Hello"

if __name__ == "__main__":
    db.create_all()
    app.run()

现象对比 #

访问/sync端点日志（正常） #

2022-09-26 09:28:21,300 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-26 09:28:21,300 INFO sqlalchemy.engine.Engine SELECT some_model.id AS some_model_id
FROM some_model
2022-09-26 09:28:21,300 INFO sqlalchemy.engine.Engine [cached since 15.05s ago] {}
2022-09-26 09:28:21,303 INFO sqlalchemy.engine.Engine ROLLBACK
127.0.0.1 - - [26/Sep/2022 09:28:21] "GET /sync HTTP/1.1" 200 -

访问/async端点日志（异常） #

2022-09-26 09:28:46,277 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-09-26 09:28:46,277 INFO sqlalchemy.engine.Engine SELECT some_model.id AS some_model_id
FROM some_model
2022-09-26 09:28:46,277 INFO sqlalchemy.engine.Engine [cached since 40.03s ago] {}
127.0.0.1 - - [26/Sep/2022 09:28:46] "GET /async HTTP/1.1" 200 -

异步请求日志缺少ROLLBACK语句，PostgreSQL中事务处于idle in transaction状态。查看源码发现，@app.teardown_appcontext中的self.session.remove()在异步视图结束时虽执行，但未正确结束会话。

问题核心 #

能否在Flask异步视图中使用现有同步Flask-SQLAlchemy会话，并让会话正确关闭？

解决方案 #

方法1：手动控制会话生命周期 #

在异步视图中显式处理事务和会话，确保事务正确结束：

@app.route("/async")
async def hello_async():
    try:
        result = SomeModel.query.all()
        # 若有数据修改操作，需显式调用 commit
        # db.session.commit()
        return "Hello"
    finally:
        # 手动回滚未提交事务并释放会话
        db.session.rollback()
        db.session.remove()

方法2：用上下文管理器封装会话操作 #

创建统一的上下文管理器，简化会话的创建与销毁逻辑：

from contextlib import asynccontextmanager

@asynccontextmanager
async def db_session():
    try:
        yield db.session
        # 按需提交，无修改可省略
        # db.session.commit()
    except Exception:
        db.session.rollback()
        raise
    finally:
        db.session.remove()

@app.route("/async")
async def hello_async():
    async with db_session() as session:
        result = session.query(SomeModel).all()
    return "Hello"

方法3：自定义异步上下文销毁回调 #

替换默认的teardown_appcontext逻辑，确保异步环境下会话被正确清理：

@app.teardown_appcontext
async def teardown_db(error=None):
    db.session.rollback()
    db.session.remove()

关键原理 #

Flask-SQLAlchemy的会话默认绑定到线程本地存储，而异步视图运行在协程中，协程切换时线程本地存储的上下文可能未被正确清理。显式调用rollback()和remove()能强制结束事务并释放连接，避免连接池资源耗尽。

内容的提问来源于stack exchange，提问作者Joshmaker