qiaolin-li opened a new issue #2677: TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException URL: https://github.com/apache/incubator-shardingsphere/issues/2677 ### Version 2.0.3 ### Description Transaction commit failed due to Mysql Socket exception Since I haven't performed a database operation for a long time, the link may be turned off by Mysql The DruidDataSource discard connection appears when a invalidated link is fetched But he just prints out this error message, and then he continues to execute the code, but when it commits this transaction, it shows up Caused by: org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException I wonder if I saved the problematic link and submitted it Because I see io.shardingjdbc.core.jdbc.adapter.AbstractConnectionAdapter.com at MIT The object is submitted in a loop and the exception information is wrapped as an SQLException ```java @Override public final void commit() throws SQLException { Collection<SQLException> exceptions = new LinkedList<>(); for (Connection each : cachedConnections.values()) { try { each.commit(); } catch (final SQLException ex) { exceptions.add(ex); } } throwSQLExceptionIfNecessary(exceptions); } ``` So I'm thinking of a couple of questions 1、Why does the link marked with the discard connection not throw the error but continue 2、Why is there an anomaly in submitting things, Is it because the first link to be discarded also exists in the io.shardingjdbc.core.jdbc.adapter.AbstractConnectionAdapter#cachedConnections So that leads to problems in submitting things... 3、This problem can cause unexpected problems, but only if the link has problems... ### Java Code ```java try { // Set mandatory routing this.setHintManager(context); Boolean result = transactionTemplate.execute(status -> { try { // The discard connection appears while executing here List<OrderAutoConfirmDeliverySyn> fetchAutoConfirmOrderList = fetchAutoConfirmOrders(context); // It's still going to be updated updateConfirmOrder(context, fetchAutoConfirmOrderList, syncMsgList); return true; } catch (Exception e) { log.error("自动确认收货更新DB异常 ", e); status.setRollbackOnly(); } }); // Things submitted TransactionSystemException occurs: Could not commit JDBC transaction } catch (Exception ex) { log.error("自动确认收货异常 ",ex); } } ``` ### The stack information ```java 2019-07-06 16:33:52,280 ERROR [autoConfirmThreadPool262-4884] Class=[com.alibaba.druid.pool.DruidDataSource] discard connection com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 1,461,993 milliseconds ago. The last packet sent successfully to the server was 7 milliseconds ago. at sun.reflect.GeneratedConstructorAccessor137.newInstance(Unknown Source) ~[?:?] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_151] at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_151] at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3562) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3462) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3903) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4796) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.alibaba.druid.filter.FilterChainImpl.connection_setAutoCommit(FilterChainImpl.java:738) ~[druid-1.1.10.jar!/:1.1.10] at com.alibaba.druid.filter.FilterAdapter.connection_setAutoCommit(FilterAdapter.java:985) ~[druid-1.1.10.jar!/:1.1.10] at com.alibaba.druid.filter.FilterChainImpl.connection_setAutoCommit(FilterChainImpl.java:733) ~[druid-1.1.10.jar!/:1.1.10] at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.setAutoCommit(ConnectionProxyImpl.java:430) ~[druid-1.1.10.jar!/:1.1.10] at com.alibaba.druid.pool.DruidPooledConnection.setAutoCommit(DruidPooledConnection.java:712) ~[druid-1.1.10.jar!/:1.1.10] at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source) ~[?:?] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151] at io.shardingjdbc.core.jdbc.adapter.invocation.JdbcMethodInvocation.invoke(JdbcMethodInvocation.java:48) ~[sharding-jdbc-core-2.0.3.jar!/:?] at io.shardingjdbc.core.jdbc.adapter.WrapperAdapter.replayMethodsInvocation(WrapperAdapter.java:74) ~[sharding-jdbc-core-2.0.3.jar!/:?] at io.shardingjdbc.core.jdbc.core.connection.ShardingConnection.getConnection(ShardingConnection.java:114) ~[sharding-jdbc-core-2.0.3.jar!/:?] at io.shardingjdbc.core.jdbc.core.connection.ShardingConnection.getMetaData(ShardingConnection.java:133) ~[sharding-jdbc-core-2.0.3.jar!/:?] at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getNextResultSet(DefaultResultSetHandler.java:258) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:197) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.6.jar!/:3.4.6] at sun.reflect.GeneratedMethodAccessor72.invoke(Unknown Source) ~[?:?] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151] at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.4.6.jar!/:3.4.6] at com.sun.proxy.$Proxy147.query(Unknown Source) ~[?:?] at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.6.jar!/:3.4.6] at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source) ~[?:?] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[mybatis-spring-1.3.2.jar!/:1.3.2] at com.sun.proxy.$Proxy104.selectList(Unknown Source) ~[?:?] at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230) ~[mybatis-spring-1.3.2.jar!/:1.3.2] at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76) ~[mybatis-3.4.6.jar!/:3.4.6] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.6.jar!/:3.4.6] at com.sun.proxy.$Proxy123.findAutoConfirmOrders(Unknown Source) ~[?:?] at sun.reflect.GeneratedMethodAccessor112.invoke(Unknown Source) ~[?:?] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_151] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_151] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197) ~[spring-aop-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at com.sun.proxy.$Proxy124.findAutoConfirmOrders(Unknown Source) ~[?:?] at com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl.fetchAutoConfirmOrders(AutoConfirmDeliveryServiceImpl.java:275) ~[trade-area-core-service-1.0.0.jar!/:1.0.0] at com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl.lambda$handeDelivery$0(AutoConfirmDeliveryServiceImpl.java:175) ~[trade-area-core-service-1.0.0.jar!/:1.0.0] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.0.7.RELEASE.jar!/:5.0.7.RELEASE] at com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl.handeDelivery(AutoConfirmDeliveryServiceImpl.java:172) ~[trade-area-core-service-1.0.0.jar!/:1.0.0] at com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl.start(AutoConfirmDeliveryServiceImpl.java:108) ~[trade-area-core-service-1.0.0.jar!/:1.0.0] at com.frxs.trade.area.biz.event.schedule.elasticjob.AutoConfirmDeliveryJob.lambda$execute$2(AutoConfirmDeliveryJob.java:125) ~[trade-area-biz-event-1.0.0.jar!/:1.0.0] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_151] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_151] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_151] Caused by: java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:210) ~[?:1.8.0_151] at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[?:1.8.0_151] at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3011) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3472) ~[mysql-connector-java-5.1.46.jar!/:5.1.46] ... 62 more 2019-07-06 16:33:52,441 INFO [autoConfirmThreadPool221-2918] Class=[com.frxs.framework.integration.rocketmq.adapter.RocketMqProducerAdapter] rocketmq 2019-07-06 16:33:52,485 INFO [autoConfirmThreadPool176-2884] Class=[com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl] 自动确认收货成功 item:4 sharding:101_668804 time:549.9 ms minDate:2019-07-02 00:00:00 maxDate:2019-07-04 23:59:59 limitNum:50 fetchCount:1 2019-07-06 16:33:52,498 INFO [autoConfirmThreadPool176-2884] Class=[com.frxs.framework.integration.rocketmq.adapter.RocketMqProducerAdapter] rocketmq 2019-07-06 16:33:52,512 INFO [autoConfirmThreadPool308-4930] Class=[com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl] 自动确认收货成功 item:0 sharding:101_668810 time:580.4 ms minDate:2019-07-02 00:00:00 maxDate:2019-07-04 23:59:59 limitNum:50 fetchCount:1 2019-07-06 16:33:52,513 INFO [autoConfirmThreadPool183-2880] Class=[com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl] 自动确认收货成功 item:1 sharding:101_668801 time:580.4 ms minDate:2019-07-02 00:00:00 maxDate:2019-07-04 23:59:59 limitNum:50 fetchCount:1 2019-07-06 16:33:52,522 INFO [autoConfirmThreadPool308-4930] Class=[com.frxs.framework.integration.rocketmq.adapter.RocketMqProducerAdapter] rocketmq 2019-07-06 16:33:52,523 INFO [inner-job-trade-area-elastic-job.autoConfirmDeliveryJobName-2076-6540] Class=[com.frxs.trade.area.biz.event.schedule.elasticjob.AutoConfirmDeliveryJob] 自动确认收货 任务完成 item:0 time:591.0 ms 2019-07-06 16:33:52,525 INFO [autoConfirmThreadPool183-2880] Class=[com.frxs.framework.integration.rocketmq.adapter.RocketMqProducerAdapter] rocketmq 2019-07-06 16:33:52,528 INFO [autoConfirmThreadPool262-4884] Class=[com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl] 自动确认收货成功 item:9 sharding:101_668809 time:587.1 ms minDate:2019-07-02 00:00:00 maxDate:2019-07-04 23:59:59 limitNum:50 fetchCount:1 2019-07-06 16:33:52,531 ERROR [autoConfirmThreadPool262-4884] Class=[com.frxs.trade.core.service.service.impl.AutoConfirmDeliveryServiceImpl] 自动确认收货异常 item:101_668809 sharding:org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is java.sql.SQLException ```
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
