AbstractRoutingDataSource and AbstractTransactionalSpringContextTests

[jsimone]

We are using an AbstractRoutingDataSource implementation as described by Mark Fisher's article "Dynamic DataSource Routing" described here http://blog.springsource.com/2007/01...ource-routing/.

In this excellent article Fisher uses AbstractDependencyInjectionSpringContextTests and not AbstractTransactionalSpringContextTests for the junit testing.
Unfortunately, having transactions in the mix to roll-back or commit the data for each test causes the routing to take place at the start of the transaction -- at the top of the test. If the code changes data sources in the middle, the changes to the new data sources are ignored. We can see evidence of this below.

If I try this simple test code extending AbstractTransactionalSpringContextTests ....

public void test_dataSourceRouting() throws Exception {

MyDataSourceKeyHolder.setDataSourceName("A");
    	System.out.println("--> name ="+MyDataSourceKeyHolder.getDataSourceName());
    	JdbcTemplate t = new JdbcTemplate(dataSource);
    	int rows = t.queryForInt("select count(*) from table");
    	System.out.println("--> Rows ="+rows);
    	
    	MyDataSourceKeyHolder.setDataSourceName("B");
    	System.out.println("--> name ="+ MyDataSourceKeyHolder.getDataSourceName());
    	t = new JdbcTemplate(dataSource);
    	rows = t.queryForInt("select count(*) from table");
    	System.out.println("--> Rows ="+rows);
    	
    	MyDataSourceKeyHolder.setDataSourceName("something that does not exist!!");
    	System.out.println("--> name ="+MyDataSourceKeyHolder.getDataSourceName());
    	t = new JdbcTemplate(dataSource);
    	rows = t.queryForInt("select count(*) from table");
    	System.out.println("--> Rows ="+rows);
}

Even though the first 2 DBs have tables with differing number of rows and the last DB does not exist, we get back exactly the same result for all 3 queries because the transaction begins with being set to the default datasource in my XML Spring config, which does exist. The debugged output is below.

INFO  Starting test SubTest#test_dataSourceRouting
DEBUG Using transaction object [org.springframework.jdbc.datasource.DataSourceTransactionManager$DataSourceTransactionObject@19ba640]
DEBUG Creating new transaction with name [null]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
====>>>>> MyRoutingDataSource.determineCurrentLookupKey null
DEBUG Acquired Connection [jdbc:db2://url, UserName=name, IBM DB2 JDBC Universal Driver Architecture] for JDBC transaction
DEBUG Switching JDBC Connection [jdbc:db2://url, UserName=name, IBM DB2 JDBC Universal Driver Architecture] to manual commit
DEBUG Bound value [org.springframework.jdbc.datasource.ConnectionHolder@11410e5] for key [MyRoutingDataSource@13e15f7] to thread [main]
DEBUG Initializing transaction synchronization
INFO  Began transaction (1): transaction manager [org.springframework.jdbc.datasource.DataSourceTransactionManager@61373f]; default rollback = true
...
...
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
--> name =A
DEBUG Executing SQL query [select count(*) from table]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
--> Rows =274
--> name =B
DEBUG Executing SQL query [select count(*) from table]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
--> Rows =274
--> name =something that does not exist!!
DEBUG Executing SQL query [select count(*) from table]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
DEBUG Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] bound to thread [main]
--> Rows =274
DEBUG Triggering beforeCompletion synchronization
DEBUG Removed value [org.springframework.jdbc.datasource.ConnectionHolder@caf0ed] for key [org.apache.commons.dbcp.BasicDataSource@159780d] from thread [main]
DEBUG Returning JDBC Connection to DataSource
DEBUG Initiating transaction rollback

The question I have is how is it that we can use all the benefits of a routing data source and all the benefits of transactions at the same time?

Is there a workaround for this?

thanks.

[jsimone]

bringing this back to the top. Hoping someone will respond.

[venugopalm59]

Hi jsimone,

Can you please share the work around, if you have already found one?

Regards,
Venugopal

[jsimone]

I have not found a solution. We are using an old version of Spring (v. 2.08 I think).

My hope is by updating to the latest version of Spring that this problem will go away.

[venugopalm59]

I removed the RoutingDataSource and instead did the following:

Extended the JdbcTemplate and did override the getDataSource() method to return the DataSource based on the context that is set in the ContextHolder. (Depending on the ThreadLocal)

Now things seem to work well with different schema and dynamic routing.

Do you forsee any problems here?

[Estebitan A.G.]

Hello Venugopal, jsimone:

I'm having the same problem.
I'm using the AbstractRoutingDataSource as mentioned in this link (http://blog.springsource.com/2007/01...ource-routing/).

Without transaction, everthing works ok.

However, when I use Transaction:


Map data = new HashMap();

DataSourceContextHolder.setKeyDataSource("children People"); //This is the only that works (curiously the first one).
data = this.DB1_DAO.findByPK("001") ;

DataSourceContextHolder.setKeyDataSource("youngPeo ple"); //This doesn't work
data = this.DB1_.findByPK("001") ;

DataSourceContextHolder.setKeyDataSource("adultPeo ple" ); //This doesn't work
data = this.DB1_.findByPK("001") ;

DataSourceContextHolder.setKeyDataSource("oldPeopl e" ); //This doesn't work
data = this.DB1_.findByPK("001") ;


Venugopal, can you show us your solution.

Thanks in advance.

Estebitan