Switching data source during a single transaction using multi tenant implementation

Question

I've been struggling for a few days to get this working, but it seems that I cannot find a solution to it. That's why I'd like to ask it here.

Short version

I have a multi tenant implementation which works with Spring boot, Spring Data JPA and Hibernate. This works like a charm. But now I'd like to implement a functionality where I switch the database (data source) during a single transaction. For example I use similar code in my service class

@Autowired
private CustomRepository customRepository;

@Autorwired
private CustomTenantIdentifierResolver customResolver;

@Transactional
public Custom getCustom(String name) {

  // Set the datasource to "one";
  this.customResolver.setIdentifier("one");
  Custom result = this.customRepository.findOneByName(name);

  //If the result is null, switch datasource to default and try again
  this.customResolver.setIdentifier("default");
  result = this.customRepository.findOneByName(name);

  return result;
}

The problem is, my data source does not switch. It uses the same source for the second request. I guess I'm doing something terribly wrong here.

What is the correct way to switch the data source during a single transaction?

EDIT (07-06-2016)
Since I noticed that switching the data source for a single transaction is not going to work, I'll add a followup.

Would it be possible to switch the data source in between two transactions for a single user request? If so, what would be the correct way to do this?

Long Version

Before moving on, I'd like to mention that my multi tenant implementation is based on the tutorial provided on this blog.

Now, my goal is to use the default data source as a fallback when the dynamic one (chosen by a custom identifier) fails to find a result. All this needs to be done in a single user request. It doesn't make a difference in the solution uses a single or multiple transactional annotated methods.

Until now I tried a couple things, one of them is described above, another includes the use of multiple transaction managers. That implementation uses a configuration file to create two transaction manager beans which each a different data source.

@Configuration
@EnableTransactionManagement
public class TransactionConfig {

  @Autowired
  private EntityManagerFactory entityManagerFactory;

  @Autowired
  private DataSourceProvider dataSourceProvider;

  @Bean(name = "defaultTransactionManager")
  public PlatformTransactionManager defaultTransactionManager() {
    JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
    jpaTransactionManager.setEntityManagerFactory(entityManagerFactory);
    jpaTransactionManager.setDataSource(dataSourceProvider.getDefaultDataSource());
    jpaTransactionManager.afterPropertiesSet();
    return jpaTransactionManager;
  }

  @Bean(name = "dynamicTransactionManager")
  public PlatformTransactionManager dynamicTransactionManager() {
    JpaTransactionManager jpaTransactionManager = new JpaTransactionManager();
    jpaTransactionManager.setEntityManagerFactory(entityManagerFactory);
    jpaTransactionManager.afterPropertiesSet();
    return jpaTransactionManager;
  }

}

Next I split the service method into two separate ones and added the @Transactional annotation including the right bean name

@Transactional("dynamicTransactionManager")
public Custom getDynamicCustom(String name) {
  ...stuff...
}

@Transactional("defaultTransactionManager")
public Custom getDefaultCustom(String name) {
  ...stuff...
}

But it didn't make any difference, the first data source was still used for the second method call (which should use the default transaction manager).

I hope someone can help me find a solution to this.
Thanks in advance.

Answer 1

Spring provides a variation of DataSource, called AbstractRoutingDatasource. It can be used in place of standard DataSource implementations and enables a mechanism to determine which concrete DataSource to use for each operation at runtime. All you need to do is to extend it and to provide an implementation of an abstract determineCurrentLookupKey method.

Keep in mind that determineCurrentLookupKey method will be called whenever TransactionsManager requests a connection. So, if you want to switch DataSource, you just need to open new transaction.

You can find example here http://fedulov.website/2015/10/14/dynamic-datasource-routing-with-spring/