JdbcLockRegistry not able to recover from TransactionSystemException

[culebras]

spring-integration/spring-integration-jdbc/src/main/java/org/springframework/integration/jdbc/lock/JdbcLockRegistry.java

Line 263 in b2e0635

catch (TransientDataAccessException | TransactionTimedOutException e) {

Should not the TransactionSystemException also be captured in that block and retried, as the acquire operation relies on a transaction isolation of type SERIALIZABLE, and therefore it should be ready to recover via retry transaction immediately for such kind of serialization problems.

I am attaching the exception we are very often getting:

Caused by: org.springframework.dao.CannotAcquireLockException: Failed to lock mutex at 04c819b6-efaa-4077-bdf9-3ee236d675b3; nested exception is org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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.
Error Code: 0
	at org.springframework.integration.jdbc.lock.JdbcLockRegistry$JdbcLock.rethrowAsLockException(JdbcLockRegistry.java:170)
	at org.springframework.integration.jdbc.lock.JdbcLockRegistry$JdbcLock.tryLock(JdbcLockRegistry.java:235)
	... 40 common frames omitted
Caused by: org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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.
Error Code: 0
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:571)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
	at org.springframework.integration.jdbc.lock.DefaultLockRepository$$EnhancerBySpringCGLIB$$aac18bb5.acquire(<generated>)
	at org.springframework.integration.jdbc.lock.JdbcLockRegistry$JdbcLock.doLock(JdbcLockRegistry.java:241)
	at org.springframework.integration.jdbc.lock.JdbcLockRegistry$JdbcLock.tryLock(JdbcLockRegistry.java:222)
	... 41 common frames omitted
Caused by: javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 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.
Error Code: 0
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:161)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)
	... 52 common frames omitted
Caused by: org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: 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.
Error Code: 0
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicCommitTransaction(DatabaseAccessor.java:454)
	at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.commitTransaction(DatasourceAccessor.java:408)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.commitTransaction(DatabaseAccessor.java:436)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicCommitTransaction(AbstractSession.java:826)
	at org.eclipse.persistence.sessions.server.ClientSession.basicCommitTransaction(ClientSession.java:189)
	at org.eclipse.persistence.internal.sessions.AbstractSession.commitTransaction(AbstractSession.java:1033)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitTransaction(UnitOfWorkImpl.java:1655)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitTransaction(RepeatableWriteUnitOfWork.java:652)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitTransactionAfterWriteChanges(UnitOfWorkImpl.java:1670)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:287)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1222)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:136)
	... 53 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.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:857)
	at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:879)
	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:387)
	at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicCommitTransaction(DatabaseAccessor.java:446)
	... 64 common frames omitted

We are running very often into that issue, and as the locking framework currently does not support immediate retry for that errors, we need to push back the message and retry ourselves a couple of seconds later., which it is affecting the performance.

[artembilan]

OK. Since there is a Hint: The transaction might succeed if retried., we definitely can treat such an exception as retryable.

I think everywhere we have this code:

catch (TransientDataAccessException | TransactionTimedOutException e) {
	// try again
}

we need to add this one more exception to the list.

Feel free to contribute the fix: https://github.com/spring-projects/spring-integration/blob/main/CONTRIBUTING.adoc !

[culebras]

Sure, I will try to prepare a PR.

[bootgenius]

Hi. Today I bumped into a similar problem. I use Spring Boot JPA and use JdbcLockRegistry to do locking. I found that sometimes when SERIALIZED transaction is failed with Postgres 9.1 and higher, the JpaSystemTransaction is thrown with the following cause inside: TransactionException (org.hibernate.TransactionException: Unable to commit against JDBC Connection). The cause inside is PSQLException with SQLStata=40001:

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.

The problem is that Spring JPA does not wrap this exception with any ConcurrencyFailureException. The problem is well known and maybe one day will be fixed. The problem and workaround is highlighted here: https://stackoverflow.com/questions/59006479/how-to-retry-a-postgresql-serializable-transaction-with-spring

Can we do something with it?

[bootgenius]

Right now I have a workaround for this problem like this:

    @Bean
    public JpaTransactionManager getDataSourceTransactionManager(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory) {
            @Override
            protected void doCommit(DefaultTransactionStatus status) {
                try {
                    super.doCommit(status);
                } catch (JpaSystemException e) {
                    Throwable firstLevelThrowable = e.getCause();
                    if (firstLevelThrowable instanceof TransactionException) {
                        Throwable secondLevelThrowable = firstLevelThrowable.getCause();
                        if (secondLevelThrowable instanceof SQLException
                                && "40001".equals(((SQLException) secondLevelThrowable).getSQLState())) {
                            throw new CannotSerializeTransactionException(firstLevelThrowable.getMessage(), firstLevelThrowable);
                        }
                        throw e;
                    }
                }
            }
        };
    }