Declarative Transaction Mgmt previously, happening, database, records, insert

amy

Hi All,

We need to rollback a transaction when we hit upon Record Already Exists Exception. Our application needs to insert about 1000 records, one by one. When there is a #8220;Record Already Exists#8221; error, we found that the transaction rollback isn#8217;t happening. We are using TransactionProxyFactoryBean. The logs suggested that the transaction rollback is happening. But we still find the previously inserted records in the database and they didnt get rolled back.Code:
16:35:39,515 ERROR [sampleDAO] Exceptin Caugt in DataAccessException
16:35:39,515 DEBUG [TransactionInterceptor] Completing transaction for [com.dao.ISampleDao.insertsampleItems] after exception: com.symcor.wir.dao.exceptions.WIRAppRecordAllreadyExistsException: Record already exists. ; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 2;DB2INST4.CC_ITEM
16:35:39,515 DEBUG [RuleBasedTransactionAttribute] Applying rules to determine whether transaction should rollback on com.dao.exceptions.WIRAppRecordAllreadyExistsException: Record already exists. ; nested exception is com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -803, SQLSTATE: 23505, SQLERRMC: 2;DB2INST4.CC_ITEM
16:35:39,515 DEBUG [RuleBasedTransactionAttribute] Winning rollback rule is: RollbackRuleAttribute with pattern [WIRAppRecordAllreadyExistsException]
16:35:39,515 DEBUG [DataSourceTransactionManager] Triggering beforeCompletion synchronization
16:35:39,515 DEBUG [DataSourceTransactionManager] Initiating transaction rollback
16:35:39,515 DEBUG [DataSourceTransactionManager] Rolling back JDBC transaction on Connection [org.tranql.connector.jdbc.ConnectionHandle@d3e796]
16:35:41,843 DEBUG [DataSourceTransactionManager] Triggering afterCompletion synchronization
16:35:41,843 DEBUG [TransactionSynchronizationManager] Clearing transaction synchronization
16:35:41,843 DEBUG [TransactionSynchronizationManager] Removed value [org..jdbc.datasource.ConnectionHolder@95041] for key [org.tranql.connector.jdbc.DataSource@1611c24] from thread [asyncDelivery0]
16:35:41,843 DEBUG [DataSourceTransactionManager] Releasing JDBC Connection [org.tranql.connector.jdbc.ConnectionHandle@d3e796] after transaction
16:35:41,843 DEBUG [DataSourceUtils] Returning JDBC Connection to DataSource
Please find below our spring configuration:Code:    lt;!-- Transaction management  --gt;      lt;bean id=quot;sampleDAOTargetquot;class=quot;com.dao.SampleDBDAOquot; lazy-init=quot;truequot;gt;     lt;/beangt;lt;bean id=quot;sampleValidatorquot; class=quot;org..transaction.interceptor.TransactionProxyFactoryBeanquot;gt;       lt;property name=quot;transactionManagerquot;gt;            lt;ref bean=quot;transactionManagerquot;/gt;      lt;/propertygt;      lt;property name=quot;targetquot;gt;            lt;ref local=quot;sampleDAOTargetquot;/gt;                               lt;/propertygt;      lt;property name=quot;transactionAttributesquot;gt;      lt;propsgt;         lt;prop key=quot;*quot;gtROPAGATION_REQUIRED,-DAOSysException,-ArithmeticException,-SQLException,-WIRAppRecordAllreadyExistsExceptionlt;/propgt;     lt;/propsgt;        lt;/propertygt;                      lt;/beangt;               lt;bean id=quot;transactionManagerquot; class=quot;org..jdbc.datasource.DataSourceTransactionManagerquot;gt;
lt;property name=quot;dataSourcequot;gt;      lt;ref local=quot;dataSourcequot;/gt;lt;/propertygt;         lt;/beangt;     lt;bean id=quot;dataSourcequot; class=quot;org..jndi.JndiObjectFactoryBeanquot;gt;lt;property name=quot;jndiNamequot;gt;      lt;valuegt;java:comp/env/jdbc/MYDataSourcelt;/valuegt;lt;/propertygt;
     lt;/beangt;
Source Code is something like given below:Code:

//DAO CLASS

public class SampleDBDAO implements ISampleDao

{     private JdbcTemplate jdbcTemplate;     private static final Log logger = LogFactory.getLog(SampleDBDAO.class);     public SampleDBDAO()     {this.jdbcTemplate = DAO.getJdbcTemplate();
     }
     public void insertsampleItem(List sampleList)throws org..dao.DataAccessException
{final String methodName = quot;insertsampleItem()quot;;      logger.info(EnvironmentConstants.ENTER + methodName);           try     {            for (int i=0;ilt;2;i++)            {                  jdbcTemplate.update(quot;insert into MYTABLE values ('XXZ','asfdfd')quot;);            }      }      catch(Exception e)      {            logger.info(quot;********* Caught in exception block ******* quot;);            //txManager.rollback(status);      }     }
}

//Interface

public interface ISampleDao

{public void insertsampleItem(List sampleList)throws org..dao.DataAccessException;               

}
On a different note, we just created an Arithmetic Exception by putting in a divide by zero, and we were able to roll back. We are not sure where we are going wrong.

Any inputs will be of great help.

Regards,
Premjith

You DAO looks well, weird.

You have some static method to retrieve the JdbcTemplate, why? Is the DataSource used by that DAO utility class the same as where the transactions apply to?

I would rewrite your constructor to something like this.Code:
public SampleDBDAO(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource);
}
And the configuration.Code:
lt;/beangt; lt;bean id=quot;sampleValidatorquot; class=quot;org..transaction.interceptor.TransactionProxyFactoryBeanquot;gt; lt;property name=quot;transactionManagerquot; ref=quot;transactionManagerquot;/gt;   lt;property name=quot;targetquot;gt;   lt;bean class=quot;com.dao.SampleDBDAOquot; lazy-init=quot;truequot;gt;     lt;constructor-arg ref=quot;dataSourcequot;/gt;   lt;/beangt; lt;/propertygt; lt;property name=quot;transactionAttributesquot;gt;   lt;propsgt;     lt;prop key=quot;*quot;gtROPAGATION_REQUIRED,-DAOSysException,-ArithmeticException,-SQLException,-WIRAppRecordAllreadyExistsExceptionlt;/propgt;     lt;/propsgt; lt;/propertygt;                 
lt;/beangt;
Your target is now an inner bean that also prevents you from using the wrong (non proxied/transactional) instance.

Also your dao has a try/catch block with Exception I wouldn't recommend that, always rethrow the exception so that it can bubble up (or simply remove the catch.Code:
public void insertsampleItem(List sampleList)throws org..dao.DataAccessException { final String methodName = quot;insertKofaxItems()quot;; logger.info(EnvironmentConstants.ENTER + methodName); for (int i=0;ilt;2;i++) {   jdbcTemplate.update(quot;insert into MYTABLE values ('XXZ','asfdfd')quot;); }
}