Announcement Announcement Module
No announcement yet.
Why does RowHandler run in its own transaction/commit? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Why does RowHandler run in its own transaction/commit?

    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?


  • #2
    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!


    • #3
      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) {
          public void testBatchAdd() {
            //arguments currently don't do anything
            bfeDao.addNewFeedEntries("sis", "");
      <?xml version="1.0" encoding="UTF-8"?>
      <beans xmlns=""
          <bean id="feedMan" class="gino.bus.FeedManager" autowire="byType">
          <bean id="eventMan" class="gino.bus.EventManager">
              <property name="eventEntryDao">
                  <ref bean="eventEntryDao"/>
            <bean id="personManager" class="gino.bus.PersonManager" autowire="byType">
          <bean id="feedEntryValidator" class="gino.validate.FeedEntryValidator"/>
           <bean id="personMatcher" class="gino.dao.ibatis.SqlMapPersonMatcher">
            <property name="sqlMapClient" ref="sqlMapClient"/>
      	<!-- ******************Data Access Objects******************-->
          <bean id="batchFeedEntryDao" class="gino.dao.ibatis.SqlMapBatchFeedEntryDao" autowire="byType">
          <bean id="batchAddRowHandler" class="gino.dao.ibatis.BatchAddRowHandler" autowire="byType"/>
          <bean id="feedEntryDao" class="gino.dao.ibatis.SqlMapFeedEntryDao">
          	<property name="sqlMapClient" ref="sqlMapClient"/>
          <bean id="eventEntryDao" class="gino.dao.ibatis.SqlMapEventEntryDao">
              <property name="sqlMapClient" ref="sqlMapClient"/>
             <bean id="personDao" class="gino.dao.ibatis.SqlMapPersonDao">
          	<property name="sqlMapClient" ref="sqlMapClient"/>
          <bean id="historyRecordDao" class="gino.dao.ibatis.SqlMapHistoryRecordDao">
          	<property name="sqlMapClient" ref="sqlMapClient"/>
          	<!-- **************End of DAOs**************-->
      	<!-- ************* Database and Transactions ************* -->
            <!-- use p6spy to log sql statements -->
            <bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource" >
            <!--destroy-method="close">   -->
       			<ref local="targetDataSource"/>
        <!-- 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:method name="*" propagation="REQUIRES_NEW"/>
        	<!--  apply the txAutoCommitAdvice to some methods -->
           <aop:pointcut id="autoCommitMethods" expression="execution(* gino.dao.PersonDao.getNextHandle(..))"/>
          <aop:advisor advice-ref="txAutoCommitAdvice" pointcut-ref="autoCommitMethods"/>
      	<!-- 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 id="targetDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"
       <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property> 
            <property name="url">
            <property name="username"><value>secret</value></property>
            <property name="password"><value>secret</value></property>
      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;
   "processing " + newFe.getSorId());
             Errors errors = new BindException(newFe,newFe.getClass().getName());
             if(errors.getErrorCount() > 0) {
                 logger.error("Failed validating feedentry for " + newFe.getSorId(),
                         (BindException) errors);
                 //no more processing
             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());
               //see how many new entries there are
               int rowCount = this.getTempTableRowCount(tempTable);
     "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 {
              //--this doesn't commit during a unittest.
              // its just here for my sanity check
    "testing adding person");
              Person ptest = new Person();
          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.

      Last edited by radtke; Feb 22nd, 2007, 01:07 PM.


      • #4
        I notice I was mistakenly using getSqlMapClient instead of getSqlMapClientTemplate.

        I made the change but it did not affect the results


        • #5
          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?


          • #6
            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 [o[email protected]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