CODEWITHSUNDEEP
hibernatejpacounterincrementrace-condition
Johny19
Johny19

Reputation: 5582

JPA/Hibernate how to properly increment a counter in the database?

Maybe this will sound like an easy question for some but what would be the correct way to increment a counter in a database?

For example if I have a table that contains a "like_count" column which gets updated every time a user likes a photo.

(Assume I have my Photo @Entity)

Photo photo = photoRepository.findByPhotoId(id)
photo.setLikeCount(photo.getLikeCount()+1);
photoRepository.save(photo)

For example, is the above code correct? Would any Race condition occur?

Thank you

Upvotes: 4

Views: 4719

Answers (1)

k_o_
k_o_

Reputation: 6288

I think the code is not correct. A parallel running thread finishing the update later, but having read the same counter before, will overwrite the counter and so one count is lost.

It also depends on the transaction isolation level, if you are using SERIALIZATION or 'REPEATABLE_READ' you are safe, but usually READ COMMITTED is used, which would show this problem usually with database like Oracle or PostgreSQL.

Also notable is that at least Hibernate is saving the complete entity not only modified columns by default. So changing a different column does not work. You can change this with the @DynamicUpdate but maybe this hard behavior change has some side effects at least regarding the performance for the dirty checking of the field to flush to the DB.

Solutions:

Correct solutions are:

  1. Pessimistic locking: Lock the row with an SELECT ... FOR UPDATE - bad this can be bad for performance because all writers have to wait and also all readers if on writer is active
  2. Atomic Updates: Better because it does no use pessimistic locking: UPDATE photo SET likecount = likecount + 1 WHERE id = :id
  3. Optimistic locking approaches. But then you have to handle OptimisticLockingExceptions and repeat the transaction

Upvotes: 1

Related Questions