Spring 1.2 + iBatis 2.1.0 = Transactions dont work !

[tex]

I've developed many sites using Spring and Hibernate but due to some problems with lazy loading and hibernate session management I've decide to try iBatis SQL Mapping (successfully used some time ago into a little project without Spring transaction management).

Surprise: transactions dont work !!!

The problem appear when the dao layer throws an exception: no rollback at all !

The 'scenario' is this:

1. UserManagerImpl (under Spring transaction management) call a dao method to update a user.

2. Dao method update some user database columns, deletes all user roles and then throws an SQLException

3. The UserManagerImpl catch the dao SQLException and throws a UserNotUpdatedException

4. The database user columns results updated and the user's roles deleted (no rollback at all !!!)

Here my configuration files:

Tomcat 5.5 Dbcp configuration (jndi written also into web.xml):
================================================== ==

<Resource name="jdbc/webDS"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSour ceFactory"
username="sysdba"
password="masterkey"
driverClassName="org.firebirdsql.jdbc.FBDriver"
url="jdbc:firebirdsql:localhost:cmg-orders"
maxWait="10000"
removeAbandoned="true"
maxActive="40"
maxIdle="4"
minIdle="4"
initialSize="4"
removeAbandonedTimeout="60"
logAbandoned="true"/>

sql map configuration:
=================

<sqlMapConfig>
<settings enhancementEnabled="true"
lazyLoadingEnabled="true"
useStatementNamespaces="true" />

<sqlMap resource="cmg/orders/maps/ApplicationUser.xml"/>
<sqlMap resource="cmg/orders/maps/ApplicationRole.xml"/>
<sqlMap resource="cmg/orders/maps/ApplicationGroup.xml"/>
</sqlMapConfig>

Spring application context configuration:

<beans>
<bean id="webDS" class="org.springframework.jndi.JndiObjectFactoryB ean">
<property name="jndiName">
<value>java:comp/env/jdbc/webDS</value>
</property>
</bean>

<bean id="sqlMapClientWeb" class="org.springframework.orm.ibatis.SqlMapClient FactoryBean">
<property name="configLocation"><value>WEB-INF/sql-map-config-web.xml</value></property>
<property name="dataSource"><ref bean="webDS"/></property>
</bean>

<bean id="transactionManagerWeb" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource"><ref local="webDS"/></property>
</bean>

<bean id="userDAO" class="cmg.orders.dao.ibatis.UserDAO">
<property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
</bean>

<bean id="userManagerTarget" class="cmg.orders.service.impl.UserManagerImpl">
<property name="userDAO">
<ref bean="userDAO"/>
</property>
</bean>

<bean id="userManager" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean">
<property name="transactionManager">
<ref bean="transactionManagerWeb"/>
</property>
<property name="target">
<ref bean="userManagerTarget"/>
</property>
<property name="transactionAttributes">
<props>
<prop key="*">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>
</beans>

I've tried to set rollback rule into Spring configuration file:

<prop key="*">PROPAGATION_REQUIRED,-UserNotUpdatedException</prop>

but this dont resolve the problem !!!

Someone can help me ?

[Juergen Hoeller]

Try to turn on debug logging for the relevant packages (org.springframework.transaction, org.springframework.orm). This should give you pretty detailed information on what's going in your transaction, in particular whether an actual rollback is performed.

Which database are you running against? The problem could also be caused on the database side, for example with MySQL and the default MyISAM table manager (which doesn't support transactions). But let's see what Spring's debug logging shows first.

Juergen

[tex]

Thanks Juergen for your reply.

I tried to enable debug for the relevant Spring packages into my log4j.properties:

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=/home/tex/prj/cmg/build/web/WEB-INF/cmg-orders.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}\:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ##
log4j.rootLogger=debug, file, stdout
log4j.logger.com.ibatis=debug
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=debug
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=debug
log4j.logger.java.sql.Connection=debug
log4j.logger.java.sql.Statement=debug
log4j.logger.java.sql.PreparedStatement=debug
log4j.logger.java.sql.ResultSet=debug

