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?

Solution 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).

Solution 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

Comments

[Johny19]

Thank you for your answer. So If I use optimistic locking, when a race condition is detected a StaleObjectStateException is thrown. The thing is that I don't want the "increase tag" to fail.. and I don't want (and I don't think is very recommended.?) to catch the the StaleObjectStateException and retry until the .save doesn't thrown the exception? Is there a way to automatically retry with the latest DB value?

[mh-dev]

It should not fail, just handle the exception accordingly. Every other solution would be hacky (but i have some if you want them ;) )

[Johny19]

Do you mean handle the exception in a while loop ? because I will need to retry incrementTag until I don't get the exception anymore. I can0t get away with this for example try {increment) catch (StaleObjectStateException e ) increment() }

[mh-dev]

When you expect a high amount of conflicts than this is probably not the best solution. When this is the case throw the changes into a queue und execute them one after another. The disadvantage is that it isn't syncron anymore. It would be necessary to take a closer look into the usecase to give a really appropriate answer to this.

[Johny19]

genius.. this is 100 times better that dealing with pessimists locks!

[Johny19]

(btw after posting my question I was looking some doc about isolation level and a fell on I just realised that it is your blog) it was very clear and well explained. so +1 for that too !

[Alkanshel]

Hmm, are we sure that a @Version annotation on an often-updated counter field is a good idea? Sounds like any little timing issue will cause it to not increment.. also the version field updates when any item of the entity is updated, so I don't understand the implication of updating the @ Version field (which is supposed to update on its own when any OTHER field is updated....)

[naimul]

Is there any way to get the updated Tag entity after update query or the updated count? In my code: @Modifying @Query("update TranscodeMetaData t set t.fileDone = t.fileDone + 1 where t.userEmail = ?1 and t.videoId = ?2") void updateTranscodeFileDone(String userEmail, String videoId);