Spring TransactionTemplate and commit

Question

I have to update status of a variable in database before processing it for ensuring only one thread process it.

For this I am using spring's transactionTemplate so my doubt is: If I have something like:



    TransactionTemplate(new TransactionCallback()){
         execute(){
            try{
              query 1 - having select for update
              query 2 - having update
            } catch(Exception e){
              TransactionStatus.setRollBackOnly();
              throw e;
            }
         }
    }

I use spring's jdbctemplate with autocommit on.

How many commits happen? Does commit happen for every query? Is this good way to achieve synchronization?

score 3 · Accepted Answer · answered Jan 27 '15 at 10:13

3

When using TransactionTemplate the JDBC connection is switched to manual commit, the template manages the transaction lifecycle and hence you will have a single commit for all operations inside the callback. Select for update acquires exclusive write locks on the accessed rows which are released when the transaction is over.

Even if the programmatic approach works, I would recommend going for the declarative way to end up having a cleaner code and flexible configuration. You could use @Transactional (take a look at the REPEATABLE_READ isolation level):

@Transactional(isolation=Isolation.REPEATABLE_READ)
public void transactionalMethod() {
  query 1 - having select for update
  query 2 - having update
}

answered Jan 27 '15 at 10:13

Sergi Almar

8,054
3
32
30

Can you post the source of this? Any documentation/code – harish.venkat Jan 28 '15 at 18:02
Its seems logical to move to manual commit. But just as a confirmation, did you find this in any documentation. – harish.venkat Jan 29 '15 at 10:20
not explicitly, but you'll be participating in a spring managed transaction so it makes sense. If you raise the log level you will see: Switching JDBC Connection to manual commit – Sergi Almar Jan 29 '15 at 10:34
1

Here's a link to the official docs : http://docs.spring.io/spring/docs/current/spring-framework-reference/htmlsingle/#transaction – MounirReg Jan 29 '15 at 11:56
2

I found a difference in behaviour between `@Transactional` vs using the Transaction Template when using "select for update" on oracle. `@Transactional` didn't queue concurrent updates in order but the template did (each waited for the lock). Maybe `@Transactional` allowed the transaction manager to reorder commits? – Carl Pritchett Sep 02 '16 at 08:44

score 2 · Answer 2 · edited May 23 '17 at 12:33

I like concept of optimistic locking in such cases where we think most of times there will be only 1 thread which is going to update that particular row. In case of more than 1 thread working for update, there is exception handler flow. I will suggest don't go for exclusive locking unless you think it will be most frequently multiple threads try to update status. To know about optimistic locking, go through post

Spring TransactionTemplate and commit

2 Answers2