Lot of SHOW TRANSACTION ISOLATION LEVEL queries in postgres

Question

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?

Answer 1

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