SQLAlchemy与数据库连接的QueuePool问题详解

青旅半醒 2022-05-21 13:27 446阅读 0赞

# 从常见的一种连接错误说起 #

有关SQLAlchemy与数据库的连接（Connection），最常见的一种*runtime error*如下所示：

QueuePool limit of size <x> overflow <y> reached, connection timed out, timeout <z>

这个异常的含义是当前系统所需并发数据库连接（对应的`sqlalchemy.engine.Connection()`或`sqlalchemy.orm.session.Session()`，下文中将这二者统称为连接）超过了当前使用的`engine`所配置的并发连接数目上限（该上限由两个值组成：`pool_size`和`max_overflow`，这点我们将在下面讨论）。

下面我们总结修复程序中出现的上述错误时需要注意的几点重要情况。

## SQLAlchemy连接数据库所使用的`Engine`对象默认采用一个连接池（a pool of connections）来管理连接 ##

当我们使用`Engine`对象所对应的SQL数据库连接的资源时，这些对数据库的连接是通过一个连接池（[Connection pooling][]）来管理的。当我们释放（[release][]）一个连接资源时，这个连接并不是被销毁了，而是仍然连接着数据库，只不过其将会被重新存储如一个用于管理连接的连接池（默认为`QueuePool`）中。放入连接池中的连接可以被复用。事实上总有一定数目的数据库连接被保存在这个连接池中，即使在我们的代码中看起来像是连接被释放了一样。这些连接会在我们的程序结束运行之后自动被销毁，或者当我们显式地调用销毁连接池的代码时被销毁。

## 连接复用 ##

由于这个连接池的存在，每当我们在代码中调用`Engine.connect()`方法或者调用ORM对应的`Session`的时候，往往会得到一个已存在与连接池中的数据库连接，而不是得到了一个全新的连接对象。然而当连接池中没有现成可用的连接对象的时候，在不超过配置所允许的连接上限的条件下，新的连接对象会被创建并返回给调用这些方法的程序。

## 默认使用的[*QueuePool*][QueuePool] ##

SQLAlchemy默认所使用的连接池为`sqlalchemy.pool.QueuePool`。当**目前总连接数没有超过配置的上限且池中没有现成可用的连接的情况**下，一个新的连接会被建立并返回给调用创建新连接的方法的程序。这个上限等于[`create_engine.pool_size`][create_engine.pool_size] 与[`create_engine.max_overflow`][create_engine.pool_size]之和。配置`engine`的代码如下所示：

engine = create_engine("mysql://user_name:password@host/db", pool_size=x, max_overflow=y, pool_timeout=z)

上文定义的`engine`同时允许最多`x+y`个并发且活跃的连接。如果在并发活跃的连接已经有`x+y`个时，新的对于连接的请求将会被阻塞（block），直到有一个连接对象可用为止。阻塞（block）的时间由[`create_engine.pool_timeout`][create_engine.pool_timeout]指定，即`z`秒（默认情况下为30秒）。其中`create_engine.pool_size`参数指定的是连接池中最多缓存的连接数目，而`create_engine.max_overflow`指定的是除连接池中已经缓存的连接对象之外，还允许连接池“上溢（overflow）”多少个连接对象来响应数据库操作的请求。

除`sqlalchemy.pool.QueuePool`外，我们还可用[Connection Pooling][Connection pooling]中提到的其他实现作为传入`create_engine.pool`的参数。例如使用`sqlalchemy.pool.NullPool`可以完全禁用连接池。对这一配置的讨论超出了本文的范围，故在此不做进一步的讨论。

## 可上溢的连接池 ##

如果我们将参数`create_engine.max_overflow`设置为”-1”，那么连接池会允许“上溢”无限多的新连接。在这种情况下，连接池永远不会阻塞一个新的数据库连接请求。相反，每当有新的连接请求且无当前可用的连接对象，连接池就会无条件地创建新的连接对象来返回给这个请求。

然而，即使我们在程序端不限制并发的数据库连接的数目，如果程序无限制的创建新的数据库连接对象，连接的数目最终会到达数据库端的连接数目上限，并且耗尽所有数据库允许的连接，最终同样会造成程序异常。更为需要注意的是，在这种情况下 ，在程序耗尽数据库连接资源之前往往还会耗尽许多其他的资源，并且还很可能会影响运行在同一台服务器上的其他依赖于数据库访问的程序或数据库本身，造成其他程序异常或崩溃。

基于以上讨论，我们可以知道连接池对于并发数据库连接的数目限制可以被看做是一道保证数据库正常运作的安全阀。在限制了连接数目的情况下，即使程序本身出现了错误导致不断请求新建数据库连接，连接池的限制仍然可以保证数据库及其他依赖数据库的程序的安全运行。所以如果我们收到上述的错误信息，最好的解决办法是根据当前程序的需求重新定义连接池允许的数目上限，或者优化程序以减少并发数据库连接的使用，而不是将上限设置为无限多。

## 导致可用连接被用尽的可能原因 ##

### 连接池的上限小于程序中需要并发使用连接的请求的数目 ###

这是导致连接被用尽问题最直接的一种原因。如果我们的程序使用一个大小为20的线程池来进行并发处理且每个线程都需要一个单独的数据库连接，而我们定义的连接池大小只有10，那么显然将会出现连接被用尽的问题。这种情况下，就应该通过增加连接池大小或减少并发线程数目的方法来解决问题。一般来说，我们应当保证连接池的大小不小于线程池的数目。

### 连接没有被释放 ###

另一个常见的导致连接用尽的原因是连接在被使用之后没有被释放，或说没有被归还给连接池。虽然当连接对象由于没有引用而被垃圾收集之后其对应的连接资源仍将被释放还给连接池，但由于垃圾收集的不确定性，这一机制不应当被用来作为释放连接资源的手段。

连接没有被释放一般是因为程序中没有显式地调用相应方法导致的。所以当我们使用完连接对象之后，应当显式地调用连接的释放方法。例如如果我们在使用ORM `Session`，则应当在合适的地方调用[`Session.close()`][Session.close]方法释放`Session`对象。或者当我们在使用Core的情况下在合适的位置调用`.close()`方法释放`Connection`对象。或者我们也可以使用所谓的*context manager*帮助我们在使用完毕后自动调用`.close()`方法释放资源（例如将代码写在“with”代码块中）。

### 程序试图执行一个运行时间很长的数据库事务（transaction） ###

数据库的事务是一种非常昂贵的操作，因此不应该用来闲置着等待某些事件发生。例如等待用户点击某个按钮，或者等待一个长时间运行的任务返回结果。对于事务，切记不要一直维持着一个事务而不去结束。如果程序需要进行数据库交互并且和一个事件互动，我们应该当且仅当需要的时候打开一个持续事件很短的事务，并在使用结束后就立即关闭。

当我们编程的时候，切记，如果文章开头的错误信息被抛出，这往往是由于程序本身的逻辑问题导致的，而连接池仅仅是在帮助我们暴露这些问题。

[Connection pooling]: http://docs.sqlalchemy.org/en/latest/core/pooling.html
[release]: http://docs.sqlalchemy.org/en/latest/glossary.html#term-releases
[QueuePool]: http://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.QueuePool
[create_engine.pool_size]: http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine.params.max_overflow
[create_engine.pool_timeout]: http://docs.sqlalchemy.org/en/latest/core/engines.html#sqlalchemy.create_engine.params.pool_timeout
[Session.close]: http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.close