Unexpected transaction behaviour

Hello,

I have a OLTP system supported by a Java standalone app using JDBC 42 Driver postgresql-jdbc-9.4-1212 and a backend DB running in "PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit”. The Os is Windows 7 64 bit.

The system normal throughput is about 110 transactions/min constantly without degradation. Recently a new Java module of the application was installed and when the app startups the system throughput starts to degrade to the point that reaches 50 transactions/min, it’s not an immediate downfall in the throughput but it looses like 5 to 10 transactions per minute until it reaches 50 transactions per minute. At this point if we terminate the only existing backend pid of the new Java module (that is in “idle in transaction” with a backend_xmin value) immediatly in the next minute the throughput rises again to 110 transactions/min.

We’re not being able to identify the root cause for this behaviour, but we found out that this occurs only when we use JDBC Driver via Java and is reproducible whenever we use JDBC driver with a small piece of Java code (simulating the new Java module). At POINT 4 below we stay with a “idle in transaction” session and a backend_xmin value corresponding to the snapshot of the moment when the “select * from x” query was fired.  This doesn’t happen when we connect via psql and reproduce the similar actions the issue is not occuring, this is, with psql we stay at POINT 4 with a “idle in transaction” session but without a backend_xmin value.

Reading PG documentation this behaviour around backend_xmin depends on the transaction isolation level, the snapshot collection is diferent. We suspect that the loss of throughput resides in the fact that the “idle in transaction” with a backend_xmin avoids or causes delays in the MVCC versioning core system of Postgres. Nevertheless we don’t understang why it happens only when using JDBC.

The new Java module does the following code:

Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://127.0.0.1:5433/postgres";
Properties props = new Properties();
props.setProperty("user”,"postgres");
props.setProperty("password”,”**********");
Connection conn = DriverManager.getConnection(url,props);
conn.setAutoCommit(true);
PreparedStatement st = conn.prepareStatement("BEGIN");
st.execute();
st = conn.prepareStatement("select * from x");
st.execute();
//POINT 4  => At this point pg_stat_activity shows "idle in transaction” and a backend_xmin value filled
Thread.sleep(300000);
conn.close();

The same actions via PSQL (after connect) are:

 BEGIN;
 select * from x;
=> At this point pg_stat_activity shows "idle in transaction” and backend_xmin is null

Why the differente behaviour? We understand that PSQL and JDBC use different protocols but in terms of transaction behaviour should be the same, the Java code and PSQL are equivalent in terms of transaction behaviour. Can someone help?

Thank you