Announcement Announcement Module
Collapse
No announcement yet.
SQLServer Express: transactionid changes inside a transaction, commit is wrong Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLServer Express: transactionid changes inside a transaction, commit is wrong

    Hi all,
    we have a webapplication made with JDK 1.4, Struts 1.3.10, Spring 2.0.6, and iBatis 2.3.0 for data mapping. It runs on Tomcat (5.0, 5.5 or 6.0, it's not important).
    The application is derived from the jpetstore example.

    We have different layers:
    • Struts action for managing MVC
    • a layer of business logic spring beans with declarative transaction management
    • a layer of dao for access DB using iBatis
    Then we have a manager (SqlManager) which permits to manage transactions (it has 3 methods: startTransaction, commitTransaction, rollbackTransaction) and to do generic queries without implementing the layers of managers and dao (get* for read record, call* from call stored procedures, update* from insert/update/delete operations). This object uses a SqlDaoJdbc class that uses TransactionManager and DataSource injected from Spring configuration and create a JdbcTemplate object for run queries.

    The spring beans declarations are

    Code:
    <bean id="sqlDao"
    
    class="it.db.dao.jdbc.SqlDaoJdbc">
    
    <property name="dataSource" ref="dataSource" />
    
    <property name="transactionManager" ref="transactionManager" />
    
    </bean>
    
    <bean id="sqlManagerTransactionProxy"
    
    class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
    
    abstract="true">
    
    <property name="transactionManager" ref="transactionManager" />
    
    <property name="transactionAttributes">
    
    <props>
    
    <prop key="get*">PROPAGATION_SUPPORTS</prop>
    
    <prop key="call*">PROPAGATION_SUPPORTS</prop>
    
    <prop key="update*">PROPAGATION_MANDATORY</prop>
    
    </props>
    
    </property>
    
    </bean>
    
    
    <bean id="sqlManager" parent="sqlManagerTransactionProxy">
    
    <property name="target">
    
    <bean class="it.bl.SqlManager">
    
    <property name="sqlDao" ref="sqlDao" />
    
    </bean>
    
    </property>
    
    </bean>
    
    [...]
    
     <bean id="oneManagerTransactionProxy"
      class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
      abstract="true">
      <property name="transactionManager" ref="transactionManager" />
      <property name="transactionAttributes">
       <props>
        <prop key="get*">PROPAGATION_SUPPORTS</prop>
       </props>
      </property>
     </bean>
    
     <bean id="oneManager"
      parent="oneManagerTransactionProxy">
      <property name="target">
       <bean class="it.bl.OneManager">
    
    [...]
    
       </bean>
      </property>
     </bean>

    We have a function that open a transaction (using SqlManager bean, startTransaction method), reads and iterates on rows selected from a table, and then executes several inserts/updates; at the end of the iterations it commits the transaction (using SqlManager bean).
    The queries are extracted using generic functionality of SqlManager bean (getObject, getRecord, getRecords...) and using spring beans (managers) obtained using ApplicationContext.getBean.

    With Oracle it's ok, if we read 3 rows initially we create/update 3 rows in another table.
    With SQL Server Express 2005 or 2008 we create/update only the last record! If we see the log, there isn't any error, we trace all the inserts/update of all 3 records, but only the last is persistent on db and the first 2 are lost....

    The methods for querying database on SqlManager are PROPAGATION_SUPPORTS, and also the method in the manager bean that read the every record inside iteration (it extracts the detail to use for inserts/updates).
    Using a Sql profiler we noted that when we call the first business logic spring bean (manager bean with declarative transaction management) the connection is maintained but the transactionId is changed, so the next operations (select, insert, update) use new transaction id, and at the end the commit operation uses (I think) the last transaction id so the first 2 rows are lost and the last is committed. It's not a problem on bean declaration because if we use another spring bean before the call that changes the transaction id, the change is on this new call (so the transaction id changes with the first call to a spring bean).

    why?

    Thanks a lot,

    sselda
Working...
X