不结束数据库事务会有什么后果？

Question

我在我的应用程序代码中发现了一个Bug，我启动了一个事务，但从未提交或回滚。连接定期使用，每10秒钟读取一些数据。在pg_stat_activity表中，它的状态报告为“空闲中的事务”，其backend_start时间已经超过一周。

这对数据库有什么影响？会导致额外的CPU和RAM使用吗？会影响其他连接吗？这个状态可以持续多长时间？

我正在使用PostgreSQL 9.1和9.4。

- wolfcastle

---

影响：1.您的数据对其他事务不可见 2.如果您最终不提交-您将失去您的数据。 - zerkms

@zerkms 我更关心对服务器的处理影响：我意识到所做的任何更改都不会对其他连接可见（尽管在这种情况下，它只是读取数据，从未修改任何内容） - wolfcastle

失去数据最终不是一个问题，因为您始终可以使用 SAVEPOINTS。 - Madthew

Answer 1

由于您只进行 SELECT 操作，影响有限。但对于任何写操作来说，情况更为严重，因为只有提交后，更改才会对其他事务可见，否则更改将会被丢失。

这将消耗一些内存，并永久占用一个允许的连接（可能会影响或不影响您的操作）。

最长运行事务的一个严重后果是：它会阻塞 VACUUM 执行其工作，因为还存在一些旧事务可以看到旧行。系统将开始膨胀。

特别地，SELECT 会在所有引用的表上获取一个 ACCESS SHARE 锁（其中最少会受阻），这不会干扰其他 DML 命令，如 INSERT、UPDATE 或 DELETE，但它会阻止 DDL 命令以及 TRUNCATE 或 VACUUM（包括自动清理任务）。请参阅“手动锁定”中的“表级锁定”部分。

这也可能干扰各种复制解决方案，并在长时间内保持打开状态时导致事务 ID 循环，如果运行足够长时间 / 消耗 XIDs 足够快，则会发生这种情况。有关更多信息，请参阅 “常规清理”手册。

如果其他事务被阻止提交并且它们已经获取了自己的锁，则阻塞效果可能会 膨胀。等等。

您可以将事务保持打开状态（几乎）无限期 - 直到连接关闭为止（当然，服务器重新启动时也会关闭连接）。 但是永远不要让事务保持打开状态比必要的时间更长。

- Erwin Brandstetter

---

它只是阻止在事务中使用的表的VACUUM操作，还是所有表都会被阻止？ - wolfcastle

实际上，VACUUM 可以通过虚拟 xid 处理被 ACCESS SHARE 锁定的表。但它不能截断表以将空间释放回操作系统。如果事务是 SERIALIZABLE 或有当前正在运行的语句，则无法删除由并发更新/删除创建的死行。自己试试吧。创建一个带有虚拟行的表。开始一个 xact 并从表中选择以锁定它。启动另一个 xact 并删除一半的行，但不要提交。从另一个会话中使用 VACUUM VERBOSE。它将删除死行。 - Craig Ringer

SERIALIZABLE或REPEATABLE READ事务会阻止VACUUM清理死行，只有没有活动快照的READ COMMITTED事务才能让VACUUM继续执行。其他一些像WITH HOLD游标、预处理事务等也是如此。 - Craig Ringer

检查 pg_stat_statements 中的 backend_xmin，以查看后端是否在阻止回收。它仅存在于 9.4 及更新版本中。 - Craig Ringer

@CraigRinger，并不是只有在该事务中的表才会停止工作，实际上所有的表都会停止工作。因为当一个事务正在运行时，之后创建的死元组将不会被所有表的vacuum清理掉，这是因为事务ID是全局生成的，并且它检查的是小于最早事务的事务ID。 - Sahil Aggarwal

Answer 2

系统有两个主要影响。

用于这些交易的表：

1. 没有被vacuumed，这意味着它们没有“清理”并且它们的统计信息也没有更新，可能会导致执行计划不好 (=慢)
2. 不能使用 ALTER TABLE 更改

- user330315