Java 17 @version and nanoseconds truncation

Philippe_Kernevez · March 8, 2022, 4:11pm

Hi all,

I’m moving a SpringBoot Application (2.6.4) based on hibernate 5.6.4 from Java 11 to Java17.
I have an issue with at least Azul Jvm.

We are using optimist locking with @version
All my updates are now failing with an OptimisticLock exception.

Our entities are like this:

public class MyEntity {
    @CreatedDate
    private ZonedDateTime createdAt;

    @Version
    private ZonedDateTime updatedAt;

When we do this sequence (without any concurrency on a DB):

        MyEntity aChild = new MyEntity(251L, "uid", "a node", 2, 251L);
        entityManager.persist(aChild);
        entityManager.flush();

        aChild.setLevel(3);
        entityManager.flush();

The last line throws an OptimisticLock exception.

After investigation, it’s due to the usage of nanoseconds in the Java code that is not supported in H2 nor Postgres (our 2 DBs for tests and run).
They only support Timestemp with 6 digits (by default for H2, could be 9, but only 6 digits for postgresql).

H2: “If fractional seconds precision is specified it should be from 0 to 9, 6 is default.”
Data Types
Postgresql: time , timestamp , and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.
PostgreSQL: Documentation: 14: 8.5. Date/Time Types

The previous sequence runs in our previous JVM produces this sql exchange:

[           main] org.hibernate.SQL              : insert into myTable (created_at, updated_at, level, name, parent_id, res grp, sort_number, uid, dtype, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, 'Node', ?)
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [TIMESTAMP] - [2020-09-09T10:15:20+02:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [TIMESTAMP] - [2022-03-08T10:29:00.108198+01:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [INTEGER] - [2]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [4] as [VARCHAR] - [a node]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [5] as [BIGINT] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [6] as [BOOLEAN] - [false]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [7] as [VARCHAR] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [8] as [BIGINT] - [251]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [9] as [VARCHAR] - [uid]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [10] as [BIGINT] - [251]
[           main] org.hibernate.SQL              : update myTable set created_at=?, updated_at=?, level=?, name=?, parent_id=?, res=?, grp=?, sort_number=? where id=? and updated_at=?
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [TIMESTAMP] - [2020-09-09T10:15:20+02:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [TIMESTAMP] - [2022-03-08T10:29:00.155311+01:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [INTEGER] - [3]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [4] as [VARCHAR] - [a node]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [5] as [BIGINT] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [6] as [BOOLEAN] - [false]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [7] as [VARCHAR] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [8] as [BIGINT] - [251]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [9] as [BIGINT] - [251]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [10] as [TIMESTAMP] - [2022-03-08T10:29:00.108198+01:00[Europe/Zurich]]

In this case everything is ok.
A request on the table return this:

select updated_at from myTable;
UPDATED_AT  
2022-03-08 10:29:00.108198

Please note that we have 6 digits: 10:29:00.108198

After the upgrade to Java 17:

[           main] org.hibernate.SQL              : insert into myTable (created_at, updated_at, level, name, parent_id, res, grp, sort_number, uid, dtype, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, 'Node', ?)
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [TIMESTAMP] - [2020-09-09T10:15:20+02:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [TIMESTAMP] - [2022-03-08T08:54:09.379015900+01:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [INTEGER] - [2]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [4] as [VARCHAR] - [a node]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [5] as [BIGINT] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [6] as [BOOLEAN] - [false]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [7] as [VARCHAR] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [8] as [BIGINT] - [251]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [9] as [VARCHAR] - [uid]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [10] as [BIGINT] - [251]
[           main] org.hibernate.SQL              : update myTable set created_at=?, updated_at=?, level=?, name=?, parent_id=?, res=?, grp=?, sort_number=? where id=? and updated_at=?
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [TIMESTAMP] - [2020-09-09T10:15:20+02:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [TIMESTAMP] - [2022-03-08T08:54:09.539991700+01:00[Europe/Zurich]]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [INTEGER] - [3]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [4] as [VARCHAR] - [a node]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [5] as [BIGINT] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [6] as [BOOLEAN] - [false]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [7] as [VARCHAR] - [null]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [8] as [BIGINT] - [251]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [9] as [BIGINT] - [251]
[           main] o.h.t.d.sql.BasicBinder        : binding parameter [10] as [TIMESTAMP] - [2022-03-08T08:54:09.379015900+01:00[Europe/Zurich]]
[           main] o.h.e.j.b.i.AbstractBatchImpl  : HHH000010: On release of batch it still contained JDBC statements
[           main] o.h.e.j.b.i.BatchingBatch      : HHH000315: Exception executing batch [org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: update myTable set created_at=?, updated_at=?, level=?, name=?, parent_id=?, res=?, grp=?, sort_number=? where id=? and updated_at=?], SQL: update myTable set created_at=?, updated_at=?, level=?, name=?, parent_id=?, res=?, grp=?, sort_number=? where id=? and updated_at=?

The difference is the usage of nanos in the parameter value: :54:09.539991700

The second request fails (the update) because the where clause is:
updated_at = 2022-03-08T08:54:09.379015900 but the value in the DB has been truncated:

select updated_at from myTable;
UPDATED_AT  
2022-03-08 09:29:00.108198

The where clause doesn’t match the raw and fail with the OptimisticLock exception.
Does exist a parameter to fix this behaviour ?
I don’t find anything in hibernate nor jdk.

Thanks for you help,
Philippe

beikov · March 15, 2022, 8:45am

Are you saying that nano-second precision is only available on JDK 17 in general or only on Azul JDK 17? I can see how this is an issue and you should create a bug in the issue tracker: https://hibernate.atlassian.net

Philippe_Kernevez · April 4, 2022, 5:28pm

Sorry for the delay, but I did some additional tests and investigations.

The ticket is here : [HHH-15166] - Hibernate JIRA

And I created a project with docker to reproduce the issue: Philippe Kernevez / hibernate and h2 issue with java 16 · GitLab

In fact it’s more related to h2 (and Java 16 of course). I’m not able to reproduce it with postgres.

Michael_Brizic · August 1, 2022, 4:33pm

I believe I’m also experiencing this problem having upgraded our Spring Boot based web app to Java 17.
We are using Boot version 2.6.8 which uses Hibernate 5.6.9. We cannot upgrade to Hibernate v6 yet as Spring Boot 2.6.8 prohibits it.

Is there another way to leverage @Version at field level for a DATETIME(6) column and the current version of Hibernate we are using?

For example, is there a way to force microseconds at JVM level or within some Hibernate configuration? Or, can I programmatically force Hibernate to use microseconds in spite of JVM using nanoseconds?

We use H2 for integration tests (my understanding is I can configure to 9 precision) but in production we use MySQL and my understanding is that MySQL limits to 6 precision.

Philippe_Kernevez · August 3, 2022, 3:09pm

I found a temporary ‘solution’ using custom type for H2:

Our final solution was to move to testcontainers and to use only one DB.

Akifb · December 13, 2023, 11:30am

We faced with the similar problem, I want to mention solution alternatives here:

If the database support 9 precision after second (like H2)
- Alternative 1: You can add @Column(columnDefinition = "TIMESTAMP(9)") on top of version column, so that Hibernate will be generating version column with 9 precision.
- Alternative 2: You can register timestamp type with “TIMESTAMP(9)” in custom H2 dialect as below. So that all timestamp columns will be created according to this registration.

registerColumnType(Types.TIMESTAMP, "TIMESTAMP(9)")

If the database does not support 9 precision like PostgreSQL, and especially the problem is not only in test scope.
- Alternative 3: You can add new UserVersionType. In this type you can truncate the created instant for version to unify OS behavior. Especially overriding seed, next and equals methods are critical.

class InstantMicroPrecisionType : UserVersionType {

    private val sqlTypeArray = intArrayOf(StandardBasicTypes.TIMESTAMP.sqlType())

    override fun sqlTypes(): IntArray {
        return sqlTypeArray
    }

    override fun seed(session: SharedSessionContractImplementor?): Any {
        return Instant.now().truncate()
    }

    override fun next(current: Any?, session: SharedSessionContractImplementor?): Any {
        return Instant.now().truncate()
    }

    override fun equals(x: Any?, y: Any?): Boolean {
        val truncatedX = (x as? Instant)?.truncate()
        val truncatedY = (y as? Instant)?.truncate()
        return truncatedX == truncatedY
    }

    private fun Instant.truncate() = this.truncatedTo(ChronoUnit.MICROS)
}

Alternative 4: You can upgrade to Hibernate 6

Topic		Replies	Views
Explain why datetime types for @Version get OLE using >= Java 15 Hibernate ORM	9	643	January 5, 2023
LocalTime.MAX and Postgres Hibernate ORM	4	495	December 13, 2023
Timestamp value precision change Hibernate ORM	5	1402	February 26, 2024
JPA entityManager.merge converts LocalDateTime to SQLServer 2012 datetime2 Hibernate ORM	1	1286	December 9, 2018
Hibernate incorrectly converting SQL Time to LocalTime between 12am and 1am (only if using LocalTime.now()) Hibernate ORM	3	1101	March 4, 2024

Java 17 @version and nanoseconds truncation

Related topics