Clarification for javax.persistence.OptimisticLockException: Data has changed.

[Joshua Chaitin-Pollak]

Hi,

We have started to get these errors on our server:

javax.persistence.OptimisticLockException: Data has changed. updated [0] rows sql[update mail_out set msg_status=? where MAIL_OUT_ID=? and USER_ID is null and MSG_TO=? and MSG_FROM=? and MSG_SUBJECT=? and MSG_BODY=? and msg_status=? and POST_DATE=? and MSG_FROM_PERSONAL is null and ATTACHMENTS is null] bind[null]

I've read what I can about this already (from the following thread, and others), but I wanted to get some clarifications:

https://groups.google.com/forum/?fromgroups#!topic/ebean/fxDnDEj_GVQ

1) I am not using a @Version annotation. Is it generally considered best practice to add a column for modification tracking? How does concurrency protection help at all? Why not just save the data and assume the software knows what is doing / doesn't care? What are we supposed to do when ebeans detects a concurrency problem?

2) My object has a java.utils.Date mapped to a 'timestamp' field in MySQL. This seems to work 99% of the time, but I gather from the link above and other spots on the web that this is risky. Is the correct Java datatype to use 'java.sql.Timestamp'?

Are there any other things to think about with this issue?

Thanks,

-Josh

[Rob Bygrave]

>> 1) I am not using a @Version annotation. Is it generally considered best practice to add a column for modification tracking?

Yes it is.

>> How does concurrency protection help at all?

It provides detection of "Lost Updates".  (which if allowed or ignored would be a sort of silent error ... you won't know if or when it happens until some user has some vague recollection of their change not being saved).

>> Why not just save the data and assume the software knows what is doing / doesn't care?

It basically depends on whether you care about "Lost updates". If you want a "the software knows/is aware" solution you need to use Pessimistic Locking or Optimistic Locking and Optimistic Locking is generally the best approach ... and using @Version is the optimal way of doing Optimistic Locking.

>> What are we supposed to do when ebeans detects a concurrency problem?

In a UI type application (user edits some data and hits a update/save button) ... then the expected thing to do is to tell the user that the data they tried to modify has since been changed by someone else ... they should "refresh" the data and reapply their change. 

This is not a new strategy ... this is the same approach as is/was used for 'Client Server' applications back in the day.

>> 2) My object has a java.utils.Date mapped to a 'timestamp' field in MySQL. This seems to work 99% of the time, but I gather from the link above and other spots on the web that this is risky.

The user on that link was using an Oracle DATE type and that stores to second precision - not milliseconds or nanoseconds ... seconds  (so there is a lot of precision lost there). So no, that is not a good DB data type choice and yes that is not recommended or suggested etc. People using Oracle should use the Oracle TIMESTAMP type - TIMESTAMP(6) or TIMESTAMP(9) is usual. 


Now if you are using MySQL 5.6.5+ then its Timestamp type stores fractional seconds up to microseconds (yay!!).
"... A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, as of MySQL 5.6.4, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded."

If your MySQL is older the fractional seconds are discarded for Timestamp. http://dev.mysql.com/doc/refman/5.5/en/fractional-seconds.html. 


So assuming you have 'seconds' precision how risky is it...

