Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

Solution 1:

Pessimistic locking is generally not recommended and it's very costly in terms of performance on database side. The problem that you have mentioned (the code part) a few things are not clear such as:

• If your code is being accessed by multiple threads at the same time.
• How are you creating session object (not sure if you are using Spring)?

Hibernate Session objects are NOT thread-safe. So if there are multiple threads accessing the same session and trying to update the same database entity, your code can potentially end up in an error situation like this.

So what happens here is that more than one thread tries to update the same entity, one thread succeeds and when the next thread goes to commit the data, it sees that its already been modified and ends up throwing StaleObjectStateException.

EDIT:

There is a way to use Pessimistic Locking in Hibernate. Check out this link. But there seems to be some issue with this mechanism. I came across posting a bug in hibernate (HHH-5275), however. The scenario mentioned in the bug is as follows:

Two threads are reading the same database record; one of those threads should use pessimistic locking thereby blocking the other thread. But both threads can read the database record causing the test to fail.

This is very close to what you are facing. Please try this if this does not work, the only way I can think of is using Native SQL queries where you can achieve pessimistic locking in postgres database with SELECT FOR UPDATE query.

Solution 2:

We have a queue manager that polls data and gives it to handlers for processing. To avoid picking up the same events again, the queue manager locks the record in the database with a LOCKED state.

    void poll() {
        record = dao.getLockedEntity();
        queue(record);
    }

this method wasn't transactional but dao.getLockedEntity() was transactional with REQUIRED.

All good and on the road, after few months in production, it failed with an optimistic locking exception.

After lots of debugging and checking in details we could find out that some one has changed the code like this:

    @Transactional(propagation=Propagation.REQUIRED, readOnly=false)
    void poll() {
        record = dao.getLockedEntity();
        queue(record);              
    }

So the record was queued even before the transaction in dao.getLockedEntity() gets committed (it uses the same transaction of poll method) and the object was changed underneath by the handlers (different threads) by the time the poll() method transaction gets committed.

We fixed the issue and it looks good now. I thought of sharing it because optimistic lock exceptions can be confusing and are difficult to debug.

Solution 3:

It doesn't appear that you are actually using the email that you retrieve from the database, but an older copy that you get as a parameter. Whatever is being used for version control on the row has changed between when the previous version was retrieved and when you are doing the update.

You probably want your code to look more like:

    @Transactional
    public void test(String id, String subject) {
       Email email = getEmailById(id);
       email.setSubject(subject);
       updateEmail(email);
    }

Solution 4:

I had the this problem on my project.

After I implemented optimistic locking, I got the same exception. My mistake was that I did not remove the setter of the field that became the @Version. As the setter was being called in java space, the value of the field did not match the one generated by the DB anymore. So basically the version fields did not match anymore. At that point any modification on the entity resulted in:

org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect)

I am using H2 in memory DB and Hibernate.

Solution 5:

This exception is probably caused by optimistic locking (or by a bug in your code). You're probably using it without knowing. And your pseudo-code (which should be replaced by real code to be able to diagnose the problem) is wrong. Hibernate saves all the modifications done to attached entities automatically. You shouldn't ever call update, merge or saveOrUpdate on an attached entity. Just do

Email email = session.get(emailId);
email.setSubject(subject);

No need to call update. Hibernate will flush the changes automatically before committing the transaction.