Atomikos Forum

Hello again,

After getting Atomikos 3.3.2 running using Spring and Hibernate, I'm now looking at introducing nested transactions. A simple example works fine:

tm.begin()
...do outer stuff...
tm.begin()
...do inner stuff...
tm.commit() // if successful
tm.rollback() // if failure

tm.commit()

But this only works for me if the objects of nested transactions are independent of the outer transaction!

What I'm trying to do is

tm.begin()
...write outer object...
tm.begin()
...write nested object which has a (hibernate) reference to the outer object...
tm.commit()
tm.commit()

I'm getting the following error when trying to write the nested object:

08:35 ERROR error in checkEnlistBeforeUse Failed to suspend branch: feedProcessorTm0000200020feedProcessorTm1
08:35 ERROR writeJob PERSISTENCE_JOB_ERROR
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not insert: [au.com.careerone.fads.ingester.persistence.JobLinkRo]; uncategorized SQLException for SQL [insert into job_links (created_on, audit_feed_id, ingest_status, job_hash, job_type) values (?, ?, ?, ?, ?)]; SQL state [null]; error code [0]; error in checkEnlistBeforeUse Failed to suspend branch: feedProcessorTm0000200020feedProcessorTm1; nested exception is com.atomikos.jdbc.AtomikosSQLException: error in checkEnlistBeforeUse Failed to suspend branch: feedProcessorTm0000200020feedProcessorTm1
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
    at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
    at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:379)
    at org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:699)
    at au.com.careerone.fads.ingester.persistence.dao.AbstractGenericDao.saveOrUpdate(AbstractGenericDao.java:56)
    at au.com.careerone.fads.ingester.persistence.dao.JobLinkDaoCore.saveOrUpdate(JobLinkDaoCore.java:29)

Anybody any idea?

Cheers,
Holger

I've written a test case to try a simple nested transaction which references the out object:

public void testAllTXPartsSuccess()
{
    TransactionManager txMan = null;
    try
    {
        txMan = jtaTransactionManager.getTransactionManager();
       
        log.debug("Begin TRAN 1");
        txMan.begin();

        //Insert a FEED
        FeedRo feedRo = new FeedRo();
        feedRo.setFeedHash("HASH");
        feedRo.setFileName("FILENAME");
        feedRo.setFormat("CSV");
        feedRo.setIngestEnd(new Date());
        feedRo.setIngestStart(new Date());
        feedRo.setSource("FTP");
        feedRo.setStatus("IN_PROGRESS");
        feedRo.setType("CC");
        feedRo.setSystemVersion("1");
        feedDao.saveOrUpdate(feedRo);

        //START ANOTHER TX
        log.debug("Begin TRAN 2");
        try
        {
            log.debug("Begin TRAN 2");
            txMan.begin();
            //insert a client
            ClientRo client1 = new ClientRo();
            client1.setClientName("Client1");
            client1.setClientType("UNKNOWN");
            client1.setCreatedOn(new Date());
            clientDao.saveOrUpdate(client1);

            feedRo.setClient(client1);
           
            log.debug("Commit TRAN 2");
            txMan.commit();
        }
        catch (Exception e)
        {
            //roll this back
            log.debug("Rollback TRAN 2", e);
            txMan.rollback();
        }
        //CONTINUE WITH OUTER TX

        log.debug("Commit TRAN 1");
        txMan.commit();
    }
    catch (Exception e)
    {
        e.printStackTrace();
        try
        {
            log.debug("Rolling Back OUTER TX");
            txMan.rollback();
        }
        catch (SystemException e1)
        {
            e1.printStackTrace();
        }
    }
}

When it gets to "Commit TRAN 1" the system hangs for some seconds (I guess according to innodb_lock_wait_timeout of the MySQL my.ini) and then throws the following exception:

10:53 WARN  SQL Error: 1205, SQLState: 41000
10:53 ERROR Lock wait timeout exceeded; try restarting transaction
10:53 ERROR Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at ...

What setting am I missing? Does Atomikos support nested transactions?

Thanks for your help!

Here's a more detailed exception (somehow the forum didn't allow it before):

