Spring:Propagation.REQUIRED not working

Question

I am inserting records in a couple of tables namely Dept and Emp. If the Dept table is successfully created then only I want to insert records in Emp table. Also, if any of the insert in Emp fails, then I want to rollback all the transaction which includes both rollback from Emp as well as Dept tables.

I tried this using Propagation.REQUIRED as shown below:

Java File

public void saveEmployee(Employee empl){
    try {
        jdbcTemplate.update("INSERT INTO EMP VALUES(?,?,?,?,?)",empl.getEmpId(),empl.getEmpName(),
                empl.getDeptId(),empl.getAge(),empl.getSex());
    } catch (DataAccessException e) {
        e.printStackTrace();
    }

}

@Transactional(propagation=Propagation.REQUIRED)
public void saveRecords(){
    saveDepartment(dept);
    saveEmployee(empl);     
}

context.xml

<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>

<bean id="transactionManager"
    class="org.springframework.jdbc.datasource.DataSourceTransactionManager">        
    <property name="dataSource" ref="dataSource" />
</bean>

Problem:

Even if an insertion in Emp table fails, the Dept insertion is getting persisted which I don't want. I want to rollback everything.

Please suggest.

Answer 1

The problem is your catch block. Since the exception is catched the tx don't rollback.

You must throw the exception :

public void saveEmployee(Employee empl){
    try {
        jdbcTemplate.update("INSERT INTO EMP VALUES(?,?,?,?,?)",empl.getEmpId(),empl.getEmpName(),
                empl.getDeptId(),empl.getAge(),empl.getSex());
    } catch (DataAccessException e) {
        e.printStackTrace();
        throw e;
    }

}

And by the way, the semantic of Progation.Required just means : create a new tx if it don't exists OR use an existing one if there is tx running.

---

Following your comment here is a suggestion to see the effect of NEW tx :

@Transactional(propagation=Propagation.REQUIRES_NEW)
public void saveEmployee(Employee empl){
    try {
        jdbcTemplate.update("INSERT INTO EMP VALUES(?,?,?,?,?)",empl.getEmpId(),empl.getEmpName(),
                empl.getDeptId(),empl.getAge(),empl.getSex());
    } catch (DataAccessException e) {
        e.printStackTrace();
        throw e;
    }

}

@Transactional(propagation=Propagation.REQUIRED)
public void saveRecords(){
    saveDepartment(dept);
    try{
       saveEmployee(empl);
    }catch(Exception e){Logger.log("Fail to save emp !");}     
}

The key point to see the effect of REQUIRES_NEW is to catch the exception around saveEmployee. If you don't catch it : the exception will propagate in the other tx (the one started when entering saveRecords() ) and it will rollback too.