log4j.logger.org.springframework.transaction=debug
log4j.logger.org.springframework.orm=debug

but I dont see any significant message about transaction management...

08:39:17,382 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,385 DEBUG Connection:42 - {conn-100030} Connection
08:39:17,390 DEBUG PreparedStatement:48 - {pstm-100031} PreparedStatement:      select * from application_role where id = ?   
08:39:17,392 DEBUG PreparedStatement:49 - {pstm-100031} Parameters: [1]
08:39:17,394 DEBUG PreparedStatement:50 - {pstm-100031} Types: [java.lang.Long]
08:39:17,400 DEBUG ResultSet:41 - {rset-100032} ResultSet
08:39:17,403 DEBUG ResultSet:61 - {rset-100032} Header: [id, role_name, description]
08:39:17,405 DEBUG ResultSet:65 - {rset-100032} Result: [1, admin, Amministrazione]
08:39:17,408 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,411 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,414 DEBUG Connection:42 - {conn-100033} Connection
08:39:17,421 DEBUG PreparedStatement:48 - {pstm-100034} PreparedStatement:      select * from application_group where id = ?   
08:39:17,424 DEBUG PreparedStatement:49 - {pstm-100034} Parameters: [1]
08:39:17,426 DEBUG PreparedStatement:50 - {pstm-100034} Types: [java.lang.Long]
08:39:17,430 DEBUG ResultSet:41 - {rset-100035} ResultSet
08:39:17,433 DEBUG ResultSet:61 - {rset-100035} Header: [id, description]
08:39:17,435 DEBUG ResultSet:65 - {rset-100035} Result: [1, Agenti]
08:39:17,438 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,499 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,501 DEBUG Connection:42 - {conn-100036} Connection
08:39:17,508 DEBUG PreparedStatement:48 - {pstm-100037} PreparedStatement:      select * from agent where id = ?   
08:39:17,510 DEBUG PreparedStatement:49 - {pstm-100037} Parameters: [14]
08:39:17,512 DEBUG PreparedStatement:50 - {pstm-100037} Types: [java.lang.Long]
08:39:17,518 DEBUG ResultSet:41 - {rset-100038} ResultSet
08:39:17,521 DEBUG ResultSet:61 - {rset-100038} Header: [id, code, description, id, id, id]
08:39:17,523 DEBUG ResultSet:65 - {rset-100038} Result: [14, 34, XXXX YYYY, 14, 14, 14]
08:39:17,526 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,530 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,532 DEBUG Connection:42 - {conn-100039} Connection
08:39:17,538 DEBUG PreparedStatement:48 - {pstm-100040} PreparedStatement:      select * from customer where id = ?   
08:39:17,541 DEBUG PreparedStatement:49 - {pstm-100040} Parameters: [0]
08:39:17,543 DEBUG PreparedStatement:50 - {pstm-100040} Types: [java.lang.Long]
08:39:17,553 DEBUG ResultSet:41 - {rset-100041} ResultSet
08:39:17,601 DEBUG ResultSet:61 - {rset-100041} Header: [id, code, description_1, description_2, address, city, zip_code, address, state, country, phone, fax, address, email, web_site, vat_number, fiscal_code, abi, abi_description, cab, cab_description, discount_class_code, price_list_code, status, locked, port_id, vector_id, default_destination_id, payment_id, agent_id, id, id, id]
08:39:17,603 DEBUG ResultSet:65 - {rset-100041} Result: [0, 0,  ,  ,  ,  ,  ,  ,  ,  ,  ,  ,  ,  ,  ,  ,  , 0,  , 0,  , 0, 0,  , 0, 0, 0, null, 0, 0, 0, 0, 0]
08:39:17,606 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,610 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,612 DEBUG Connection:42 - {conn-100042} Connection
08:39:17,618 DEBUG PreparedStatement:48 - {pstm-100043} PreparedStatement:      update application_user set        id = ?,       user_name = ?,       pwd = ?,       first_name = ?,       last_name = ?,       email = ?,       status = ?,       customer_id = ?,        agent_id = ?     where id = ?   
08:39:17,621 DEBUG PreparedStatement:49 - {pstm-100043} Parameters: [7, tex, , Gianluca 11, Tessarolo 11, tex@_xx__nospam_xx__ting.it, , 0, 14, 7]
08:39:17,623 DEBUG PreparedStatement:50 - {pstm-100043} Types: [java.lang.Long, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.Long, java.lang.Long, java.lang.Long]
08:39:17,635 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,638 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,647 DEBUG Connection:42 - {conn-100044} Connection
08:39:17,655 DEBUG PreparedStatement:48 - {pstm-100045} PreparedStatement:      delete from application_user_role     where user_id = ?   
08:39:17,657 DEBUG PreparedStatement:49 - {pstm-100045} Parameters: [7]
08:39:17,696 DEBUG PreparedStatement:50 - {pstm-100045} Types: [java.lang.Long]
08:39:17,701 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,704 DEBUG DataSourceUtils:106 - Opening JDBC Connection
08:39:17,706 DEBUG Connection:42 - {conn-100046} Connection
08:39:17,712 DEBUG PreparedStatement:48 - {pstm-100047} PreparedStatement:          select gen_id(gen_application_user_role,1) from rdb$database     
08:39:17,714 DEBUG PreparedStatement:49 - {pstm-100047} Parameters: []
08:39:17,717 DEBUG PreparedStatement:50 - {pstm-100047} Types: []
08:39:17,721 DEBUG ResultSet:41 - {rset-100048} ResultSet
08:39:17,724 DEBUG ResultSet:61 - {rset-100048} Header: [GEN_ID]
08:39:17,726 DEBUG ResultSet:65 - {rset-100048} Result: [150]
08:39:17,745 DEBUG PreparedStatement:48 - {pstm-100049} PreparedStatement:           insert into application_user_role (       id,       user_id,       role_id     ) values (       ?,       ?,       ?     )   
08:39:17,748 DEBUG PreparedStatement:49 - {pstm-100049} Parameters: [150, 7, 10000]
08:39:17,750 DEBUG PreparedStatement:50 - {pstm-100049} Types: [java.lang.Long, java.lang.Long, java.lang.Long]
08:39:17,756 DEBUG DataSourceUtils:271 - Closing JDBC Connection
08:39:17,759  WARN UserManagerImpl:78 - User not updated, probably due to referential constraints...:   
--- The error occurred in cmg/orders/maps/ApplicationRole.xml.  
--- The error occurred while applying a parameter map.  
--- Check the ApplicationRole.insertUserRole-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544466. violation of FOREIGN KEY constraint "APPL_USER_ROLE_ROLE_FK" on table "APPLICATION_USER_ROLE"

