CODEWITHSUNDEEP
javasqlspringjdbctransactions
Edward Wang
Edward Wang

Reputation: 355

Spring Transaction roll back both `UPDATE` and `INSERT`

@Service
@Transactional
public class OrderService {

  //Implemented with JdbcTemplate (no ORM)
  //Both Dao use the same DataSource
  private AccountDao accountDao;
  private OrderDao orderDao;

public void update(int accountId){

  //Get account and do some calculation (simplifed version here)
  Account account = accountDao.findByid(accountId)
  int newAmount = account.getAmoount * 100;

  //Update account table 
  accountDao.updateAmount(accountId, newAmount);

  //Insert new order
  Order order = ....
  orderDao.save(order);

}

}

In the code above, I would like to rollback both accountDao.updateAmount and orderDao.save if the updated account row is modified by another transaction. enter image description here

Upvotes: 0

Views: 669

Answers (1)

Mourad Zouabi
Mourad Zouabi

Reputation: 2207

You need to implement optimistic locking manuallay.

Add VERSION column for each table.

When You load and entity .. you need to load the current version

when you update an entity you have to add version check in where clause and get the updated row count after execution ( int rowCount = st.executeUpdate(); )

example : UPDATE ACCOUNT set AMOUNT = x, VERSION = VERSION+1 where ID = XX and VERSION = CURRENT_VERSION

If the updated row count is <> 1 it means that the row has been modified by another transaction ( UPDATE or DELETE )

Upvotes: 1

Related Questions