如何使用Flask-SQLAlchemy避免QueuePool限制错误？

Question

我正在使用Flask-SQLAlchemy和Postgre数据库开发Web应用程序，我的网页中有一个下拉列表，它从数据库中选择数据进行填充。在选择几次不同的值后，我遇到了"sqlalchemy.exc.TimeoutError:"错误。

我的软件包版本如下:

Flask-SQLAlchemy==2.5.1
psycopg2-binary==2.8.6
SQLAlchemy==1.4.15

我的数据库连接参数设置如下：

app.config['SQLALCHEMY_POOL_SIZE'] = 20
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 20
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 5
app.config['SQLALCHEMY_POOL_RECYCLE'] = 10

我收到的错误信息是：

sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 20 reached, connection timed out, timeout 5.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)

将'SQLALCHEMY_MAX_OVERFLOW'的值从20更改为100后，在下拉列表中进行了一些值更改后，我遇到了以下错误。

psycopg2.OperationalError: connection to server at "localhost" (::1), port 5432 failed: FATAL:  sorry, too many clients already

每次从下拉列表中选择一个新值时，都会触发四个查询到数据库，并用这些查询结果填充HTML中的四个相应表格。

在每个查询到数据库之后，我都有一个“db.session.commit（）”语句，但即使我这样做了，经过几次下拉列表的更改后，我仍然会遇到错误。

我知道我应该正确管理连接会话，但我一直在努力解决这个问题。我考虑将池超时设置为5秒，而不是默认的30秒，希望会话能以更快的方式关闭并返回池，但似乎没有帮助。

根据@snakecharmerb的建议，我检查了输出：

select * from pg_stat_activity;

在出现错误之前，我运行了10个不同的值的web应用程序，这意味着所有20 + 20个会话都被使用并处于“事务空闲”状态。

是否有任何想法或建议，我应该更改或查找什么？

- ftani

---

db.session.commit()没有关闭与数据库的连接（因此您的连接可能处于空闲状态）。要关闭会话连接，您应该调用db.session.close()。 - jorzel

@snakecharmerb，我将连接限制设置为20，最大溢出量为20，在检查pg_stat_activity时，我获得了40个“空闲事务”连接，然后就收到了错误提示。我将在上面的描述中更新这些信息。 - ftani

看起来您的会话没有被关闭。Flask-SQLAlchemy 应该会自动进行关闭（技术上是删除） automatically，那么问题就是，为什么在您的代码中没有发生呢？此时，如果提供一个 [mre] 将会非常有用，因为我们只能猜测其中的原因。 - snakecharmerb

@jorzel 我尝试在查询数据库后立即使用 'db.session.close()'，但连接仍处于“空闲事务”状态。我做错了吗？ - ftani

"idle in transaction" 意味着您打开了一个事务但没有提交/回滚。为了回答您的问题，我们需要查看一些应用程序代码（在sqlalchemy中管理事务非常棘手，因为即使您只使用“select”查询数据，事务也可以隐式地创建）。 - jorzel

Answer 1

我在StackOverFlow的另一篇帖子中找到了解决我面临问题的方法。

当你将你的flask应用程序分配给db变量时，除了指定它应该使用哪个Flask应用程序之外，还可以传递会话选项，如下所示：

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(app, session_options={'autocommit': True})

使用“自动提交”解决了我的问题。

现在，如建议所示，我正在使用：

app.config['SQLALCHEMY_POOL_SIZE'] = 1
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 0

现在一切都按照预期工作。

帮助我的原始帖子是：Flask-SQLAlchemy中的自动提交

@snakecharmerb、@jorzel、@J_H -> 感谢你们的帮助！

- ftani

---

我很高兴你发现在autocommit=True时有好的结果，但我鼓励你在编写新应用程序代码时明确地进行提交(commit)。如果你查阅https://docs.sqlalchemy.org/en/14/changelog/migration_14.html，你会看到sqlalchemy作者实际上撤回/重新考虑了一些设计决策，我认为这非常积极。他们避免使用自动化(如自动提交)，并鼓励应用程序员显式地结束每个事务。同样，`with`上下文管理器可以是一个很好的工具来达到这个目的。 - J_H

