OptimisticLockException with Ebean/Play

Question

I have a Play 2.1.3 Java app using Ebean. I am getting the OptimisticLockException below.

[OptimisticLockException: Data has changed. updated [0] rows sql[update person 
set name=? where id=? and email=? and name=? and password is null and created=? 
and deleted is null] bind[null]]

I understand that it is trying to tell me the record has changed between when I read it and when I tried to write it. But the only change is happening in this method.

public void updateFromForm(Map<String, String[]> form) throws Exception {
    this.name = form.get("name")[0];

    String password = form.get("password")[0];
    if (password != null && password.length() != 0) {
        String hash = Password.getSaltedHash(password);
        this.password = hash;
    }

    this.update();
}

Am I doing this wrong? I saw similar logic in zentasks. Also, should I be able to see the the values for the bind variables?

UPDATE: I am calling updateFromForm() from inside a controller:

@RequiresAuthentication(clientName = "FormClient")
public static Result updateProfile() throws Exception {

    final CommonProfile profile = getUserProfile();
    String email = getEmail(profile);           
    Person p = Person.find.where().eq("email", email).findList().get(0);

    Map<String, String[]> form = request().body().asFormUrlEncoded();

    if (p == null) {
        Person.createFromForm(form);
    } else {
        p.updateFromForm(form);
    }

    return ok("HI");
}

Lynxeyed

Could you include the method wherein you are invoking updateFromForm? – Bezel

Ok, I updated it. I call updateFromForm() inside a controller. updateFromForm itself is defined in a model. Do you know- should I see values for the bind variables in my error message or no? Thanks for any help. – Lynxeyed

Answer 1

Little bit late, but for your case @Version annotation should be the solution. We're using it mostly with java.util.Date, so it can be also used also for determining the date of last record update, in Play model that's just:

@Version
public java.util.Date version; 

In such case update statement will be done with id and version fields only - useful especially when using with large models:

update person set name='Bob' 
where id=1 and version='2014-03-03 22:07:35'; 

Note: you don't need/should update this field manually at each save, Ebean does it itself. version value changes ONLY when there was updated data (so using obj.update() where nothing changes doesn't update version field)

Handyman

Its never too late. In fact, I am encountering the same problem now in a different project. When I try to add (at)Version to a property (A string that is filled with a uuid) it says "Error reading annotations for models.MyModel". Is (at)Version a new or deprecated feature? Thank you for any insight. – Lynxeyed

No it's not old or depreciated mayby try to use full qualified path @javax.persistence.Version otherwise create new question and show us your model. – Handyman

Answer 2

I have an alternative approach to this, where I add the annotation

@EntityConcurrencyMode(ConcurrencyMode.NONE)

to the Entity class.

This disables the optimistic locking concurrent modification check meaning the SQL becomes

update person set name=? where id=?

This is even more optimistic since it simply overwrites any intermediate changes.

Phaidra

in what package is the @EntityConcurrencyMode annotation? – Collimore

package com.avaje.ebean.annotation; – Phaidra

Answer 3

Mystery solved.

First- this public service announcement. "OptimisticLockException" is a big bucket. If you are trying to track one of these down be open to the idea that it could really be anything.

I figured out my problem by dumping SQL to the log and finding this:

update person set name='Bob' 
where id=1 and email='[email protected]' 
and name='Robert' and password is null 
and created=2013-12-01 and deleted is null

So I guess what happens when you do an update is that it builds a WHERE clause with all the known entities and their values as they were originally ready.

That means, if any other part of your code or another process changes something behind your back, this query will fail. I wrongly assumed that the problem was that somehow .setName('Bob') had changed the name in the DB or some object cache.

Really what was happening is that the WHERE clause includes a date while my database includes an entire timestamp with date, time, and timezone.

For now, I fixed it by just commenting out the timestamp in the model until I can figure out if/how Ebean can handle this data type.

Lynxeyed

Thank you! I had a similar problem caused by a date being set via a manual insert using the postgres now() function. Turns out the postgres date has a different precision to the Ebean date. – Mayers

Answer 4

I had the same problem, after hours of search i found the reason.. It was of inconsistency of the parameters type in the data base (in my case string) and the object i created and tried to save -java.util.Date.

after changing the database to hold datetime object the problem was solved

Hamlani