Spring 1.2 + Hibernate aggressive release + read-only txn

[Lawrence Ho]

After upgrading to Spring 1.2 final (from RC2), I was shocked to see that my app blows up at runtime, in semi-random fashion. The JDBC driver is telling me that I was trying to do updates while the transaction is read-only.

The config is as follows:
- Spring 1.2
- C3P0 0.8.5.2
- Hibernate 3.0.3
- PostgreSQL 8.0.3 + JDBC3 311 build driver
- load* and find* are PROPAGATION_REQUIRED,readOnly
- others are PROPAGATION_REQUIRED

My 1st question is: Is aggressive release and read-only txn a wrong combination?

Long story short (a few hours of debugging and tracing), I found Spring would reset the connection's readOnly status by doing cleanupAfterCompletion() near the end of commit of AbstractPlatformTransactionManager.

If aggressive release is enabled, Hibernate will call ConnectionProvider directly to close the connection, the connection (possibly still "read-only") is returned to the pool as a result. When Spring finally get the chance to do the normal cleanup, it gets a connection from the pool again. If it is luckly, that particular connection will be picked, and bad things won't happen. Otherwise, when some other transaction picks up that connection and do some updates, a nasty SQLException will be thrown.

Workaround: set hibernate.connection.release_mode to on_close (for the bean definition of the LocalSessionFactoryBean) to restore the old behaviour.

My 2nd question is: Is this the way to go? If yes, some additional documentation would help. If there is any other way, I would like to know too.

Thanks,
Lawrence

Edit:
JIRA link:
http://opensource.atlassian.com/proj...browse/SPR-960

[Juergen Hoeller]

I've already replied on JIRA, but I'll reply here too, so that forum users can find this...

Arguably, a decent connection pool should always reset a Connection when checked back into the pool, so that the next user of the Connection will get the equivalent of a fresh Connection (read-write, default transaction isolation). According to my tests, C3P0 does this, for example, while Commons DBCP does not.

Unfortunately, this special Hibernate 3.0.3 support was a last-minute change in Spring 1.2 final, and has just been tested against DriverManagerDataSource and C3P0, which worked for me...

It seems appropriate to me to change the default in Spring 1.2.1 back to "on_close", so that it will work with all connection pools out-of-the-box. That allows for proper cleanup in HibernateTransactionManager in any case, without relying on any specific connection pool behavior.

Everybody: If you use Spring 1.2 final with Hibernate 3.0.3 and HibernateTransactionManager, specify Hibernate's "hibernate.connection.release_mode" property as "on_close", to be on the safe side.

<bean id="sessionFactory" ...
  <property name="hibernateProperties">
    <props>
      <prop key="hibernate.connection.release_mode">on_close</prop>
    </props>
  </property>
</bean>

Juergen[/b]

[alesj]

Juergen, can you please share the C3PO data source bean configuration (<bean id="dataSource" ...>)?

Ales

[Juergen Hoeller]

It's a C3P0 standard configuration, with driver class, url, username and password. Nothing special there. I intend to research C3P0's behavior a bit more this weekend, when I have some time left.

Anyway, I absolutely recommend to set "hibernate.connection.release_mode" to "on_close" to avoid this issue in the first place. Spring 1.2.1 (which is likely to be released next Friday) will use this as default, making the explicit "on_clsoe" configuration redundant.

Juergen

[alesj]

I intend to research C3P0's behavior a bit more this weekend, when I have some time left.

Would you (after this weekend ) recommend using C3P0 over DBCP?

Ales

ps: thanx also for that 'SessionFactoryUtils change' post reply