What's wrong ?

Seems that no transaction management was enabled (the user roles are deleted at the end of the process !)

[tex]

... I forget to answer your main question...

the underlying database is FireBird SQL 1.5.2 with JayBird 1.5.5 JDBC Driver...

[Juergen Hoeller]

Yes, it looks like your transaction demarcation is not actually applied. Are you 100% sure you're going through the transasctional UserManager proxy for that operation? The behavior you're seeing is what I would expect when talking to the DAO directly, bypassing the transactional proxy...

Juergen

[tex]

Hi Juergen,

thanks for your reply,

I'm 100% sure I'm going through the transactional UserManager proxy.

See all my configurations:

1. contex.xml (database pool under dbcp)
==================================

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/cmg">
  <Logger className="org.apache.catalina.logger.FileLogger" 
                prefix="cmg." suffix=".log" 
                timestamp="true"/>
  <Realm className="org.apache.catalina.realm.JDBCRealm"
               connectionName="sysdba" connectionPassword="masterkey" 
               connectionURL="jdbc&#58;firebirdsql&#58;localhost&#58;cmg-orders" debug="99" 
               driverName="org.firebirdsql.jdbc.FBDriver" roleNameCol="ROLE_NAME" 
               userCredCol="PWD" userNameCol="USER_NAME" 
               userRoleTable="V_USER_ROLE" userTable="APPLICATION_USER"/>
  <Resource name="jdbc/webDS"
            auth="Container"
            type="javax.sql.DataSource"
            factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
            username="sysdba"
            password="masterkey"
            driverClassName="org.firebirdsql.jdbc.FBDriver"
            url="jdbc&#58;firebirdsql&#58;localhost&#58;cmg-orders"
            maxWait="10000"
            removeAbandoned="true"
            maxActive="40"
            maxIdle="4"
            minIdle="4"
            initialSize="4"
            removeAbandonedTimeout="60"
            logAbandoned="true"/>
  <Resource name="jdbc/erpDS"
            auth="Container"
            type="javax.sql.DataSource"
            factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
            username="sysdba"
            password="masterkey"
            driverClassName="org.firebirdsql.jdbc.FBDriver"
            url="jdbc&#58;firebirdsql&#58;localhost&#58;cmg-erp"
            maxWait="10000"
            removeAbandoned="true"
            maxActive="10"
            maxIdle="2"
            minIdle="2"
            initialSize="2"
            removeAbandonedTimeout="60"
            logAbandoned="true"/>
