Greetings,
I am having quite peculiar behavior in the area of transaction rollback. I am calling a setRollbackOnly() from an EJB stateless session bean. The rollback is only partially successful - updates from "plain" update queries roll back successfully, updates from SQL stored procedures do not. It seems as if autocommit is in the picture for the SPs but not for the queries. I have rewritten one of them as a java stored procedure and added a setAutoCommit(false) method call. This allows proper transaction rollback behavior. There are definitely no commits in the stored procedures. Per IBM, I have confirmed my set up of a Web resource pointing to a jndi data source with a transaction isolation level of read-uncommitted.
Stepping through the iBatis source code, I see commits still being called but assume they are ignored since with EJB container managed transactions, there should be a Global transaction controlling things, right?
I hate the thought of rewriting all of the SPs as java stored procedures (there are many) where I seem to have some control over autocommit.
I realize that this is probably an IBM issue, but any help would be greatly appreciated.
The environment:
Using both SqlMap and DAO products
Websphere 5.1
db2 udb for iSeries
ejb stateless session bean controlling transaction consisting of:
- SQL stored procedure performing an insert
- other table updates via queries
web datasource resource reference specifying transaction-read-uncommitted isolation level
jdbc driver = jtopen 4.6 ( com.ibm.as400.access.AS400JDBCXADataSource )
My iBatis setup (based upon the wiki's Websphere suggestions):
sqlmap config:
<transactionManager commitRequired="true" type="EXTERNAL" >
<property name="DefaultAutoCommit" value="false" />
<property name="SetAutoCommitAllowed" value="false" />
<dataSource type="JNDI">
<property name="DataSource" value="${DBSOURCE}"/>
</dataSource>
</transactionManager>
daoconfig:
<daoConfig>
<!-- Common SQLMAP context (app start-up, etc. -->
<context>
<transactionManager type="SQLMAP">
<property name="SqlMapConfigResource"
value="com/mcnichols/mserve/persistence/sqlmap/sql/sql-map-config-common.xml"/>
</transactionManager>
<dao interface="com.mcnichols.mserve.persistence.iface.common.CommonDao"
implementation="com.mcnichols.mserve.persistence.sqlmap.common.IbatisCommonDao"/>
</context>
</daoConfig>
Regards,
Tim
