I am using JdbcTemplate mechanism of Spring framework. I have also scheduler classes to parse the large XML files (>500mb) which are scheduled with cron expressions in xml based configuration with pool-size =10 .When a scheduler reads an XML file , parse each element and insert into mariadb database, it insert around 8000 records but after that it freezes or skips some records with following exception:
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=localhost)(port=3307)(type=master) : Address already in use: connect
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:245)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy36.findAccountByACCust(Unknown Source)
at com.intersect.aml.service.impl.AccountMasterServiceImpl.findAccountByACCust(AccountMasterServiceImpl.java:147)
at com.intersect.aml.schedule.CbsAcctMasterXmlScheduler.xmlParsingToEntity(CbsAcctMasterXmlScheduler.java:182)
at com.intersect.aml.schedule.CbsAcctMasterXmlScheduler.execute(CbsAcctMasterXmlScheduler.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=localhost)(port=3307)(type=master) : Address already in use: connect
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:234)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.connException(ExceptionMapper.java:95)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1132)
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:560)
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:174)
at org.mariadb.jdbc.Driver.connect(Driver.java:92)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144)
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:155)
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:120)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:204)
... 24 more
Caused by: java.net.BindException: Address already in use: connect
at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.doConnect(Unknown Source)
at java.net.AbstractPlainSocketImpl.connectToAddress(Unknown Source)
at java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:406)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1124)
... 34 more
My xml parser class snippet is as follows:
public class ModelXmlScheduler {
........
File fXmlFile = new File(xmlFile);
DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder dBuilder;
Document doc = null;
try {
dBuilder = dbFactory.newDocumentBuilder();
doc = dBuilder.parse(fXmlFile);
} catch (ParserConfigurationException e) {
System.out.println("ParserConfigurationException:" + e.getMessage());
e.printStackTrace();
} catch (SAXException ee) {
System.out.println("SAXException" + ee.getMessage());
ee.printStackTrace();
}
doc.getDocumentElement().normalize();
System.out.println("Root element :" + doc.getDocumentElement().getNodeName());
NodeList dataElement = doc.getElementsByTagName("row");
if (dataElement.getLength() > 0) {
xmlParsingToEntity(dataElement);
}
.
.
.
.
.
boolean xmlParsingToEntity(NodeList nodeList) {
Modal dto = null;
for (int i = 0; i < nodeList.getLength(); i++){
dto = new Modal();
dto.setField(nodeElement.getElementsByTagName("tag_name").item(0).getTextContent());
int exists = modalService.findByNumber(dto.getNumber());
if(exists>0)
modalService.updateModel(dto);
else
modalService.insertModel(dto);
dto=null;
}
My scheduler configuration xml :
.
.
.
<task:scheduled-tasks scheduler="xmlSchedulers">
...
<task:scheduled ref="ModalXmlScheduler" method="execute" cron="0 01 21 * * *" />
...
</task:scheduled-tasks>
<task:scheduler id="cbsXmlSchedulers" pool-size="1" />
Also schedulerConfig.java:
.
.
.
@Bean(destroyMethod="shutdown")
public Executor taskExecutor() {
return Executors.newScheduledThreadPool(1);
}
}
I have already set Heap memory to 2gb for both Java and tomcat, and there are five such schedulars for respective xml files to read data and insert into database.All the threads goes in TIME_WAIT state please help to resolve address already in use exception in this case
switched to prepared statements for finding , bulk inserting and bulk updating records
used the pool datasource and set max & min idle,wait and active time following properties in application.properties file
datasource.type =org.apache.tomcat.jdbc.pool.DataSource
datasource.initial-size=15
datasource.max-wait=6000
datasource.max-active=50
datasource.max-idle=3600
datasource.min-idle=8
used the StAX parser approach to read large sized XML files