title: Spring Boot use multiple DataSources in one Transaction
body: <p>My Setup is the following: </p>
  
  <p>I Have a Spring Boot 2.1.1 Application. For Connection Pooling Hikari
  is used.</p>
  
  <p>I would like to write one service which routes the READs to a READER database and the WRITES to a Master database in one transaction/method. </p>
  
  <p>So i want the following to get to work:</p>
  
  <pre><code>@Transactional
  public test(Test test){
      Optional&lt;Res&gt; result = myRepo.findByAttr(test.getTest()); //do that on reader database
      validateIfResultExists(result); 
  
      myRepo.save(test);//do this on master database
  }
  </code></pre>
  
  <p>For that i wrote a custom DbContextHolder:</p>
  
  <pre><code>public class DbContextHolder {
  private static final ThreadLocal&lt;DbType&gt; contextHolder = new ThreadLocal&lt;DbType&gt;();
  
  
  public static void setDbType(DbType dbType) {
      if(dbType == null){
          throw new NullPointerException();
      }
      contextHolder.set(dbType);
  }
  
  public static DbType getDbType() {
      if(contextHolder.get() == null){
          DbContextHolder.setDbType(DbType.MASTER);
      }
  
      return (DbType) contextHolder.get();
  }
  
  public static void clearDbType() {
      contextHolder.remove();
  }
  }
  </code></pre>
  
  <p>I also have a custom RoutingDataSource:</p>
  
  <pre><code>public class RoutingDataSource extends AbstractRoutingDataSource {
  @Override
  protected Object determineCurrentLookupKey() {
      return DbContextHolder.getDbType();
  }
     }
  </code></pre>
  
  <p>And a DataSource for configuring database with hikari:</p>
  
  <pre><code>@Configuration
  public class DataSourceConfig {
  
  private static final String PRIMARY_DATASOURCE_PREFIX = "spring.primary.datasource";
  private static final String REPLICA_DATASOURCE_PREFIX = "spring.replica.datasource";
  
  @Autowired
  private Environment environment;
  
  @Value("${spring.datasource.hikari.connectionTimeout}")
  private Long CONNECTION_TIMEOUT;
  
  @Value("${spring.datasource.hikari.idleTimeout}")
  private Long IDLE_TIMEOUT;
  
  @Value("${spring.datasource.hikari.maxLifetime}")
  private Long MAX_LIFETIME_TIMEOUT;
  
  @Value("${spring.datasource.hikari.minimumIdle}")
  private int MINIMUM_IDLE;
  
  @Value("${spring.datasource.hikari.maximumPoolSize}")
  private int MAX_POOL_SIZE;
  
  @Resource
  
  @Bean
  @Primary
  public DataSource dataSource() {
      final RoutingDataSource routingDataSource = new RoutingDataSource();
  
      final DataSource primaryDataSource = buildDataSource("PrimaryHikariPool", PRIMARY_DATASOURCE_PREFIX);
      final DataSource replicaDataSource = buildDataSource("ReplicaHikariPool", REPLICA_DATASOURCE_PREFIX);
  
      final Map&lt;Object, Object&gt; targetDataSources = new HashMap&lt;&gt;();
      targetDataSources.put(DbType.MASTER, primaryDataSource);
      targetDataSources.put(DbType.REPLICA, replicaDataSource);
  
      routingDataSource.setTargetDataSources(targetDataSources);
      routingDataSource.setDefaultTargetDataSource(primaryDataSource);
  
      return routingDataSource;
  }
  
  private DataSource buildDataSource(String poolName, String dataSourcePrefix) {
      final HikariConfig hikariConfig = new HikariConfig();
      hikariConfig.setConnectionTimeout(CONNECTION_TIMEOUT);
      hikariConfig.setIdleTimeout(IDLE_TIMEOUT);
      hikariConfig.setMaxLifetime(MAX_LIFETIME_TIMEOUT);
      hikariConfig.setMinimumIdle(MINIMUM_IDLE);
      hikariConfig.setMaximumPoolSize(MAX_POOL_SIZE);
  
      hikariConfig.setPoolName(poolName);
      hikariConfig.setJdbcUrl(environment.getProperty(String.format("%s.url", dataSourcePrefix)));
      hikariConfig.setUsername(environment.getProperty(String.format("%s.username", dataSourcePrefix)));
      hikariConfig.setPassword(environment.getProperty(String.format("%s.password", dataSourcePrefix)));
      hikariConfig.setDriverClassName(environment.getProperty(String.format("%s.driver", dataSourcePrefix)));
  
      return new HikariDataSource(hikariConfig);
  }
  
  }
  </code></pre>
  
  <p>But that doesn't seem to work. If i log the current DbType everythint looks gread, but it doesn't work since i get the following error:</p>
  
  <blockquote>
    <p>ERROR: cannot execute INSERT in a read-only transaction</p>
  </blockquote>