Application:
I have implemented a job quartz that calls a method implemented in a @Service bean. Into this method there are interactions with two Data base (Oracle and PostgreSql). Every Db is inizialized with a Hikari Connection pool
Postgresql DB in configuration bean:
@Bean(value="sreDataSource")
public DataSource dataSource() {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName(driverClassName);
hikariConfig.setJdbcUrl(datasourceUrl);
hikariConfig.setUsername(datasourceUsername);
hikariConfig.setPassword(datasourcePassword);
hikariConfig.setConnectionInitSql("set time zone 'UTC'");
hikariConfig.setMaximumPoolSize(maxPoolSize);
hikariConfig.setConnectionTestQuery("SELECT 1");
hikariConfig.setPoolName("springHikariCP");
return new HikariDataSource(hikariConfig);
}
/**
* @return {@link JdbcTemplate}
*/
@Bean(value="sreJdbcTemplate")
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
jdbcTemplate.setFetchSize(Integer.parseInt(fetchSize));
jdbcTemplate.setQueryTimeout(60);
return jdbcTemplate;
}
/**
* Create the namedJdbcTemplate bean for the SRE database
* @return {@link NamedParameterJdbcTemplate}
*/
@Bean(value="sreNamedParameterJdbcTemplate")
public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
return new NamedParameterJdbcTemplate(jdbcTemplate());
}
/**
* @param dataSource
* @return {@link PlatformTransactionManager}
*/
@Bean(value="sreTransactionManager")
public PlatformTransactionManager transactionManager(@Qualifier("sreDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* @param sreTransactionManager
* @param assetTransactionManager
* @return {@link ChainedTransactionManager}
*/
@Bean(value="chainedTransactionManager")
public ChainedTransactionManager transactionManager(@Qualifier("sreTransactionManager") PlatformTransactionManager sreTransactionManager,
@Qualifier("assetTransactionManager") PlatformTransactionManager assetTransactionManager) {
return new ChainedTransactionManager(sreTransactionManager, assetTransactionManager);
}
Oracle DB in configuration bean:
/**
* Datasource
* @return Datasource
*/
@Bean(value="assetDataSource")
public DataSource dataSource() {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setDriverClassName(driverClassName);
hikariConfig.setJdbcUrl(datasourceUrl);
hikariConfig.setUsername(datasourceUsername);
hikariConfig.setPassword(datasourcePassword);
hikariConfig.addDataSourceProperty("oracle.jdbc.timezoneAsRegion", "false");
hikariConfig.setMaximumPoolSize(maxPoolSize);
hikariConfig.setConnectionTestQuery("SELECT 1 from dual");
hikariConfig.setPoolName("springHikariAssetCp");
return new HikariDataSource(hikariConfig);
}
/**
* JDBCTemplate
* @return JDBCTemplate
*/
@Bean(value="assetJdbcTemplate")
public JdbcTemplate jdbcTemplate() {
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
jdbcTemplate.setFetchSize(Integer.parseInt(fetchSize));
jdbcTemplate.setQueryTimeout(60);
return jdbcTemplate;
}
/**
* Create the namedJdbcTemplate bean for the RMD database
* @return NamedParameterJdbcTemplate
*/
@Bean(value="assetNamedParameterJdbcTemplate")
public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
return new NamedParameterJdbcTemplate(jdbcTemplate());
}
/**
* Create transaction manager
* @param dataSource Datasource
* @return transaction manager
*/
@Bean(value="assetTransactionManager")
public PlatformTransactionManager transactionManager(@Qualifier("assetDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
The service bean is called from job quartz in a cluster.The service bean is defined as the followed:
/**
* @author sgalgani
*
*/
@Service("processSreService")
@Transactional("chainedTransactionManager")
public class ProcessSreServiceImpl implements ProcessSreService {
private static final Logger LOGGER = LogManager.getLogger(ProcessSreServiceImpl.class);
@Autowired
@Qualifier("historianService")
private HistorianService historianService;
@Autowired
@Qualifier("sreEvolutionService")
private SreEvolutionService sreEvolutionService;
@Autowired
@Qualifier("assetService")
private AssetService assetService;
@Autowired
@Qualifier("configService")
private ConfigService configService;
@Autowired
@Qualifier("engineSupportService")
private EngineSupportService engineSupportService;
@Autowired
@Qualifier("tagAvailabilityService")
private TagAvailabilityService tagAvailabilityService;
@Autowired
@Qualifier("engineTransformService")
private EngineTransformService engineTransformService;
@Autowired
@Qualifier("quarantineService")
private QuarantineService quarantineService;
@Autowired
@Qualifier("simpleRuleEventService")
private SimpleRuleEventService simpleRuleEventService;
@Value("${save.oracle.temporary.results}")
private boolean saveOracleTemporaryResults;
@Value("${enable.save.local.db.alerts}")
private boolean enableSaveLocalDbAlerts;
@Value("${simple.rule.event.service.enable}")
private boolean simpleRuleEventServiceEnable;
@SuppressWarnings("javadoc")
@Override
@Transactional(value = "chainedTransactionManager", rollbackFor = Exception.class, timeout = 120)
public void processSre(Sre sre, List<TagLastTimestamp> tagsLastTimestamp, Metric metrics) throws Exception {
LOGGER.info("[processSre] for JobId : {} and lineup: {} ",sre.getId(), sre.getLineupId() );
After failover DB action on Amazon RDS postgresql (lost connection to DB), I would exepect a transaction timeout exception after 120 seconds, but timeout is not working, why ?
I resolved my issue with the set the property as written in the followed article:
faster-postgresql-connection-recovery
My code is added configuration HikariCP:
hikariConfig.addDataSourceProperty("socketTimeout", "120");
socketTimeout to 120 seconds, so in any case the property throws the followed exception after timeout:
Caused by: java.net.SocketTimeoutException: Read timed out at java.net.SocketInputStream.socketRead0(Native Method) ~[?:?]
at java.net.SocketInputStream.socketRead(SocketInputStream.java:115) ~[?:?]
at java.net.SocketInputStream.read(SocketInputStream.java:168) ~[?:?]
at java.net.SocketInputStream.read(SocketInputStream.java:140) ~[?:?]
at sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:448) ~[?:?]
at sun.security.ssl.SSLSocketInputRecord.bytesInCompletePacket(SSLSocketInputRecord.java:68) ~[?:?]
at sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1103) ~[?:?]
at sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:823) ~[?:?]
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161) ~[postgresql-42.2.10.jar!/:42.2.10]
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128) ~[postgresql-42.2.10.jar!/:42.2.10]
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113) ~[postgresql-42.2.10.jar!/:42.2.10]
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73) ~[postgresql-42.2.10.jar!/:42.2.10]
at org.postgresql.core.PGStream.receiveChar(PGStream.java:370) ~[postgresql-42.2.10.jar!/:42.2.10]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2015) ~[postgresql-42.2.10.jar!/:42.2.10]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:311) ~[postgresql-42.2.10.jar!/:42.2.10]... 17 more