PostgreSQL之设置SQL语句执行超时时间

pg中有个参数叫statement_timeout，它表示中止任何使用了超过指定时间的语句。如果指定值没有单位，则以毫秒为单位。0表示将禁用超时，即表示执行时间没有限制。

参数释义见：https://postgresqlco.nf/doc/zh/param/statement_timeout/

超时从命令到达服务器的时间开始计算，直到服务器完成该命令为止。如果在单个simple-query消息中出现多个SQL语句，超时将分别应用于每个语句。(注：13之前的PostgreSQL版本通常将超时处理应用于整个查询字符串。)

一般不推荐在postgresql.conf中设置statement_timeout，因为在postgresql.conf中直接进行修改是实例级别的，会影响所有会话。

我们一般选择在具体的事务级别或者会话级别进行设置。

会话级别：只需要在每个会话开始时设置即可，语句为：

set  statement_time='1000ms';
复制

比如下面的用法：

SET statement_timeout = 10000;
SET
test=# SELECT pg_sleep(15);
ERROR:  canceling statement due to statement timeout
复制

事务级别：示例：

begin;  
set local statement_time='1000ms';  
select count(*) from mytable;   
end;
复制

库级别：示例如下，下面的语句将确保默认情况下连接到该数据库的任何应用程序或人员的查询运行时间不会超过60秒。

ALTER DATABASE mydatabase SET statement_timeout = ‘60s’;
复制

限制单条语句的执行时间在其他数据库引擎也有类似的实现。

MongoDB

MongoDB中有一个叫maxTimeMS的参数。它可以允许我们为每个查询指定最大运行时间。

文档见：

https://docs.mongodb.com/manual/reference/method/cursor.maxTimeMS/

MongoDB中关于MaxTimeMS行为的一些注释如下:

• MaxTimeMS为游标上的处理操作设置累积时间限制(以毫秒为单位)。

• 游标的网络延迟和空闲时间不计入MaxTimeMS。

• 到达MaxTimeMS之后，该操作将在下一个安全的中断点被终止(例如，当该操作产生时)。这意味着如果某个操作当前不可中断，则该操作可能超过MaxTimeMS。

比如下面的用法：

> db.mycol.find({$where: "sleep(1000) || true"}).maxTimeMS(50)
Error: error: {
"ok" : 0,
"errmsg" : "operation exceeded time limit",
"code" : 50,
"codeName" : "MaxTimeMSExpired"
}
复制

{$where: "sleep(1000) || true"}，表示对每个找到的文档休眠1秒后再继续，而maxTimeMS(50)表示查询允许的最大执行时间是50ms，所以该语句就会抛出MaxTimeMSExpired异常。

MySQL

mysql中有一个max_execution_time参数

(注：5.7及以后的命名，5.6版本叫max_statement_time)，用来控制select语句的最大执行时间，单位是毫秒，可以动态修改，分为session和global级别。

用法：

mysql> set net_read_timeout=2;
Query OK, 0 rows affected (0.00 sec)


mysql> select count(*) from t where t.name='test'
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
复制

或者：

select /*+ max_execution_time(3000)*/ count(*) from t where t.name='test'
复制

在pg中还有一个与这个statement_timeout很类似的参数叫

idle_in_transaction_session_timeout，它是从PostgreSQL 9.6版本开始引入的参数，支持自动查杀超过指定时间的 idle in transaction 空闲事务连接，用于清理应用代码中忘记关闭已开启的事务。

我前面一篇文中提到我们需要注意应用中未正常关闭的连接，这个参数可以在一定程度上避免这种情况。