If I call Connection.commit(), the DB cursor remains open after the query. I believe that this behavior causes my application to lose cursors and experience DB errors related to using the cursor.
It seems that the call is commit()not needed ... Is this behavior expected? Is there a way to configure the JDBC connection pool to reliably issue cursors when closing resources without calling commit?
I use this query to search for open cursors:
select * from v$open_cursor where CURSOR_TYPE = 'OPEN'
If I call commit()after closing statementand ResultSet, the cursors will not be open duringsleep()
try (Connection con = pooledDataSource.getConnection()) {
try (PreparedStatement statement = con.prepareStatement("select 1 from dual a");
ResultSet rs = statement.executeQuery()) {
}
con.commit();
}
Thread.sleep(20000);
commit statement ResultSet, sql select 1 from b, 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(), `select 1 double c , JVM.
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);