Search code examples
javajdbcspring-data-jpa

Jdbc connection time-out

After getting many concurrent requests JDBC gives timeout & doesn't process any more requests.

Below is the error message

Exception in thread "main" org.hibernate.exception.JDBCConnectionException: unable to obtain isolated JDBC connection
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcIsolationDelegate.delegateWork(JdbcIsolationDelegate.java:103)
    at org.hibernate.id.enhanced.TableGenerator$1.getNextValue(TableGenerator.java:530)
    at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40)
    at org.hibernate.id.enhanced.TableGenerator.generate(TableGenerator.java:526)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:105)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:192)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:38)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:177)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:32)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:682)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:674)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:669)
    at com.ihealthmantra.upload.com.ihealthmantra.customer.upload.service.SellerUploadImpl.lambda$4(SellerUploadImpl.java:801)
    at java.util.ArrayList.forEach(Unknown Source)
    at com.ihealthmantra.upload.com.ihealthmantra.customer.upload.service.SellerUploadImpl.createTimeSlotUploadedSeller(SellerUploadImpl.java:738)
    at com.ihealthmantra.upload.com.ihealthmantra.customer.upload.service.SellerUploadImpl.populateSlot(SellerUploadImpl.java:1146)
    at com.ihealthmantra.upload.App.main(App.java:81)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 820,677 milliseconds ago.  The last packet sent successfully to the server was 820,679 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
.....
Caused by: java.net.SocketException: Connection reset by peer: socket write error
Solution

  • My queries are heavy and they take time to process so I have solved the issue by configuring a few values like:

    1. Increasing maximumPoolSize to increase concurrent request handling.
    2. Increasing connectionTimeout so that requests will wait a little longer before timing out.

    You can change these configs by adding the following values in application.properties. (The values that worked for me might not work for you as well so you need to test different values as per your use case)

    {datasource}.maximumPoolSize=100 
{datasource}.connectionTimeout=60000