I had posted this question on stackoverflow and somone suggesting writing a test program and open a jira ticket . To summarize, I have a service method A with @Transactional marked readOnly calling a service2 method B with @Transactional marked non-readOnly. We have been using spring 3.x for a while and are now migrating to spring 4 and found that now it gives the exception
"Connection is read-only. Queries leading to data modification are not allowed; "
I tested this with Spring 4.0.9 and it seems to work fine but not 4.1 and above. I was under the assumption a propagation change from readOnly to readWrite in an underlying tx advice if so specified would switch the transaction to write mode. Maybe my understanding is incorrect but this flow seems to work until 4.1. I have attached a test program which illustrates this.
mvn test will fail if the pom.xml in the test package contains spring .version = 4.1.5.RELEASE but work pass through if spring.version = 4.0.9.RELEASE.
We will go back and refactor our code but just curious if in case this is a bug or expected behavior which has been eventually resolved in 4.1.
What's the stacktrace for that exception? This probably comes out of your JDBC driver? Are you possibly using Hibernate there, in which case you might be affected by #13599 (which finally applies the read-only flag to a Hibernate-managed JDBC Connection)?
This fundamentally works as designed, since this was never designed to upgrade a read-only status to read-write on the fly. The exact behavior depends on the underlying drivers and dialects, so in some scenarios, you may indeed experience this as a regression.
If it is indeed Hibernate in your scenario, you could specify a custom HibernateJpaDialect and set the "prepareConnection" flag to "false".
Thanks Juergen. Yes we do indeed use hibernate 4. The stacktrace does come from the jdbc driver
Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
I will look through #13599 and see if it indeed is the case. Just curious, will this effect, OpenSessioninViewFilter or that also just manages just the flush mode and not the underlying connection per se.?
I wrote a custom HibernateJpaDialect with prepareConnection as false and the error indeed does go away. So I guess we are hitting the #13599 issue. Thanks. !
We will rework our code to behave like spring does instead of using the dialect I think. But does having prepareConnection false /true effect performance . ? In other words we will gain any benefit by using readOnly=true now with with 4.1 since a readOnly connection is being passed on to the underlying connections
Thanks for the help.
This is indeed a performance benefit since the JDBC driver may enable significant optimizations for read-only connections, depending on the specific database. For example, on MySQL, this is necessary for reading from slave nodes in a master-slave scenario. In general, a DBMS may largely avoid the transaction log for read-only connections.
Note that we have been setting the connection read-only flag for many kinds of transactions before, including native use of Hibernate (i.e. HibernateTransactionManager). What changed as of 4.1 is simply that the Hibernate-JPA case (JpaTransactionManager) is being covered as well.