</Context>

2. applicationContext.xml
====================

<beans>
  <bean id="webDS" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName">
      <value>java&#58;comp/env/jdbc/webDS</value>
    </property>
  </bean>

  <bean id="sqlMapClientWeb" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="configLocation"><value>WEB-INF/sql-map-config-web.xml</value></property>
    <property name="dataSource"><ref bean="webDS"/></property>
  </bean>

  <bean id="transactionManagerWeb" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
     <property name="dataSource"><ref local="webDS"/></property>
  </bean>
  
  <bean id="erpDS" class="org.springframework.jndi.JndiObjectFactoryBean"> 
    <property name="jndiName"> 
      <value>java&#58;comp/env/jdbc/erpDS</value>
    </property>
  </bean>
  
  <bean id="sqlMapClientErp" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    <property name="configLocation"><value>WEB-INF/sql-map-config-erp.xml</value></property>
    <property name="dataSource"><ref bean="erpDS"/></property>
  </bean>
     
  <bean id="transactionManagerErp" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
     <property name="dataSource"><ref local="erpDS"/></property>
  </bean>

  <bean id="userDAO" class="cmg.orders.dao.ibatis.UserDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>

  <bean id="lookupDAO" class="cmg.orders.dao.ibatis.LookupDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>

  <bean id="webOrderDAO" class="cmg.orders.dao.ibatis.WebOrderDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>

  <bean id="priceListAgentDAO" class="cmg.orders.dao.ibatis.PriceListAgentDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>
  
  <bean id="priceListDateDAO" class="cmg.orders.dao.ibatis.PriceListDateDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>
  
  <bean id="productSupplyDAO" class="cmg.orders.dao.ibatis.ProductSupplyDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>

  <bean id="orderTypeDAO" class="cmg.orders.dao.ibatis.OrderTypeDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>

  <bean id="productFamilyDAO" class="cmg.orders.dao.ibatis.ProductFamilyDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>
  
  <bean id="configDAO" class="cmg.orders.dao.ibatis.ConfigDAO">
    <property name="sqlMapClient"><ref local="sqlMapClientWeb"/></property>
  </bean>
  
  <bean id="userManagerTarget" class="cmg.orders.service.impl.UserManagerImpl">
    <property name="userDAO">
      <ref bean="userDAO"/>
    </property>
    <property name="lookupMgr">
      <ref bean="lookupManagerTarget"/>
    </property>
  </bean>
 
  <bean id="lookupManagerTarget" class="cmg.orders.service.impl.LookupManagerImpl">
    <property name="lookupDAO">
      <ref bean="lookupDAO"/>
    </property>
  </bean>
  
  <bean id="webOrderManagerTarget" class="cmg.orders.service.impl.WebOrderManagerImpl">
    <property name="webOrderDAO">
      <ref bean="webOrderDAO"/>
    </property>
  </bean>
  
  <bean id="priceListAgentManagerTarget" class="cmg.orders.service.impl.PriceListAgentManagerImpl">
    <property name="priceListAgentDAO">
      <ref bean="priceListAgentDAO"/>
    </property>
  </bean>
  
  <bean id="priceListDateManagerTarget" class="cmg.orders.service.impl.PriceListDateManagerImpl">
    <property name="priceListDateDAO">
      <ref bean="priceListDateDAO"/>
    </property>
  </bean>
  
  <bean id="productSupplyManagerTarget" class="cmg.orders.service.impl.ProductSupplyManagerImpl">
    <property name="productSupplyDAO">
      <ref bean="productSupplyDAO"/>
    </property>
  </bean>
  
  <bean id="orderTypeManagerTarget" class="cmg.orders.service.impl.OrderTypeManagerImpl">
    <property name="orderTypeDAO">
      <ref bean="orderTypeDAO"/>
    </property>
  </bean>
  
  <bean id="productFamilyManagerTarget" class="cmg.orders.service.impl.ProductFamilyManagerImpl">
    <property name="productFamilyDAO">
      <ref bean="productFamilyDAO"/>
    </property>
  </bean>
  
  <bean id="configManagerTarget" class="cmg.orders.service.impl.ConfigManagerImpl">
    <property name="configDAO">
      <ref bean="configDAO"/>
    </property>
  </bean>
  
  <bean id="userManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="userManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="lookupManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="lookupManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="webOrderManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="webOrderManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="priceListAgentManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="priceListAgentManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="priceListDateManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="priceListDateManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="productSupplyManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="productSupplyManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="orderTypeManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="orderTypeManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  
  <bean id="productFamilyManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="productFamilyManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>

  <bean id="configManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="configManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
