InvalidIsolationLevelException: HibernateJpaDialect Error encountered for read write datasource routing Hibernate

I am implementing a read write datasource routing using Vlad Mihlceas's post

Below is my config class

@Configuration
@EnableTransactionManagement
@EnableAspectJAutoProxy
@EnableJpaRepositories(
value = "com.api.repositories",repositoryBaseClass = BaseJpaRepositoryImpl.class
)
public class DBConfig {
@Autowired
private DbProps dbProps;
@Bean
public DataSource readWriteDataSource() {
LOGGER.info("DB Props: {}", dbProps.toString());
PGSimpleDataSource dataSource = new PGSimpleDataSource();
dataSource.setUrl(dbProps.getDbUrl());
dataSource.setUser(dbProps.getDbUser());
dataSource.setPassword(dbProps.getDbPassword());
dataSource.setLogUnclosedConnections(true);
return connectionPoolDataSource(dataSource);
}
@Bean
public DataSource readOnlyDataSource() {
PGSimpleDataSource dataSource = new PGSimpleDataSource();
dataSource.setUrl(dbProps.getReplicaDbUrl());
dataSource.setUser(dbProps.getDbUser());
dataSource.setPassword(dbProps.getDbPassword());
dataSource.setLogUnclosedConnections(true);
return connectionPoolDataSource(dataSource);
}
@Bean
@Primary
public TransactionRoutingDataSource actualDataSource() {
TransactionRoutingDataSource routingDataSource = new TransactionRoutingDataSource();
Map<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put(DataSourceType.READ_WRITE, readWriteDataSource());
dataSourceMap.put(DataSourceType.READ_ONLY, readOnlyDataSource());
routingDataSource.setTargetDataSources(dataSourceMap);
routingDataSource.setDefaultTargetDataSource(readOnlyDataSource());
return routingDataSource;
}
protected HikariConfig hikariConfig(DataSource dataSource) {
HikariConfig hikariConfig = new HikariConfig();
int cpuCores = Runtime.getRuntime().availableProcessors();
hikariConfig.setMaximumPoolSize(cpuCores * 4);
hikariConfig.setDataSource(dataSource);
hikariConfig.setIdleTimeout(600000);
hikariConfig.setLeakDetectionThreshold(30000);
hikariConfig.setConnectionTimeout(30000);
hikariConfig.setMaxLifetime(1800000);
hikariConfig.setAutoCommit(false);
return hikariConfig;
}
protected HikariDataSource connectionPoolDataSource(DataSource actualDataSource) {
return new HikariDataSource(hikariConfig(actualDataSource));
}
private DataSource dataSource() {
return ProxyDataSourceBuilder
.create(actualDataSource())
.name("Proxy-datasource")
.build();
}
@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
entityManagerFactoryBean.setPersistenceUnitName(getClass().getSimpleName());
entityManagerFactoryBean.setPersistenceProvider(new HibernatePersistenceProvider());
entityManagerFactoryBean.setDataSource(dataSource());
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
HibernateJpaDialect jpaDialect = vendorAdapter.getJpaDialect();
jpaDialect.setPrepareConnection(false);
entityManagerFactoryBean.setJpaVendorAdapter(vendorAdapter);
entityManagerFactoryBean.setJpaProperties(additionalProperties());
entityManagerFactoryBean.setPackagesToScan("com.api.models");
return entityManagerFactoryBean;
}
@Bean
public JpaTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(entityManagerFactory);
return transactionManager;
}
@Bean
public TransactionTemplate transactionTemplate(EntityManagerFactory entityManagerFactory) {
return new TransactionTemplate(transactionManager(entityManagerFactory));
}
protected Properties additionalProperties() {
Properties properties = new Properties();
properties.setProperty("hibernate.dialect", "org.hibernate.dialect.PostgreSQLDialect");
//properties.setProperty("hibernate.connection.release_mode", "after_transaction");
properties.setProperty("hibernate.connection.provider_disables_autocommit",Boolean.TRUE.toString());
return properties;
}
}

I'm having issues doing writes where I manually change Isolation level e.g

@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.REPEATABLE_READ, rollbackFor = Exception.class)

I get the following exception

org.springframework.transaction.InvalidIsolationLevelException: HibernateJpaDialect is not allowed to support custom isolation levels: make sure that its 'prepareConnection' flag is on (the default) and that the Hibernate connection release mode is set to ON_CLOSE

when I leave the jpaDialect.setPrepareConnection property as default as advised in the error message it works but I lose the routing functionality and all queries both reads and writes are sent to the primary instance. How do enable change of isolation level while still being able to route the the queries?

I would suggest to stay clear of the idea to perform DB routing via determining whether tx is readonly or not - it looks attractive in someone's blogpost, however there are to many edge cases when it might not work as expected.

By the way, I do believe you may overcome your issue via implementing your own PlatformTransactionManager, something like:

public class JpaTransactionManagerExposingReadOnlyStatus extends JpaTransactionManager {

    @Override
    protected Object doSuspend(Object transaction) {
        TxReadOnlyStatus.begin(null);
        boolean success = false;
        try {
            Object result = super.doSuspend(transaction);
            success = true;
            return result;
        } finally {
            if (!success) {
                TxReadOnlyStatus.complete();
            }
        }
    }

    @Override
    protected void doResume(Object transaction, Object suspendedResources) {
        try {
            super.doResume(transaction, suspendedResources);
        } finally {
            TxReadOnlyStatus.complete();
        }
    }

    @Override
    protected void doBegin(Object transaction, TransactionDefinition definition) {
        TxReadOnlyStatus.begin(definition.isReadOnly());
        boolean success = false;
        try {
            super.doBegin(transaction, definition);
            success = true;
        } finally {
            if (!success) {
                TxReadOnlyStatus.complete();
            }
        }
    }

    @Override
    protected void doCleanupAfterCompletion(Object transaction) {
        try {
            super.doCleanupAfterCompletion(transaction);
        } finally {
            TxReadOnlyStatus.complete();
        }
    }

}

public class TxReadOnlyStatus {

    private static final ThreadLocal<Deque<Boolean>> txStack = ThreadLocal.withInitial(LinkedList::new);

    public static boolean isReadOnly() {
        Boolean readOnly = getTxStack().peekLast();
        if (readOnly != null) {
            return readOnly;
        }
        if (isSynchronizationActive()) {
            // active synchronization
            return isCurrentTransactionReadOnly();
        }
        return true;
    }

    private static Deque<Boolean> getTxStack() {
        return txStack.get();
    }

    static void begin(Boolean readOnly) {
        getTxStack().addLast(readOnly);
    }

    static void complete() {
        getTxStack().removeLast();
    }

}

demo on GitHub

After a bit of research and also taking into consideration Andrey's answer I have sorted my issue by routing my traffic using PGPool2. However it is advisable to disable connection pooling ins spring boot and use the connection pool in PGPool.

This solution is also good since even other applications done in other languages can also take advantage of the routing.