Spring, JPA, and Hibernate - how to increment a counter without concurrency issues

Question

I'm playing around a bit with Spring and JPA/Hibernate and I'm a bit confused on the right way to increment a counter in a table.

My REST API needs to increment and decrement some value in the database depending on the user action (in the example bellow, liking or disliking a tag will make the counter increment or decrement by one in the Tag Table)

tagRepository is a JpaRepository (Spring-data) and I have configured the transaction like this

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"/>  @Controller public class TestController {      @Autowired     TagService tagService      public void increaseTag() {         tagService.increaseTagcount();     }     public void decreaseTag() {         tagService.decreaseTagcount();      } }  @Transactional @Service public class TagServiceImpl implements TagService {       public void decreaseTagcount() {         Tag tag = tagRepository.findOne(tagId);         decrement(tag)     }      public void increaseTagcount() {         Tag tag = tagRepository.findOne(tagId);         increment(tag)     }      private void increment(Tag tag) {         tag.setCount(tag.getCount() + 1);          Thread.sleep(20000);         tagRepository.save(tag);     }      private void decrement(Tag tag) {         tag.setCount(tag.getCount() - 1);          tagRepository.save(tag);     } } 

As you can see I have put on purpose a sleep of 20 second on increment JUST before the .save() to be able to test a concurrency scenario.

initial tag counter = 10;

1) A user calls increaseTag and the code hits the sleep so the value of the entity = 11 and the value in the DB is still 10

2) a user calls the decreaseTag and goes through all the code. the value is the database is now = 9

3) The sleeps finishes and hits the .save with the entity having a count of 11 and then hits .save()

When I check the database, the value for that tag is now equal to 11.. when in reality (at least what I would like to achieve) it would be equal to 10

Is this behaviour normal? Or the @Transactional annotation is not doing is work?

Answer 1

The simplest solution is to delegate the concurrency to your database and simply rely on the database isolation level lock on the currently modified rows:

The increment is as simple as this:

UPDATE Tag t set t.count = t.count + 1 WHERE t.id = :id; 

and the decrement query is:

UPDATE Tag t set t.count = t.count - 1 WHERE t.id = :id; 

The UPDATE query takes a lock on the modified rows, preventing other transactions from modifying the same row before the current transaction commits (as long as you don't use READ_UNCOMMITTED).

Answer 2

For example use Optimistic Locking. This should be the easiest solution to solve your problem. For more details see -> https://docs.jboss.org/hibernate/orm/4.0/devguide/en-US/html/ch05.html