4

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

Johny19
  • 5,364
  • 14
  • 61
  • 99
  • I think you are right. I just give two advices: first one is you don't need to use photoRespository.save() because the photo entity has already been associated with session. If you modify it's attitude,the modification will be persistent in the end of the transaction.The second one is that maybe you should record the user's id when the user like the photo because usually one use just can like one photo once. – MageXellos Dec 04 '14 at 01:17
  • Thank you for your reply!. Well good to know that i don't need to worry about race conditions or other problem doing it like this. I have tried not to use .save but it does not persist in my database? (my db is PostgreSQL and i am using the entityManager (not the Session) is this why ? – Johny19 Dec 04 '14 at 08:53
  • My work now is still developing a software using PostgreSQL but we haven't used entityManager so I can not give you the excal answer. As I know, if you use session that you really don't need to use save() in the same session. Maybe that is why you must use save() to save the entity but I think it is okay. – MageXellos Dec 05 '14 at 00:55
  • Does this answer your question? [Spring, JPA, and Hibernate - how to increment a counter without concurrency issues](https://stackoverflow.com/questions/30143594/spring-jpa-and-hibernate-how-to-increment-a-counter-without-concurrency-issu) – walen Mar 31 '21 at 12:08

1 Answers1

1

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
k_o_
  • 5,143
  • 1
  • 34
  • 43