Spring/Hibernate/WebSphere5/DB2:read-only connection issue

[gt1601]

All,
I have an application running on WebSphere Server v5.1.1 that uses Spring and Hibernate to access a remote DB2 v8.2 database via IBM DB2 JDBC Type 4 driver. I have a method whose transaction attribute is set to readonly through Spring config file

******* BEGIN SPRING CONFIG FILE **********
...
<bean id="organizationService" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="target"><ref local="organizationServiceTarget"/></property>
<property name="transactionAttributes">
<props>
<prop key="executeOrganizationSearch">PROPAGATION_REQUIR ED,readOnly</prop>
</props>
</property>
</bean>
...
******* END SPRING CONFIG FILE **********

It retrieves the information I need but it keeps giving the following warning:

"JDBCExceptionReporter - SQL Warning
com.ibm.db2.jcc.b.SqlWarning: Connection readOnly mode is not enforcable after the connection has been established. To enforce a read only connection, set the readOnly data source or connection property. "

I do not have this problem when I use IBM DB2 JDBC TYPE 2, but applications in production environment must use TYPE 4 driver.


******* BEGIN WARNING *******
...
2005-01-26 18:18:21,717 DEBUG TransactionInterceptor - Invoking commit for trans
action on method 'executeOrganizationSearch' in class [us.il.state.idph.fdd.serv
ice.demographics.OrganizationService]
2005-01-26 18:18:21,717 DEBUG HibernateTransactionManager - Triggering beforeCom
mit synchronization
2005-01-26 18:18:21,718 DEBUG HibernateTransactionManager - Triggering beforeCom
pletion synchronization
2005-01-26 18:18:21,718 DEBUG HibernateTransactionManager - Initiating transacti
on commit
2005-01-26 18:18:21,718 DEBUG HibernateTransactionManager - Committing Hibernate
transaction on session [net.sf.hibernate.impl.SessionImpl@1e05ce0]
2005-01-26 18:18:21,718 DEBUG JDBCTransaction - commit
2005-01-26 18:18:21,719 DEBUG SessionImpl - transaction completion
2005-01-26 18:18:21,719 DEBUG JDBCTransaction - re-enabling autocommit
2005-01-26 18:18:21,719 DEBUG HibernateTransactionManager - Triggering afterComp
letion synchronization
2005-01-26 18:18:21,720 DEBUG TransactionSynchronizationManager - Clearing trans
action synchronization
2005-01-26 18:18:21,720 DEBUG TransactionSynchronizationManager - Removed value
[org.springframework.orm.hibernate.SessionHolder@6d d766] for key [net.sf.hiberna
te.impl.SessionFactoryImpl@11bd50e] from thread [Servlet.Engine.Transports : 0]
2005-01-26 18:18:21,720 DEBUG TransactionSynchronizationManager - Removed value
[org.springframework.jdbc.datasource.ConnectionHold er@1a9f278] for key [com.ibm.
ws.rsadapter.jdbc.WSJdbcDataSource@1ac9cff] from thread [Servlet.Engine.Transpor
ts : 0]
2005-01-26 18:18:21,720 DEBUG DataSourceUtils - Resetting read-only flag of conn
ection [com.ibm.ws.rsadapter.jdbc.WSJccConnection@1620aa9]
2005-01-26 18:18:21,721 DEBUG HibernateTransactionManager - Closing Hibernate se
ssion [net.sf.hibernate.impl.SessionImpl@1e05ce0] after transaction
2005-01-26 18:18:21,721 DEBUG SessionFactoryUtils - Closing Hibernate session
2005-01-26 18:18:21,721 DEBUG SessionImpl - closing session
2005-01-26 18:18:21,721 DEBUG SessionImpl - disconnecting session
2005-01-26 18:18:21,722 DEBUG JDBCExceptionReporter - SQL Warning
com.ibm.db2.jcc.b.SqlWarning: Connection readOnly mode is not enforcable after t
he connection has been established. To enforce a read only connection, set the r
eadOnly data source or connection property.
at com.ibm.db2.jcc.b.o.setReadOnly(o.java:1507)
at com.ibm.db2.jcc.b.wb.setReadOnly(wb.java:202)
at com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImp l.setReadOnly(WSRdb
ManagedConnectionImpl.java:3280)
at com.ibm.ws.rsadapter.spi.InternalGenericDataStoreH elper.setReadOnly(I
nternalGenericDataStoreHelper.java:266)
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.setRead Only(WSJdbcConnecti
on.java:2115)
at org.springframework.jdbc.datasource.DataSourceUtil s.prepareConnection
ForTransaction(DataSourceUtils.java:194)
at org.springframework.orm.hibernate.HibernateTransac tionManager.doBegin
(HibernateTransactionManager.java:378)
at org.springframework.transaction.support.AbstractPl atformTransactionMa
nager.getTransaction(AbstractPlatformTransactionMa nager.java:269)
at org.springframework.transaction.interceptor.Transa ctionAspectSupport.
createTransactionIfNecessary(TransactionAspectSupp ort.java:200)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.in
voke(TransactionInterceptor.java:49)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(
ReflectiveMethodInvocation.java:138)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynami
cAopProxy.java:152)
at $Proxy0.executeOrganizationSearch(Unknown Source)
at us.il.state.idph.fdd.web.party.SearchOrganizationA ction.getSearchOrga
nization(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchM ethod(DispatchActio
n.java:278)
at org.apache.struts.actions.DispatchAction.execute(D ispatchAction.java:
218)
...
******* END WARNING *******

I hope you guys can help me. Thanks !

Regards,
Guillermo

[Juergen Hoeller]

Well, the read-only flag is a kind of stepchild in the JDBC spec... Many drivers (and DBMS) don't really support a true read-only transaction. Some do optimize transaction processing, though, so setting the readOnly flag is nevertheless usually worth it.

In your case, the driver simply isn't able to switch the connection to true read-only mode and logs a corresponding warning. This can safely be ignored; your transaction won't get read-only optimizations, but that doesn't hurt.

You could remove the "readOnly" marker from your transaction attribute to avoid the warning. However, "readOnly" also triggers other optimizations, for example it suppresses Hibernate flush attempts for read-only operations, so I recommend to keep those markers and simply ignore the warnings.

Juergen

[sjivan]

In your case, the driver simply isn't able to switch the connection to true read-only mode and logs a corresponding warning. This can safely be ignored; your transaction won't get read-only optimizations, but that doesn't hurt.

You could remove the "readOnly" marker from your transaction attribute to avoid the warning. However, "readOnly" also triggers other optimizations, for example it suppresses Hibernate flush attempts for read-only operations, so I recommend to keep those markers and simply ignore the warnings.

I discovered that setting the "readOnly" marker is more than a nice-to-have.

I had a problem http://forum.springframework.org/viewtopic.php?t=4849 when using Spring MVC where despite having validation errors in my onBindAndValidate() method, the bad user entered values bound to my domain objects would get persisted by Hibernate. Turned out that this was because I had the transaction attribute for all service methods without the readOnly marker.

<prop key="*">PROPAGATION_REQUIRED</prop>

After changing the transaction attributes to

<prop key="save*">PROPAGATION_REQUIRED</prop>
<prop key="delete*">PROPAGATION_REQUIRED</prop>
<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>

the invalid bean properties did not get persisted if validation errors were present.

Sanjiv

[Martin Kersten]

I had a problem http://forum.springframework.org/viewtopic.php?t=4849 when using Spring MVC where despite having validation errors in my onBindAndValidate() method, the bad user entered values bound to my domain objects would get persisted by Hibernate. Turned out that this was because I had the transaction attribute for all service methods without the readOnly marker.

Shouldn't you detach the POJO if you use it that way? I don't think that it is a quite good idea to bind a form to a non-detached POJO.

[sjivan]

Shouldn't you detach the POJO if you use it that way? I don't think that it is a quite good idea to bind a form to a non-detached POJO.

The POJO's are detached in the Spring Form/Command object between requests. When a form is submitted, Spring binds back to these POJO's and since I'm using the Open Session In View (OSIV) pattern, when the request completes the Hibernate transaction commits (at this point the web page is displayed back to the user with validation errors) . The detached objects are brought in context of the Hibernate session before commit takes place.

I'm not doing anything special here. Just using the prescribed patterns/methodology of Spring MVC w/ Hibernate.

Sanjiv

[Martin Kersten]

Turned out that this was because I had the transaction attribute for all service methods without the readOnly marker.

Can't you avoid persisting a form's content when having a validation error? I would have had a bad feeling about requiring a read-only transaction. How do you persist the object, if the validation error does not occure? Wouldn't you require a non-read-only transaction then?

I can't think that this is really the recommanded way... . People tend to often just forget to set read-only flags and just come back if things get to the tuning stage. I wouldn't expect to be forced to set read-only, I only saw it as an option to tweak performance... .


Cheers,

Martin (Kersten)

[sjivan]

Can't you avoid persisting a form's content when having a validation error?

Thats what I've been trying to do and as I mentioned, I learnt that the way to do this is to make sure that no non-readOnly transactions are opened before validation takes place. In Spring MVC binding of web form data to beans takes placed before validation is carried out. We have no control over this order.

Can't you avoid persisting a form's content when having a validation error? I would have had a bad feeling about requiring a read-only transaction. How do you persist the object, if the validation error does not occure? Wouldn't you require a non-read-only transaction then?

Not sure how familiar you're with Spring's transaction demarcation. As posted above, after making the change I have methods of my service API starting with "save" and "delete" declared without the readOnly attribute. In my web controller I call non save/delete methods on the service API when carrying out validation (for example getUser(String user) to check if user already exists). So if validation fails, the only transaction opened would be in readOnly mode and the binding that Spring carries out on my business objects is discarded. If validation passes, a "save*" or "delete*" method on my service API is called and changes are persisted if the operation completes successfully. Spring handles the transaction stuff transparently.

Juergen said

However, "readOnly" also triggers other optimizations, for example it suppresses Hibernate flush attempts for read-only operations, so I recommend to keep those markers and simply ignore the warnings.

This seems to be the key as to whether properties bound by Spring MVC to domain objects are persisted or discarded on completion of the web request.[/quote]

[Martin Kersten]

Spring handles the transaction stuff transparently.

Mmm Hibernate does not support nested transactions, but it can upgrade a transaction from read-only to write. This is interesting. I thougth this is not possible due the hibernate transaction limitation. There is always new stuff to learn lurking in dark corners, I guess... .


Cheers,

Martin (Kersten)

PS: I still do not like it, but now I understand it. 8-)