Issue
I'm having trouble with transactions in Spring Boot using @Transactional annotation. The latest Spring is connected to a MS SQL Database.
I have following service, which periodically executes transactional method according to some criteria:
@Service
public class SomeService {
SomeRepository repository;
public SomeService(SomeRepository someRepository) {
this.repository = someRepository;
}
@Scheduled(fixedDelayString="${property}") //10 seconds
protected scheduledIteration() {
if(something) {
insertDataInNewTransaction(getSomeData());
}
}
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
protected void insertDataInNewTransaction(List<Data> data) {
//insert data to db
repository.saveAll(data);
//call verify proc
repository.verifyData();
}
}
The algorithm supposed to process data, insert them into table and perform check (db procedure). If the procedure throws an exception, the transaction should be rollbacked. I'm sure, that the procedure does not perform commit of the transaction.
The problem I'm facing is, that calling the method does not begin new transaction (or does but it's auto-commited), because I've tried following:
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
protected void insertDataInNewTransaction(List<Data> data) {
int counter = 0;
for(Data d : data) {
repository.save(d);
counter++;
//test
if(counter == 10) {
throw new Exception("test");
}
}
}
After the test method is executed, the first 10 rows remain in the table, where they were supposed to be rollbacked. During debugging I've noticed, that calling repository.save() in the loop inserts to the table outside transaction, because I can see the row from DB IDE while debugger sitting on next row. This gave me an idea, that the problem is caused by auto-commit, as it's MS SQL default. So I have tried to add following properties, but without any difference:
spring.datasource.hikari.auto-commit=false
spring.datasource.auto-commit=false
Is there anything I'm doing wrong?
Solution
If you use Spring Proxy AOP, then you need to turn the method insertDataInNewTransaction as public.
Remember that if the method is public, but it is invoked from the same bean, it will not create a new transaction (because spring proxies won't be call).
Short answer:
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public void insertDataInNewTransaction(List<Data> data) {
//insert data to db
repository.saveAll(data);
//call verify proc
repository.verifyData();
}
But if you really need a new separate transaction use Propagation.REQUIRES_NEW instead of Propagation.REQUIRED.
Answered By - Ady Junior