10:52 DEBUG Commit TRAN 1
10:53 WARN  SQL Error: 1205, SQLState: 41000
10:53 ERROR Lock wait timeout exceeded; try restarting transaction
10:53 ERROR Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
    at org.springframework.orm.hibernate3.SpringSessionSynchronization.beforeCommit(SpringSessionSynchronization.java:135)
    at org.springframework.transaction.jta.SpringJtaSynchronizationAdapter.beforeCompletion(SpringJtaSynchronizationAdapter.java:119)
    at com.atomikos.icatch.jta.Sync2Sync.beforeCompletion(Unknown Source)
    at com.atomikos.icatch.imp.TransactionStateHandler.commit(Unknown Source)
    at com.atomikos.icatch.imp.CompositeTransactionImp.doCommit(Unknown Source)
    at com.atomikos.icatch.imp.CompositeTerminatorImp.commit(Unknown Source)
    at com.atomikos.icatch.jta.TransactionImp.commit(Unknown Source)
    at com.atomikos.icatch.jta.TransactionManagerImp.commit(Unknown Source)
    at com.atomikos.icatch.jta.UserTransactionManager.commit(Unknown Source)
    at au.com.careerone.fads.ingester.transactions.FadsTXManager.testAllTXPartsSuccess(FadsTXManager.java:88)

Hi,

Atomikos supports nested transactions. That being said, mapping nested transactions to database internals is not trivial and often contradicting to the spirit of nesting. For instance, from your posts I suspect there is a deadlock in synchronization of Hibernate and the DBMS for the nested parts.


What exactly determines your need for nested transactions?

Guy

Hi,

As stated previously this is what were trying to achieve:
//TX-1-Start
tm.begin()
...write outer object...
//TX-2-Start
tm.begin()
...write nested object which has a (hibernate) reference to the outer object...
//TX-2-End
tm.commit()
//TX-1-End
tm.commit()

To give you and idea of the business case:
We receive a xml feed file which contains jobs.
1.When the feed file we store information about the feed. (outer tx)
2.We process the jobs (which are linked to the feed) and store them to the db (inner tx).
3. In case any job fails, the full inner tx will be rolled back, but the feed information remains.
4. Otherwise all jobs and the feed information will be stored.
5. After (succesfully or unsuccessfully) processing all jobs, the feed information is updated. If storing the feed information fails, all should be rolled back.

Is there any working example which uses nested transactions, Hibernate and Spring?

After moving from MySQL to MS SQL Server 2009 we've got distributed transactions working. However, I'm still struggeling with the nested transactions. Currently the whole transaction gets rolled back, if the nested transaction fails...

Thank you for your help!
Cheers

Here're some parts of my spring config

    <bean id="fadsDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
        <property name="uniqueResourceName" value="FADSXADBMS"/>
        <property name="xaDataSourceClassName" value="com.microsoft.sqlserver.jdbc.SQLServerXADataSource"/>
        <property name="xaProperties">
            <props>
                <prop key="user">${fads.jdbc.username}</prop>
                <prop key="password">${fads.jdbc.password}</prop>
                <prop key="serverName">localhost</prop>
            </props>
        </property>
        <property name="maxPoolSize" value="10"/>
    </bean>
...

    <bean id="fadsSessionFactory"
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="fadsDataSource" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.connection.useUnicode">true</prop>
                <prop key="hibernate.connection.charSet">utf8</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
                <prop key="hibernate.connection.autocommit">false</prop>
                <prop key="hibernate.connection.release_mode">after_transaction</prop>
                <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</prop>
                <prop key="hibernate.transaction.manager_lookup_class">com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup</prop>
                <prop key="javax.persistence.transactionType">jta</prop>
                <prop key="atomikos.transaction.timeout">300000</prop>
            </props>
        </property>
...

    <tx:advice id="feedProcessorTransactorTxAdvice" transaction-manager="fadsTransactionManager">
        <tx:attributes>
            <tx:method name="process*" propagation="REQUIRES_NEW" rollback-for="FadsException"/>
        </tx:attributes>
    </tx:advice>

    <tx:advice id="jobProcessorTxAdvice" transaction-manager="fadsTransactionManager">
        <tx:attributes>
            <tx:method name="process*" propagation="NESTED" rollback-for="FadsException"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="feedProcessorTransactorMethods" expression="execution(* au.com.careerone.fads.ingester.common.FeedProcessingTransactor.*(..))"/>
        <aop:pointcut id="jobProcessorMethods" expression="execution(* au.com.careerone.fads.ingester.common.JobProcessor.*(..))"/>
    </aop:config>
...