The scenario is that a 'user' would have to fetch some data, change its value and update it within 1 second ...  and in the meantime another 'user' does the same or more likely 'background program' updates the same data and changes the 'last updated timestamp' (but because of precision loss it doesn't effectively change) ... so the user's change succeeds and you got a 'lost update'.

So if your application has code that does find, change data, save/update without any "user think time" or "with less than 1 second user think time" then that is the risk.  So 1 second is relatively long in computer time (so you could code a lost update fairly easily) and for short user think time (1 sec) you might have something like a button that changes the status of a record .... so perhaps user can just view and then hit the button.



>> My object has a java.utils.Date ...

The short answer is that java.util.Date is fine.

The long answer is ... java.sql.Timestamp supports nano precision which is higher than java.util.Date which is millisecond precision.  Now System.currentTimeMillis() returns millisecond precision so in that sense it is moot if you use java.util.Date or java.sql.Timestamp - IF you used some other DateTime source which had nano precision then you really should use java.sql.Timestamp.

Personally (Rob opinion) I prefer to use java.sql.Timestamp for "DateTime" and java.sql.Date for "Date only" which I think is more explicit but I know that a heck of a lot of people don't do that (use Joda or java.util.Date). Ideally the Date/Time JSR sorts this out and we move to new types but that is still some time in the future.

>> Is the correct Java datatype to use 'java.sql.Timestamp'?

Discussed above ... personally I prefer java.sql.Timestamp over java.util.Date as it is more explicit but plenty of people prefer java.util.Date for some reason (e.g java.sql may seem less domain agnostic to because it is in the java.sql package).

>> Are there any other things to think about with this issue?

Yes. 

"Last Updated Timestamps" have practical uses if you start want to do things like use Elastic Search or SOLR or any external system where you may want to push changes to e.g. push all the data that has changed since <SomeDateTime>.

>> where MAIL_OUT_ID=? and USER_ID is null and MSG_TO=? and MSG_FROM=? and MSG_SUBJECT=? and MSG_BODY=? and msg_status=? and POST_DATE=? and MSG_FROM_PERSONAL is null and ATTACHMENTS is null

If you use a @Version then the WHERE clause becomes a lot simplier as it only includes the primary key and the version column. This can be significantly more efficient for the Database because you are likely to get better 'hits' in the database prepared statement cache ... (because those nulls in the where clause mean there are many statements that are almost the same but not ... so less hits in the database prepared statement cache - well in Oracle anyway but its very likely the other DBs have similar prepared statement caches ... which calculate the execution plan etc). 

So in short, a @Version makes your update statements more performant from a DB perspective.


Also note that when you don't use a @Version the BLOBS and CLOBS are not included in the WHERE clause because of the extra cost.

[Joshua Chaitin-Pollak]

Hi Rob,

This was a huge help. I'll add the @Version.

We are using MySQL 5.0 right now, so I guess we just have second-level precision. We've got a server move coming up, I'll make sure we include an upgrade to Mysql 5.5 in that move too.

Based on your detailed writeup, I am a bit confused about why this is happening. There should only be one thread updating the db record in question, although it definitely updates at sub-second precision. Basically we are using the database as a message queue (yuck, I know) for outbound email. Multiple threads write to the table, but just one consumes from it. This thread polls the table for unsent messages, sends the message, and then updates the record's msg_status field with 'SENT', and saves it back to the database (with ebean, of course).

Since we have second-level precision, is it possible that if the writer and consumer run within the same second, the OptimisticLockException could be triggered? 

On reflection, I have a hard time imagining how this problem could happen unless two consumers were running (which shouldn't be possible). Considering the state of our code however, I suppose its possible.

-Josh

[Durchholz, Joachim]

> Since we have second-level precision, is it possible that if the writer
> and consumer run within the same second, the OptimisticLockException
> could be triggered?

Not if the writers just INSERT.
In that scenario, you don't even need transaction support, since you can't
have concurrent access on a record that isn't inserted yet.
(The only exception would be a deeply braindamaged database that exposes
half-written records to the application. I think not even Mysql 3.x did that.)

The other possibility would be that they first INSERT, then UPDATE with
additional info. I'd avoid doing that for performance and locking reasons,
but sometimes other considerations come with higher priority.

> On reflection, I have a hard time imagining how this problem could happen
> unless two consumers were running (which shouldn't be possible).
> Considering the state of our code however, I suppose its possible.

I agree that a multiple consumer scenario should be excluded.

[Josh Kamau]

A coincidence ;)

Different Josh... Same question ... Same usage scenario (Email message queuing) 

https://groups.google.com/forum/?fromgroups#!msg/ebean/DDVf7GEUsr4/Pe0wKjoJb64J

[Rob Bygrave]

>> I have a hard time imagining how this problem could happen

Can you reproduce the issue?  How often do you see it?

If you have Ebean's transaction logging turned on then you can look in the logs for the exception and try and find another update on that same row/id, that should help to identify the issue.

The OptimisiticLockException is 'detected' by a sql update statement or sql delete statement executed that says that it updated/deleted 0 rows. The implication is that it updated/deleted 0 rows because the where clause (primary key + version column  or ... primary key + previous values) .... which means either the value in the version column doesn't match (if you are using @Version) ... or one of the column values has changed (if you are not using @Version).



Cheers, Rob.

[Ömer Faruk Gül]

Hi everyone,

I have problem about this OptimisticLockException, and problem arises from the fraction of seconds in java.sql.Timestamp.

I have explained my problem in more detail here: https://groups.google.com/forum/#!topic/ebean/E0OdxS_bUsA

What can I do about this?