Hibernate Isolation Level With Postgres 9.0

Question

We are using Postgres 9.1.0 with Hibernate 3.2.5.

I have downloaded latest JDBC driver JDBC4 Postgresql Driver, Version 9.1-901.

I had set hibernate isolation property.

<property name="connection.isolation">2</property>

which means

2=READ_COMMITTED

But it gives me error when trying to access database.

Caused by: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
at org.postgresql.jdbc2.AbstractJdbc2Connection.setTransactionIsolation(AbstractJdbc2Connection.java:821)
at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:103)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)

If i remove isolation level property. It works fine. Any help would be appreciated

Answer 1

Looking at the error message: "Cannot change transaction isolation level in the middle of a transaction", it seems that Hibernate starts a transaction (e.g. by running a SELECT) and then tries to change the isolation level.

That seems to be a bug in the PostgreSQL integration of Hibernate, because it should either change the isolation level as the first thing after opening the connection or end whatever has been started by issuing a commit or rollback before changing the isolation level.

Your only option (apart from opening a bug report) is to leave out the isolation setting. As READ_COMMITTED is the default isolation level for Postgres anyway that shouldn't make a difference.

Answer 2

I'm no Hibernate expert, and I don't know if you need to be changing the isolation level or doing everything at one level, but you can set the default isolation level for your database with something like:

ALTER DATABASE mydatabase SET default_transaction_isolation = 'serializable'
\g

See http://www.postgresql.org/docs/9.1/interactive/sql-alterdatabase.html

You can also use ALTER USER to set default_transaction_isolation on a per-user basis, or set it in your postgresql.conf file for all databases and users.