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)
) aJpaSystemException
(Unable to commit against JDBC Connection) is thrown. But the root cause of this exception is aSQLException
with SQLSTATE 40001 . - if a statement is rejected the
SQLException
thrown by postgresql has SQLSTATE 40001 and is translated into aCannotAcquireLockException
.
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 ?
Comment From: 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.
Comment From: 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).