Updating an Entity with composite PK that is created in the same transaction fails

Description

Downstream quarkus bug: JPA Composite key update fails · Issue #43826 · quarkusio/quarkus

Updating a JPA Entity, created in the same transaction, with a composite key (via @IdClass and @Embedded + @Id) fails with a org.hibernate.StaleObjectStateException at the next em.flush():

jakarta.persistence.OptimisticLockException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [root.TestEntity#TimeScalePkOidDatum{oid='yyy', datum=2024-10-11T09:14:39.756707689Z}]
	at org.hibernate.internal.ExceptionConverterImpl.wrapStaleStateException(ExceptionConverterImpl.java:209)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:95)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:167)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:173)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1433)
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1415)
	at io.quarkus.hibernate.orm.runtime.session.TransactionScopedSession.flush(TransactionScopedSession.java:235)
	at org.hibernate.engine.spi.SessionLazyDelegator.flush(SessionLazyDelegator.java:83)
	at org.hibernate.Session_OpdLahisOZ9nWRPXMsEFQmQU03A_Synthetic_ClientProxy.flush(Unknown Source)
	at test.DummyTest.doTest(DummyTest.java:31)
	at test.DummyTest_Subclass.doTest$$superforward(Unknown Source)
	at test.DummyTest_Subclass$$function$$2.apply(Unknown Source)
	at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:73)
	at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:62)
	at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:136)
	at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:107)
	at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.doIntercept(TransactionalInterceptorRequired.java:38)
	at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.intercept(TransactionalInterceptorBase.java:61)
	at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.intercept(TransactionalInterceptorRequired.java:32)
	at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired_Bean.intercept(Unknown Source)
	at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
	at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:30)
	at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27)
	at test.DummyTest_Subclass.doTest(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at io.quarkus.test.junit.QuarkusTestExtension.runExtensionMethod(QuarkusTestExtension.java:973)
	at io.quarkus.test.junit.QuarkusTestExtension.interceptTestMethod(QuarkusTestExtension.java:823)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [root.TestEntity#TimeScalePkOidDatum{oid='yyy', datum=2024-10-11T09:14:39.756707689Z}]
	at org.hibernate.engine.jdbc.mutation.internal.ModelMutationHelper.identifiedResultsCheck(ModelMutationHelper.java:75)
	at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.lambda$doStaticUpdate$9(UpdateCoordinatorStandard.java:785)
	at org.hibernate.engine.jdbc.mutation.internal.ModelMutationHelper.checkResults(ModelMutationHelper.java:50)
	at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:141)
	at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:55)
	at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:55)
	at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.doStaticUpdate(UpdateCoordinatorStandard.java:781)
	at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.performUpdate(UpdateCoordinatorStandard.java:328)
	at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.update(UpdateCoordinatorStandard.java:245)
	at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:169)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:644)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:511)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:414)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41)
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1429)
	... 24 more

The hibernate-orm reproducer PR is here: https://github.com/hibernate/hibernate-orm/pull/9084

Linked issues

relates to

HHH-16791

EntitManager#find with IdClass with OffsetDateTime field returns null when Batch is enabled

Web links

Activity

Show:

Gavin King October 18, 2024 at 12:52 PM

it would be nice if Hibernate could at least provide a better error message, or a warning on startup if an entity uses a composite PK with an Instant, etc.

I really don’t know what we could possibly do to detect the problematic usage, without lots of false positives for perfectly legit things.

DM October 18, 2024 at 12:45 PM
Edited

I did some more testing, and it turns out this problem only happens with our local H2 DB we use for testing. Our production PG DB doesn’t have this issue.

What I ended up doing was creating a startup step for all of our tests and updating the column datatype with to TIMESTAMP(9):

select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS c
inner join INFORMATION_SCHEMA.TABLES t
    on
            c.TABLE_SCHEMA = t.TABLE_SCHEMA
        and c.TABLE_NAME   = t.TABLE_NAME
    where
            c.TABLE_SCHEMA       != 'INFORMATION_SCHEMA'
        and c.DATA_TYPE           = 'TIMESTAMP WITH TIME ZONE'
        and c.DATETIME_PRECISION  = 6
        and t.TABLE_TYPE          = 'BASE TABLE'
;

And then alter table %s.%s alter column %s set data type TIMESTAMP(9) WITH TIME ZONE for each result.

This solves the problem for us, however, it would be nice if Hibernate could at least provide a better error message, or a warning on startup if an entity uses a composite PK with an Instant, etc.

Gavin King October 18, 2024 at 10:47 AM

Is it possible to limit Hibernates java.time.Instant handling to microseconds

I mean, if you want to trim nanoseconds of your Instant before sending it to the JDBC driver, then I suppose you can write a custom type to do that. Or you can just make sure that you do it yourself before setting the Instant onto the entity. This is really your responsibility, IMO.

JPA forces us to have a PK

Not quite: JPA requires you to have an @Id. It is not necessarily the case that the identifier has to agree with the definition of the primary key in the database. (But of course if Hibernate is generating the DDL, then that’s what you’ll get.)

DM October 18, 2024 at 9:34 AM

Is it possible to limit Hibernates java.time.Instant handling to microseconds instead of changing the DB datatype to TIMESTAMP(9)?

We never actually need nanosecond precission, and our production database (PostgreSQL + Timescale) only supports microseconds https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT anyway.

DM October 18, 2024 at 8:50 AM

The workaround with @FractionalSeconds(9) worked, thanks!

I would also question whether nanosecond + oid is a sensible primary key type.

We want to use JPA / Hibernate on a Timescale table. However, there is a limitation that the primary key (if present) must include the time column. Since JPA forces us to have a PK (at least as far as I understand it), we are forced to also include the timestamp in the PK.

Won't Do

Details

Assignee

Unassigned

Reporter

Affects versions

6.6.1

Priority

Major

Created October 11, 2024 at 11:08 AM

Updated December 3, 2024 at 9:23 AM

Resolved October 18, 2024 at 12:53 PM

Configure