postgresql "空闲事务"，所有锁定均已授权

Question

一个非常简单的针对小表（700行）按键进行删除的操作，虽然所有锁定标记均为“已授权”，但有时会在空闲事务中停留数分钟（通常只需几毫秒）。

我该怎么办才能找出是什么原因导致了这种情况？ 我正在使用以下选择：

  SELECT a.datname,
     c.relname,
     l.transactionid,
     l.mode,
     l.GRANTED,
     a.usename,
     a.waiting,
     a.query, 
     a.query_start,
     age(now(), a.query_start) AS "age", 
     a.pid 
FROM  pg_stat_activity a
 JOIN pg_locks         l ON l.pid = a.pid
 JOIN pg_class         c ON c.oid = l.relation
ORDER BY a.query_start;

显示了许多“RowExclusiveLock”，但所有锁都已被授予...所以我不知道是什么导致了延迟峰值。

- Leo

---

空闲事务意味着您没有运行 commit/rollback/end 或者正在使用具有事务共享的连接池。 - Vao Tsun

该交易由Spring @Transaction处理，通常情况下它能够正常工作，但是偶尔会停留在这种状态达数分钟之久。 - Leo

Answer 1

这是一个应用服务器的问题。

当应用程序没有使用COMMIT或ROLLBACK结束事务时，会出现会话状态为"idle in transaction"。这被认为是应用程序中的一个错误。

锁保持不变（当然已被授予，否则会话不能处于空闲状态），直到事务结束。

从PostgreSQL 9.6开始，您可以设置参数idle_in_transaction_session_timeout自动以ROLLBACK终止此类事务，但这只是为避免数据库出现问题的权宜之计，而非解决方案。

- Laurenz Albe

---

那么应用程序实际执行查询是不可能的吗？ "事务空闲" 表示查询（删除）已经完成并返回，但是事务尚未提交？ - Leo

是的，没错。SQL语句已经执行完毕，应用程序还没有提交，数据库正在等待事务中的下一条语句。 - Laurenz Albe

PostgreSQL 默认启用自动提交吗？为什么我们需要运行 COMMIT 或 ROLLBACK 命令？ - Fayaz

是的，确实可以。但是除非您在某个地方明确启动了事务，否则连接永远不可能处于“空闲事务”状态。 - Laurenz Albe

@LaurenzAlbe 这是一个相当旧的帖子，但我们在我们的应用程序中看到了这个问题。此外，我们还看到了一些OOM错误。应用程序进程死亡OOM可能是这些空闲DB事务的原因吗？ - AxelTheGerman

Answer 2

这也可能是由以下组合造成的：

1. 连接池耗尽
2. 在事务中进行事务操作
3. 隐式 Postgres 事务中进行事务操作

这篇文章让我对这个问题有了更多认识 https://www.birdie.care/blog/birdie-engineering-update

这就解释了为什么数据库本身没有死锁，只是因为应用程序的连接池达到了上限。

- Anton Kuzmin