设置 transaction_timeout 以防止出现长事务

由 John Doe 十二月 19, 2024

摘要：在本文中，我们将学习如何在 PostgreSQL 中设置 transaction_timeout，来防止出现长时间运行的事务。

介绍

PostgreSQL 在会话和语句方面已经支持了各种超时参数。有 idle_in_transaction_session_timeout、idle_session_timeout 和 statement_timeout。默认情况下，所有这些参数都处于禁用状态，但可以启用它们，以防止出现长时间运行的会话或语句。从 PostgreSQL 17 开始，将会有另一个与超时相关的参数：transaction_timeout。顾名思义，这个参数应用在事务级别。

示例

要了解其工作原理，可以进行一个简单的测试：

set transaction_timeout = '5s';

begin;

select pg_sleep(6);
FATAL:  terminating connection due to transaction timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

如果也设置了 idle_in_transaction_session_timeout 和 statement_timeout，但 transaction_timeout 设置为一个较短的时间，则 transaction_timeout 将会发挥作用：

set idle_in_transaction_session_timeout = '10s';

set statement_timeout = '10s';

set transaction_timeout = '5s';

begin;

select pg_sleep(6);
FATAL:  terminating connection due to transaction timeout
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.