</beans>

3. sql-map-config-web.xml (iBatis map configuration)
===========================================

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http&#58;//www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings enhancementEnabled="true"
              lazyLoadingEnabled="true"
              useStatementNamespaces="true" />
              
    <sqlMap resource="cmg/orders/maps/Agent.xml"/>
    <sqlMap resource="cmg/orders/maps/ApplicationUser.xml"/>
    <sqlMap resource="cmg/orders/maps/ApplicationRole.xml"/>
    <sqlMap resource="cmg/orders/maps/ApplicationGroup.xml"/>
    <sqlMap resource="cmg/orders/maps/Balance.xml"/>
    <sqlMap resource="cmg/orders/maps/Config.xml"/>
    <sqlMap resource="cmg/orders/maps/Customer.xml"/>
    <sqlMap resource="cmg/orders/maps/Destination.xml"/>
    <sqlMap resource="cmg/orders/maps/Discount.xml"/>
    <sqlMap resource="cmg/orders/maps/OrderType.xml"/>
    <sqlMap resource="cmg/orders/maps/Payment.xml"/>
    <sqlMap resource="cmg/orders/maps/Port.xml"/>
    <sqlMap resource="cmg/orders/maps/PriceList.xml"/>
    <sqlMap resource="cmg/orders/maps/PriceListAgent.xml"/>
    <sqlMap resource="cmg/orders/maps/PriceListDate.xml"/>
    <sqlMap resource="cmg/orders/maps/Product.xml"/>
    <sqlMap resource="cmg/orders/maps/ProductFamily.xml"/>
    <sqlMap resource="cmg/orders/maps/ProductGroup.xml"/>
    <sqlMap resource="cmg/orders/maps/ProductSubGroup.xml"/>
    <sqlMap resource="cmg/orders/maps/ProductSupply.xml"/>
    <sqlMap resource="cmg/orders/maps/Vector.xml"/>
    <sqlMap resource="cmg/orders/maps/WebOrder.xml"/>
    <sqlMap resource="cmg/orders/maps/WebOrderRow.xml"/>
    <sqlMap resource="cmg/orders/maps/WebOrderUpdate.xml"/>
</sqlMapConfig>

4. sql-map-config-erp.xml (iBatis map configuration)
===========================================

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http&#58;//www.ibatis.com/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings enhancementEnabled="true" 
              lazyLoadingEnabled="true" 
              useStatementNamespaces="true" />
                                
    <sqlMap resource="cmg/orders/maps/ErpOrder.xml"/>
    <sqlMap resource="cmg/orders/maps/ErpOrderRow.xml"/>
