title: Long transaction in Mariadb with Spring boot [Socket Exception]
body: <p>we have a large number of data in database and we want to retrieve those data but while we query in mariadb then it takes very large amount of time.</p>
  <p>We have Blob type for one of the column of table that is approximate 1.6 MB json data in each row and we try to get those data from table with join query and select query.</p>
  <p>so we got below mentioned error..</p>
  <pre><code>28-02-2022 10:38:28.034 [pool-3-thread-1] WARN  c.zaxxer.hikari.pool.ProxyConnection.checkException - HikariPool-1 - Connection org.mariadb.jdbc.MariaDbConnection@3bab7992 marked as broken because of SQLSTATE(08), ErrorCode(0)
  java.sql.SQLNonTransientConnectionException: (conn=12937) Connection reset
      at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:175)
      at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
      at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
      at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334)
      at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386)
      at org.mariadb.jdbc.MariaDbConnection.rollback(MariaDbConnection.java:729)
      at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:377)
      at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)
      at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:116)
      at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.rollback(JdbcResourceLocalTransactionCoordinatorImpl.java:251)
      at org.hibernate.engine.transaction.internal.TransactionImpl.rollback(TransactionImpl.java:100)
      at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:563)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:838)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:812)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:550)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:298)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
      at com.smartproof.cert.service.CertificateImageService$$EnhancerBySpringCGLIB$$3dbeebd3.scheduleCertificateImageGeneration(&lt;generated&gt;)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
      at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: java.sql.SQLException: Connection reset
  Query is: ROLLBACK
      at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:200)
      at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328)
      ... 29 common frames omitted
  Caused by: java.net.SocketException: Connection reset
      at java.net.SocketInputStream.read(SocketInputStream.java:210)
      at java.net.SocketInputStream.read(SocketInputStream.java:141)
      at java.io.BufferedInputStream.read1(BufferedInputStream.java:284)
      at java.io.BufferedInputStream.read(BufferedInputStream.java:345)
      at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:260)
      at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:207)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readResultSet(AbstractQueryProtocol.java:1601)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1373)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1323)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:197)
      ... 30 common frames omitted
  28-02-2022 10:38:28.041 [pool-3-thread-1] ERROR o.s.t.i.TransactionInterceptor.completeTransactionAfterThrowing - Application exception overridden by rollback exception
  java.lang.OutOfMemoryError: Java heap space
      at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:301)
      at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.readNextValue(SelectResultSet.java:390)
      at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.fetchAllResults(SelectResultSet.java:310)
      at org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet.&lt;init&gt;(SelectResultSet.java:177)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readResultSet(AbstractQueryProtocol.java:1625)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1373)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1323)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:252)
      at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:209)
      at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:150)
      at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeQuery(MariaDbPreparedStatementClient.java:164)
      at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
      at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
      at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.getResultSet(AbstractLoadPlanBasedLoader.java:419)
      at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeQueryStatement(AbstractLoadPlanBasedLoader.java:191)
      at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:121)
      at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
      at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
      at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4121)
      at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
      at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
      at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
      at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:278)
      at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:121)
      at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
      at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1240)
      at org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:1123)
      at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:682)
      at org.hibernate.type.EntityType.resolve(EntityType.java:464)
      at org.hibernate.type.ManyToOneType.resolve(ManyToOneType.java:239)
      at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:171)
  28-02-2022 10:38:28.043 [pool-3-thread-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler.handleError - Unexpected error occurred in scheduled task.
  org.springframework.transaction.TransactionSystemException: Could not roll back JPA transaction; nested exception is org.hibernate.TransactionException: Unable to rollback against JDBC Connection
      at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:567)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:838)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:812)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:550)
      at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:298)
      at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
      at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
      at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
      at com.smartproof.cert.service.CertificateImageService$$EnhancerBySpringCGLIB$$3dbeebd3.scheduleCertificateImageGeneration(&lt;generated&gt;)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
      at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
      at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
      at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
      at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
      at java.lang.Thread.run(Thread.java:748)
  Caused by: org.hibernate.TransactionException: Unable to rollback against JDBC Connection
      at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:122)
      at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.rollback(JdbcResourceLocalTransactionCoordinatorImpl.java:251)
      at org.hibernate.engine.transaction.internal.TransactionImpl.rollback(TransactionImpl.java:100)
      at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:563)
      ... 21 common frames omitted
  Caused by: java.sql.SQLNonTransientConnectionException: (conn=12937) Connection reset
      at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:175)
      at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:110)
      at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:228)
      at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:334)
      at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:386)
      at org.mariadb.jdbc.MariaDbConnection.rollback(MariaDbConnection.java:729)
      at com.zaxxer.hikari.pool.ProxyConnection.rollback(ProxyConnection.java:377)
      at com.zaxxer.hikari.pool.HikariProxyConnection.rollback(HikariProxyConnection.java)
      at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:116)
      ... 24 common frames omitted
  Caused by: java.sql.SQLException: Connection reset
  Query is: ROLLBACK
      at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:119)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:200)
      at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:328)
      ... 29 common frames omitted
  Caused by: java.net.SocketException: Connection reset
      at java.net.SocketInputStream.read(SocketInputStream.java:210)
      at java.net.SocketInputStream.read(SocketInputStream.java:141)
      at java.io.BufferedInputStream.read1(BufferedInputStream.java:284)
      at java.io.BufferedInputStream.read(BufferedInputStream.java:345)
      at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacketArray(StandardPacketInputStream.java:260)
      at org.mariadb.jdbc.internal.io.input.StandardPacketInputStream.getPacket(StandardPacketInputStream.java:207)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readResultSet(AbstractQueryProtocol.java:1601)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1373)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1323)
      at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:197)
      ... 30 common frames omitted
  
  </code></pre>
  <p>is anyone any idea about it?</p>