How handle currents updates in spring-boot hibernate problem? Also need to make app scalable

Question

Project type :- Spring-boot JPA project

Hi, I have below Rest service which increments a number in database.

  @RestController
    public class IncrementController {

    @Autowired
    MyNumberRepository mynumberRepository;

    @GetMapping(path="/incrementnumber")
    public String incrementNumber(){    
        Optional<MyNumber> mynumber = mynumberRepository.findById(1);
        int i = mynumber.get().getNumber();

        System.out.println("value of no is "+i);
        i = i+1;
        System.out.println("value of no post increment is "+i);
        mynumber.get().setNumber(i);

        MyNumber entity = new MyNumber();
        entity.setId(1);
        entity.setNumber(i);

        mynumberRepository.save(entity);
        return "done";      
    }   
}

Entity is as below :-

    @Entity
@Table(name = "my_number")
public class MyNumber {

    @Id
    private Integer id;

    private Integer number;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getNumber() {
        return number;
    }

    public void setNumber(Integer number) {
        this.number = number;
    }
 }

Below is the Repository :-

   public interface MyNumberRepository extends JpaRepository<MyNumber, Integer>{

   }

The service works well when I call increment number sequentially , but when concurrent threads call the incrementservice then i get non consistent results. How can I handle this situation ?

Also have to deploy the app on multiple places and connecting to same DB. i.e Scalability concern.

Thanks, Rahul

Simon Martinelli · Answer 1 · 2019-02-04T13:23:35.107

0

You must use a pessimistic lock. This will issue a SELECT FOR UPDATE and lock the row for the transaction and it's not possible for another transaction to overwrite the row.

public interface MyNumberRepository extends JpaRepository<MyNumber, Integer> {

  @Lock(LockModeType.PESSIMISTIC_WRITE)
  Optional<MyNumber> findById(Integer id);
}

And then you have to make your REST method transactional by adding @Transactional

@RestController
public class IncrementController {

    @Autowired
    MyNumberRepository mynumberRepository;

    @Transactional
    @GetMapping(path="/incrementnumber")
    public String incrementNumber(){    
        Optional<MyNumber> mynumber = mynumberRepository.findById(1);
        int i = mynumber.get().getNumber();

        System.out.println("value of no is "+i);
        i = i+1;
        System.out.println("value of no post increment is "+i);
        mynumber.get().setNumber(i);

        MyNumber entity = new MyNumber();
        entity.setId(1);
        entity.setNumber(i);

        mynumberRepository.save(entity);
        return "done";      
    }   
}

edited Feb 04 '19 at 13:23

answered Feb 04 '19 at 10:18

Simon Martinelli

34,053
5
48
82

Thanks Simon ! .Got the below exception after adding this, May you please help me with this I am new to hibernate concepts. – Rahul Bansal Feb 04 '19 at 11:38
What execption? – Simon Martinelli Feb 04 '19 at 11:39
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'incrementController': Unsatisfied dependency expressed through field 'mynumberRepository'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'myNumberRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: – Rahul Bansal Feb 04 '19 at 11:41
Failed to create query for method public abstract java.util.Optional com.example.rest.increment.evaluationtask.MyNumberRepository.findByIdAndLock(java.lang.Integer)! No property lock found for type MyNumber! – Rahul Bansal Feb 04 '19 at 11:41
Oh I'm sorry. Have a look at the fixed query in the answer – Simon Martinelli Feb 04 '19 at 11:46
Thanks , But now getting another run time exception when I hit the service org.hibernate.exception.GenericJDBCException: could not extract ResultSet Caused by: java.sql.SQLException: Cannot execute statement in a READ ONLY transaction. – Rahul Bansal Feb 04 '19 at 12:43

score 0 · Answer 2 · answered Feb 04 '19 at 13:48

Above solution will work , but i feel you are doing over-engineering for very simple problem. My recommendation would be to use database sequence.I feel your requirement is quite straight forward.In your service u can simply call getnextvalue on the sequence and then set the value in the Id field.This way u don't have to manage locks also as Database will do that for you.

In oracle particularly sequences are managed in a different transactions . So if ur calling code fails with exception , still the value of sequence will be incremented . This will ensure that multi-threads will not see the same value of the sequence in case of exceptions.

score 0 · Answer 3 · answered Feb 04 '19 at 13:49

0

Instead of locking transaction, you could also use an Oracle sequence or MySQL "AUTO_INCREMENT" feature which will prevent any ID being returned twice.

https://community.oracle.com/thread/4156674

Thread safety of MySql's Select Last_Insert_ID

answered Feb 04 '19 at 13:49

Tristan

8,733
7
48
96

How handle currents updates in spring-boot hibernate problem? Also need to make app scalable

3 Answers3