I'm using Spring JPA, dual data source in my application. Both Datasources works just fine. But intermittently (usually when application is idle / when difference between access time for 1st data source is 1 minute), it throw below exception when a new request comes in
For example, I made a write on master db -> Access slave db for some read operation for more than 1 minute -> Access master to write again, here below error is thrown
Could not open JPA EntityManager for transaction; nested exception is org.hibernate.TransactionException: JDBC begin transaction failed
Attaching master and slave database configuration as below.
Master Database Config
@Configuration
@EnableJpaRepositories(basePackages = "com.myproject.master.dao",
entityManagerFactoryRef = "masterEntityManager",
transactionManagerRef = "masterTransactionManager")
public class MasterDatabaseConfig {
@Autowired
private Environment env;
@Bean
@Primary
public LocalContainerEntityManagerFactoryBean masterEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(masterDataSource());
em.setPackagesToScan(new String[] { "com.myproject.master.dao" });
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("spring.jpa.properties.hibernate.dialect",
env.getProperty("spring.jpa.properties.hibernate.dialect"));
properties.put("spring.jpa.hibernate.ddl-auto",
env.getProperty("spring.jpa.hibernate.ddl-auto"));
properties.put("spring.jmx.default-domain", env.getProperty("spring.jmx.default-domain"));
properties.put("spring.datasource.tomcat.initial-size",
env.getProperty("spring.datasource.tomcat.initial-size"));
properties.put("spring.datasource.tomcat.max-wait",
env.getProperty("spring.datasource.tomcat.max-wait"));
properties.put("spring.datasource.tomcat.max-active",
env.getProperty("spring.datasource.tomcat.max-active"));
properties.put("spring.datasource.tomcat.max-idle",
env.getProperty("spring.datasource.tomcat.max-idle"));
properties.put("spring.datasource.tomcat.min-idle",
env.getProperty("spring.datasource.tomcat.min-idle"));
properties.put("spring.datasource.tomcat.default-auto-commit",
env.getProperty("spring.datasource.tomcat.default-auto-commit"));
properties.put("spring.jpa.open-in-view", env.getProperty("spring.jpa.open-in-view"));
properties.put("spring.jpa.show-sql", env.getProperty("spring.jpa.show-sql"));
properties.put("hibernate.physical_naming_strategy",
SpringPhysicalNamingStrategy.class.getName());
properties.put("hibernate.implicit_naming_strategy",
SpringImplicitNamingStrategy.class.getName());
em.setJpaPropertyMap(properties);
return em;
}
@Bean
@Primary
public DataSource masterDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.datasource.url"));
dataSource.setUsername(env.getProperty("spring.datasource.username"));
dataSource.setPassword(env.getProperty("spring.datasource.password"));
return dataSource;
}
@Bean
@Primary
public PlatformTransactionManager masterTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(masterEntityManager().getObject());
return transactionManager;
}
}
Slave Database Config
@Configuration
@EnableJpaRepositories(basePackages = "com.myproject.salve.services",
entityManagerFactoryRef = "slaveEntityManager",
transactionManagerRef = "slaveTransactionManager")
public class SlaveDatabaseConfig {
@Autowired
private Environment env;
@Bean
public LocalContainerEntityManagerFactoryBean slaveEntityManager() {
LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
em.setDataSource(slaveDataSource());
em.setPackagesToScan(new String[] { "com.myproject.salve.services" });
HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
em.setJpaVendorAdapter(vendorAdapter);
HashMap<String, Object> properties = new HashMap<>();
properties.put("spring.jpa.properties.hibernate.dialect",
env.getProperty("spring.jpa.properties.hibernate.dialect"));
properties.put("spring.jpa.hibernate.ddl-auto",
env.getProperty("spring.jpa.hibernate.ddl-auto"));
properties.put("spring.jmx.default-domain", env.getProperty("spring.jmx.default-domain"));
properties.put("spring.datasource.tomcat.initial-size",
env.getProperty("spring.datasource.tomcat.initial-size"));
properties.put("spring.datasource.tomcat.max-wait",
env.getProperty("spring.datasource.tomcat.max-wait"));
properties.put("spring.datasource.tomcat.max-active",
env.getProperty("spring.datasource.tomcat.max-active"));
properties.put("spring.datasource.tomcat.max-idle",
env.getProperty("spring.datasource.tomcat.max-idle"));
properties.put("spring.datasource.tomcat.min-idle",
env.getProperty("spring.datasource.tomcat.min-idle"));
properties.put("spring.datasource.tomcat.default-auto-commit",
env.getProperty("spring.datasource.tomcat.default-auto-commit"));
properties.put("spring.jpa.open-in-view", env.getProperty("spring.jpa.open-in-view"));
properties.put("spring.jpa.show-sql", env.getProperty("spring.jpa.show-sql"));
em.setJpaPropertyMap(properties);
return em;
}
@Bean
public DataSource slaveDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.replica.ds.url"));
dataSource.setUsername(env.getProperty("spring.replica.ds.username"));
dataSource.setPassword(env.getProperty("spring.replica.ds.password"));
return dataSource;
}
@Bean
public PlatformTransactionManager slaveTransactionManager() {
JpaTransactionManager transactionManager = new JpaTransactionManager();
transactionManager.setEntityManagerFactory(slaveEntityManager().getObject());
return transactionManager;
}
}