偶发：java.sql.SQLException: ORA-00028: your session has been killed

最新推荐文章于 2024-01-04 18:48:09 发布

志大洋

最新推荐文章于 2024-01-04 18:48:09 发布

阅读量7.8k

点赞数 5

分类专栏： #开发疑难杂症

本文链接：https://blog.csdn.net/wzg725/article/details/82220290

版权

#开发疑难杂症专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近一段时间，新项目上线。其中一个项目是关于Kafka消息消费的处理工程。

日志中，很低概率地会发生下面的错误：

	org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: Closed Connection
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:342) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:366) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:212) [spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:134) [spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:97) [spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.mybatis.spring.MyBatisExceptionTranslator.initExceptionTranslator(MyBatisExceptionTranslator.java:89) [mybatis-spring-1.3.0.jar:1.3.0]
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) [mybatis-spring-1.3.0.jar:1.3.0]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447) [mybatis-spring-1.3.0.jar:1.3.0]
	at com.sun.proxy.$Proxy79.update(Unknown Source) [?:?]
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295) [mybatis-spring-1.3.0.jar:1.3.0]
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:59) [mybatis-3.4.0.jar:3.4.0]
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53) [mybatis-3.4.0.jar:3.4.0]
	at com.sun.proxy.$Proxy83.modifyOrderStatusByOrderId(Unknown Source) [?:?]
	at sun.reflect.GeneratedMethodAccessor700.invoke(Unknown Source) ~[?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_45]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_45]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) [spring-tx-4.3.13.RELEASE.jar:?]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at com.sun.proxy.$Proxy84.modifyOrderStatusByOrderId(Unknown Source) [?:?]
	at com.xxx.retailorder.syncpostmall.service.impl.PaymentCompServiceImpl.paymentDataSync(PaymentCompServiceImpl.java:101) [PaymentCompServiceImpl.class:?]
	at com.xxx.retailorder.syncpostmall.service.impl.PaymentCompServiceImpl$$FastClassBySpringCGLIB$$2b9f2446.invoke(<generated>) [PaymentCompServiceImpl.class:?]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) [spring-core-4.3.13.RELEASE.jar:?]
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282) [spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673) [spring-aop-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	at com.xxx.retailorder.syncpostmall.service.impl.PaymentCompServiceImpl$$EnhancerBySpringCGLIB$$74a32680.paymentDataSync(<generated>) [PaymentCompServiceImpl.class:?]
	at sun.reflect.GeneratedMethodAccessor699.invoke(Unknown Source) ~[?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_45]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_45]
	at com.xxx.retailorder.syncpostmall.multithread.MessageHandThread.run(MessageHandThread.java:85) [MessageHandThread.class:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_45]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_45]
	at java.lang.Thread.run(Thread.java:745) [?:1.8.0_45]
Caused by: java.sql.SQLException: Closed Connection
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.PhysicalConnection.getMetaData(PhysicalConnection.java:1578) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at org.apache.commons.dbcp.DelegatingConnection.getMetaData(DelegatingConnection.java:345) ~[commons-dbcp-1.4.jar:1.4]
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.getMetaData(PoolingDataSource.java:245) ~[commons-dbcp-1.4.jar:1.4]
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:331) ~[spring-jdbc-4.3.13.RELEASE.jar:4.3.13.RELEASE]
	... 40 more
	2018-08-29 09:02:49 739[ERROR][pool-2-thread-2][TransactionInterceptor:]:Application exception overridden by rollback exception
	; nested exception is java.sql.SQLException: ORA-00028: your session has been killed
	2018-08-29 09:02:49 748[ERROR][pool-2-thread-2][MessageHandThread:]:serviceMethod.invoke encountered an exception

日志告诉我们：当前某个业务被分配到的数据库链接被关闭了。

