Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring: separate datasource for read-only transactions

Thanks for reading this.

I have 2 MySQL databases - master for writes, slave for reads. The perfect scenario I imagine is that my app uses connection to master for readOnly=false transactions, slave for readOnly=true transactions.

In order to implement this I need to provide a valid connection depending on the type of current transaction. My data service layer should not know about what type of connection it uses and just use the injected SqlMapClient (I use iBatis) directly. This means that (if I get it right) the injected SqlMapClients should be proxied and the delegate should be chosen at runtime.

public class MyDataService {

    private SqlMapClient sqlMap;

    @Autowired
    public MyDataService (SqlMapClient sqlMap) {
        this.sqlMap = sqlMap;
    }

    @Transactional(readOnly = true)
    public MyData getSomeData() {
        // an instance of sqlMap connected to slave should be used
    }

    @Transactional(readOnly = false)
    public void saveMyData(MyData myData) {
        // an instance of sqlMap connected to master should be used
    }
}

So the question is - how can I do this?

Thanks a lot

like image 431
artemb Avatar asked Jul 19 '10 12:07

artemb


People also ask

How do I make a transaction read-only in Spring boot?

The Spring JPA repository base class marks all the methods as read-only transactions. By adding this annotation at the class level, the behavior of the methods can change by just adding the @Transactional at the method level.

How do you use database routing in read-only and read-write in Spring?

In a Spring application, the web @Controller calls a @Service method, which is annotated using the @Transactional annotation. By default, Spring transactions are read-write, but you can explicitly configure them to be executed in a read-only context via the read-only attribute of the @Transactional annotation.

How do you split read-only and read/write transactions with JPA and Hibernate?

READ_WRITE key and the readOnlyDataSource using the DataSourceType. READ_ONLY key. So, when executing a read-write @Transactional method, the readWriteDataSource will be used while when executing a @Transactional(readOnly = true) method, the readOnlyDataSource will be used instead.

Why is it a best practice to mark transaction as read-only Spring?

Transactions indeed put locks on the database — good database engines handle concurrent locks in a sensible way — and are useful with read-only use to ensure that no other transaction adds data that makes your view inconsistent.


1 Answers

It's an interesting idea, but you'd have a tough job on your hands. The readOnly attribute is intended as a hint to the transaction manager, and isn't really consulted anywhere meaningful. You'd have to rewrite or extend multiple Spring infrastructure classes.

So unless you're hell-bent on getting this working a you want, your best option is almost certainly to inject two separate SqlMapClient objects into your DAO, and for the methods to pick the appropriate one. The @Transactional annotations would also need to indicate which transaction manager to use (assuming you're using DataSourceTransactionManager rather than JpaTransactionManager), taking care to match the transaction manager to the DataSource used by the SqlMapClient.

like image 75
skaffman Avatar answered Sep 19 '22 04:09

skaffman