Oracle PoolDataSource使数据库游标保持打开状态，直到commit()为止，这是预期的行为吗? [英] Oracle PoolDataSource leaves DB cursor open until commit(), is this expected behavior?

问题描述

除非我调用Connection.commit()，否则数据库游标在查询后保持打开状态.我相信这种行为正在导致我的应用程序泄漏游标并遇到与游标使用相关的数据库错误.

Unless I call Connection.commit(), the DB cursor remains open after a query. I believe this behavior is causing my application to leak cursors and experience DB errors related to cursor usage.

似乎不需要commit()调用...这是预期的行为吗?有什么方法可以配置JDBC连接池，以便在关闭资源时可靠地释放游标，而无需调用commit?

It seems like the commit() call should be unnecessary... Is this behavior expected? Is there any way to configure the JDBC connection pool to reliably release cursors when resources are closed, without calling commit?

我正在使用此查询来查找打开的游标:

I am using this query to find open cursors:

select * from v$open_cursor where CURSOR_TYPE = 'OPEN'

如果在关闭statement和ResultSet之后调用commit()，在sleep()

If I call commit() after closing the statement and ResultSet, no cursors are open during the sleep()

   try (Connection con = pooledDataSource.getConnection()) {
        try (PreparedStatement statement = con.prepareStatement("select 1 from dual a");
             ResultSet rs = statement.executeQuery()) {
        }
        con.commit();
    }

    Thread.sleep(20000);

如果在关闭statement和ResultSet之前调用commit，则在sleep()期间查询打开的游标时会找到sql select 1 from b.

If I call commit prior to closing statement and ResultSet, I find the sql select 1 from b when I query for open cursors during the sleep().

        try (Connection con = pooledDataSource.getConnection();
             PreparedStatement statement = con.prepareStatement("select 1 from dual b");
             ResultSet rs = statement.executeQuery()) {{

             con.commit();
        }}

        Thread.sleep(20000);

这里也是一样.如果我不打commit()，我会在打开的游标查询中找到`从双c中选择1，在JVM退出之前，该选择一直保持打开状态.

Same thing here. If I don't call commit() I find `select 1 from dual c in my open cursor query, which remains open until the JVM exits.

        try (Connection con = pooledDataSource.getConnection();
                PreparedStatement statement = con.prepareStatement("select 1 from dual c");
                ResultSet rs = statement.executeQuery()) {{
        }}

这些是我的配置

        PoolDataSource pooledDataSource = PoolDataSourceFactory.getPoolDataSource();
        pooledDataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        pooledDataSource.setURL("jdbc:oracle:thin:@//" + host + ":1521/" + service);
        pooledDataSource.setUser(user);
        pooledDataSource.setPassword(pw);
        pooledDataSource.setInitialPoolSize(1);
        pooledDataSource.setMinPoolSize(1);
        pooledDataSource.setMaxPoolSize(1);
        pooledDataSource.setAbandonedConnectionTimeout(5);
        pooledDataSource.setConnectionWaitTimeout(5);
        pooledDataSource.setInactiveConnectionTimeout(5);

推荐答案

在12.2.0.1( UCPTest.java .无论如何，请注意，这不是错误.您观察到的原因是由于在关闭语句时驱动程序没有进行往返.而是在下一次往返时背负该操作.这是减少客户端和服务器之间往返总数的优化.之所以在12.2.0.1中未观察到相同的行为，是因为当连接释放到池中时，UCP会单向访问数据库，并会刷新背back式调用.如果您想人为冲刷搭载电话，也可以 ((oracle.jdbc.OracleConnection)con).pingDatabase()这是一次完整的往返行程，因此带有piggy带呼叫队列.

This behavior doesn't reproduce in 12.2.0.1 (JDBC Download page) using the following test UCPTest.java. In any case please note that this isn't a bug. What you observe is due to the fact that the driver doesn't make a roundtrip when a statement is closed. Instead the operation is piggybacked on the next roundtrip. This is an optimization to reduce the overall number of roundtrips between the client and the server. The reason why you don't observe the same behavior in 12.2.0.1 is because UCP makes a one-way trip to the database when the connection is released to the pool and that flushes the piggyback calls. If you want to artificially flush the piggyback calls you can also do ((oracle.jdbc.OracleConnection)con).pingDatabase() which is a full roundtrip and hence carries along the queue of piggyback calls.

这篇关于Oracle PoolDataSource使数据库游标保持打开状态，直到commit()为止，这是预期的行为吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！