Multiplexing read-only/read-write datasource

Question

I've implemented CustomRoutingDataSource. It should multiplex between two DataSources depending on whether current transation is ReadOnly or not.

public class CustomRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // Logic to determine whether to use read or write DataSource]
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "read" : "write";
    }
}

@Configuration
public class RoutingDBConfig {

    @Primary
    @Bean(name = "customRoutingDataSource")
    public DataSource customRoutingDataSource(
            @Qualifier("readDataSource") DataSource readDataSource,
            @Qualifier("writeDataSource") DataSource writeDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("read", readDataSource);
        targetDataSources.put("write", writeDataSource);

        CustomRoutingDataSource routingDataSource = new CustomRoutingDataSource();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(writeDataSource); // Fallback to write
        routingDataSource.afterPropertiesSet(); // Finalize initialization
        return routingDataSource;
    }
...


@RestController
public class FooBarController {

  private final BarRepository barRepo;

  @Autowired
  FooBarController(BarRepository barRepo) {
    this.barRepo = barRepo;
  }

  @GetMapping("/foobar/{id}")
  @Transactional(readOnly = true)
  public String fooBar(@PathVariable("id") Long id) {

    boolean ro = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
    Bar bar = barRepo.findById(id).orElseThrow(() -> new RuntimeException("Bar not found"));

    return bar.getBar() + "!";
  }

@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {

  @Transactional(readOnly = true)
  Optional<Foo> findById(Long id);

}

I followed various HOWTOs from Internet but unfortunatelly this does not work. Maybe it worked in older version but not now. According to debugger isCurrentTransactionReadOnly() returns false. SpringBoot injected code calls getConnection() to get connection from DataSource before it calls TransactionSynchronizationManager.setCurrentTransactionReadOnly(true).

When in determineCurrentLookupKey() the transactions is still read-write. Then connection is returned, then transaction is set to read-only and finnally inside method FooBarController.fooBar() the variable ro is set to true.

NOTE: I do not want to withdraw connection from pool and then execute alter session set read_only=true. I want to multiplex read-only transations into standby replica.

PS: I am on SpringBoot 3.4.0