嗨@J_H，非常感谢您的建议。我在我的Flask应用程序中实现了一个新路由，但它不适用于autocommit=True。当我将其设置为false时，它确实可以工作，但是我之前遇到的问题又回来了，所以我有一个问题或另一个问题...我从未使用过上下文管理器，我在您之前分享的文档链接中看到了一些这种方法的示例，我是否应该将其与我在应用程序中拥有的所有“db.session.add”语句一起使用？如果您能指出关于上下文的文章，我将不胜感激。 - ftani

请展示您正在运行的代码，可以通过github链接展示。您可能想要添加一个“with”上下文管理器，在一个辅助程序中，然后每个执行添加操作的代码块都将调用该中央辅助程序。上下文管理器最大的贡献是确保即使发生意外异常，也会发生提交/回滚。如果您选择不遵循https://stackoverflow.com/help/minimal-reproducible-example指南发布示例代码，则会使帮助变得更加困难。 - J_H

嗨@J_H，非常感谢您教我应该搜索什么（使用上下文管理器），我使用“with db.session.begin(): db.session.add(var)”解决了我的问题。这个项目对我来说很大，所以我不能轻易地提供一个最小可重现示例。抱歉。 - ftani

解决了我的问题。 - Malice

Answer 2

你正在泄露连接。

有点违反直觉的是， 你可能会发现设置一个更低的池限制可以获得更好的结果。 对于简单的单数据库查询，给定的python线程只需要一个池化连接。 将限制设置为1，并使用0溢出， 将使你更早地注意到泄漏的连接。 这样可以更容易地将责任归咎于泄漏连接的源代码。 目前，你有很多代码，而错误被推迟到发出许多查询之后， 这使得很难推断系统行为。 我假设你正在使用sqlalchemy 1.4.29。

为了避免泄漏，请尝试使用以下内容：

from contextlib import closing
from sqlalchemy import create_engine, text
from sqlalchemy.orm import scoped_session, sessionmaker

engine = create_engine(some_url, future=True, pool_size=1, max_overflow=0)
get_session = scoped_session(sessionmaker(bind=engine))
...
with closing(get_session()) as session:
    try:
        sql = """yada yada"""
        rows = session.execute(text(sql)).fetchall()
        session.commit()
        ...
        # Do stuff with result rows.
        ...
    except Exception:
        session.rollback()

- J_H

---

嗨@J_H，感谢您指引我正确的方向。如果不使用Flask-SQLAlchemy并转而使用SQLAlchemy，这将意味着对我的代码进行重大更改，是否有使用Flask-SQLAlchemy包的解决方案？ - ftani

哦，对不起。是的，你说得对。我并没有想引导你进行重大更改。然而，你没有提供任何 "泄漏代码"，只有一些配置，所以我从记忆中 recited 了一个熟悉的模式。帮助我来帮助你。如果你发布一个展示泄漏的新问题，我很乐意帮助你进行批评/修复。确保给我打上标签，这样我就能看到它。https://stackoverflow.com/help/minimal-reproducible-example 顺便说一句，使用 with 上下文处理程序非常重要，可以确保即使在调用函数时出现意外致命错误的情况下，也能释放资源。 - J_H

Answer 3

我正在使用 Flask-RESTful。 所以当我遇到这个错误 -> QueuePool limit of size 20 overflow 20 reached, connection timed out, timeout 5.00 (Background on this error at: https://sqlalche.me/e/14/3o7r)。

在日志中，我发现我的已检出的连接没有关闭。我使用 logger.info(db_session.get_bind().pool.status()) 找到了这个问题。

def custom_decorator(error_message, db_session):
    def api_decorator(func):
        def api_request(self, *args, **kwargs):
            try:
                response = func(self)
                db_session.commit()
                return response
            except Exception as err:
                db_session.rollback()
                logger.error(error_message.format(err))
                return error_response(
                message=f"Internal Server Error",
                status_code=HTTPStatus.INTERNAL_SERVER_ERROR,
                )
            finally:
                db_session.close()
        return api_request

    return api_decorator

因此，我不得不创建这个装饰器，它可以自动处理db_session的关闭。使用这个装饰器，我不会得到任何活动的已检出连接。

您可以在函数中使用以下装饰器：

@custom_decorator("blah", db_session)
def example():
    "some code"

- Rohit Kumar