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?

Lullaby

Maybe your obfuscation layer (aka "ORM") is doing that, or c3p0 - check their configurations (e.g. maybe it's configured as the "validation query" in c3p0). It's not something Postgres does on its own. And how do you know that that specific query is the reason for your database to "hang" (also which server exactly is "hang"? The database? The application server?) – Lenitalenitive

My application server gets hang and 98% queries are of SHOW TRANSACTION ISOLATION LEVEL and remaining are of validation query select 1. My heap memory gets full. – Lullaby

Well then it's apparently a problem with the configuration of your application server. And how do you know those queries are "hanging"? If you are checking pg_stat_activity you need to check the state column to know if the query is currently executing or if this was just the last query that the session executed. You have provided far to less information for anyone to be able to answer this – Lenitalenitive

I'm seeing a similar problem. My application is randomly throwing org.springframework.transaction.CannotCreateTransactionException when it tries to create a transaction with an explicit isolation level. The error is caused by the connection already having an active transaction with a different isolation level (which shouldn't be the case). I sometimes see connections stuck in the 'idle in transaction' state and the last run query was 'SHOW TRANSACTION ISOLATION LEVEL'. – Caelian

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

Duquette

Answer 2

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.

Sirois

Answer 3

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().

Burroughs

Answer 4

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

Taft

Answer 5

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.

Angelinaangeline