search

apache / incubator-seata

:fire: Seata is an easy-to-use, high-performance, open source distributed transaction solution.
https://seata.apache.org/
Apache License 2.0
25.29k stars 8.78k forks source link

Could not close prepare statement in oracle #2457

Closed yougecn closed 9 months ago

yougecn commented 4 years ago

seata+druid +oracle "SQL state [99999]; error code [17009]; 关闭的语句; nested exception is java.sql.SQLException: 关闭的语句"问题 一、环境 oracle：11.2.0.1.0 jdk：1.8 spring boot：2.2.5 druid：1.1.21 seata：1.1.0 、1.2.0-SNAPSHOT oracle驱动：ojdbc8 19.3 二、问题 执行insert、delete时出现“关闭的语句”异常，是由OracleClosedStatement的下面两个方法抛出的， 一个是exitImplicitCacheToActive，另一个是exitImplicitCacheToClose； 通常情况下一次http请求涉及到两个业务，两个全局事务时会发生，如果中间加个断点等等，往往就不报异常了。 1、exitImplicitCacheToActive 堆栈 PreparedStatementCallback; uncategorized SQLException for SQL [delete from GG_JS_CD where id=?]; SQL state [99999]; error code [17009]; 关闭的语句; nested exception is java.sql.SQLException: 关闭的语句 Caused by: java.sql.SQLException: 关闭的语句 at oracle.jdbc.driver.OracleClosedStatement.exitImplicitCacheToActive(OracleClosedStatement.java:2957) ~[ojdbc8-19.3.jar:19.3.0.0.0] at oracle.jdbc.driver.OraclePreparedStatementWrapper.exitImplicitCacheToActive(OraclePreparedStatementWrapper.java:1249) ~[ojdbc8-19.3.jar:19.3.0.0.0] at com.alibaba.druid.util.OracleUtils.exitImplicitCacheToActive(OracleUtils.java:87) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.PreparedStatementPool.get(PreparedStatementPool.java:66) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:345) ~[druid-1.1.21.jar:1.1.21] at io.seata.rm.datasource.exec.BaseTransactionalExecutor.buildTableRecords(BaseTransactionalExecutor.java:295) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.exec.DeleteExecutor.beforeImage(DeleteExecutor.java:61) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitFalse(AbstractDMLBaseExecutor.java:72) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.doExecute(AbstractDMLBaseExecutor.java:60) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.exec.BaseTransactionalExecutor.execute(BaseTransactionalExecutor.java:92) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:101) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:47) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at io.seata.rm.datasource.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:64) ~[seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE] ... 157 common frames omitted

2、exitImplicitCacheToClose 2020-03-24 14:29:01.177 ERROR 912 --- [io-8082-exec-73] c.a.druid.pool.PreparedStatementPool : exitImplicitCacheToClose error

java.sql.SQLException: 关闭的语句 at oracle.jdbc.driver.OracleClosedStatement.exitImplicitCacheToClose(OracleClosedStatement.java:2969) ~[ojdbc8-19.3.jar:19.3.0.0.0] at oracle.jdbc.driver.OraclePreparedStatementWrapper.exitImplicitCacheToClose(OraclePreparedStatementWrapper.java:1261) ~[ojdbc8-19.3.jar:19.3.0.0.0] at com.alibaba.druid.util.OracleUtils.exitImplicitCacheToClose(OracleUtils.java:80) ~[druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.PreparedStatementPool.closeRemovedStatement(PreparedStatementPool.java:170) [druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.PreparedStatementPool$LRUCache.removeEldestEntry(PreparedStatementPool.java:198) [druid-1.1.21.jar:1.1.21] at java.util.LinkedHashMap.afterNodeInsertion(LinkedHashMap.java:299) [na:1.8.0_131] at java.util.HashMap.putVal(HashMap.java:663) [na:1.8.0_131] at java.util.HashMap.put(HashMap.java:611) [na:1.8.0_131] at com.alibaba.druid.pool.PreparedStatementPool.put(PreparedStatementPool.java:97) [druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.DruidPooledConnection.closePoolableStatement(DruidPooledConnection.java:171) [druid-1.1.21.jar:1.1.21] at com.alibaba.druid.pool.DruidPooledPreparedStatement.close(DruidPooledPreparedStatement.java:201) [druid-1.1.21.jar:1.1.21] at io.seata.rm.datasource.AbstractStatementProxy.close(AbstractStatementProxy.java:103) [seata-all-1.2.0-SNAPSHOT.jar:1.2.0-SNAPSHOT] at org.springframework.jdbc.support.JdbcUtils.closeStatement(JdbcUtils.java:105) [spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:639) [spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) [spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:883) [spring-jdbc-5.2.4.RELEASE.jar:5.2.4.RELEASE]

三、其他 换用HikariCP数据源后就正常了

l81893521 commented 4 years ago

Thanks for your report.

DuanDuanPan commented 4 years ago

1、更新druid至1.1.21 2、设置pool-prepared-statements: false 3、增加事务控制@Transactional(rollbackFor = Exception.class) 4、替换ojdbc8驱动为ojdbc6

wenzhenkun commented 2 years ago

这两段使用try-with-resource的方式关闭顺序有问题，改过来就好了 1、io.seata.rm.datasource.exec.BaseTransactionalExecutor buildTableRecords ResultSet rs = null; try (PreparedStatement ps = statementProxy.getConnection().prepareStatement(selectSQL)) { if (CollectionUtils.isNotEmpty(paramAppenderList)) { for (int i = 0, ts = paramAppenderList.size(); i < ts; i++) { List paramAppender = paramAppenderList.get(i); for (int j = 0, ds = paramAppender.size(); j < ds; j++) { ps.setObject(i * ds + j + 1, paramAppender.get(j)); } } } rs = ps.executeQuery(); return TableRecords.buildRecords(tableMeta, rs); } finally { IOUtil.close(rs); } 2、io.seata.rm.datasource.exec.UpdateExecutor afterImage ResultSet rs = null; try (PreparedStatement pst = statementProxy.getConnection().prepareStatement(selectSQL)) { SqlGenerateUtils.setParamForPk(beforeImage.pkRows(), getTableMeta().getPrimaryKeyOnlyName(), pst); rs = pst.executeQuery(); return TableRecords.buildRecords(tmeta, rs); } finally { IOUtil.close(rs); }

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.