title: How to retry a PostgreSQL serializable transaction with Spring?
body: <p>I am developing against a PostgreSQL v12 database. I am using <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE" rel="noreferrer"><code>SERIALIZABLE</code></a> transactions. The general idea is that when PostgreSQL detects a serialization anomaly, one should retry the complete transaction.</p>
  
  <p>I am using Spring's <a href="https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/support/AbstractFallbackSQLExceptionTranslator.html" rel="noreferrer"><code>AbstractFallbackSQLExceptionTranslator</code></a> to translate database exceptions to Spring's exception classes. This exception translator should translate the PostgreSQL error <a href="https://www.postgresql.org/docs/10/errcodes-appendix.html" rel="noreferrer"><code>40001</code>/<code>serialization_failure</code></a> to a <a href="https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/dao/ConcurrencyFailureException.html" rel="noreferrer"><code>ConcurrencyFailureException</code></a>. Spring JDBC maintains a <a href="https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/resources/org/springframework/jdbc/support/sql-error-codes.xml#L261" rel="noreferrer">mapping file</a> to map the PostgreSQL-specific code <code>40001</code> to a generic <code>cannotSerializeTransactionCodes</code> class of database exceptions, which translates into a <code>ConcurrencyFailureException</code> for the API user.</p>
  
  <p>My idea was to rely on the Spring Retry project to retry a <code>SERIALIZABLE</code> transaction which is halted due to a serialization error as following:</p>
  
  <pre><code>@Target({ElementType.METHOD, ElementType.TYPE})
  @Retention(RetentionPolicy.RUNTIME)
  @Documented
  @Retryable(include = ConcurrencyFailureException.class, maxAttempts = ..., backoff = ...)
  @Transactional(isolation = Isolation.SERIALIZABLE)
  public @interface SerializableTransactionRetry {
  }
  </code></pre>
  
  <p>In service implementation, I would simply replace <code>@Transactional</code> by <code>@SerializableTransactionRetry</code> and be done with it.</p>
  
  <p>Now, back to PostgreSQL. Essentially, there are two stages at which a serialization anomaly can be detected:</p>
  
  <ol>
  <li>during the execution of a statement</li>
  <li>during the commit phase of a transaction</li>
  </ol>
  
  <p>It seems that Spring's <code>AbstractFallbackSQLExceptionTranslator</code> 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:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>As you can see, PostgreSQL detects a serialization anomaly (<code>ERROR: could not serialize access due to ...</code>), but this is translated by Spring into a <a href="https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/TransactionSystemException.html" rel="noreferrer"><code>TransactionSystemException</code></a> instead of a <code>ConcurrencyFailureException</code>.</p>
  
  <p>I could alter the <code>SerializableTransactionRetry</code> annotation above to include a <code>TransactionSystemException</code> 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.</p>
  
  <p>Is this a shortcoming in Spring's <code>AbstractFallbackSQLExceptionTranslator</code>? I am using Spring 5.2.1.</p>