How to retry a PostgreSQL serializable transaction with Spring?

Question

I am developing against a PostgreSQL v12 database. I am using SERIALIZABLE transactions. The general idea is that when PostgreSQL detects a serialization anomaly, one should retry the complete transaction.

I am using Spring's AbstractFallbackSQLExceptionTranslator to translate database exceptions to Spring's exception classes. This exception translator should translate the PostgreSQL error 40001/serialization_failure to a ConcurrencyFailureException. Spring JDBC maintains a mapping file to map the PostgreSQL-specific code 40001 to a generic cannotSerializeTransactionCodes class of database exceptions, which translates into a ConcurrencyFailureException for the API user.

My idea was to rely on the Spring Retry project to retry a SERIALIZABLE transaction which is halted due to a serialization error as following:

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Retryable(include = ConcurrencyFailureException.class, maxAttempts = ..., backoff = ...)
@Transactional(isolation = Isolation.SERIALIZABLE)
public @interface SerializableTransactionRetry {
}

In service implementation, I would simply replace @Transactional by @SerializableTransactionRetry and be done with it.

Now, back to PostgreSQL. Essentially, there are two stages at which a serialization anomaly can be detected:

1. during the execution of a statement
2. during the commit phase of a transaction

It seems that Spring's AbstractFallbackSQLExceptionTranslator is properly translating a serialization anomaly which is detected during the execution of a statement, but fails to translate one during the commit phase. Consider the following stack trace:

org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is 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.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:332)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:533)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.retry.interceptor.RetryOperationsInterceptor$1.doWithRetry(RetryOperationsInterceptor.java:91)
    at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:287)
    at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:164)
    at org.springframework.retry.interceptor.RetryOperationsInterceptor.invoke(RetryOperationsInterceptor.java:118)
    at org.springframework.retry.annotation.AnnotationAwareRetryOperationsInterceptor.invoke(AnnotationAwareRetryOperationsInterceptor.java:153)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)

As you can see, PostgreSQL detects a serialization anomaly (ERROR: could not serialize access due to ...), but this is translated by Spring into a TransactionSystemException instead of a ConcurrencyFailureException.

I could alter the SerializableTransactionRetry annotation above to include a TransactionSystemException as well, but I believe that would be wrong, as now we will be retrying upon any kind of transaction error, which is not what we want here.

Is this a shortcoming in Spring's AbstractFallbackSQLExceptionTranslator? I am using Spring 5.2.1.

Aglaia

I also asked this on github.com/spring-projects/spring-framework/issues/24064. The general policy is to ask questions on SO and raise concrete issues on github, but I wasn't sure if this could be considered as an issue or not. I haven't found any definite resource which states what ConcurrencyFailureException exactly represents, so one could argue that a AbstractFallbackSQLExceptionTranslator is doing its job just fine. However, in that case, I would argue that it is not really practical to use. – Aglaia

Answer 1

As explained in https://github.com/spring-projects/spring-framework/issues/24064#issuecomment-557800496, the SQLExceptionTranslator is actually not used for SQL exceptions happening during the commit phase.

In the same ticket, a proposal has been made to introduce this in Spring 5.3 (which I believe will be released somewhere in Q2 2020).

Aglaia

Answer 2

If you're using spring-boot you can create a custom DataSourceTransactionManager and in its doCommit method throw one of ConcurrencyFailureException subclasses if the caught exception's sql error code is 40001.

import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.dao.CannotSerializeTransactionException;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionSystemException;
import org.springframework.transaction.support.DefaultTransactionStatus;

@Component
public class MyDataSourceTransactionManager {

  @Bean
  DataSourceTransactionManager getDataSourceTransactionManager(DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource) {
      @Override
      protected void doCommit(DefaultTransactionStatus status) {
        try {
          super.doCommit(status);
        } catch (TransactionSystemException e) {
          Throwable throwable = e.getCause();
          if (throwable instanceof SQLException && "40001"
              .equals(((SQLException) throwable).getSQLState())) {
            throw new CannotSerializeTransactionException(throwable.getMessage(), throwable);
          }
          throw e;
        }
      }
    };
  }

}

You can also use a SQLExceptionTranslator to translate the SQLException into a DataAccessException instead of checking the sql error code.

import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionSystemException;
import org.springframework.transaction.support.DefaultTransactionStatus;

@Component
public class MyDataSourceTransactionManager {

  @Bean
  DataSourceTransactionManager getDataSourceTransactionManager(DataSource dataSource) {
    final SQLExceptionTranslator exTranslator = new SQLErrorCodeSQLExceptionTranslator(dataSource);
    return new DataSourceTransactionManager(dataSource) {
      @Override
      protected void doCommit(DefaultTransactionStatus status) {
        try {
          super.doCommit(status);
        } catch (TransactionSystemException e) {
          if (e.getCause() instanceof SQLException) {
            DataAccessException exception = exTranslator
                .translate("commit", null, (SQLException) e.getCause());
            if (!(exception instanceof UncategorizedSQLException)) {
              throw exception;
            }
          }
          throw e;
        }
      }
    };
  }

}

Kimble