Lot of SHOW TRANSACTION ISOLATION LEVEL queries in postgres

I am using Hibernate 4, PostgreSQL and C3P0.

In my web application, after sometime I am getting multiple SHOW TRANSACTION ISOLATION LEVEL queries in database due to which my server gets hang. In my code all my connections are properly closed.

Is it due to a connection leak?

5 Answers

You should also check the state of each query, if it's idle it's most likely nothing problematic.

pg_stat_activity will show last query that was executed by each open connection. And c3p0 uses SHOW TRANSACTION ISOLATION LEVEL to keep the connection open (normal and expected behavior).

This is what's happening:

1. Connection is opened
2. SHOW TRANSACTION ISOLATION LEVEL is executed to keep the connection open.
3. Connection pool will send this query periodically (for example every 10 minutes) to keep the connection open.
4. Those queries show up in pg_stat_activity because in some cases those were the last queries executed via given connection. Also they will show up as idle because this connection is not in active use

It sounds you may be churning through the Connections in your Connection pool way too fast.

This could be because you have set an overly aggressive maxIdleTime or maxConnectionAge, or because Connections are failing Connection tests and getting evicted, or because your application mistakenly reconstructs the pool when it asks for Connections rather than holding and using a stable pool. (That's a very bad but surprisingly common mistake.)

c3p0 checks Connection isolation levels one time per Connection acquired. Since aquired Connections should have a long lifetime in the pool, the amortized overhead of that is negligible.

But if, due to some configuration problem or bug, your application has c3p0 continually acquiring Connections, one per client or much worse if you are reconstructing the pool for each client, then the transaction isolation checks might become the visible symptom of a worse underlying problem.

multiple SHOW TRANSACTION ISOLATION LEVEL queries in database due to which my server gets hang.

It's really hard (I would said impossible) that your server hang due to multiples queries of this. If your server hang you should check your configuration and that you are using the latest minor patch available for you version.

SHOW TRANSACTION ISOLATION LEVEL is executed every time the application calls Connection.getTransactionIsolation(), C3P0 calls getTransactionIsolation() every time it creates a connection.

If the connection pooler is creating and destroying lots of connections, you end up with many queries of SHOW TRANSACTION ISOLATION LEVEL to the database because the PgJDBC driver execute the query every single time it calls getTransactionIsolation().

change the Test connection on checkin and checkout as false in c3p0

I saw the same problem. It seemed to happen when using higher postgress version. I fixed it by upgrading the postgress driver 42.2.6.