不结束数据库事务的后果是什么？ [英] What are the consequences of not ending a database transaction?

问题描述

我在我的应用程序代码中发现了一个错误，该错误使我开始了事务，但是从不提交或回滚。该连接会定期使用，每10秒钟左右就会读取一些数据。在 pg_stat_activity 表中，其状态报告为交易空闲，并且其 backend_start 时间超过一周

I have found a bug in my application code where I have started a transaction, but never commit or do a rollback. The connection is used periodically, just reading some data every 10s or so. In the pg_stat_activity table, its state is reported as "idle in transaction", and its backend_start time is over a week ago.

此操作对数据库有什么影响？是否会导致额外的CPU和RAM使用率？会影响其他连接吗？

What is the impact on the database of this? Does it cause additional CPU and RAM usage? Will it impact other connections? How long can it persist in this state?

我正在使用PostgreSQL 9.1和9.4。

I'm using postgresql 9.1 and 9.4.

推荐答案

由于只进行 SELECT ，因此影响是有限的。对于任何写操作来说，情况都更为严峻，在更改之前，所做的更改对其他任何事务都不可见-如果从未提交，则丢失。

Since you only SELECT, the impact is limited. It is more severe for any write operations, where the changes are not visible to any other transaction until committed - and lost if never committed.

RAM ，并永久地占用您允许的连接之一（可能无关紧要）。

It does cost some RAM and permanently occupies one of your allowed connections (which may or may not matter).

后果更严重的情况之一运行时间很长的交易：它会阻止 VACUUM 完成工作，因为仍然有一个旧事务可以看到旧行。系统将开始膨胀。

One of the more severe consequences of very long running transactions: It blocks VACUUM from doing it's job, since there is still an old transaction that can see old rows. The system will start bloating.

特别是， SELECT 获得了访问权限锁定（所有块中的最少阻塞）。这不会干扰其他DML命令，例如 INSERT ， UPDATE 或 DELETE ，但它会阻止DDL命令以及 TRUNCATE 或 VACUUM （包括自动真空作业）。 请参见手册中的表级锁。

In particular, SELECT acquires an ACCESS SHARE lock (the least blocking of all) on all referenced tables. This does not interfere with other DML commands like INSERT, UPDATE or DELETE, but it will block DDL commands as well as TRUNCATE or VACUUM (including autovacuum jobs). See "Table-level Locks" in the manual.

如果长时间保持打开状态，它也可能会干扰各种复制解决方案，并且从长远来看会导致事务ID环绕足够/您足够快地刻录了足够的XID。 有关常规真空清理的手册中的更多信息。

It can also interfere with various replication solutions and lead to transaction ID wraparound in the long run if it stays open long enough / you burn enough XIDs fast enough. More about that in the manual on "Routine Vacuuming".

如果其他交易被禁止提交并且已经获得了自己的锁，则封锁效果可能会蘑菇。

Blocking effects can mushroom if other transactions are blocked from committing and those have acquired locks of their own. Etc.

您可以无限期地（几乎）保持事务打开状态-直到关闭连接为止（显然，服务器重新启动时也会发生这种情况）。

但永远不要让交易的开放时间超过需要。

You can keep transactions open (almost) indefinitely - until the connection is closed (which also happens when the server is restarted, obviously.)
But never leave transactions open longer than needed.

这篇关于不结束数据库事务的后果是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！