SQLAlchemy异步连接PostgreSQL关闭时抛出CancelledError异常问题

基于Python 3.7.10版本搭建FastAPI框架API服务，集成sqlalchemy、asyncio、psycopg2-binary、asyncpg组件对接PostgreSQL数据库，服务通过AWS Elastic Beanstalk部署。应用整体运行正常，但前端每次调用接口时，都会出现数据库连接未正常关闭的问题。该问题仅在部署环境出现，本地开发环境无异常，仅在结合asyncio使用SQLAlchemy异步模式时触发。

Jun  1 21:17:33 web: ERROR:sqlalchemy.pool.impl.AsyncAdaptedQueuePool:Exception closing connection <AdaptedConnection <asyncpg.connection.Connection object at 0x7fd8b005cb90>>
Jun  1 21:17:33 web: Traceback (most recent call last):
Jun  1 21:17:33 web: File "/var/app/venv/staging-LQM1lest/lib64/python3.7/site-packages/sqlalchemy/pool/base.py", line 247, in _close_connection
Jun  1 21:17:33 web: self._dialect.do_close(connection)
Jun  1 21:17:33 web: File "/var/app/venv/staging-LQM1lest/lib64/python3.7/site-packages/sqlalchemy/engine/default.py", line 688, in do_close
Jun  1 21:17:33 web: dbapi_connection.close()
Jun  1 21:17:33 web: File "/var/app/venv/staging-LQM1lest/lib64/python3.7/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 749, in close
Jun  1 21:17:33 web: self.await_(self._connection.close())
Jun  1 21:17:33 web: File "/var/app/venv/staging-LQM1lest/lib64/python3.7/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 68, in await_only
Jun  1 21:17:33 web: return current.driver.switch(awaitable)
Jun  1 21:17:33 web: File "/var/app/venv/staging-LQM1lest/lib64/python3.7/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 121, in greenlet_spawn
Jun  1 21:17:33 web: value = await result
Jun  1 21:17:33 web: File "/var/app/venv/staging-LQM1lest/lib64/python3.7/site-packages/asyncpg/connection.py", line 1334, in close
Jun  1 21:17:33 web: await self._protocol.close(timeout)
Jun  1 21:17:33 web: File "asyncpg/protocol/protocol.pyx", line 581, in close
Jun  1 21:17:33 web: concurrent.futures._base.CancelledError

数据库引擎与会话配置 #

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from app.model.base import CustomBase
from app.core.config import SQLALCHEMY_DATABASE_URI

engine = create_async_engine(SQLALCHEMY_DATABASE_URI)

SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    class_=AsyncSession,
    bind=engine,
    expire_on_commit=False,
)

FastAPI依赖注入获取会话逻辑 #

async def get_db() -> AsyncSession:
    async with SessionLocal() as session:
        yield session

根因 #

1. 异步引擎初始化时未配置连接池存活检查、自动回收参数，AWS Elastic Beanstalk默认的负载均衡/反向代理会主动断开空闲超过60s的TCP连接，SQLAlchemy连接池无法感知该层的连接断开操作，当会话结束尝试归还/关闭连接时，底层asyncpg连接已经被中间层取消，触发CancelledError。本地环境无中间层连接截断逻辑，因此无法复现问题。
2. Python 3.7版本在Amazon Linux环境下默认的asyncio事件循环策略存在兼容问题，会导致异步资源清理逻辑被意外取消。
3. 同时引入同步驱动psycopg2-binary和异步驱动asyncpg，会造成SQLAlchemy异步连接调度的逻辑冲突。
4. 原有数据库依赖仅靠上下文管理器自动释放资源，请求异常中断时没有显式的回滚、关闭兜底，容易出现连接泄漏。

修复步骤 #

• 调整异步引擎连接池参数，添加存活检查与自动回收规则，替换原有引擎创建代码：

engine = create_async_engine(
    SQLALCHEMY_DATABASE_URI,
    pool_pre_ping=True, # 从连接池取连接时先做存活检测，自动丢弃失效连接
    pool_recycle=300, # 连接存活超过5分钟自动回收，周期小于AWS侧空闲连接超时阈值
    pool_size=5,
    max_overflow=10,
    pool_timeout=30,
)

• 重构数据库会话依赖，添加异常回滚、显式关闭的兜底逻辑：

async def get_db() -> AsyncSession:
    session = SessionLocal()
    try:
        yield session
        await session.commit()
    except Exception:
        await session.rollback()
        raise
    finally:
        await session.close()

• 在应用入口文件添加Linux环境下的事件循环策略配置，解决Python 3.7的系统兼容问题：

import asyncio
import sys

if sys.platform == "linux":
    asyncio.set_event_loop_policy(asyncio.DefaultEventLoopPolicy())

• 移除不必要的同步驱动依赖，异步引擎场景下只保留asyncpg即可，卸载psycopg2-binary避免驱动冲突。

部署验证：更新代码后观察服务日志，连接关闭报错会完全消失，数据库侧的连接数会稳定在连接池配置的阈值范围内，不会出现连接数持续增长的泄漏问题。

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