最近在做一个项目迁移工作,大抵是把一个“祖传架构”上面的部分功能迁移到当前比较通用的SpringBoot上面。在做功能测试的时候遇到了这个问题,涉及到的逻辑是要连续执行2k左右的update语句,在执行到接近300条的时候出现了这样一个错误。简单查了下是oracle中最大游标数(open_cursors)引起的一个问题。关于游标的概念和原理这里就不细说了,大抵就是在同一个PreparedStatement下每执行一次update、delete等语句会打开一个新的游标,而oracle中的open_cursors是有限制的,默认300,最大1000,而我这里明显是超过了默认值了。
具体解决方案有以下几种:
1 直接修改oracle的最大游标数,Command Window
SQL> alter system set open_cursors=1000 scope=both;//修改游标数
SQL> show parameter open_cursors;//查看当前最大游标数值
问题:这种处理方式也只是把限制从300提到1000,没有从根本解决问题,而我这里的情况也并不适用,且很多情况下我们是没有修改数据库open_cursors的权限的。
2 代码层面处理:这里出现这种问题是因为该SpringBoot项目用到了druid连接池,且对连接池做了优化,对游标做了缓存(或者说是PreparedStatement,连接池中conn.close方法PreparedStatement不会被关闭),其目的是为了防止游标的反复创建。只不过在我现在这种情况因为游标不释放引发了新的问题。针对这种情况可以特殊问题特殊处理,每次关闭PreparedStatement,或者修改逻辑尽量少的使用PreparedStatement。这里写一个例子-基于SpringBoot 2.2.13
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public boolean jdbcTest() {
Connection conn = null;
PreparedStatement ps = null;
int count = 500;
try {
conn = jdbcTemplate.getDataSource().getConnection();
// 关闭自动提交
conn.setAutoCommit(false);
String insertSql = " insert into W003_COST_TEST (id, is_deleted) values (?,0) ";
ps = conn.prepareStatement(insertSql);
for (int i = 0; i < count; i++) {
// 索引从1 开始
ps.setString(1, i + "");
// 添加到集合中
ps.addBatch();
}
// 批量处理
int[] rt = ps.executeBatch();
System.out.println(Arrays.toString(rt));
conn.commit();
ps.close();
} catch (SQLException e1) {
e1.printStackTrace();
} finally {
try {
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
这样的好处就是保留了之前的连接池关于PreparedStatement缓存优化的配置,且这种方式效率比较高(当然如果是插入操作可以考虑insert all 这种合并到一条sql的方式,也可以规避游标问题,这里只是举个例子)。缺点就是如果需要处理的地方比较多的话,代码显得不够整洁且很难写出比较通用的工具类。
3 修改配置:既然是PreparedStatement缓存导致的问题,直接修改配置无疑是最简单的了,代价就是无法享受缓存带来的好处。找到对应的位置,pool-prepared-statements设置为false。
#数据源配置
spring:
datasource:
url: ${blade.datasource.dev.url}
username: ${blade.datasource.dev.username}
password: ${blade.datasource.dev.password}
druid:
pool-prepared-statements: false
一般这样处理就可以了(不同版本参数名称可能有出入),然而我这里比较尴尬的是居然没起作用!想到我这个框架属于二次开发,或许在别的地方覆盖了我的配置,然后看了看含有prepared-statement 的参数,就看到了max-open-prepared-statements,然后看了下说明,如果该参数大于0的话pool-prepared-statements默认为true 。到这里也只能试一下子了,随便赋了个小于300的数 ,试了试,没啥问题、完整配置
#数据源配置
spring:
datasource:
url: ${blade.datasource.dev.url}
username: ${blade.datasource.dev.username}
password: ${blade.datasource.dev.password}
druid:
pool-prepared-statements: false
share-prepared-statements: false
max-open-prepared-statements: 200
max-pool-prepared-statement-per-connection-size: 200