使用SQLAlchemy引擎与连接池连接SQL Server时，空闲会话中出现未关闭事务的原因及相关问题咨询

我有一个用Python和Flask构建的Web应用，目前正尝试用SQLAlchemy来管理SQL Server的连接池。自从上线这个配置后，我们遇到过一次SQL Server内存占满的情况，导致公司内多个应用和数据刷新任务崩溃——现在我们非常怀疑是连接池的设置引发了这次过载。

我发现，当用SQLAlchemy Engine执行完事务后，SQL Server上会留下一个持久化的会话（这应该是连接池的预期行为），但这个会话还带着一个持续打开的事务，这就不是我预期的了。

我是照着官方文档里的事务管理最佳实践来写的代码，而且从pool.echo的日志里也能看到，在engine.begin()代码块结束时事务已经提交了。

import sqlalchemy as sa

conn_string = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=<serverAddress>,<port>;DATABASE=<myDB>;UID=<myUID>;PWD=<myPass>;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=10"

engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={conn_string}"
                          , echo_pool=True
                          , echo=True)

with engine.begin() as conn:
    result = conn.execute(sa.text("select top 1 * from SomeTable"))
    print(result.all())

运行这段代码后，日志输出如下：

2025-01-15 09:47:49,391 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2025-01-15 09:47:49,393 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-01-15 09:47:49,428 INFO sqlalchemy.engine.Engine SELECT schema_name()
2025-01-15 09:47:49,429 INFO sqlalchemy.engine.Engine [generated in 0.00088s] ()
2025-01-15 09:47:49,531 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2025-01-15 09:47:49,532 INFO sqlalchemy.engine.Engine [generated in 0.00140s] ()
2025-01-15 09:47:49,563 INFO sqlalchemy.engine.Engine SELECT 1 FROM fn_listextendedproperty(default, default, default, default, default, default, default)
2025-01-15 09:47:49,565 INFO sqlalchemy.engine.Engine [generated in 0.00120s] ()
2025-01-15 09:47:49,624 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-15 09:47:49,626 INFO sqlalchemy.engine.Engine select top 1 * from SomeTable
2025-01-15 09:47:49,627 INFO sqlalchemy.engine.Engine [generated in 0.00135s] ()
[(1,)]
2025-01-15 09:47:49,660 INFO sqlalchemy.engine.Engine COMMIT

从日志最后一行的COMMIT能看到事务已经提交了（这个例子是只读查询，但写事务也是同样的日志）。但当我用下面的SQL查询SQL Server的活跃会话时，能看到处于sleeping状态的会话仍然带着1个未关闭的事务：

SELECT s.session_id, s.status, s.transaction_isolation_level, s.open_transaction_count,
       st.transaction_id, s.row_count, s.login_time, s.last_request_start_time, 
       s.last_request_end_time, s.client_version, s.client_interface_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions st ON st.session_id = s.session_id
WHERE login_name='myUID' AND host_name='myHost'
       AND EXISTS (
               SELECT *
               FROM sys.dm_tran_session_transactions AS t
               WHERE t.session_id = s.session_id
           )
           AND NOT EXISTS (
               SELECT *
               FROM sys.dm_exec_requests AS r
               WHERE r.session_id = s.session_id
           )

查询结果显示：该会话状态为sleeping，open_transaction_count值为1，且没有正在执行的请求。

我的同事认为这是孤儿事务，是导致服务器变慢甚至过载的根源。但我不确定——我觉得这可能只是SQLAlchemy连接池的正常实现方式，之前的内存过载可能是因为我们有很多部署环境，每个环境都有自己的连接池，最后累积了太多这样的空闲连接。

我的三个疑问： #

1. 有没有人能解释下这到底是怎么回事？
2. 这种看似是孤儿事务的情况是正常的、可以预期的吗？
3. 是不是我的配置或代码使用方式有问题，才导致了这些未关闭的事务？

我的技术栈： #

Flask==2.2.5
pyodbc==5.2.0
sqlalchemy==2.0.37
python==3.11.7

我做的实验和发现： #

1. 重复执行查询后的现象

当我再次用engine执行另一个查询时，SQL Server里的会话会生成新的transaction_id，但open_transaction_count仍然保持1。这让我更倾向于认为这是SQLAlchemy QueuePool的正常行为。
测试代码：

with engine.begin() as conn:
    result = conn.execute(sa.text("select top 1 * from SchemaChangeHistory"))
    print(result.all())

日志输出：

2025-01-15 10:20:13,821 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-15 10:20:13,822 INFO sqlalchemy.engine.Engine select top 1 * from SchemaChangeHistory
2025-01-15 10:20:13,823 INFO sqlalchemy.engine.Engine [cached since 1944s ago] ()
[(1,)]
2025-01-15 10:20:13,905 INFO sqlalchemy.engine.Engine COMMIT

SQL查询结果：同一个sleeping会话，open_transaction_count还是1，但transaction_id更新为新值。

2. 调用engine.dispose()的效果

如果在执行完查询后调用engine.dispose()，这个带未关闭事务的空闲会话会被清除。但这样做的话，连接池的意义不就没了吗？
日志输出：

2025-01-15 10:24:49,344 INFO sqlalchemy.pool.impl.QueuePool Pool disposed. Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
2025-01-15 10:24:49,345 INFO sqlalchemy.pool.impl.QueuePool Pool recreating

此时再查询SQL Server的会话，已经找不到对应的记录了。

3. 使用NullPool的效果

如果改用NullPool作为连接池，就不会出现这种带着未关闭事务的空闲会话。我的备选方案就是直接用这个，但我还是希望能把连接池配置好，享受它带来的性能优势。
测试代码：

import sqlalchemy as sa

engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={os.environ.get('SQLCONNSTR_pe_DW')}"
                          , echo_pool=True
                          , echo=True
                          , poolclass=sa.pool.NullPool)

with engine.begin() as conn:
    result = conn.execute(sa.text("select top 1 * from SchemaChangeHistory"))
    print(result.all())

日志输出：

2025-01-15 10:32:35,261 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2025-01-15 10:32:35,262 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-01-15 10:32:35,296 INFO sqlalchemy.engine.Engine SELECT schema_name()
2025-01-15 10:32:35,298 INFO sqlalchemy.engine.Engine [generated in 0.00267s] ()
2025-01-15 10:32:35,398 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2025-01-15 10:32:35,400 INFO sqlalchemy.engine.Engine [generated in 0.00206s] ()
2025-01-15 10:32:35,432 INFO sqlalchemy.engine.Engine SELECT 1 FROM fn_listextendedproperty(default, default, default, default, default, default, default)
2025-01-15 10:32:35,434 INFO sqlalchemy.engine.Engine [generated in 0.00239s] ()
2025-01-15 10:32:35,500 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-15 10:32:35,502 INFO sqlalchemy.engine.Engine select top 1 * from SomeTable
2025-01-15 10:32:35,503 INFO sqlalchemy.engine.Engine [generated in 0.00152s] ()
[(1,)]
2025-01-15 10:32:35,748 INFO sqlalchemy.engine.Engine COMMIT

此时查询SQL Server的会话，结果为空，没有遗留的空闲会话和未关闭事务。

我考虑的下一步方案： #

要么：

1. 直接用NullPool，彻底避开这个问题（哈哈）；
   要么：
2. 合理配置pool_recycle和pool_use_lifo参数，调整连接池的连接生命周期，确保空闲连接不会在不需要的时候一直留存。

备注：内容来源于stack exchange，提问作者coruble