Making DAO relying on RoutingDataSource thread safe

[bmelloni]

I have a library that provides access to many databases, all with the same table structure. The library has its own application-context.xml, and is exposed through a service bean in that context. Other applications use an import statement in their contexts and set a reference to the service bean.

The service bean's purpose is to retrieve data from one of the databases, based on a key value that determines the database to use. So, I have a DAO that relies on a RoutingDataSource, and the service bean calls a method in the DAO to set the key before making a call to retrieve data.

Here comes the problem:

- The application and even the service bean may use default Spring behavior and instantiate as singletons. That is optimal since it conserves memory and still remains threadsafe.

- But... if I load the DAO as a property into the service bean (as shown below), even if I set the DAO and RoutingDataSource with "prototype" scope (also shown below), I would be getting a single instance of the DAO in the service and lose thread safety (one thread could set the key to "1", a second thread set it to "2" and then the first thread could be accessing the wrong database!)

The cleanest solution I can think of (not a very good one) is:

- Not to define a myDao property in the myServiceImpl class
- Store the context in a static variable of myServiceImpl
- Get a fresh instance of the DAO in every method call by calling context.getBean().

Still, this "feels" wrong. Is there a better way?

Service bean, with a singleton instance of myDao:

  <bean id="myService" class="my.domain.MyServiceImpl">
    <property name="myDao" ref="myDao" />
  </bean>

DAO bean:

  <bean id="myDao" class="my.domain.MyDaoImpl" scope="prototype">
    <property name="dataSource"           ref="myRoutingDataSource" />
  </bean>

RoutingDataSource:

  <bean id="myRoutingDataSource" class="my.domain.MyRoutingDataSourceImpl" scope="prototype"> 
    <property name="targetDataSources">
      <map key-type="java.lang.Long">
        <entry key="1" value-ref="myDataSource1" />
        ...
        <entry key="3" value-ref="myDataSource10" />
      </map>
    </property>
  </bean>

[Marten Deinum]

You are making up problems which aren't there... The RoutingDataSource is thread safe... So your dao, service etc. can all be singletons... So no the different threads wouldn't see the other datasource, if you use springs data access material it relies on the thread bound connection/transaction and that (as stated) is thread bound.

[bmelloni]

Marten, I hear what you are saying but I'm not understanding how. Let me explain what I understand and perhaps you can point me to the spot where my logic breaks:

- Let's say we have application X. Application X has its own context which has an include statement for the library. Application X instantiates loads the context and instantiates its main bean (a single instance).

- The main bean has a property with a reference to the myService bean, which in turn has a property with a reference to myDao and it has a datasource property with the myRoutingDataSource. This chain of properties gets loaded at the time the main bean is loaded from the context. Each one is a single object. Based on the "prototype" scope, myService loads as a singleton, while myDao and myRoutingDataSource load a new instance of those 2 objects (even if a prior application or thread had loaded a reference to myService).

- Now, lets say that the bean launches 3 threads, and each of them use the myService reference to access the data. In the object chain present in application X, there is exactly one instance of myService, myDao and myRoutingDataSource. The threads simply access them.

- So, remembering that we have a single datasource object, lets say that the timing is "perfectly wrong" so that the following calls happen in this order:
--- Thread 1 sets the key to database 1.
--- Thread 2 sets the key to database 2 (overriding the previously set value).
--- Thread 3 sets the key to database 3 (again, overriding the previously set value).
--- Thread 1 queries some data. Because the key is set to point to database 3, now thread 1 retrieves data from database 3 instead of the expected database 1.

This would not be threadsafe. That is why I believe that I need to load the DAO from the context at the beginning of each service method call... thus getting a new instance of the DAO and RoutingDataSource... which because of the "prototype" scope, will load as a new object and thus independent from the equivalent objects from other threads and therefore guarantee retrieval from the right database.

The only way where I could see the above becoming threadsafe would be to do the setting of the key and the query all within the same transaction. Unfortunately, when you use @Transactional in a DAO method, the @Transactional code loads (or attempts to load) a datasource *before* the spot in the transactional method where you set the key for the database... resulting in errors or worse... queries from the wrong database. I (and others) have tested this and that is exactly what happens. If you use @Transactional for the DAO methods, the method only works properly if you set the RoutingDataSource key before calling the @Transactional DAO method.

So Marten, can you please tell me *where* my logic is wrong and how to modify the code so that it will be truly thread-safe while using simple references from application to service to DAO to RoutingDataSource in the contexts?

[Marten Deinum]

As I already stated it is thread safe... A connection is obtained on/before transactions start (next to that the real transactional layer should be your service NOT your dao layer!). The method determineCurrentLookupKey should obtain the key from a ThreadLocal and before you start a transaction the thread should set this threadlocal. If you have that mechanism in place your code is threadsafe (which IMHO it already is). It basically depends on how you retrieve/obtain the lookupKey. Any other way would beat the purpose of the AbstractRoutingDataSource...

So remove the prototype from your dao and your datasource and fix your implementation and imho you should also fix your transactional layer...