Search code examples
hibernatespring-datadatabase-locking

Hibernate prevent locking of records when updating

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?

Solution

  • 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.