Clinton,
Thanks for your suggestion. I threw together a simple factory dishing out non-iBatis Daos and kept SqlMaps as is (not changing its configuration at all - still External and using the settings necessary for WebSphere). The problem remains. I have a pmr open with IBM related to this issue - I suppose I will proceed along those lines. As stated previously, my only workaround at this time is to change from SQL stored procedures to java stored procedures (correction from earlier post - I do not have to call setAutoCommit(false)). This solves the problem allowing for a complete transaction rollback. It seems that the use of the SQL stored procedure always uses a new connection? Whereas, in the java stored procedure the connection is obtained using the following code which apparently correctly honors the original connection:
Connection con = DriverManager.getConnection("jdbc:default:connection");
BTW, we are experiencing other DB2 UDB for iSeries related lock space issues - kind of a mess right now. I think that our combination of WebSphere and DB2 UDB for iSeries makes for a pretty shaky environment.
Tim
| Clinton Begin <[EMAIL PROTECTED]>
02/26/2005 11:11 PM
|
|
Tim,
Although I can't pinpoint the exact problem, I would suggest you NOT
use iBATIS DAO in your particular case. It's simply too many
transaction managers. All you really need is a factory for your DAOs.
Your transactions are controlled by the container and the SQL and
JDBC is handled by iBATIS. Your DAO needs are simple enough that you
might as well just use your own factory.
If you agree, and make the change, then let us know if the problem continues.
Cheers,
Clinton
On Mon, 21 Feb 2005 15:27:15 -0500, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
>
> 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
>
>
>
