JdbcLockRegistry not able to recover from JpaSystemException that wraps SQLException with Postgres 40001 SQLState

[bootgenius]

I was trying to implement LockRegistryLeaderInitiator with the ability to use one table and using 2 or more regions in it.
The project is implemented using Spring Data Jpa and Postgres 13. When I run the application with this configuration sometimes I get JpaSystemException with inner 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

To reproduce this problem create JPA based application + use Postgres 13.2, create 2 JdbcLockRegistries, 2 DefaultLockRepositories and 2 LockRegistryLeaderInitiators, for example like this:

@Configuration
public class DatabaseLockConfig {

    private final String LOCK_PREFIX = "DISTRIBUTED_";

    @Bean("lockRepository1")
    public DefaultLockRepository lockRepository1(DataSource dataSource) {
        DefaultLockRepository defaultLockRepository = new DefaultLockRepository(dataSource);
        defaultLockRepository.setPrefix(LOCK_PREFIX);
        defaultLockRepository.setRegion("REGION_1");
        return defaultLockRepository;
    }

    @Bean("leaderInitiator1")
    public LockRegistryLeaderInitiator leaderInitiator1(@Qualifier("lockRepository1") LockRepository lockRepository) {
        JdbcLockRegistry lockRegistry = new JdbcLockRegistry(lockRepository);
        return new LockRegistryLeaderInitiator(lockRegistry, new DefaultCandidate("TEST_1", "TEST_1"));
    }

    @Bean("lockRepository2")
    public DefaultLockRepository lockRepository2(DataSource dataSource) {
        DefaultLockRepository defaultLockRepository = new DefaultLockRepository(dataSource);
        defaultLockRepository.setPrefix(LOCK_PREFIX);
        defaultLockRepository.setRegion("REGION_2");
        return defaultLockRepository;
    }

    @Bean("leaderInitiator2")
    public LockRegistryLeaderInitiator leaderInitiator2(@Qualifier("lockRepository2") LockRepository lockRepository) {
        JdbcLockRegistry lockRegistry = new JdbcLockRegistry(lockRepository);
        return new LockRegistryLeaderInitiator(lockRegistry, new DefaultCandidate("TEST_2", "TEST_2"));
    }
}

Create an event listener in the application like this:

@Component
public class LockLeaderListener {

    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    @EventListener(OnGrantedEvent.class)
    public void onGrantedEvent(OnGrantedEvent event) {
        logger.info(String.format("Leadership in %s has been granted for this node", event.getRole()));
    }

    @EventListener(OnRevokedEvent.class)
    public void onRevokedEvent(OnRevokedEvent event) {
        logger.info(String.format("Leadership in %s has been revoked for this node", event.getRole()));
    }
}

After that, if you run this application very soon you will see the problem. The leadership will be granted and revoked from time to time because the exception described above is not handled properly.

One of the workarounds for this problem is to override JpaTransactionManager and handle this exception manually:

@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;
                    }
                }
            }
        };
    }

[artembilan]

I looked into this again and I'm realizing that there is nothing to do (or can be done at all) on Spring Integration side.
Please, reconsider to raise a more general issue for JpaTransactionManager in https://github.com/spring-projects/spring-framework/issues project.
We still can have a link to this one from there.

Thanks.

[artembilan]

Closed as non-project-related.