3

When using an Oracle JDBC connection pool, is there a way to control how the connections are handed out? In particular, is there a way to specify using a LIFO strategy? It seems the connections may be handed out in a round robin fashion.

In this scenario:

  • max connections in pool are used (10)
  • thereafter, only one concurrent connection is ever used, every 5 sec retrieved and returned
  • inactivity timeout is set to 60 sec

if a round robin strategy is used, each of the 10 pooled connections will be used within a 60 sec time period. When the inactivity timeout check occurs, every connection will have been active within the last minute, so no connection will be a candidate to be closed. The connection pool will remain with 10 connections, although in reality, only 1 is required. At least that is what I seem to be experiencing. I would like the pool to shrink down to only 1 connection.

Is my understanding of how the driver works correct? Is there a way to control the connection allocation strategy from the pool (LIFO, FIFO, round robin) or would I have to use other pooling mechanisms?

Below is a test (using deprecated apis). In this case, 3 connections were created, and it would only shrink back down to 2, not 1.

EDIT to more closely reflect above description:

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import oracle.jdbc.pool.OracleDataSource;


public class Main {

    public static void main(String[] args) throws InterruptedException, IOException, ClassNotFoundException, SQLException {

        String url = "jdbc:oracle:thin:@//host:1521/SID";
        String user = "user";
        String pwd = "pwd";

        OracleDataSource ocpds;

        ArrayList<Connection> tempConnList = new ArrayList<>();


        try {

            ocpds = new OracleDataSource();
            ocpds.setURL(url);
            ocpds.setUser(user);
            ocpds.setPassword(pwd);

            java.util.Properties prop = new java.util.Properties();
            prop.setProperty("MinLimit", "1");
            prop.setProperty("MaxLimit", "10");

            prop.setProperty("InactivityTimeout", "60");    //  seconds
            prop.setProperty("AbandonedConnectionTimeout", "60");  //  seconds
            prop.setProperty("PropertyCheckInterval", "60"); // seconds            

            // set DataSource properties
            ocpds.setConnectionCachingEnabled(true);
            ocpds.setConnectionCacheProperties(prop);
            ocpds.setConnectionCacheName("TestCache");


            // Ramp up to max
            for (int i=0; i<10; i++) {
                Connection conn = ocpds.getConnection();
                tempConnList.add(conn);
            }

            // Release them all
            for (Connection conn : tempConnList) {
                conn.close();
            }


            // Grab and release one connection at a time
            for (int i = 0; i < 60; i++) {

                System.out.println(new java.util.Date());

                // Grab and release
                Connection conn = ocpds.getConnection();
                conn.close();

                try {
                    Thread.currentThread().sleep(5000);
                } catch (InterruptedException ie) {
                    System.err.println("error message: " + ie.getMessage());
                }

            }

        } catch (SQLException e) {
            System.err.println("error message: " + e.getMessage());
        } finally {
            for (Connection conn : tempConnList) {
                if (conn != null) { try { conn.close(); } catch (SQLException ignored) {}; }
            }
        }
    }

}

1 Answer 1

2

Oracle support has responded that a round robin method is used for feeding back the connections from the pool. In Oracle JDBC 12 (current version is 11.2.0.3), there will be a property, "UseLIFO", which will allow "last in first out" retrieval:

prop.setProperty("UseLIFO", "true");

In the example posted in the question, this will let the idle time out to shrink the pool down to a single connection.

Sign up to request clarification or add additional context in comments.

1 Comment

From 11g onwards, Oracle has deprecated OracleImplicitConnectionCache which provides UseLIFO property. Oracle says to use UniversalConnectionPool instead.UCP needs a datasource; where this property is not supported. So moving to 11g or 12c driver using UCP, is it still possible to use this behaviour ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.