Spring RoutingDataSource to Work with Multiple DataSources - II

In previous article, we have discussed about using Spring 'AbstractRoutingDataSource' to dynamically routing the database call to desired database (i.e. data source). This is very handy feature and useful in various scenarios. 

One important use case can be in multi-tenant application. In multi-tenant application, one of the database design consideration can be to use separate database for each tenant. It helps to keep the data of each tenant separate, and would be good for performance also. And, good design would require to use the same application layer code to work with all tenant and their specific database. In this scenario, use of 'AbstractRoutingDataSource' can help to achieve these design considerations. Extend the AbstractRoutingDataSource to create a custom 'MultiTenantRoutingDataSource'. Use a thread local application context state holder to maintain the tenant state for current flow. Use this thread local context in extended 'MultiTenantRoutingDataSource' to return the tenant specific data source. Again keep in consideration please, that Routing Data Source will be invoked by TransactionManager before starting the transaction only. Once transaction is started, data source can not be changed. 

Let us see the example of data source definition in Context for dynamically switching between OLTP and Reporting Database: 

<bean id="dataSourceOltp" name="oltpDataSource" class="..PooledDataSource">
<property name="targetDataSource">
<jee:jndi-lookup jndi-name="jdbc/DB" cache="true" />

<bean id="dataSourceReportDb" name="dataSourceReportDb" class="..PooledDataSource">
<property name="targetDataSource">
<jee:jndi-lookup jndi-name="jdbc/ReportDB" cache="true" />

<bean id="customDataSource" class="com.a.b.common.db.DBCustomRoutingDataSource ">
<property name="targetDataSources">
<map key-type="com.a.b.util.DataSourceConstants">
<entry key="OLTP_DB" value-ref="dataSourceOltp"/>
<entry key="REPORT_DB" value-ref="dataSourceReportDb"/>
<property name="defaultTargetDataSource" ref="dataSourceOltp"/>

<bean id="txAwareCustomDS" name="txAwareCustomDS" class="org.springframework.jdbc.datasource.Transac tionAwareDataSourceProxy">
<property name="targetDataSource" ref="customDataSource">

<bean id="CustomTxManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="txAwareCustomDS" />

In above context definition, we may define data sources for multiple tenants in place of OLTP or Reporting Database. To extend the design further for multiple tenants, definition can be made dynamic.

Important Note:

If you are using TransactionAwareDataSourceProxy with DataSourceTransactionManager, it should always be the outer most wrapper in the hierarchy. Keep data sources hierarchy as following:

TransactionAwareDataSourceProxy > Routing Data Source > Lazy Data Source > Pooled Data Source

Reason is, DataSourceTransactionManager will never work with TransactionAwareDataSourceProxy, rather it picks the wrapped data source to work upon. DataSourceTransactionManager has special handling for TransactionAwareDataSourceProxy to work with actual DataSource wrapped in it. So it is important to keep the TransactionAwareDataSourceProxy as outer most layer. Or otherwise, DataSourceTransactionManager will not be able to handle the transactions properly. Read more at following links:

Java Technology Enthusiasts - How to set Transaction Manager Programatically
Spring Forum - Problem in Managing Managing Transaction with AbstractRoutingDataSource

Hope it will help. Please share comments for addition. You may contribute by:
  • Posting your comments which will add value to the article contents
  • Posting the article link on Social Media using 'Social Media Bookmark' bar
  • Consider joining us at 'Java Technology Enthusiasts' linkedin group to participate in discussions
  • Connecting with 'VedantaTree' on Facebook

People who read this post also read :


徐明明 said...

i meet same question,but use above solution,it does't work,a little different is in my transaction method,there are read and write method,i use aop to intercept sqlsession's insert ,update,with writedatasource,select with readdatasource,inner transaction method,once has readdatasource,other method‘s operation both use read
can above solution solve this situation?

Mohit Gupta said...

A simply transaction can work with one data source at a time, be it read or write. If requirement is to work with multiple data sources in same transaction, then it will need distributed transactions with some third part extensions with 1 or 2 Phase Commit. JTA is used for distributed tx. You need to find a suitable implementation of JTA. If any J2EE compliant server is in use, it provides JTA implementation by default or can use open source implementations also like JOTM. Following link will add more information. http://spring.io/.../configuring-spring-and-jta-without.../. Hope it helps.

Anonymous said...

Great article. Thanks!

Hemant Nagpure said...

In my case, it ran perfectly.
But hbm2ddlschemaupdate was performed only on a single DB (DefaultTargetDatabase), whereas schema wasn’t updated for any other targetDB in AbstractRoutingDataSource


Post a Comment