Exceptions thrown from iBATIS break transactions.

[SamCarr]

After many many months of pain I've tracked down my problem. I am using Spring with iBATIS to access Oracle. If a *non-transactional* iBATIS call throws any sort of exception, it seems that the database connection it was using gets left in a strange sort of transactional limbo state. Consequently the next (and all subsequent) *transactional* code to try and use that connection fails with the following error:

org.springframework.transaction.CannotCreateTransa ctionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java: 743)
...

For instance I have a piece of DAO code a bit like this:

try
{
    getSqlMapClientTemplate().insert("insertFavouriteListing", lMap);
}
catch (DataIntegrityViolationException exception)
{
    // Ignore this exception since it is to be expected in certain normal cases.
}

If the DataIntegrityViolationException is thrown, the DB connection gets shafted. It seems that any exception thrown across the boundary between iBATIS and Spring causes this to happen. The only way to workaround this issue seems to be to make all my DAO transactional, but that is surely horribly inefficient - I don't want to use transactions unless they are necessary and most of my DAO/manager code is just performing a single DB access.

Any thoughts? This seems like a bug somewhere in iBATIS or Spring to me.

[Will Jaynes]

We have been seeing this problem crop up in plain Spring JDBC code, so it may not relate to iBATIS at all. We hadn't yet tracked it down very well, so we'll use your information and look at it again.

-Will

[SamCarr]

It strikes me that the *non-transactional* call must at some point be executing the "SET TRANSACTION ... " Oracle SQL. This is the only explanation as far as I can see. However I don't see why it would be executing this SQL for non-transactional code, so I think that maybe there is a bug in Spring that does this when it shouldn't, or fails to act correctly when an exception is thrown out of iBATIS under this scenario.

Let me know if you find anything.

[Will Jaynes]

I don't really have a handle on this problem. I tried to create an example using the SingleConnectionDataSource in a Unit test. But couldn't make it happen. So I set up my application server (Resin) with a datasource that has one connection in the pool, so it would be reused. And I created a TestController. I'm using Spring 2.0M4. The Dao just extends JdbcDaoSupport. iBATIS isn't part of this.

I set up my service object with the following transaction attributes:

<property name="transactionAttributes">
<props>
<prop key="log*">PROPAGATION_REQUIRED,ISOLATION_READ_COM MITTED</prop>
<prop key="get*">PROPAGATION_REQUIRED,ISOLATION_READ_COM MITTED</prop>
</props>
</property>

My Dao has a "log" method that inserts into the db. The service object has two methods: getDao() and a "log" method that calls getDao().log().

So, if I call service.log("fail"), which causes the sql insert to throw an exception, the next call to service.log() works fine.

If I call service.getDao().log("fail"), which again causes the exception, then the next call to service.getDao().log() fails with the ORA-01453 error. If fact all subsequent log calls fail.

Also, I've set up a postInteceptor with a DataAccessExceptionAdvice which logs error messages. On the call to service.getDao().log("fail"), the advice is not called. It looks like the service.getDao() is unaffected by the declarative transaction.

I don't think I've presented this very well, but something is going on that I don't expect or understand.

-Will

[SamCarr]

Are you sure that you're calling the service object through the transactional proxy in all cases? If you somehow manage to circumvent the proxy then there is no transaction defined and you see the Oracle error. This is exactly my problem - in that most times I don't want a transaction, but then if any exception is thrown my DB connections get broken :-/

Are you in a position to try and track down and fix this issue? If so I'll give you all the help I can!

[Will Jaynes]

I am always using the proxy. Although, as I say, it looks like when I call service.getDao().log() that there is no transaction, or something.

Unfortunately, I can't put any more time into this right now. Maybe later.

-Will

[SamCarr]

I have raised an issue in the Spring issue tracking system:

http://opensource.atlassian.com/proj...rowse/SPR-2090

[bayard]

If Will is able to attach his test case (even if it's not a unit test case) to the JIRA issue, it might make it easier for whomever looks at things and speed things up.

[Mark1900]

I moved the datasource declaration to the template SqlMapClientFactoryBean object, rather than individually declare it per DAO. This made this error disappear.

Old code;

<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryB ean">
<property name="jndiName" value="java:${dsjndi}" />
</bean>

<bean id="tpiSqlMapClient" class="org.springframework.orm.ibatis.SqlMapClient FactoryBean">
<property name="configLocation" value="classpath:something/dao/ibatis/sql/ibatis-sqlmap-config.xml" />
</bean>

<bean id="accountServiceDAO" class="something.dao.ibatis.AccountServiceSQLMapDA O">
<property name="dataSource" ref="dataSource" />
<property name="sqlMapClient" ref="tpiSqlMapClient" />
</bean>

<bean id="customerDAO" class="something.dao.ibatis.CustomerSQLMapDAO">
<property name="dataSource" ref="dataSource" />
<property name="sqlMapClient" ref="tpiSqlMapClient" />
</bean>


New code;


<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryB ean">
<property name="proxyInterface" value="javax.sql.DataSource" />
<property name="jndiName" value="java:${dsjndi}" />
</bean>

<bean id="tpiSqlMapClient" class="org.springframework.orm.ibatis.SqlMapClient FactoryBean">
<property name="dataSource"><ref bean="dataSource"/></property>
<property name="configLocation" value="classpath:something/dao/ibatis/sql/ibatis-sqlmap-config.xml" />
</bean>

<bean id="accountServiceDAO" class="something.dao.ibatis.AccountServiceSQLMapDA O">
<property name="sqlMapClient" ref="tpiSqlMapClient" />
</bean>

<bean id="customerDAO" class="something.dao.ibatis.CustomerSQLMapDAO">
<property name="sqlMapClient" ref="tpiSqlMapClient" />
</bean>