MySQLNonTransientConnectionException: No operations allowed after statement closed

错误日志:

[2018-09-18 19:25:01.564] [24793183102180408write] [ERROR] [c.m.e.m.i.h.p.AlipayExcelProcessor] - 校验流水导入数据错误:{}
org.springframework.dao.DataAccessResourceFailureException:
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
### The error may exist in file [/mnt/632-sit/tomcat-8080/webapps/ec-manage/WEB-INF/classes/coresqlmaps/shard/FIncomeDetailsSrcMapper.xml]
### The error may involve com.midea.ec.manage.impl.core.dao.FIncomeDetailsSrcMapper.selectByExample-Inline
### The error occurred while setting parameters
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:80) ~[spring-jdbc-3.2.13.RELEASE.jar:3.2.13.RELEASE]
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.2.2.jar:1.2.2]
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) ~[mybatis-spring-1.2.2.jar:1.2.2]
        at com.sun.proxy.$Proxy66.selectList(Unknown Source) ~[na:na]
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198) ~[mybatis-spring-1.2.2.jar:1.2.2]
        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) ~[mybatis-3.2.7.jar:3.2.7]
        at com.sun.proxy.$Proxy73.selectByExample(Unknown Source) ~[na:na]
        at com.midea.ec.manage.impl.handle.processor.SaleAfterExcelProcessor.checkAccountIsUnique(SaleAfterExcelProcessor.java:323) [SaleAfterExcelProcessor.class:na]
        at com.midea.ec.manage.impl.handle.processor.SaleAfterExcelProcessor.notSftpImport(SaleAfterExcelProcessor.java:286) [SaleAfterExcelProcessor.class:na]
        at com.midea.ec.manage.impl.handle.processor.SaleAfterExcelProcessor.insertProcess(SaleAfterExcelProcessor.java:239) [SaleAfterExcelProcessor.class:na]
        at com.midea.ec.manage.impl.utils.ExcelHelperEx$ExcelImportTask.run(ExcelHelperEx.java:457) [ExcelHelperEx$ExcelImportTask.class:na]
        at com.midea.ec.manage.impl.utils.ExcelHelperEx$3.run(ExcelHelperEx.java:126) [ExcelHelperEx$3.class:na]
        at java.lang.Thread.run(Thread.java:745) [na:1.7.0_65]
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.7.0_65]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_65]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_65]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_65]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.Util.getInstance(Util.java:384) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:463) ~[mysql-connector-java-5.1.30.jar:na]
        at com.mysql.jdbc.StatementImpl.getResultSet(StatementImpl.java:2354) ~[mysql-connector-java-5.1.30.jar:na]
        at com.dangdang.ddframe.rdb.sharding.jdbc.ShardingStatement.getResultSet(ShardingStatement.java:326) ~[sharding-jdbc-core-1.4.2.jar:na]
        at sun.reflect.GeneratedMethodAccessor294.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_65]
        at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_65]
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:72) ~[mybatis-3.2.7.jar:3.2.7]
        at com.sun.proxy.$Proxy250.getResultSet(Unknown Source) ~[na:na]
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getFirstResultSet(DefaultResultSetHandler.java:175) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:143) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:60) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) ~[mybatis-3.2.7.jar:3.2.7]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_65]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_65]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_65]
        at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_65]
        at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.2.7.jar:3.2.7]
        at com.github.pagehelper.PageHelper.intercept(PageHelper.java:171) ~[trade-common-0.0.1-20180521.063519-170.jar:na]
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:60) ~[mybatis-3.2.7.jar:3.2.7]
        at com.sun.proxy.$Proxy248.query(Unknown Source) ~[na:na]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) ~[mybatis-3.2.7.jar:3.2.7]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) ~[mybatis-3.2.7.jar:3.2.7]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.7.0_65]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[na:1.7.0_65]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_65]
        at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_65]
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) ~[mybatis-spring-1.2.2.jar:1.2.2]
        ... 12 common frames omitted

根据日志分析,原因是:连接断开,但是提交事务时,找不到连接,所以抛异常。开启事务与提交事务的时长超出了,连接等待的时长。

解决办法:加大dataSource的removeAbandonedTimeout。

一般来讲,这只能解决不抛出异常的问题,为什么事务会超时呢??这个问题得好好分析。

最后发现:由于重构过程中对表结构进行了统一的管理,把一张300万数据量的表索引删除了,导致单个查询语句都10S+,这才是导致事务超时的原因。

终极解决办法:加索引,降低查询时间。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值