I have a springboot application which calls a stored procedure quite frequently which performs some inserts and update to some of tables. I can see that functionally this code works but after a while I can more number of connections are open and any new request to run any query is not being satisified and getting below error
'Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:100; busy:100; idle:0; lastwait:30000].","logger_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper"'
Below is code which invokes the stored procedure, I tried changing to a different implementation using entity manager as well but same result
Will appreciate any thoughts on the issue , also let me know if anything is not mentioned.
@Transactional
private Status bulkSaveOrderInterfaceStatus(String procName,String orderIds,
interfaceType, String errorMsg, String status) {
String outPutPram;
try(Session session = (Session) em.getDelegate()) {
SessionFactoryImplementor sessionFactory = (SessionFactoryImplementor) session.getSessionFactory();
Session ses = sessionFactory.openSession();
ses.beginTransaction();
ProcedureCall storedProcedure = ses.createStoredProcedureCall(procName);
storedProcedure.registerParameter(SqlConstants.BULK_INS_PROCEDURE_ORDERID_INPUTPARM, String.class, ParameterMode.IN).bindValue(orderIds);
storedProcedure.registerParameter(SqlConstants.BULK_INS_PROCEDURE_INTERFACETYPE_INPUTPARM,String.class, ParameterMode.IN).bindValue(interfaceType);
storedProcedure.registerParameter(SqlConstants.BULK_INS_PROCEDURE_ERRORMSG_INPUTPARM,String.class, ParameterMode.IN).bindValue(errorMsg);
storedProcedure.registerParameter(SqlConstants.BULK_INS_PROCEDURE_STATUS_INPUTPARM,String.class, ParameterMode.IN).bindValue(status);
storedProcedure.registerParameter(SqlConstants.PROCEDURE_OUTPUT_MESSAGE, String.class, ParameterMode.OUT);
outPutPram = (String) storedProcedure.getOutputs().getOutputParameterValue(SqlConstants.PROCEDURE_OUTPUT_MESSAGE);
ses.getTransaction().commit();
if (outPutPram.equals(SqlConstants.PROCEDURE_STATUS_SUCCESS)) {
return Status.SUCCESS;
} else {
return Status.ERROR;
}
} catch (Exception e) {
return Status.ERROR;
}
}