一次异常java.sql.SQLRecoverableException: Closed Statement

 

环境：springboot2 + oracle + druid + mybatis

依赖包：

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.28</version>
</dependency>
问题现象及报错信息
报错的sql并没有语法错误，使用连接池也不存在连接关闭的情况
### Cause: java.sql.SQLRecoverableException: Closed Statement
; Closed Statement; nested exception is java.sql.SQLRecoverableException: Closed Statement
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:100)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
        at com.sun.proxy.$Proxy66.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
        at com.sun.proxy.$Proxy67.addXbkConfigLog(Unknown Source)
        at com.xbkconfig.common.AsyncCommon.addXbkConfigLog(AsyncCommon.java:52)
        at com.xbkconfig.common.AsyncCommon$$FastClassBySpringCGLIB$$828e03bc.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687)
        at com.xbkconfig.common.AsyncCommon$$EnhancerBySpringCGLIB$$506fb65d.addXbkConfigLog(<generated>)
        at com.xbkconfig.common.aop.AppCheckAop.around(AppCheckAop.java:65)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

原因及解决

1.0.28版本的druid连接池，执行了某条错误的sql后，会将报错信息保存在执行线程中，当下一条使用这条线程执行sql时，就不会正常执行sql了，而是会直接抛出异常
解决方法是升级连接池版本，我升级到1.0.29版本，目前运行正常
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.0.29</version>
</dependency>

 



atomic的XA异常

现象：生产库上运行完全正常，在测试库测试的时候一直抛出XA异常

排查思路：

  1. 检查连接池配置，多次尝试无果
  2. 检查版本，与生产一致
  3. 百度查到是当前版本的oracle是商业版本，需要自行给用户开通XA支持

grant select on sys.dba_pending_transactions to <user name>;

grant select on sys.pending_trans$ to <user name>;

grant select on sys.dba_2pc_pending to <user name>;

grant execute on sys.dbms_system to <user name>;