自学内容网 自学内容网

【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_timeoutIDLE IN TRANSACTION状态会话超时时间
idle_session_timeoutIDLE 状态会话超时时间
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                                                                                                   |
+-----------------+-----------------------------------------------------------------------------------------------------+

image.png

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;

image.png


在一个事务里重复执行打印字符以及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

image.png

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


原文地址:https://blog.csdn.net/weixin_47308871/article/details/139302795

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!