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");
}

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)

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.

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.

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