Ebean and the OptimisticLockException

I encountered today a strange Exception with PlayFramework 2.0 / Ebean when updating an entity:

javax.persistence.OptimisticLockException: Data has changed. updated [0] rows

At first sight, this exception is quite cryptic and it took me some time to understand why it was thrown and how to get rid of it.

What is optimistic locking ?

Optimistic locking is used when you don’t want to lock your data between the time you read it and the time you update it. It is based on the hypothesis that the value will probably not change in the database between reading and updating.

The benefit of using optimistic over pessimistic locking is that it does not require any lock on the database.

How Ebean manage optimisic locking ?

To understand the OptimisticLockingException, we need to dive into Ebean’s internals and look at how it handles optimistic locking.

Ebean has 2 strategies to manage optimistic locking (More info). The default strategy is called “All Column”. When Ebean executes an UPDATE SQL statement, it will include in the WHERE clause all the values it has read when retrieving the entity.

For example :

When retrieving a user like this

User user = User.find.byId(1)

The SQL statement will be something like

SELECT id, name, age FROM user WHERE id = 1

Let’s say it returns : 1, 'John', 25

If you update your entity and save it

user.setAge(26)
user.save()

The SQL statement will be something like

UPDATE user SET age = 26 WHERE id = 1 AND name = 'John' AND age = 25

This update statement will ensure that nobody has modified this data in the database since the entity has been retrieved. Ebean will then check if the number of records affected by this statement is 1 and if it’s 0, it means the data has been altered and it will throw the OptimisticLockException. That’s why you can see updated [0] rows in the Exception message.

Most of the time, this works well. It will succeed if the data has not been modified and fail if the data has been modified.

However sometimes it can fail even if the data has not been modified. This can happens for example if you have a column with type double and data containing a lot of decimals. It can happen that you retrieve in your Java code something like 0.46712538003907 but in fact, in the database, the data stored is something like 0.467125380039068. When updating, the Java value will be included in the WHERE clause and, as it doesn’t match the value stored in the database, the update will fail.

The solution

The easiest solution in this case is to use Ebean’s other strategy for managing optimistic locking. This second strategy is called “Version column”. For that, you can create a field in your entity that will be used to maintain its “version”. This field should have the @Version annotation. For example:

public class User extends Model {
    …
    @Version
    public Timestamp lastUpdate;
    …
}

In this case, Ebean will use this column in the WHERE clause of the UPDATE statement:

UPDATE user SET age = 26 WHERE last_update = '2012-11-08 15:00:40'

With that @Version column, everything will always work as expected !