Consistent `CannotAcquireLockException` translation for PostgreSQL serialization failure behind JPA

[ah1508]

When isolation is Serializable Snapshot Isolation (SSI) the application must be prepared to retry rejected transactions. Quote from postgresql documentation: "It is important that an environment which uses this technique have a generalized way of handling serialization failures (which always return with an SQLSTATE value of '40001'), because it will be very hard to predict exactly which transactions might contribute to the read/write dependencies and need to be rolled back to prevent serialization anomalies.".

Spring retry could be the "generalized way" but based on what type of exception ?

With JDBC the SQLSTATE 40001 is translated in a CannotAcquireLockException.

With Jpa and postgresql it is depends:

• if the commit is rejected (transactionManager.commit(tx)) a JpaSystemException (Unable to commit against JDBC Connection) is thrown. But the root cause of this exception is a SQLException with SQLSTATE 40001 .
• if a statement is rejected the SQLException thrown by postgresql has SQLSTATE 40001 and is translated into a CannotAcquireLockException.

An option is to test the root exception:

boolean serializationFailure = NestedExceptionUtils.getRootCause(exception) instanceof SQLException se && "40001".equals(se.getSQLState());

At a high level the common denominator is DataAccessException, but then any data access problem would triggers a retry. Bad sql queries should result in test failure during dev but still, a specific SerializationFailureException would help. I don't know if it is realistic, is state 40001 really reliable across databases ? How about NoSQL ACID database ?

[jhoeller]

We specifically handle '40001' as a CannotAcquireLockException across relational databases as far as possible, so that would be the exception to use for a retry. It doesn't have to be a more specialized exception that we'd have to introduce.

That said, the problem in your scenario above is the JPA commit which does not translate to a CannotAcquireLockException. We aim to translate lock exceptions even for a commit attempt, see JdbcTransactionManager, so I wonder why this does not kick in for JpaTransactionManager. Are you using HibernateJpaVendorAdapter there, with the specific translation in HibernateJpaDialect?

Could you paste the stacktrace of that transaction exception with its SQLException root cause here? I suppose this a JPA RollbackException at the top level, with a Hibernate TransactionException as the immediate cause, and then the SQLException as the root cause? By design, we should be able to find a lock failure indication there and translate it to a CannotAcquireLockException... but it seems that due to Hibernate's TransactionException wrapping, this does not work in your scenario and you end up with the JpaSystemException fallback. Let's try to address that part within this GitHub issue.

[ah1508]

Indeed, if CannotAquireLockException already handles every 40001 error there is no room for a new exception class. How about a word in the documentation of this class regarding serialization anomaly as a possible cause for this exception ?

Regarding JPA, when a statement is rejected, what I wrote in my first message about JPA is not correct. Correction:

if a statement is rejected due to serialization anomaly:

Error message:

org.hibernate.exception.LockAcquisitionException: could not execute statement [ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during write.
Hint: The transaction might succeed if retried.] [insert into ...]

(the real sql query is shown, not "...").

Stacktrace:

jakarta.persistence.OptimisticLockException: org.hibernate.exception.LockAcquisitionException: could not execute statement [ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during write.
Hint: The transaction might succeed if retried.] [insert into ...]
at org.hibernate.internal.ExceptionConverterImpl.wrapLockException(ExceptionConverterImpl.java:249)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:98)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:162)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:168)
at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:761)
at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:739)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:311)

If the commit (txManager.commit) is rejected due to serialization anomaly

org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:320)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:229)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:565)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
...
Caused by: org.hibernate.TransactionException: Unable to commit against JDBC Connection
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:92)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:268)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:561)
... 11 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried.

JpaSystemException extends DataAccessException (but not CannotAquireLockException) and LockAcquisitionException is not translated into a Spring exception. I don't use any specific JpaVendorAdapter (but a bean of this type exists in the context due to autoconfiguration).