为什么 SQLAlchemy 会发出如此多的 ROLLBACK

背景

项目采用flask+flask-sqlalchemy进行数据库查询，数据库查询经常报错连接池超出错误

参数

Flask-SQLAlchemy：3.0.2

SQLAlchemy：1.4.39

pymysql：1.0.2

启动配置项

db = SQLAlchemy(
    app,
    engine_options={
        "pool_recycle": 7200,  # 数据库连接回收时间
        "pool_size": 10,  # 连接池大小
        "pool_pre_ping": True,  # 启用连接池预检
        "pool_timeout": 25,  # 连接池超时时间
        # "pool_use_lifo": True,  # 连接池使用后进先出
        # "echo_pool": True,  # 打印连接池
        "max_overflow": 20,  # 连接池溢出大小
    },
)

措施

开启MySQL的General_log排查日志

通过打印的日志发现每个flask-sqlalchemy的查询都会在日志中出现最少两次Rollback语句

...
2024-03-27T09:41:30.777164Z      8182 Query     ROLLBACK
2024-03-27T09:41:30.777313Z      8182 Query     ROLLBACK

查阅sqlalchemy的官方文档发现有以下解释，为什么 SQLAlchemy 会发出如此多的 ROLLBACK？

SQLAlchemy 在处理数据库连接时，会默认假设 DBAPI 连接处于“非自动提交”模式。这是因为 Python 数据库 API 的默认行为就是这样，意味着必须假设一个事务始终在进行中。当一个连接被返回到连接池时，连接池会发出 connection.rollback() 操作，以确保释放连接上剩余的任何事务资源。 在一个像 PostgreSQL 或 MSSQL 这样积极锁定表资源的数据库上，这是至关重要的，以确保不再使用的连接不会保持锁定行和表，从而避免应用程序挂起。这不仅仅是锁定的问题，对于任何具有某种事务隔离级别的数据库，包括使用 InnoDB 的 MySQL，这一点同样重要。任何仍然处于旧事务中的连接都会返回陈旧数据，如果在该连接上已经查询了该数据。 为了解决这个问题，SQLAlchemy 在连接返回时执行回滚操作，以确保不会在连接池中的连接上留下未完成的事务。这样做有助于防止数据不一致性和资源锁定，确保应用程序的稳定性和可靠性。 此外，对于使用 MyISAM 存储引擎的 MySQL 数据库，可以通过设置 reset_on_return=False 来更改连接池的行为，从而避免在连接返回时执行回滚操作。对于 SQL Server，可以通过将 reset_on_return 设置为 "commit" 来将回滚操作转换为提交操作。 需要注意的是，SQLAlchemy 并不支持在事务进行中透明地重新连接数据库连接。如果数据库连接在事务进行中断开，通常的做法是重新尝试整个操作，而不是尝试透明地重新连接并继续执行。这是因为一旦连接丢失，整个事务都会丢失，数据库无法简单地重新连接并从中断的地方继续执行。 总的来说，SQLAlchemy 发出许多回滚操作是为了确保数据库连接在返回到连接池时不会留下未完成的事务，从而维护数据库的一致性和应用程序的稳定性。

查看MySQL的"autocommit"参数，默认为开启状态

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

翻阅sqlalchemy中提供的mysql官方解释，原文

InnoDB 是 MySQL 默认的事务型存储引擎，它支持事务处理，包括提交和回滚操作。自动提交是 MySQL 服务器的一个设置，当启用时，每个 SQL 语句都会自动提交，也就是说，执行 SQL 语句后，更改会立即生效，并且不会保留在事务中。 在 MySQL 5.7 版本中，自动提交默认是启用的。但是，可以通过设置 autocommit 变量来更改此行为。如果将 autocommit 设置为 OFF，则需要显式使用 COMMIT 或 ROLLBACK 语句来提交或回滚事务。

SQLAlchemy 中的 reset_on_return 设置与 MySQL 的自动提交行为相关。当 SQLAlchemy 连接返回到连接池时，如果 reset_on_return 设置为 False，则不会执行回滚操作。这在某些情况下可能有助于减少不必要的回滚操作，特别是在使用具有自动提交功能的数据库时。 对于 MySQL 5.7 版本，如果 autocommit 被禁用（即设置为 OFF），那么在事务结束时显式提交或回滚操作是必要的。在这种情况下，SQLAlchemy 的 reset_on_return=False 设置可能不适用，因为它期望在连接返回时执行回滚操作以清理事务。如果 autocommit 被启用（即设置为 ON），则每个 SQL 语句都会自动提交，SQLAlchemy 的 reset_on_return 设置将不会影响 MySQL 的自动提交行为。

因此，在使用 MySQL 5.7 时，应根据应用程序的需求和数据库配置来决定是否使用 SQLAlchemy 的 reset_on_return 设置。如果您的 MySQL 配置启用了自动提交，那么在某些情况下，您可能不需要在 SQLAlchemy 中进行额外的回滚操作。如果您的配置禁用了自动提交，那么 SQLAlchemy 的 reset_on_return 设置可以帮助确保事务正确结束。

所以两者大概率是有冲突的，我暂时先在flask-sqlalchemy中增加原文中的配置项，关闭flask-sqlalchemy的rollback，继续观察

配置项修改如下

db = SQLAlchemy(
    app,
    engine_options={
        "pool_recycle": 7200,  # 数据库连接回收时间
        "pool_size": 10,  # 连接池大小
        "pool_pre_ping": True,  # 启用连接池预检
        "pool_timeout": 25,  # 连接池超时时间
        "pool_reset_on_return": False,
        # "pool_use_lifo": True,  # 连接池使用后进先出
        # "echo_pool": True,  # 打印连接池
        "max_overflow": 20,  # 连接池溢出大小
    },
)