Multiple consecutive transactions (different in read and write) correct routing missing spring setting

[alinturbut]

Hey,

As this code has proved to be useful in my endeavour to create a master-slave routing for a mysql read replica configuration and having wasted a few hours on trying to understand why after a read transaction, the next write transaction would go to the read replica, I've found a missing link in this code.

Spring, by default (in newer versions) uses handling_mode: IMMEDIATE_ACQUISITION_AND_HOLD which closes a database connection only on session close (therefor going to the read replica database for the next transaction after a read only transaction). In order to solve this, you need to pass the following application property: spring.jpa.properties.hibernate.connection.handling_mode: DELAYED_ACQUISITION_AND_RELEASE_AFTER_TRANSACTION

I am not creating a PR as this repository seems not maintained anymore, so an issue would be more visible.

[abhishekvats007]

Hi, @alinturbut
Mine is working fine without setting this flag. After a read transaction, the next write is going to correct write db only.

@Override
@Transactional
public UserData saveUser() {
    UserData userData = new UserData();
    userData.setFirstName("Abhishek");
    userData.setName("Abhishek Vats");
    userData.setLastName("Vats");
    return userRepository.save(userData);
}

@Override
@Transactional(readOnly = true)
public UserData getUser(final int id) {
    Optional<UserData> byId = userRepository.findById(id);
    return byId.get();
}

This is simple use case I am testing for read after write. Can you please let me know what use case, I am missing ?

[pmd30011991]

If you do READ before WRITE, when the Transaction is open, it would acquire a replica connection and use it until the end of outer transaction

@Transaction()
function outer() {
   call getUser()
   call saveUser() <-- error throw here, could not write on read only connection
}

[kanakharaharsh]

@alinturbut @pmd30011991 I am not using Hibernate. How can I change the Database connection handling mode?

I tried the below approach but it's not working too
https://stackoverflow.com/questions/76136715/transactionsynchronizationmanager-iscurrenttransactionreadonly-always-returnin