Optimistic Locking and Timestamp Precision with Ebean and Postgres

Lately I've been experimenting with the Play Framework. I'm using Avaje Ebean (JPA), the default ORM for Play Framework, with Postgres. I have an Akka background task that kicks off search jobs. To force a job to run I can simply change the run-at field

update search set next_run_at = now()

But I found this results in an OptimisticLockException when the scheduler runs:

javax.persistence.OptimisticLockException: Data has changed. updated [0] rows sql[update search set next_run_at=?, last_run_at=?, run_count=? where id=? and name is null and search=? and low is null and high is null and exact_match=? and active=? and notify_immediately=? and scheduled=? and frequency_hours=? and search_date is null and next_run_at=? and last_run_at=? and created_at=? and updated_at is null and error_count=? and result_count=? and run_count=? and location=? and account_id=?] bind[null] at com.avaje.ebeaninternal.server.persist.dml.DmlHandler.checkRowCount(DmlHandler.java:95) ~[avaje-ebeanorm.jar:na] at com.avaje.ebeaninternal.server.persist.dml.UpdateHandler.execute(UpdateHandler.java:81) ~[avaje-ebeanorm.jar:na] at com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:86) ~[avaje-ebeanorm.jar:na] at com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.update(DmlBeanPersister.java:66) ~[avaje-ebeanorm.jar:na] at com.avaje.ebeaninternal.server.persist.DefaultPersistExecute.executeUpdateBean(DefaultPersistExecute.java:82) ~[avaje-ebeanorm.jar:na] at com.avaje.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:452) ~[avaje-ebeanorm.jar:na]

It turns out that Ebean uses optimistic locking by default. This works by updating the row only if none of the columns changed since the initial select query. The problem occurs since my POJO uses the Java Date class, which provides only millisecond precision and the Postgres now function provides microsecond precision (an additional 3 digits). This results in a loss of precision and the fields don't match so Ebean thinks another process updated the fields. For example, the Ebean update statement looks like

update table set ... where next_run_at = "2014-01-15 08:18:08.518000"

but the field has microsecond precision: "2014-01-15 08:18: 08.518337" so it fails to update

There are a few solutions to this problem. One solution is to be mindful of SQL updates to timestamps that occur outside of your application and only use only millisecond precision. For this I found the date_trunc Postgres function:

date_trunc('milliseconds', now())

Another option is to set the precision of the timestamp field to milliseconds

ALTER TABLE ... next_run_at timestamp(3)

You might think you'd just turn off optimistic locking if you don't need it, but it appears that it is not possible to do so with Ebean.

Another possible solution is to use the @Version JPA Annotation. With @Version, Ebean will only include the version field to determine perform optimistic locking