Announcement Announcement Module
Collapse
No announcement yet.
Another question about 'Transaction not rollbacked' on exception Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Another question about 'Transaction not rollbacked' on exception

    hello.. i've got a very strange problem because transactions are not rollbacking when encountered an exception
    - i'm using spring 1.2.5 with ibatis 2.1.5.582
    - i already tried using c3p0 and commons dbcp, also setting the corresponding autoCommitOnClose (c3p0) or defaultAutoCommit (DBCP) to false, but it didn't help

    this is my applicationContext.xml (i just switch between c3p0 and dbcp):

    Code:
    	<bean id="apacheDBCPDataSource" class="org.apache.commons.dbcp.datasources.SharedPoolDataSource">
    		<property name="connectionPoolDataSource">
    			<bean class="org.apache.commons.dbcp.cpdsadapter.DriverAdapterCPDS">
    		        <property name="driver"><value>${jdbc.driver}</value></property>
    		        <property name="url"><value>${jdbc.url}</value></property>
    		        <property name="user"><value>${jdbc.user}</value></property>
    		        <property name="password"><value>${jdbc.password}</value></property>
    			</bean>
    		</property>
    		<property name="maxActive"><value>0</value></property>
    		<property name="maxWait"><value>2000</value></property>
    		<property name="maxIdle"><value>10</value></property>
            <property name="defaultAutoCommit"><value>false</value></property>
    	</bean>
     
    	<bean id="c3p0dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    		<property name="driverClass"><value>${jdbc.driver}</value></property>
    		<property name="jdbcUrl"><value>${jdbc.url}</value></property>
    		<property name="user"><value>${jdbc.user}</value></property>
    		<property name="password"><value>${jdbc.password}</value></property>
    		<property name="acquireRetryAttempts"><value>50</value></property>
    		<property name="autoCommitOnClose"><value>false</value></property>
    		<property name="maxIdleTime"><value>300</value></property>
    	</bean>
     
    	<bean id="parentDao" abstract="true">
            <property name="dataSource"><ref bean="c3p0dataSource"/></property>
            <property name="sqlMapClient">
    		    <bean class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
    		        <property name="configLocation">
    		            <value>classpath:sql-map-config.xml</value>
    		        </property>
    		    </bean>
    		</property>  
    	</bean>
    	
        <bean id="parentManager" 
            class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true">
            <property name="transactionManager"><ref bean="ekatestTransactionManager"/></property>
            <property name="transactionAttributes">
                <props>
                    <prop key="*">PROPAGATION_REQUIRED</prop>
                </props>
            </property>
        </bean>        
    
        <bean id="ekatestTransactionManager" 
            class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource">
    			<ref bean="c3p0dataSource"/>
            </property>
        </bean>
    my application-servlet.xml:

    Code:
        <bean id="uwManager" parent="parentManager">
            <property name="target">
    		    <bean class="com.blabla.service.UwManager">
    		        <property name="uwDao">
    				    <bean class="com.blabla.dao.UwDao" parent="parentDao" /> 
    				</property>
    		    </bean>	
            </property>
        </bean>
    my UwDao.java:

    Code:
    public class UwDao extends SqlMapClientDaoSupport {
    
    	public void insertMst_production(Date prodDate, String spaj, int tahun, int premi, int prod_ke) {
    		Map params = new HashMap();
    		params.put("prodDate", prodDate);
    		params.put("spaj", spaj);
    		params.put("tahun", new Integer(tahun));
    		params.put("premi", new Integer(premi));
    		params.put("prod_ke", new Integer(prod_ke));
    		insert("insert.mst_production", params);
    	}
    
    	public void insertMst_detail_production(String spaj, Date prodDate,
    			String lsbs_id, String lsdbs_number, Integer prod_ke, Integer tahun, Integer premi,
    			Double msdb_premium, Double msdpr_discount, Double mspr_tsi) {
    		Map params = new HashMap();
    		params.put("spaj", spaj);
    		params.put("prodDate", prodDate);
    		params.put("lsbs_id", lsbs_id);
    		params.put("lsdbs_number", lsdbs_number);
    		params.put("tahun", tahun);
    		params.put("premi", premi);
    		params.put("prod_ke", prod_ke);
    		params.put("msdb_premium", msdb_premium);
    		params.put("msdpr_discount", msdpr_discount);
    		params.put("mspr_tsi", mspr_tsi);
    		insert("insert.mst_detail_production", params);
    	}
    
    	//.. and other methods..
    }
    my UwManager.java:

    Code:
    public class UwManager {
    	static Logger logger = Logger.getLogger(UwManager.class);
    	private UwDao uwDao;
    
    	public void setUwDao(UwDao uwDao) {
    		this.uwDao = uwDao;
    	}
    
    	public boolean transferToPayment(String reg_spaj, BindException errors, int bulanRK, List billInfo, Properties props, User currentUser){
    		try{
    			//.. other processes
    
    			String businessId = this.uwDao.selectBusinessId(reg_spaj);
    			
    			//.. other processes
    			
    			Premi premi = this.uwDao.selectPremiTertanggung(reg_spaj);
    
    			if(premi==null || premi.getNama_pemegang()==null){
    				errors.reject("payment.namaPemegangKosong");
    				return false;
    			}
    			
    			if(prosesProduksi(reg_spaj, errors, bulanRK, billInfo, props, currentUser, businessId)){
    				//.. other processes
    			}
    
    		}catch(Exception e){
    			errors.reject("payment.error", e.getMessage());
    			return false;
    		}
    	}
    
    	private boolean prosesProduksi(String reg_spaj, BindException errors, int bulanRK, List billInfo, Properties props, User currentUser, String businessId) throws Exception{
    		//.. other processes
    
    		Date rkDate = ((TopUp) this.uwDao.selectTopUp(reg_spaj, 1, 1, "desc").get(0)).getMspa_date_book();
    	
    		//.. other processes
    
    		this.uwDao.insertMst_production(prodDate, reg_spaj, 1, 1, 1);
    
    		//.. other processes
    
    		this.uwDao.insertMst_detail_production(reg_spaj, prodDate, tmp.get("LSBS_ID").toString(), 
    				tmp.get("LSDBS_NUMBER").toString(), new Integer(1), new Integer(1), new Integer(1), 
    				new Double(tmp.get("MSDB_PREMIUM").toString()),
    				new Double(tmp.get("MSDB_DISCOUNT").toString()), 
    				new Double(tmp.get("MSPR_TSI").toString()));
    
    		//.. other processes
    
    	}
    
    }
    in the method transferToPayment I have a nested method prosesProduksi, which executes 2 insert statement to the database, and in the second insert statement the insert failed.. the stack trace is in the next post...

    I noticed after the second insert and the java.sql.SQLException occured, it still invoked commit for the transaction on method transferToPayment, and not rolling back although an exception occured..
    can anyone help me with this?.. Please help me on this one.. thank you.

  • #2
    this is the error trace:

    Code:
    2005/12/02 03:44 - [DEBUG] (TransactionAspectSupport.java:198) - Getting transaction for method 'transferToPayment' in class [com.blabla.service.UwManager]
    2005/12/02 03:44 - [DEBUG] (BasicResourcePool.java:1083) - resource age is okay: com.mchange.v2.c3p0.impl.NewPooledConnection@1531164 ---> age: 469   max: 300000 [com.mchange.v2.resourcepool.BasicResourcePool@1df2964]
    2005/12/02 03:44 - [DEBUG] (BasicResourcePool.java:1161) - trace com.mchange.v2.resourcepool.BasicResourcePool@1df2964 [managed: 3, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1080876)
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100012} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100013} PreparedStatement:    select bla bla from bla bla 
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100013} Parameters: [19200500094]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100013} Types: [java.lang.String]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100015} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100016} PreparedStatement:    select bla bla from bla bla 
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100016} Parameters: [19200500094]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100016} Types: [java.lang.String]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100018} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100019} PreparedStatement:    select bla bla from bla bla 
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100019} Parameters: [1]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100019} Types: [java.lang.Integer]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100021} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100022} PreparedStatement:    select trunc(sysdate+?) from dual  
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100022} Parameters: [0]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100022} Types: [java.lang.Integer]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100024} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100025} PreparedStatement:    select bla bla from bla bla 
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100025} Parameters: [19200500094, 1, 1]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100025} Types: [java.lang.String, java.lang.Integer, java.lang.Integer]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100027} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100028} PreparedStatement:    select bla bla from bla bla 
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100028} Parameters: [15]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100028} Types: [java.lang.Integer]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100030} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100031} PreparedStatement:    INSERT INTO eka.mst_production               (lstb_id, reg_spaj, mspro_prod_ke, mspro_prod_date, msbi_tahun_ke,                msbi_premi_ke, mspro_jn_prod, lku_id, mspro_nilai_kurs,                mspro_beg_date, mspro_end_date, mspro_policy_cost, mspro_stamp,                lca_id, lwk_id, lsrg_id)      SELECT a.lstb_id, a.reg_spaj, ?, ?, ?, ?, 1, a.lku_id,             nvl(c.lkh_currency,0) lkh_currency, b.msbi_beg_date, b.msbi_end_date,             b.msbi_policy_cost, b.msbi_stamp, b.lca_id, b.lwk_id, b.lsrg_id        FROM eka.mst_policy a,             (SELECT *                FROM eka.mst_billing               WHERE msbi_tahun_ke = ? AND msbi_premi_ke = ?) b,             eka.lst_daily_currency c       WHERE a.reg_spaj = RPAD (?, 11, ' ')         AND a.reg_spaj = b.reg_spaj         AND a.lku_id = c.lku_id(+)  
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100031} Parameters: [1, 2005-07-31, 1, 1, 1, 1, 1920094]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100031} Types: [java.lang.Integer, java.sql.Date, java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.String]
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100032} Connection
    2005/12/02 03:44 - [DEBUG] (NewPooledConnection.java:316) - com.mchange.v2.c3p0.impl.NewPooledConnection@1531164 handling a throwable.
    java.sql.SQLException: Invalid column type
    //... omitted
    
    2005/12/02 03:44 - [DEBUG] (SqlUtils.java:83) - Attempted to convert SQLException to SQLException. Leaving it alone. [SQLState: null; errorCode: 17004]
    java.sql.SQLException: Invalid column type
    //... omitted
    
    2005/12/02 03:44 - [DEBUG] (DefaultConnectionTester.java:67) - Testing a Connection in response to an Exception:
    java.sql.SQLException: Invalid column type
    //... omitted
    
    2005/12/02 03:44 - [DEBUG] (TransactionAspectSupport.java:241) - Invoking commit for transaction on method 'transferToPayment' in class [com.blabla.service.UwManager]
    2005/12/02 03:44 - [DEBUG] (BasicResourcePool.java:1161) - trace com.mchange.v2.resourcepool.BasicResourcePool@1df2964 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1080876)
    2005/12/02 03:44 - [DEBUG] (TransactionAspectSupport.java:198) - Getting transaction for method 'selectDaftarSPAJ' in class [com.blabla.service.UwManager]
    2005/12/02 03:44 - [DEBUG] (BasicResourcePool.java:1083) - resource age is okay: com.mchange.v2.c3p0.impl.NewPooledConnection@4ca6b6 ---> age: 2015   max: 300000 [com.mchange.v2.resourcepool.BasicResourcePool@1df2964]
    2005/12/02 03:44 - [DEBUG] (BasicResourcePool.java:1161) - trace com.mchange.v2.resourcepool.BasicResourcePool@1df2964 [managed: 3, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1080876)
    2005/12/02 03:44 - [DEBUG] (ConnectionLogProxy.java:42) - {conn-100034} Connection
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:48) - {pstm-100035} PreparedStatement:    SELECT    SUBSTR (a.reg_spaj, 1, 2)          || '.'          || SUBSTR (a.reg_spaj, 3, 4)          || '.'          || SUBSTR (a.reg_spaj, 7) spaj_formatted,          a.reg_spaj,             SUBSTR (a.mspo_policy_no, 1, 2)          || '.'          || SUBSTR (a.mspo_policy_no, 3, 3)          || '.'          || SUBSTR (a.mspo_policy_no, 6, 4)          || '.'          || SUBSTR (a.mspo_policy_no, 10, 5) policy_formatted,          a.mspo_policy_no, a.lspd_id, a.lstb_id     FROM eka.mst_policy a    WHERE (a.lspd_id = ?)      AND (a.lstb_id = ?)  
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:49) - {pstm-100035} Parameters: [4, 1]
    2005/12/02 03:44 - [DEBUG] (PreparedStatementLogProxy.java:50) - {pstm-100035} Types: [java.lang.Integer, java.lang.Integer]
    2005/12/02 03:44 - [DEBUG] (ResultSetLogProxy.java:41) - {rset-100036} ResultSet
    2005/12/02 03:44 - [DEBUG] (ResultSetLogProxy.java:61) - {rset-100036} Header: [SPAJ_FORMATTED, REG_SPAJ, POLICY_FORMATTED, MSPO_POLICY_NO, LSPD_ID, LSTB_ID]
    2005/12/02 03:44 - [DEBUG] (ResultSetLogProxy.java:65) - {rset-100036} Result: [19.2005.00094, 19200500094, 19.040.2005.00022, 19040200500022, 4, 1]
    2005/12/02 03:44 - [DEBUG] (ResultSetLogProxy.java:65) - {rset-100036} Result: [02.2005.00112, 02200500112, 02.119.2005.00001, 02119200500001, 4, 1]
    2005/12/02 03:44 - [DEBUG] (ResultSetLogProxy.java:65) - {rset-100036} Result: [37.2005.00034, 37200500034, 37.124.2005.00002, 37124200500002, 4, 1]
    2005/12/02 03:44 - [DEBUG] (TransactionAspectSupport.java:241) - Invoking commit for transaction on method 'selectDaftarSPAJ' in class [com.blabla.service.UwManager]
    2005/12/02 03:44 - [DEBUG] (BasicResourcePool.java:1161) - trace com.mchange.v2.resourcepool.BasicResourcePool@1df2964 [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@1080876)
    2005/12/02 03:44 - [DEBUG] (DispatcherServlet.java:915) - Rendering view [org.springframework.web.servlet.view.JstlView: name 'payment'; URL [/WEB-INF/elions/uw/payment.jsp]] in DispatcherServlet with name 'spring'
    2005/12/02 03:44 - [DEBUG] (AbstractView.java:234) - Rendering view with name 'payment' with model {.. .. .. }], org.springframework.validation.BindException.cmd=org.springframework.validation.BindException: BindException: 2 errors; Error in object 'cmd': codes=[payment.error.cmd,payment.error]; arguments=[null]; defaultMessage=[(SqlMapClient operation): encountered SQLException [  
    --- The error occurred in com/blabla/dao/sql-map-insert.xml.  
    --- The error occurred while applying a parameter map.  
    --- Check the elions.uw.insert.mst_detail_production-InlineParameterMap.  
    --- Check the parameter mapping for the 'msdpr_discount' property.  
    --- Cause: java.sql.SQLException: Invalid column type]; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
    --- The error occurred in com/blabla/dao/sql-map-insert.xml.  
    --- The error occurred while applying a parameter map.  
    --- Check the elions.uw.insert.mst_detail_production-InlineParameterMap.  
    --- Check the parameter mapping for the 'msdpr_discount' property.  
    --- Cause: java.sql.SQLException: Invalid column type]; Error in object 'cmd': codes=[payment.transferFailed.cmd,payment.transferFailed]; arguments=[null]; defaultMessage=[null]} and static attributes {}
    I noticed after the second insert and the java.sql.SQLException occured, it still invoked commit for the transaction on method transferToPayment, and not rolling back although an exception occured..
    can anyone help me with this?

    Comment


    • #3
      I have solved this problem by programmatically forcing the transaction to rollback in the catch block, like this:

      Code:
      catch(Exception e){
      			this.uwDao.getTransactionTemplate().execute(
      					new TransactionCallbackWithoutResult()	{
      						protected void doInTransactionWithoutResult(TransactionStatus status) {
      							status.setRollbackOnly();
      						}
      					}
      			);
      		}
      but I am still very confused because I don't know what's wrong with not rollbacking on exception.. it was supposed to be a nested transactions, which if an exception occured, the parent transaction also rollbacked

      this is not the first project i've done, but still there's always something that made me pulling out my hair.. can anyone give me some explanations on this?

      sorry for poor grammar

      Comment

      Working...
      X