关键组件版本及配置是这样的。

    <!-- 数据源公用配置 -->
    <bean id="parentDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name= "testWhileIdle" value="true" />
        <property name= "testOnBorrow" value="false" />
        <property name= "testOnReturn" value="false" />
        <property name= "timeBetweenEvictionRunsMillis" value="30000" />
    </bean>
    <!-- 数据连接池 -->
    <bean id="dataSource" parent="parentDataSource" primary="true">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="${jdbc.postmall-master.url}" />
        <property name="username" value="${jdbc.postmall-master.username}" />
        <property name="password" value="${jdbc.postmall-master.password}" />
        <property name="initialSize" value="${jdbc.postmall-master.initialSize}" />
        <property name="maxActive" value="${jdbc.postmall-master.maxActive}" />
        <property name="maxIdle" value="${jdbc.postmall-master.maxIdle}" />
        <property name="minIdle" value="${jdbc.postmall-master.minIdle}" />
        <property name="validationQuery" value="${jdbc.postmall-master.validationQuery}" />
    </bean>
 
   <!-- spring事务管理器 -->
    <bean id="transactionManager"  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />  
    </bean>
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" /> 
        <property name="configLocation" value="classpath:mybatis-configuration.xml"></property>
        <property name="mapperLocations" value="classpath:mybatis/*.xml"/>
    </bean>
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> 
        <property name="basePackage" value="com.xxx.retailorder.*.dao" />
    </bean>
    <tx:annotation-driven transaction-manager="transactionManager" />
    <!-- SqlSessionTemplate sqlSession模板 -->
    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg index="0" ref="sqlSessionFactory" />
    </bean>
    <!-- 配置使Spring采用CGLIB代理 -->
    <aop:aspectj-autoproxy expose-proxy="true" proxy-target-class="true" />

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.2.0</version>
        </dependency>

查了很多资料，同事帮我找到这个资料：https://stackoverflow.com/questions/7350680/dbcp-returns-closed-connections

On DBCP, your best bet is to validate connections before returning by a testOnBorrow=true and a validationQuery setting, e.g. SELECT 1.

还有人说DBCP在高并发时不太好。所以，换成了Duird，并且升级了Spring-Mybatis包，将连接池的配置testOnBorrow改成了true。

接着就改成了下面的配置。

    <!-- 数据源公用配置 -->
    <bean id="parentDataSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <property name= "testWhileIdle" value="true" />
        <property name= "testOnBorrow" value="true" />
        <property name= "testOnReturn" value="false" />
        <property name= "timeBetweenEvictionRunsMillis" value="30000" />
        <property name= "minEvictableIdleTimeMillis" value="300000" />
    </bean>
... ...

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.2.0</version>
        </dependency>

改成上面发布生产后，一天内发生了两次下面的错误。

	2018-08-30 09:08:30 087[ERROR][Druid-ConnectionPool-Destroy-351064647][JdbcUtils:]:close connection error
	java.sql.SQLException: ORA-00028: your session has been killed
		at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:141) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:465) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:1203) ~[ojdbc14-10.2.0.2.0.jar:Oracle JDBC Driver version - "10.2.0.3.0"]
	at com.alibaba.druid.util.JdbcUtils.close(JdbcUtils.java:73) [druid-1.1.10.jar:?]
	at com.alibaba.druid.pool.DruidDataSource.shrink(DruidDataSource.java:2797) [druid-1.1.10.jar:?]
	at com.alibaba.druid.pool.DruidDataSource$DestroyTask.run(DruidDataSource.java:2562) [druid-1.1.10.jar:?]
	at com.alibaba.druid.pool.DruidDataSource$DestroyConnectionThread.run(DruidDataSource.java:2549) [druid-1.1.10.jar:?]

很显然，跟前回版本的错误不一样了。这次是线程池回收线程中发现了被关闭链接的sqlsession。

直到现在，还是没有找到根本原因。

下个版本，准备将ojdbc14从10.2.0.2升级到10.2.0.4试试。