9

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.

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?

I'm using postgresql 9.1 and 9.4.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
wolfcastle
  • 5,850
  • 3
  • 33
  • 46
  • The impacts: 1. your data is not visible to other transactions 2. if you don't eventually commit - you lose your data. – zerkms Nov 19 '15 at 22:29
  • @zerkms I'm more interested in the processing impact on the server: I realize any changes made wouldn't be visible to other connections (although in this case, it's only reading data, never modifying anything) – wolfcastle Nov 19 '15 at 22:33
  • Losing your data is not eventually a concern cause you can always use SAVEPOINTS. – Madthew Nov 19 '15 at 22:38

2 Answers2

19

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.

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

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.

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.

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Does it just block VACUUM for tables used in the transaction, or all tables? – wolfcastle Nov 19 '15 at 22:42
  • @wolfcastle: Just the ones referenced in the transaction (in any way!). Be aware that the effect can mushroom ... I added some more above. – Erwin Brandstetter Nov 19 '15 at 23:08
  • 2
    Actually, `VACUUM` can process a table that's locked for `ACCESS SHARE` by a virtual xid. It cannot truncate it to release space back to the OS, though. If the transaction is `SERIALIZABLE`, or has a currently running statement, it cannot remove dead rows created by concurrent updates/deletes. Try it yourself. Create a table with dummy rows. Start an xact and select from the table to lock it. Start another xact and delete half the rows but don't commit yet. `VACUUM VERBOSE` from another session. It'll remove the dead rows. – Craig Ringer Nov 20 '15 at 05:05
  • 1
    `SERIALIZABLE` or `REPEATABLE READ` xacts block vacuum from removing dead rows though. It's only a `READ COMMITTED` xact with no active snapshot that lets vacuum proceed. Some other things, like `WITH HOLD` cursors, prepared xacts, etc do too. – Craig Ringer Nov 20 '15 at 05:08
  • 1
    Check `backend_xmin` in `pg_stat_statements` to see if a backend holds back vacuum. It's only in 9.4 and newer. – Craig Ringer Nov 20 '15 at 05:27
  • @CraigRinger you mean [pg_stat_activity](https://www.postgresql.org/docs/current/static/monitoring-stats.html)? – Evan Carroll Jan 14 '17 at 00:03
  • This is a really bad ass post all around, thanks for your help clarifying stuff this helped me write my own answer here to a different problem (I referenced this answer too) http://dba.stackexchange.com/a/161042/2639 – Evan Carroll Jan 14 '17 at 00:28
  • 1
    1 @CraigRinger it is not true that vaccum stops working for only tables in that transaction , it will stop working for all tables because when there is one running transaction , then dead tuples created after that will not be cleaned up by vacuum for all the tables, because transaction id is generated globally and it check for transaction id less that transaction id of oldest transaction – Sahil Aggarwal Aug 17 '18 at 06:54
  • Also written a blog on issue i face hope this will also help in understanding consequences of opening a database connection open https://hello-worlds.in/2021/03/28/a-database-session-leak-can-slow-down-your-database/ – Sahil Aggarwal Apr 19 '21 at 06:38
3

There are two major impacts to the system.

The tables that have been used in those transactions:

  1. are not vacuumed which means they are not "cleaned up" and their statistics aren't updated which might lead to bad (=slow) execution plans
  2. cannot be changed using ALTER TABLE