postgresqljdbcsqltransaction

PostgreSQL 9.3 idle in transaction

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.

Solution

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