</sqlMapConfig>

5. Startup log
===========

10:01:39,185  INFO ContextLoader:146 - Root WebApplicationContext: initialization started
10:01:40,253  INFO [/cmg]:633 - Loading Spring root WebApplicationContext
10:01:40,481  INFO XmlBeanDefinitionReader:132 - Loading XML bean definitions from ServletContext resource [/WEB-INF/applicationContext.xml]
10:01:41,018  INFO XmlWebApplicationContext:90 - Bean factory for application context [Root WebApplicationContext]: org.springframework.beans.factory.support.DefaultListableBeanFactory defining beans [webDS,sqlMapClientWeb,transactionManagerWeb,erpDS,sqlMapClientErp,transactionManagerErp,userDAO,lookupDAO,webOrderDAO,priceListAgentDAO,priceListDateDAO,productSupplyDAO,orderTypeDAO,productFamilyDAO,configDAO,userManagerTarget,lookupManagerTarget,webOrderManagerTarget,priceListAgentManagerTarget,priceListDateManagerTarget,productSupplyManagerTarget,orderTypeManagerTarget,productFamilyManagerTarget,configManagerTarget,userManager,lookupManager,webOrderManager,priceListAgentManager,priceListDateManager,productSupplyManager,orderTypeManager,productFamilyManager,configManager]; root of BeanFactory hierarchy
10:01:41,052  INFO XmlWebApplicationContext:287 - 33 beans defined in application context [Root WebApplicationContext]
10:01:41,068  INFO CollectionFactory:61 - JDK 1.4+ collections available
10:01:41,112  INFO CollectionFactory:66 - Commons Collections 3.x available
10:01:41,127  INFO XmlWebApplicationContext:411 - Unable to locate MessageSource with name 'messageSource': using default [org.springframework.context.support.DelegatingMessageSource@d1a1ab]
10:01:41,141  INFO XmlWebApplicationContext:433 - Unable to locate ApplicationEventMulticaster with name 'applicationEventMulticaster': using default [org.springframework.context.event.SimpleApplicationEventMulticaster@cd2192]
10:01:41,157  INFO UiApplicationContextUtils:67 - No ThemeSource found for [Root WebApplicationContext]: using ResourceBundleThemeSource
10:01:41,177  INFO DefaultListableBeanFactory:262 - Pre-instantiating singletons in factory [org.springframework.beans.factory.support.DefaultListableBeanFactory defining beans [webDS,sqlMapClientWeb,transactionManagerWeb,erpDS,sqlMapClientErp,transactionManagerErp,userDAO,lookupDAO,webOrderDAO,priceListAgentDAO,priceListDateDAO,productSupplyDAO,orderTypeDAO,productFamilyDAO,configDAO,userManagerTarget,lookupManagerTarget,webOrderManagerTarget,priceListAgentManagerTarget,priceListDateManagerTarget,productSupplyManagerTarget,orderTypeManagerTarget,productFamilyManagerTarget,configManagerTarget,userManager,lookupManager,webOrderManager,priceListAgentManager,priceListDateManager,productSupplyManager,orderTypeManager,productFamilyManager,configManager]; root of BeanFactory hierarchy]
10:01:41,179  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'webDS'
10:01:41,339  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'sqlMapClientWeb'
10:01:43,957  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'transactionManagerWeb'
10:01:43,989  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'erpDS'
10:01:43,992  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'sqlMapClientErp'
10:01:44,076  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'transactionManagerErp'
10:01:44,078  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'userDAO'
10:01:44,144  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'lookupDAO'
10:01:44,174  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'webOrderDAO'
10:01:44,192  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'priceListAgentDAO'
10:01:44,209  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'priceListDateDAO'
10:01:44,236  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'productSupplyDAO'
10:01:44,255  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'orderTypeDAO'
10:01:44,272  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'productFamilyDAO'
10:01:44,288  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'configDAO'
10:01:44,310  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'userManagerTarget'
10:01:44,323  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'lookupManagerTarget'
10:01:44,339  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'webOrderManagerTarget'
10:01:44,357  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'priceListAgentManagerTarget'
10:01:44,372  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'priceListDateManagerTarget'
10:01:44,390  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'productSupplyManagerTarget'
10:01:44,410  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'orderTypeManagerTarget'
10:01:44,423  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'productFamilyManagerTarget'
10:01:44,437  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'configManagerTarget'
10:01:44,449  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'userManager'
10:01:44,504  INFO DefaultAopProxyFactory:59 - CGLIB2 available: proxyTargetClass feature enabled
10:01:44,594 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,727  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'lookupManager'
10:01:44,729 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,786  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'webOrderManager'
10:01:44,789 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,805  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'priceListAgentManager'
10:01:44,810 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,835  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'priceListDateManager'
10:01:44,837 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,857  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'productSupplyManager'
10:01:44,860 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,876  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'orderTypeManager'
10:01:44,879 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,897  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'productFamilyManager'
10:01:44,900 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,913  INFO DefaultListableBeanFactory:218 - Creating shared instance of singleton bean 'configManager'
10:01:44,920 DEBUG NameMatchTransactionAttributeSource:100 - Adding transactional method [*] with attribute [PROPAGATION_REQUIRED,ISOLATION_DEFAULT]
10:01:44,944  INFO ContextLoader:161 - Using context class [org.springframework.web.context.support.XmlWebApplicationContext] for root WebApplicationContext
10:01:44,946  INFO ContextLoader:171 - Root WebApplicationContext: initialization completed in 5761 ms

