Postgres中如何防止长时间运行的事务？


如何彻底杜绝长时间运行事务的发生？简短的回答：仅使用 Postgres 设置是不能的。

从 PG16 / 2023 开始，Postgres 没有提供限制事务持续时间的方法

有两个限制设置可以帮助减少长时间运行的事务发生的机会，但不能完全消除风险：

  1. statements_timeout – 限制单个查询的最大持续时间。对于网络/移动应用程序，将其设置为较低的值，例如 30 秒或 15 秒。您可以在 Postgres 文档中找到“不推荐”，但该建议不切实际，我认为它没有成效。我们确实需要对 Web 和移动应用程序全局限制 statements_timeout，以得到保护：无论如何，应用程序代码通常都会受到限制，当应用程序达到超时（例如 30 秒）时，这不是一个好情况，但 Postgres 仍在处理孤立查询。用户通常不会等待超过几秒钟。
  2. idle_in_transaction_session_timeout – 设置事务中查询之间允许的最大空闲时间。这里类似的建议：将其设置为较低的值，15-30s。绝对需要它的会话可以覆盖全局值。

如果这两个选项都设置为较低的值，无法完全防止长时间运行的事务发生：
例如，如果我们将这两个时间段都设置为 30 秒，那么交易事务可能仍会运行数小时：

开始；
查询持续 < 30 秒
短暂延迟（< 30 秒）
另一个查询持续 < 30 秒
...

在这种情况下，两个阈值都没有达到，但我们可以进行几个小时甚至几天的事务处理。

为什么长时间运行的事务可能会成为问题
在 OLTP 环境（如移动和网络应用程序）中，长时间运行的事务通常会造成危害，原因有两个：

  • 阻塞问题。锁一旦获得，只能在事务结束时释放。这会阻塞其他事务。有时，即使是 "最弱 "的锁，如果被锁定时间过长，也会造成很大问题；即使是从表中读取数据的简单打开事务，也会造成很大麻烦。
  • 对autovacuum 活动的负面影响。如果我们有一个带有某个事务 ID 的未结事务，例如，所有带有的死tuple（换句话说，就是在我们的事务开始后变成死的tuple--那些由带有的事务产生的tuple）在我们的事务结束前都无法被 autovacuum 删除。这可能会导致臃肿和性能下降。

如何分析长时间运行的事务
获取所有长时间运行的事务的列表很简单：

select clock_timestamp() - xact_start, *
from pg_stat_activity
where clock_timestamp() - xact_start > interval '1 minute'
order by clock_timestamp() - xact_start desc;