Our webapp upgraded to Java 17 (greater than or equal to Java 15 is sufficient to reproduce) and noticed OLE for entities that are versioned @Version using Java datetime types such as Instant.
I came to know what Hibernate versions prior to 6.0.0 don’t handle the Java nanosecond precision. This occurs if the underlying database is restricted to at most microsecond precision such as MySQL (our case).
But is someone able to explain how exactly does this fail? If the entity datetime is much greater in time than the last update how does the loss of precision on @Version throw the OLE? Or, is this only a problem if the updates are exact minute and seconds and differ by the microseconds whereby losing the nanosecond precision causes the OLE?
For example, if I annotate an entity field like so:
@Column(columnDefinition = “DATETIME(6)”)
private Instant version;
And use with an H2 in-memory database (that supports nanosecond precision for datetime columns), I get an OLE (OptimisticLockException).
However, if I update the field to:
@Column(columnDefinition = “DATETIME(9)”)
private Instant version;
I don’t get the OLE.
You must tell Hibernate the precision so that it can generate the appropriate version values. Using just a column definition is not going to work. Why don’t you use
@Column(precision = 9) instead?
On newer JDKs the clock precision was improved and now produces values with nanosecond precision whereas before it was restricted to microseconds.
You have to look into the resolved JIRA issues for version 6, there is one about this exact problem.
Thanks, appreciate the response. I’ve seen the resolved JIRA that is available in version 6 (and we plan on upgrading soon).
But for now, the reason I posted this query to the forum is to better understand why the precision is required.
In one case, our web app uses MySQL which restricts datetime precision to 6, and despite the OS and JDK allowing for nanosecond precision, it doesn’t seem to be a problem.
But as soon as we point that web app to H2 (which supports up to precision 9) I see the OLE.
So am hoping to understand what is the code mechanism that causes this issue.
When you use Hibernate 6 with H2, it will assume a precision of 9 for the attribute and hence generate a value with that precision and also pass that to the insert. Now, when you query
... where column = :param and bind the full precision, I think that the new H2 version doesn’t coerce the parameter value to the precision of the column anymore and the predicate usually is always false. Setting the precision to
6 will instruct Hibernate to only generate microsecond precision values and hence will just work.
I’m referring to the situation I describe while still using Hibernate 5.6.9.FINAL and noticing these differences between MySQL and H2 (same code, libraries). Only difference is I get the OLE when using H2, but not MySQL.
In Hibernate 5 there is no mitigation for the broken H2 2.0+ behavior because it requires new SPIs that were only introduced in Hibernate 6. So if you want to use Hibernate 5 on JDK 15+ with H2 2.0+, you will have to replace the
InstantJavaType to return an Instant with precision 6. It’s a H2 issue.
We are using Hibernate 5.6.9, MySQL 5.7 and H2 1.4.200.
If I don’t annotate the @Version Java Instant field with datetime(9) when using JDK 15+, then H2 throws optimistic locking failed; nested exception is org.hibernate.StaleObjectStateException.
However, I don’t seem to need datetime(9) to be defined if I’m only using with MySQL.
My sense is that due to the JDK on Linux being capable of returning nanosecond precision, and the fact that H2 supports precision of nanoseconds, that this explains the need to declared the columnDefinition with precision of 9 but I don’t have a reasonable understanding as to why exactly.
As follow-up, I got this explanatory snippet from H2 team:
Instant values in Java also support up to 9 fractional digits. When you insert a value with non-zero nanoseconds into a column with lower fractional seconds precision, this value is rounded to that precision by H2 (the SQL Standard doesn’t specify an exact behavior, it only requires an implementation-defined rounding or truncation). When Hibernate reads it back it gets a rounded value instead of expected original one. To avoid it, you must define an explicit fractional seconds precision of 9 for this column.
IF at all possible, I’m interested in learning is how exactly this fails the @Version check within Hibernate, i.e. why does the rounding fail the Instant equality or compareTo check with regards to the Versioned field/property?
Hibernate before version 6 generates a value with
Instant.now() so it uses nanosecond precision on JDK15+ and stores that in memory as version for an entity.
On insert, it passes a
Timestamp with these nanos to the JDBC driver to an
insert into .... In this case, the database will do this truncation/rounding based on the column it inserts into.
Now when updating that entity, it will do something like this:
update tbl set col1 = ?, version = ? where id = ? and version = ?. The set clause param will be bound to the new version and the where clause param to the old version. Again, a
Timestamp with nanos is passed to the JDBC driver.
Now, MySQL is like other production ready databases sane and coerces params based on the context to the appropriate data type and does the same truncation/rounding to precision 6 to both parameters.
H2 on the other hand, does not seem to do so for the comparison in the where clause, and hence the predicate is false and update doesn’t do anything, finally failing with an OLE.
This is a H2 issue and they have similar issues with collations when comparing
char(N) even with string literals. The only solution for you if you must use H2 is to register a custom version of the
InstantJavaType in Hibernate that produces a value with precision 6.