6. UserDAO.java code (with exception simulation...)
==========================================

public class UserDAO extends BaseDAO implements cmg.orders.dao.UserDAO {    
    public void insert(ApplicationUser user) throws SQLException {
        if (user != null) {
            getSqlMapClient().insert("ApplicationUser.insert", user);
            insertRoles(user);
            insertGroups(user);
        }
    }
    
    public void update(ApplicationUser user) throws SQLException {
        if (user != null) {
            getSqlMapClient().update("ApplicationUser.update", user);
            updateRoles(user);

            // Exception simulation code:
            ((ApplicationGroup) user.getGroups().get(0)).setId(new Long(10000));

            updateGroups(user);
        }
    }

    ...

7. UserManagerImpl.java code
=========================

public class UserManagerImpl extends BaseManager implements UserManager {
            
    // DAO
    private UserDAO userDAO = null;
    
    public UserDAO getUserDAO() {
        return userDAO;
    }

    public void setUserDAO(UserDAO userDAO) {
        this.userDAO = userDAO;
    }    

    // Lookup Manager
    private LookupManager lookupMgr = null;
    
    public LookupManager getLookupMgr() {
        return lookupMgr;
    }

    public void setLookupMgr(LookupManager lookupMgr) {
        this.lookupMgr = lookupMgr;
    }
    
    public void save(ApplicationUser user) 
    throws UserNotInsertedException,
           UserNotUpdatedException,
           UserNotDeletedException {
        if (user != null) {
            if (user.getId() == null) {
                try {
                    userDAO.insert(user);
                } catch (SQLException se) {
                    log.warn("User not inserted, probably user already exists or due to referential constraints...: " + se.getMessage());
                    throw new UserNotInsertedException(se.getMessage());
                }
            } else {
                if (user.isDelete()) {
                    try {
                        userDAO.delete(user);
                    } catch (SQLException se) {
                        log.warn("User not deleted, probably due to referential constraints...: " + se.getMessage());
                        throw new UserNotDeletedException(se.getMessage());
                    }                    
                } else {
                    try {
                        userDAO.update(user);
                    } catch (SQLException se) {
                        log.warn("User not updated, probably due to referential constraints...: " + se.getMessage());
                        throw new UserNotUpdatedException(se.getMessage());
                    }
                }
            }
        }              
    }

