Why does RowHandler run in its own transaction/commit?

[radtke]

Hi, I have a dao that retrieves a large number of FeedEntry objects from the DB using iBatis.
There is FeedManager class that takes each FeedEntry object and performs various other DB changes.

I use a rowHandler and getSqlMapClient().queryWithRowHandler to call the FeedManager for each row (FeedEntry).

The RowHandler is a bean and Spring sets its FeedManager bean (thought I've also tried various other things like inner classes).

When I run my unit test for this, the code that runs within the rowHandler commits while the rest of the DB statements that happen in the same method of the dao roll back. I would expect all DB changes to roll back.

For example

public void daoMethod() {

doSomeDbThing; //rollsback
doDBStuffWithRowHandler; //commits
doSomeDbthing2; //rollsback

}

Anyone know why the rowHandler stuff doesn't roll back?

thanks

[karldmoore]

Is it possible to see the test class, the code that isn't working and the applicationContext.xml. If you put it all in [ code] [ /code] tags, its sooooo much easier to read!

[radtke]

Here is the test case. GinoBaseTest sets the location of the applicationcontext

import gino.dao.ibatis.SqlMapBatchFeedEntryDao;
import gino.domain.Person;

public class BatchFeedEntryDaoTest extends GinoBaseTest {
   protected SqlMapBatchFeedEntryDao bfeDao;
    
    public void setFeedEntryDao(SqlMapBatchFeedEntryDao in) {
        this.bfeDao=in;
    }
    
    public void testBatchAdd() {
      //arguments currently don't do anything
      bfeDao.addNewFeedEntries("sis", "");
        
    }
}

appContext

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd">

    <bean id="feedMan" class="gino.bus.FeedManager" autowire="byType">
      </bean>
    
    <bean id="eventMan" class="gino.bus.EventManager">
        <property name="eventEntryDao">
            <ref bean="eventEntryDao"/>
        </property>
    </bean>

      <bean id="personManager" class="gino.bus.PersonManager" autowire="byType">
      
    </bean>
    
    <bean id="feedEntryValidator" class="gino.validate.FeedEntryValidator"/>


    
     <bean id="personMatcher" class="gino.dao.ibatis.SqlMapPersonMatcher">
      <property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>


	<!-- ******************Data Access Objects******************-->
    <bean id="batchFeedEntryDao" class="gino.dao.ibatis.SqlMapBatchFeedEntryDao" autowire="byType">
    </bean>
    
    <bean id="batchAddRowHandler" class="gino.dao.ibatis.BatchAddRowHandler" autowire="byType"/>
  
    <bean id="feedEntryDao" class="gino.dao.ibatis.SqlMapFeedEntryDao">
    	<property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>

    <bean id="eventEntryDao" class="gino.dao.ibatis.SqlMapEventEntryDao">
        <property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>
    
       <bean id="personDao" class="gino.dao.ibatis.SqlMapPersonDao">
    	<property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>

    <bean id="historyRecordDao" class="gino.dao.ibatis.SqlMapHistoryRecordDao">
    	<property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>
    	<!-- **************End of DAOs**************-->


	<!-- ************* Database and Transactions ************* -->

      <!-- use p6spy to log sql statements -->
      <bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource" >
      <!--destroy-method="close">   -->
 		<constructor-arg>
 			<ref local="targetDataSource"/>
 		</constructor-arg>
  	 </bean>
    
 
  <!-- For use with methods that need to commit changes to the DB without
  affecting the current transaction. Creates a new transaction that gets
  commited at the end of the method. -->
    <tx:advice id="txAutoCommitAdvice" transaction-manager="transactionManager">
    <!-- the transactional semantics... -->
    <tx:attributes>
      <tx:method name="*" propagation="REQUIRES_NEW"/>
    </tx:attributes>
  </tx:advice>
  
  
  <aop:config>
  	<!--  apply the txAutoCommitAdvice to some methods -->
     <aop:pointcut id="autoCommitMethods" expression="execution(* gino.dao.PersonDao.getNextHandle(..))"/>
    <aop:advisor advice-ref="txAutoCommitAdvice" pointcut-ref="autoCommitMethods"/>
   

  </aop:config>
  
	<!-- Transaction manager for a single JDBC DataSource -->
	<!-- (see dataAccessContext-jta.xml for an alternative) -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"/>
	</bean>

<bean id="targetDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"
	autowire-candidate="false"> 
 <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property> 
      <property name="url">
        <value>jdbc:oracle:thin:@acisora1:1521:secret</value>
      </property>
      <property name="username"><value>secret</value></property>
      <property name="password"><value>secret</value></property>
    </bean>
</beans>

The transactional aop stuff is new to me. In the above, I have the 'autoCommit' advice (basically creates a new transaction) apply to a single method in PersonDao, and the rest of the code runs in the same transaction




The rowHandler

ackage gino.dao.ibatis;

import gino.bus.FeedManager;
import com.ibatis.sqlmap.client.event.RowHandler;

public class BatchAddRowHandler implements RowHandler {
    
    /** Logger for this class and subclasses */
    protected final Log logger = LogFactory.getLog(getClass());
    
    public FeedManager feedManager;
    
    public FeedEntryValidator fev;

    public void handleRow(Object arg0) {
       FeedEntry newFe= (FeedEntry) arg0;
       
       logger.info("processing " + newFe.getSorId());
       
       Errors errors = new BindException(newFe,newFe.getClass().getName());
       fev.validate(newFe,errors);
       
       if(errors.getErrorCount() > 0) {
    
           logger.error("Failed validating feedentry for " + newFe.getSorId(),
                   (BindException) errors);
           //no more processing
           return;
       } 
       List<PersonIdentifier> ids = feedManager.addFeedEntryInteractive(newFe); 
       if (ids.size() > 1 ) {
           logger.warn("Multiple matches found for " + newFe.getSorId());
       } else {
           logger.debug("Matched " + ids.get(0) + " to " + newFe.getSorId());
       }
       
   
    }

    public void setFeedManager(FeedManager feedManager) {
        this.feedManager = feedManager;
    }

    public void setFev(FeedEntryValidator fev) {
        this.fev = fev;
    }

}

Here is the doa.

package gino.dao.ibatis;


import java.sql.SQLException;

public class SqlMapBatchFeedEntryDao extends SqlMapClientDaoSupport /* implements BatchFeedEntryDao*/ {

    /** Logger for this class and subclasses */
    protected final Log logger = LogFactory.getLog(getClass());
    
   

    PersonDao personDao;
    
    BatchAddRowHandler barHandler;

    public void setPersonDao(PersonDao personDao) {
        this.personDao = personDao;
    }

    public void setBarHandler(BatchAddRowHandler barHandler) {
        this.barHandler = barHandler;
    }
    
 
    public void addNewFeedEntries(String feedName, String limit) {
        
        //create a temp table to hold the id of the new people
         String prepTable = "sis_person_prep";
         String tempTable="new_" + feedName +"_entries";

         createTempTable(tempTable,"select externalid,titlegroup from feedentries where 1=0");
        
         //populate the table    
         String query = "insert into " + tempTable +
        " select distinct pt.externalid, pt.titlegroup " +
        " from " + prepTable + " pt " +
        " where 1=1 " + limit +
        " minus " +
        " select distinct pt.externalid, pt.titlegroup " +
        " from feedentries f join " + prepTable + " pt  on  " +
        " (f.externalid=pt.externalid and f.titlegroup=pt.titlegroup) " +
        " where f.feedshortname='"+feedName+"'";
         
         JdbcTemplate jt = new JdbcTemplate(this.getDataSource());
         jt.execute(query);

         //see how many new entries there are
         int rowCount = this.getTempTableRowCount(tempTable);
         logger.info("Found " + rowCount + " rows in " + tempTable);
  
        try {
            this.getSqlMapClient().queryWithRowHandler("getNewFeedEntries", barHandler );
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            //TODO use SQLStateSQLExceptionTranslator
            SQLStateSQLExceptionTranslator sqlET = new SQLStateSQLExceptionTranslator();
            throw sqlET.translate("batch add", "", e);
           
        } finally {
            dropTempTable(tempTable);
        }
        
        //--this doesn't commit during a unittest.
        // its just here for my sanity check
        logger.info("testing adding person");
        Person ptest = new Person();
        ptest.setHandle("abcd123");
        personDao.insertPerson(ptest);  
        
    }
    
    public void createTempTable(String name, String select) {
        JdbcTemplate jt = new JdbcTemplate(this.getDataSource());
        jt.execute("create global temporary table " + name + " on commit delete rows as " + select);
    }
    
    public void dropTempTable(String name) {
        JdbcTemplate jt = new JdbcTemplate(this.getDataSource());
        jt.execute("drop table " + name);
    }
    
    private int getTempTableRowCount(String name) {
        SimpleJdbcTemplate sjt = new SimpleJdbcTemplate(this.getDataSource());
        return sjt.queryForInt("select count(*) from " + name, (Object[])null);
    }

}

If you want FeedManager (and all its dependent doas) then I can post those as well - but my unit tests for FeedManager on its own (and on a facade that includes FeedManager) show that is rolls back changes after a unit test.



Any advice would be appreciated.

thanks

[radtke]

I notice I was mistakenly using getSqlMapClient instead of getSqlMapClientTemplate.

I made the change but it did not affect the results

[karldmoore]

Are you actually sure you're running within a transaction at all? The current AOP expression doesn't seem to match the code; execution(* gino.dao.PersonDao.getNextHandle(..)). How do you know the code is doing a commit?

[radtke]

Thanks for the help karldmoore.


I assumed it was running in a transaction because my tests extend 'AbstractTransactionalDataSourceSpringContextTests ' and in the log for my tests that work as expected, and the above ones where it doesn't - I see

2007-03-02 14:58:20,045 INFO [gino.test.BatchFeedManagerTest] - Began transaction (1): transaction manager [org.springframework.jdbc.datasource.DataSourceTransactionManager@1f7708]; default rollback = true
[logging from test hapening]
2007-03-02 14:59:04,758 INFO [gino.test.BatchFeedManagerTest] - Rolled back transaction after test execution

I know that the code is committing because I can see the DB changes after the unit test runs.

I'll try playing with my transaction stuff to see what shows up.

Intrestingly if I instead of processing per row, I return a list of to FeedEntries to a class that calls SqlMapBatchFeedEntrayDao and do the same processing there, then the commit doesn't happen