JPA and readonly transactions when using mysql replication driver

I'm using Spring 2.5.2/HibernateJpaVendorAdapter/JpaTransactionManager/MySQLReplicationDriver.

If connection.isReadOnly() is true then MySQL's ReplicationDriver uses one of the slave databases, otherwise it uses the master database. To this end, I have set the @Transactional(readOnly=true) annotation whenever I'm doing queries.

Unfortunately, the underlying JpaTransactionManager or HibernateJpaDialect classes never set con.setReadOnly(true) when the readOnly setting is set to true. Here's the code:
@Override
public ConnectionHandle getJdbcConnection(EntityManager entityManager, boolean readOnly)
throws PersistenceException, SQLException {

Session session = getSession(entityManager);
Connection con = session.connection();
return (con != null ? new SimpleConnectionHandle(con) : null);
}

As you can see, the readOnly boolean input never gets used. This differs to say the HibernateTransactionManager that calls DataSourceUtils.prepareConnectionForTransaction() which in turn calls con.setReadOnly(true).

My question is why is it that when using Jpa the con.setReadOnly(true) isn't being set if readOnly has been set to true in the @Transactional annotation?

Cheers
Chico

I've implemented the following custom HibernateJpaDialect workaround:

package test.orm.jpa.vendor;

import java.sql.SQLException;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;

import org.springframework.jdbc.datasource.ConnectionHand le;
import org.springframework.orm.jpa.vendor.HibernateJpaDia lect;

public class CustomHibernateJpaDialect extends HibernateJpaDialect {

@Override
public ConnectionHandle getJdbcConnection(EntityManager entityManager, boolean readOnly)
throws PersistenceException, SQLException {
ConnectionHandle connectionHandle = super.getJdbcConnection(entityManager, readOnly);
if (null != connectionHandle && null != connectionHandle.getConnection()) {
if (readOnly) {
connectionHandle.getConnection().setReadOnly(true) ;
} else {
connectionHandle.getConnection().setReadOnly(false );
}
}
return connectionHandle;
}
}

and

package test.orm.jpa.vendor;

import org.springframework.orm.jpa.JpaDialect;
import org.springframework.orm.jpa.vendor.HibernateJpaVen dorAdapter;

public class CustomHibernateJpaVendorAdapter extends HibernateJpaVendorAdapter {

private final JpaDialect jpaDialect = new CustomHibernateJpaDialect();

@Override
public JpaDialect getJpaDialect() {
return this.jpaDialect;
}
}

What I would like to know is if there are any inherent problems by doing this that I'm not aware of?

Cheers
Chico

Although I see there has been many views on this thread (over 200), it hasn't received any replies. Is there nobody on this forum that can help ascertain why read-only transactions are 'ignored' when using JPA, and if my custom HibernateJpaDialect workaround is sound?

@chico.charlesworth

I am running into the same problem (with Spring 3.1, wherein HibernateJpaDialect is still swallowing readOnly flag in getJdbcConnection. Also do not understand why this is the case - other than, perhaps, devs simply didn't get to this.

As far as your workaround above, I don't think it's quite right. The whole point of a ConnectionHandle is to possibly defer taking out a connection (via getConnection()). You're calling it eagerly - to set readOnly flag. Instead, I think one'd have to:
1) extend ConnectionHandle to store state of 'readOnly' flag
2) extend ConnectionHandle.getConnection to also call cxn.setReadOnly
3) possibly revert readOnly cxn setting in CH.releaseConnection - not sure

I'll give the above a shot and follow up here. Did you by chance learn anything new on this topic since your last post?