【PostgreSQL17新特性之-事务级别超时参数transaction_timeout】

PostgreSQL数据库里有多个和会话相关的参数，PostgreSQL17-beta1版本新增了一个transaction_timeout参数，来限制事务的持续时间。

当前的一些和会话相关的超时参数如下

+-------------------------------------+---------+------+-------------------------------------------------+---------+---------+---------+------------+----------+
|                name                 | setting | unit |                    category                     | context | vartype | min_val |  max_val   | enumvals |
+-------------------------------------+---------+------+-------------------------------------------------+---------+---------+---------+------------+----------+
| idle_in_transaction_session_timeout | 0       | ms   | Client Connection Defaults / Statement Behavior | user    | integer | 0       | 2147483647 |          |
| idle_session_timeout                | 0       | ms   | Client Connection Defaults / Statement Behavior | user    | integer | 0       | 2147483647 |          |
| lock_timeout                        | 0       | ms   | Client Connection Defaults / Statement Behavior | user    | integer | 0       | 2147483647 |          |
| statement_timeout                   | 0       | ms   | Client Connection Defaults / Statement Behavior | user    | integer | 0       | 2147483647 |          |
| transaction_timeout                 | 0       | ms   | Client Connection Defaults / Statement Behavior | user    | integer | 0       | 2147483647 |          |
+-------------------------------------+---------+------+-------------------------------------------------+---------+---------+---------+------------+----------+
(5 rows)

参数名	作用
idle_in_transaction_session_timeout	IDLE IN TRANSACTION状态会话超时时间
idle_session_timeout	IDLE 状态会话超时时间
lock_timeout	锁等待超时时间
statement_timeout	一条SQL语句执行的超时时间
transaction_timeout	一个事务执行的超时时间

一、关于transaction_timeout参数

Expanded display is on.
postgres<17beta1>(ConnAs[postgres]:PID[21590] 2024-05-28/17:08:02)=# select * from pg_settings where name like 'transaction_timeout';
+-[ RECORD 1 ]----+-----------------------------------------------------------------------------------------------------+
| name            | transaction_timeout                                                                                 |
| setting         | 0                                                                                                   |
| unit            | ms                                                                                                  |
| category        | Client Connection Defaults / Statement Behavior                                                     |
| short_desc      | Sets the maximum allowed duration of any transaction within a session (not a prepared transaction). |
| extra_desc      | A value of 0 turns off the timeout.                                                                 |
| context         | user                                                                                                |
| vartype         | integer                                                                                             |
| source          | default                                                                                             |
| min_val         | 0                                                                                                   |
| max_val         | 2147483647                                                                                          |
| enumvals        |                                                                                                     |
| boot_val        | 0                                                                                                   |
| reset_val       | 0                                                                                                   |
| sourcefile      |                                                                                                     |
| sourceline      |                                                                                                     |
| pending_restart | f                                                                                                   |
+-----------------+-----------------------------------------------------------------------------------------------------+

1.相关注意

如果transaction_timeout值为0，则认为超时控制无效。如果transaction_timeout小于或等于idle_in_transaction_session_timeout或statement_timeout，则忽略较长的超时。

2.使用场景

可以根据实际场景合理配置，一定程度避免长事务。
比如一个事务内发出许多低延迟的查询，但是一个事务内执行的SQL数量很大的时候，整个事务需要很长时间处理。可以通过该参数让这个长的事务回滚。

二、transaction_timeout参数效果测试

首先编辑一个SQL文件，编辑test_transaction_timeout.sql
内容如下：

begin;
\timing
set statement_timeout = 1500;
--set transaction_timeout=3000;
select  'ysla tests-1';
select pg_sleep(1);
select  'ysla tests-2';
select pg_sleep(1);
select  'ysla tests-3';
select pg_sleep(1);
select  'ysla tests-4';
commit;

在一个事务里重复执行打印字符以及pg_sleep(1)函数,这两部分分别当作低延迟的查询。然后在事务的开头设置statement_timeout = 1500。transaction_timeout参数暂时先注释掉。事务中的任何 SQL 语句都不会花费超过 1500 毫秒的时间，因此事务将运行完成。

下面跑一下结果，可以看到未触发statement_timeout超时，事务正常结束了。

[postgres@xmaster-PostgreSQL-17beta1-06 ~]$ psql -f test_transaction_timeout.sql
Border style is 2.
Line style is ascii.
BEGIN
Timing is on.
SET
Time: 0.168 ms
+--------------+
|   ?column?   |
+--------------+
| ysla tests-1 |
+--------------+
(1 row)

Time: 0.298 ms
+----------+
| pg_sleep |
+----------+
|          |
+----------+
(1 row)

Time: 1013.626 ms (00:01.014)
+--------------+
|   ?column?   |
+--------------+
| ysla tests-2 |
+--------------+
(1 row)

Time: 0.334 ms
+----------+
| pg_sleep |
+----------+
|          |
+----------+
(1 row)

Time: 1014.618 ms (00:01.015)
+--------------+
|   ?column?   |
+--------------+
| ysla tests-3 |
+--------------+
(1 row)

Time: 0.741 ms
+----------+
| pg_sleep |
+----------+
|          |
+----------+
(1 row)

Time: 1014.584 ms (00:01.015)
+--------------+
|   ?column?   |
+--------------+
| ysla tests-4 |
+--------------+
(1 row)

Time: 0.343 ms
COMMIT
Time: 0.525 ms

然后把SQL文件里的transaction_timeout参数的注释打开，此时，statement_timeout=1500，事务内的任何 SQL 语句都不会超过 1500ms，但第三次pg_sleep(1)执行的时候将超过transaction_timeout设置的3s阈值，导致超时错误，会话将被终止，事务也被终止。可以看到打印的信息里也显示了： FATAL: terminating connection due to transaction timeout
表示是由于触发了transaction_timeout参数的阈值而引发的连接断开。

[postgres@xmaster-PostgreSQL-17beta1-06 ~]$ psql -f test_transaction_timeout.sql
Border style is 2.
Line style is ascii.
BEGIN
Timing is on.
SET
Time: 0.185 ms
SET
Time: 0.090 ms
+--------------+
|   ?column?   |
+--------------+
| ysla tests-1 |
+--------------+
(1 row)

Time: 0.520 ms
+----------+
| pg_sleep |
+----------+
|          |
+----------+
(1 row)

Time: 1000.747 ms (00:01.001)
+--------------+
|   ?column?   |
+--------------+
| ysla tests-2 |
+--------------+
(1 row)

Time: 0.365 ms
+----------+
| pg_sleep |
+----------+
|          |
+----------+
(1 row)

Time: 1004.818 ms (00:01.005)
+--------------+
|   ?column?   |
+--------------+
| ysla tests-3 |
+--------------+
(1 row)

Time: 0.440 ms
psql:test_transaction_timeout.sql:10: FATAL:  terminating connection due to transaction timeout
psql:test_transaction_timeout.sql:10: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:test_transaction_timeout.sql:10: error: connection to server was lost

所以，PostgreSQL17-beta1新增的这个务级别超时参数transaction_timeout，在结合我们实际场景合理配置的情况下，可以一定程度避免长事务，避免数据库里存在：单个SQL执行时间短，但是单个事务里包含的SQL较多导致的长事务。需要额外提一嘴的是，如果实际业务就是做的一个事务里包含大量的低延迟SQL，那最好是和业务沟通是否可以进行优化拆分，毕竟参数只是限制时长，达到阈值让他回滚，避免数据库里的事务长时间存在。但是不能从源头上阻止此类长事务的产生。