Multiple threads using same physical connection

This just doesn’t sound good. Having multiple threads use the same physical connection sounds like an exercise in frustration. Although the OracleDataSource connection object is thread safe, there is really no need to create a smaller number of connections than threads and then use them as static class variables.

We had this issue last week, when a developer claimed there was a bug with the Oracle driver. I used the test case below to show I could duplicate the stack trace he was seeing. By simply using oracle connection pooling, he could eliminate using a single connection.

Our test class simply creates ten threads, each of which try to use a static class variable of the type Connection. Each connection executes a database stored procedure which sleeps for 60 seconds.

import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

/*
create or replace function slow_query(p_secs in number) return number is
begin
  dbms_lock.sleep(p_secs);
  return p_secs;
end;
/
*/

class slowQuery implements Runnable {
  static oracleConnMgr cm;
  Thread t;
  static Connection conn;

  public static void main(String args[]) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      OracleDataSource ods = new OracleDataSource();
      ods.setURL("jdbc:oracle:thin:rep/[email protected]:1521/batch");
      Properties prop = new Properties();
      prop.setProperty("MinLimit", "1");
      ods.setConnectionCacheProperties (prop);
      conn = ods.getConnection();
      ods.setConnectionCachingEnabled(true);
      ods.setFastConnectionFailoverEnabled(true);
      for(int i=1; i < 10; i++){
        slowQuery r = new slowQuery();
      }
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }

  slowQuery() {
    this.t = new Thread(this);
    t.start();
  }

  public void run() {
    System.out.println(new java.util.Date());
    try {
      ResultSet rst = conn.createStatement().executeQuery("select slow_query(60) from dual");
      while (rst.next()) {
        System.out.println("test " + rst.getInt(1));
      }
      conn.close();
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
}

We then issue a call to the jps executable, which shows the running java processes on this host. Issue a call to jstack with the PID of the process returned by jps above.

The output will show that our first thread successfully gets a connection, and begins to execute our "slow_query" procedure.

"Thread-1" prio=6 tid=0x02f75400 nid=0x14c4 runnable [0x0315f000]
   java.lang.Thread.State: RUNNABLE
	at java.net.SocketInputStream.socketRead0(Native Method)
	at java.net.SocketInputStream.read(SocketInputStream.java:129)
	at oracle.net.ns.Packet.receive(Packet.java:239)
	at oracle.net.ns.DataPacket.receive(DataPacket.java:92)
	at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:172)
	at oracle.net.ns.NetInputStream.read(NetInputStream.java:117)
	at oracle.net.ns.NetInputStream.read(NetInputStream.java:92)
	at oracle.net.ns.NetInputStream.read(NetInputStream.java:77)
	at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1023)
	at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:999)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:584)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:774)
	at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:849)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
	- locked <0x27ee0390> (a oracle.jdbc.driver.T4CConnection)
	at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)
	at slowQuery.run(slowQuery.java:36)
	at java.lang.Thread.run(Thread.java:619)

...while the other nine threads that show the following...

"Thread-9" prio=6 tid=0x02fb7400 nid=0x15f8 waiting for monitor entry [0x033df000]
   java.lang.Thread.State: BLOCKED (on object monitor)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1348)
	- waiting to lock <0x27ee0390> (a oracle.jdbc.driver.T4CConnection)
	at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)
	at slowQuery.run(slowQuery.java:36)
	at java.lang.Thread.run(Thread.java:619)
 

The standard output when the program is run will show that each thread got a connection, even though only the first one did. This is because the getConnection() method does not throw an exception, but simply returns null. When the connection is first used (when we execute our stored procedure), it will block until it can successfully use the connection (when the holding thread completes its run of our stored procedure).