Hibernation isolation level using Postgres 9.0

Question

We are using Postgres 9.1.0 with Hibernate 3.2.5.

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

I set the sleep isolation property.

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

which means

 2=READ_COMMITTED 

But this gives me an error while trying to access the 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 the isolation level property. It is working fine. Any help would be appreciated

Answer 1

Looking at the error message: “Unable to change transaction isolation level in the middle of a transaction”, it seems that Hibernate starts the transaction (for example, by running SELECT) and then tries to change the isolation level.

This seems to be a mistake in integrating Hibernate into PostgreSQL, because it should either change the isolation level, as the first thing, after opening the connection, or stop everything that was started by issuing a commit or rolling back before changing the isolation level.

Your only option (other than opening an error report) is to leave the isolation setting. Since READ_COMMITTED is the default isolation level for Postgres, this should not matter.

Answer 2

I'm not a Hibernate expert, and I don’t know if I need to change the isolation level or do everything at the same level, but you can set the default isolation level for your database like this:

 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 for each user or install it in your postgresql.conf file for all databases and users.