spring mybatis transaction for two db call

Question

I'm using mybatis with Spring. I'm having an API which performs delete and then insert operation. I want these operations to be in one transaction so that if insert fails, delete operation is rolled back.

@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
@MapperScan(basePackages = "com.abc..mapper")
public class ABCCoreDataConfig {

    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        ....
        .....
        sessionFactory.setMapperLocations(
            new PathMatchingResourcePatternResolver()
                .getResources("classpath*:com/abc//mapper/**/*.xml"));

        return sessionFactory;
    }

    @Bean(name = "transactionManager")
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
}

Service class:

@Service
@Transactional
public class MyService implements IMyService{

  @Autowired
    private MyMapper myMapper;

  @Transactional(propagation = Propagation.REQUIRED)
    public boolean addRecord() throws Exception {
            myMapper.deleteRecord(id);
            myMapper.insertRecord(- - -);
      return true;
    }
}

Even if insert fails due to MySQLIntegrityConstraintViolationException, delete operation is still not reverted.

Note: delete and insert are two different method at mapper level. Therefore, we have used transaction at the service layer.

Stacktrace:

23:11:29.452  DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession
23:11:29.458  DEBUG org.mybatis.spring.SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d40dbdb]
23:11:36.685  DEBUG o.m.s.t.SpringManagedTransaction - JDBC Connection [jdbc:mysql://127.0.0.1:13306/abc?connectTimeout=1000, UserName=xyz@10.0.0.0, MySQL Connector Java] will be managed by Spring
23:11:36.690  DEBUG MyDao.delete - ==>  Preparing: DELETE FROM .....
23:11:36.711  DEBUG MyDao.delete - ==> Parameters: ....
23:11:36.807  DEBUG MyDao.delete - <==    Updates: 2
23:11:36.807  DEBUG org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d40dbdb]
23:11:36.807  DEBUG org.mybatis.spring.SqlSessionUtils - Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d40dbdb] from current transaction
23:11:36.832  DEBUG MyDao.insert - ==>  Preparing: INSERT INTO .....
23:11:36.833  DEBUG MyDao.insert - ==> Parameters: ...
23:11:36.927  DEBUG org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d40dbdb]
23:11:36.997  DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d40dbdb]
23:11:36.998  DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7d40dbdb]

nik_7 · Answer 1 · 2018-05-19T07:58:07.820

Not sure why do you want to delete the record, and reinsert, update would be a better option if possible. This relates more to the concept of transaction, here is an idea.You can do following in your mapper class.

@Transactional(propagation = Propagation.REQUIRED)
public boolean deleteRecord() throws Exception {
// delete code here      
return true;
  }

@Transactional(propagation = Propagation.REQUIRED)
public boolean addRecord() throws Exception {
// add code here      
return true;
  }
}

Here spring annotation will take care of your transaction, For more information ,actually transaction uses proxy concept explained here Spring - @Transactional - What happens in background?

spring mybatis transaction for two db call

1 Answers1