2

I have a long process that run on every user in my database and updates the score and hits fields, while this is running, sometimes a user tries to update his name/phone etc. right now when this happens the user update fails with:

org.springframework.dao.CannotAcquireLockException
could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement

The thing is, I know that the user cant update he's score/hits field, so can I prevent the row from being locked and allow both updates to run? (I am using hibernates @DynamicUpdate, because I thought that will prevent the lock since the query will only update changed fields, but without luck)

BTW i'm using spring boot 1.3.3.RELEASE with java 8, (running in tomcat.

*** UPDATE ***

To answer @Thierry question I am using one big transaction for all the user score / hits update. I must have all the user updated at once. And also I cant prevent the users from updating their records while this process run, since this is will badly affect their use of the system.

*** UPDATE 2 ***

So after a lot of search I think I found exactly what I want. according to this article: http://www.intertech.com/Blog/hibernate-optimistic-lock-without-a-version-or-timestamp/, when I set optimistic lock with lock type dirty and dynamic update then: "This alternative allows other concurrent processes to update the same row so long as they do not modify the same columns." the thing is Iv'e set my entity as:

@DynamicUpdate
@OptimisticLocking(type = OptimisticLockType.DIRTY)
@Entity
public class User { ..

And I still get the lock exception. Am I still doing something wrong?

Vy Do
  • 46,709
  • 59
  • 215
  • 313
Cony
  • 213
  • 1
  • 8
  • 21

1 Answers1

1

Save the updated user record to another table temporarily, and have another process that put this data to your user table once the long running process is finished (could also be the last step of the long running process).

When showing the profile page to the user (or the page where he can update his records), you'll have to aggregate between the main user table (which might be locked) and what is inside the other table.

When updating the user records, catch the CannotAcquireLockException, and save the info to the fallback table.

Thierry
  • 5,270
  • 33
  • 39
  • Iv'e updated my question to answer your question. the first option you suggested is not possible if i want to keep my users. the second sounds kind of a bad hack for me, and im not quite sure it will help since at the end of the process i still need to update all the users which will take some time in which the records are locked. – Cony Jun 15 '16 at 14:32
  • @Osher. Ok, so the only solution I see, is the last part of my answer : create another table where you store temporarily updated record. Feed them to the main user table once the running process is finished. When showing the profile page to the user, you'll have to aggregate between the main user table (which might be locked) and what is inside the other table. (i've updated my answer to only show this last part, as the rest was not relevant anymore). – Thierry Jun 15 '16 at 15:00