Spring transaction closes connection once commit for Propagation type REQUIRED_NEW

Question

In my application i am processing messages from queue using camel and process it in multiple threads.

I tried to persist the data to a table during the process with PlatformTransactionManager, with Propagation type "REQUIRED_NEW", but on using the commit the transaction seems to be closed. and connection not available for other process.

The application context.xml looks as in below snippet.

<!-- other definitions -->

    <context:property-placeholder location="classpath:app.properties"/>
    
  <bean id="appDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
        <property name="url" value="${dburl}"/>
        <property name="username" value="${dbUserName}"/>
        <property name="password" value="${dbPassword}"/>        
    </bean>
    
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="appDataSource" />
    </bean>
    
    <!-- Other bean reference. -->
    <bean id="itemDao" class="app.item.dao.ItemDao">
       <property name="dataSource" ref="appDataSource"/>
    </bean>
    
    <bean id="orderProcess" class="app.order.process.OrderProcess"  scope="prototype">
        <property name="itemDao" ref="itemDao"/>
    </bean>

• I have a DAO classes something like below, also there are other Dao's.

public class ItemDao{

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    private PlatformTransactionManager transactionManager;
    private TransactionStatus transactionStatus;
    
    //Setter injection of datasource
    public void setDataSource(DataSource dataSource) {
       this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
       this.transactionManager = new DataSourceTransactionManager(dataSource);
    }
    
    //setterInjection
    public void setTransactionManager(PlatformTransactionManager transactionManager) {
        this.transactionManager = transactionManager;
    }

    public void createAndStartTransaction()
    {
        DefaultTransactionDefinition transDef = new DefaultTransactionDefinition();
        transDef.setPropagationBehavior(Propagation.REQUIRES_NEW.ordinal());
        if (transactionManager != null)
        {
            transactionStatus = transactionManager.getTransaction(transDef);
        } // if transactionManager null log something went incorrect 
    }
    
    public void commit() throws Exception
    {
        if (transactionManager != null && transactionStatus != null)
        {
            transactionManager.commit(transactionStatus);
        }
    }
    
    public void rollBack() throws Exception
    {
        if (transactionManager != null && transactionStatus != null)
        {
            transactionManager.rollback(transactionStatus);
        }
    }
}

• Finally in the code flow, once the context is defined and using those beans process the message.

  • Parse the message from a queue
  • validate the message, check if the metadata information in database, insert the data to the database.
  • I am trying to persist the data to database immediately at this time
  • After that the flow will be processing further.

• The challange is that when we tried to use the

• Below is what I did to persist the data to database. Refer the code snippet.

• But this is working when i perform a a testing with single instance.

//....
//.. fetch info from data base using other dao's
//.. insert into another table

// Below code i added where i need to persist the data to database
try{
 orderProcess.itemDao.createAndStartTransaction();
 orderProcess.itemDao.
}catch(Exception exe){
   orderProcess.itemDao.rollBack();
}finally{
 //within try catch
    orderProcess.commit(); 
} 
//.. other dao's used to fetch the data from different table database
//.. still the process is not completed

• When the process try to fetch the next message from queue, it was not able to get the connection and throws connection null exception.
  • What is observed is the process closes the connection abruptly, so when the process picks the next message it is not having connection defined.

SQL state [null]; error code [0]; Connection is null.; nested exception is java.sql.SQLException: Connection is null.
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)

Any idea how to persist the transaction independently during the process.

Answer 1

The design is not maintainable, but was able to modify the code for my requirement. Didn't notice any side effect

The DAO call was done from different layer.

• I extracted the insert/update/delete to Specific DAO class.
• And created a sperate method to call the insert(), etc. in this DAO.

public void checkAndValidate(Object input){
  // check data exsits in DB
   boolean exists =   readDao.checkForData(input);
   
   if(!exists){
      // the method which was annotated with transactional
      insertDataToDB(input);
   }
   //.. other process..
}

@Transactional
public Object insertDataToDB(Object data) throws exception {

   try{
     writeDao.insertData(data);
   } catch(Exception exe)
   {
       //handle exception 
   }
}