0

In our product sometimes there is a situation when a database is locked with an idle in transaction connection.

I am using postgresql-9.3(9.4), java connection via jdbc driver.
All connections are set to be autocommit.

The problem can only be solved by using SELECT pg_terminate_backend(PID). I don't like such a decision, but that or rebooting the service are the only things that work.

So there is a question how to solve such a problem when I don't manipulate transactions manually. All connections to Postgres are local, so i don't think that's a network problem.

Update All right no doubt - there is an application bug.

Imagine, that we have got a query:

try(PreparedStatement statement=connection.prepareStatement("update table1 set some_field=1 where id=2")){
    statement.executeUpdate();
}

So a simple query for local connection. Connection is active. Autocommit is true. What issue should happen for such connection to be idle in transaction?

Update Thanks anyone for attention. The problem was solved by changing jdbc-driver to the latest version.

Alex
  • 706
  • 7
  • 16
  • Postgres 9.3 is [no longer supported](https://www.postgresql.org/support/versioning/) and 9.4 will be unsupported in a few months. You should plan an upgrade as soon as possible. –  Oct 11 '19 at 09:15
  • There should be a problem cause government restrictions for nowadays to upgrade to higher version – Alex Oct 11 '19 at 09:38
  • That's ridiculous (I know it's not your fault) Those government restrictions should prohibit the use of outdated software that doesn't receive any security fixes anymore (in Germany we have a law for certain infrastructures that prohibits that). Upgrading from 9.3.25 to 11.5 [gives you 1851 bugfixes](https://why-upgrade.depesz.com/show?from=9.3.25&to=11.5) –  Oct 11 '19 at 09:42

2 Answers2

0

Transactions that remain in status idle in transaction for a long time are an application bug, but you are probably aware of that.

One stop-gap “solution” to the problem is to set idle_in_transaction_session_timeout to a non-zero value. This parameter is available from 9.6 on, which gives you one more excellent reason to upgrade to the latest version soon.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
0

Thanks anyone for attention. The problem was solved by changing jdbc-driver to the latest version.

Alex
  • 706
  • 7
  • 16