    ...

7. Web layer calling manager code
============================

public class SaveUserAction extends BaseAction {
    public ActionForward execute(ActionMapping mapping, 
                                 ActionForm form,
                                 HttpServletRequest req, 
                                 HttpServletResponse res)
        throws IllegalAccessException, 
               InvocationTargetException,
               IdNotNumericException,
               CustomerNotFoundException,
               AgentNotFoundException,
               UserNotFoundException,
               RoleNotFoundException,
               GroupNotFoundException,
               UserNotInsertedException,
               UserNotUpdatedException,
               UserNotDeletedException {
        
        Log log                 = LogFactory.getLog(getClass());
        String param            = mapping.getParameter();
        UserManager userMgr     = (UserManager) getBean(BEAN_MANAGER_USER);
        LookupManager lookupMgr = (LookupManager) getBean(BEAN_MANAGER_LOOKUP);
        String stringId         = null;
        Long id                 = null;
        ApplicationUser user    = null;
        Agent agent             = null;
        Customer customer       = null;
        
        if (param != null && param.equalsIgnoreCase(PARAM_DELETE_MODE)) {
            // Delete mode
            stringId = req.getParameter("userId");
            try {
                id = new Long(stringId);
            } catch (Exception e) {
                log.error("SaveUserAction delete mode: user id '" + stringId + "' is not numeric !");
                throw new IdNotNumericException(e.getMessage());
            }
            user = lookupMgr.findUserById(id);
            userMgr.delete(user);
        } else {
            // Save mode
            DynaValidatorForm dvf = (DynaValidatorForm) form;
            
            // Get user object from the form
            user = (ApplicationUser) FormUtils.getFormValues(form, 
                                                             this, 
                                                             mapping,
                                                             req);            
            
            // Set roles
            userMgr.setUserRolesId(user, req.getParameterValues("roles"));
            
            // Set groups
            userMgr.setUserGroupsId(user, req.getParameterValues("groups"));
            
            // Set agent
            stringId = (String) dvf.get("agent");
            try {
                id = new Long(stringId);
            } catch (Exception e) {
                log.error("SaveUserAction save mode: agent id '" + stringId + "' is not numeric !");
                throw new IdNotNumericException(e.getMessage());
            }
            agent = lookupMgr.findAgentById(id);
            user.setAgent(agent);

            // Set user customer
            stringId = (String) dvf.get("customer");
            try {
                id = new Long(stringId);
            } catch (Exception e) {
                log.error("SaveUserAction save mode: customer id '" + stringId + "' is not numeric !");
                throw new IdNotNumericException(e.getMessage());
            }
            customer = lookupMgr.findCustomerById(id);
            user.setCustomer(customer);
            
            // Save user
            userMgr.save(user);            
        }
        
        return mapping.findForward(FORWARD_SUCCESS);
    }
}

Note: no code for programmatic transaction management at all into Web, Manager, DAO layers !!!

[tex]

Solved !

Reading the forum I've find equinox-ibatis project and I studied the sources and configuration files then I write the following updates in my code:

1. Into DAO layer sources now I use getSqlClientTemplate() instead of getSqlClient()

before

    ...
    getSqlMapClient().insert("ApplicationUser.insert", user);
    ...

now

    ...
    getSqlMapClientTemplate().insert("ApplicationUser.insert", user);
    ...

2. Used TransactionProxy interceptor on class definition instead on a target object and applied rollback rules for my own exceptions:

before

  <bean id="userManagerTarget" class="cmg.orders.service.impl.UserManagerImpl">
    <property name="userDAO">
      <ref bean="userDAO"/>
    </property>
  </bean>

  <bean id="userManager" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="target">
      <ref bean="userManagerTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>

now

  <bean id="txProxyTemplate" abstract="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManagerWeb"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>

  <bean id="userManager" parent="txProxyTemplate">
    <property name="target">
      <bean class="cmg.orders.service.impl.UserManagerImpl">
        <property name="userDAO"><ref bean="userDAO"/></property>
      </bean>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED,-UserNotInsertedException,-UserNotUpdatedException,-UserNotDeletedException</prop>
      </props>
    </property>
  </bean>

Now transactions works !