0

I am using ORM in Java for connecting to mysql database. In java, I have an API to update a record in db. But when I am getting multiple requests at same time, The record is updated twice with inconsistency. I can not change my entity class to add a version for optimistic lock. How can i maintain persistence without that. The code looks like this:

 public void updateRecord(String userId, Double amount) {
    User user = userRepository.findById(userId);
    double balance = user.getBalance();
    user.setBalance(balance-amount);
    userRepository.save(user);
 }

I am not sure is this where we use pessimist locks or may be @Transactional annotations. Please help. Thanks in adavance !

thatman
  • 333
  • 3
  • 14

1 Answers1

1

If you wanna use pessimistic locking than add to your userRepository method 

@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("select u from User u where u.id = :id")
User findByIdAndLock(@Param("id") Integer id);

and use it instead of findById

User user = userRepository.findByIdAndLock(userId);

And also it's highly recommended to manage database transactions from the application. If you don't use @Transactional higher in method call chain than mark updateRecord with it. Like this:

@Transactional
public void updateRecord(String userId, Double amount)
Bohdan Petrenko
  • 997
  • 2
  • 17
  • 34
  • Its actually locking the entire table instead of single row. – thatman Aug 21 '18 at 06:52
  • Are you sure? How did you find it out? Please check sql, generated by JPA. In my case I checked sql generated for Postgresql and `findByIdAndLock` will lock only selected row. Sql looks like `select * from users where id = ? for update`. I think in mysql behavior is very similar. See this [link](https://stackoverflow.com/questions/49147149/does-pessimistic-write-lock-the-whole-table) for more details with Postgresql. [Lock details for MySql](https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html) – Bohdan Petrenko Aug 21 '18 at 07:20
  • It's working after adding a unique constraint to column 'id', But still, If I lock using column id and then try to get the same record using some other column ( eg. name ), I am able to get the record. Its locked only if I try to get using the same column – thatman Aug 30 '18 at 06:17