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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
\r\nimport java.io.*;\r\nimport java.util.*;\r\nimport java.sql.*;\r\nimport oracle.jdbc.*;\r\nimport oracle.jdbc.pool.*;\r\n\r\n\/*\r\ncreate or replace function slow_query(p_secs in number) return number is\r\nbegin\r\n dbms_lock.sleep(p_secs);\r\n return p_secs;\r\nend;\r\n\/\r\n*\/\r\n\r\nclass slowQuery implements Runnable {\r\n static oracleConnMgr cm;\r\n Thread t;\r\n static Connection conn;\r\n\r\n public static void main(String args[]) {\r\n try {\r\n Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n OracleDataSource ods = new OracleDataSource();\r\n ods.setURL(\"jdbc:oracle:thin:rep\/rep@192.168.1.19:1521\/batch\");\r\n Properties prop = new Properties();\r\n prop.setProperty(\"MinLimit\", \"1\");\r\n ods.setConnectionCacheProperties (prop);\r\n conn = ods.getConnection();\r\n ods.setConnectionCachingEnabled(true);\r\n ods.setFastConnectionFailoverEnabled(true);\r\n for(int i=1; i < 10; i++){\r\n slowQuery r = new slowQuery();\r\n }\r\n }\r\n catch(Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n\r\n slowQuery() {\r\n this.t = new Thread(this);\r\n t.start();\r\n }\r\n\r\n public void run() {\r\n System.out.println(new java.util.Date());\r\n try {\r\n ResultSet rst = conn.createStatement().executeQuery(\"select slow_query(60) from dual\");\r\n while (rst.next()) {\r\n System.out.println(\"test \" + rst.getInt(1));\r\n }\r\n conn.close();\r\n }\r\n catch(Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n}\r\n<\/pre>\nWe 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.<\/p>\n
The output will show that our first thread successfully gets a connection, and begins to execute our \"slow_query\" procedure.<\/p>\n
\r\n\"Thread-1\" prio=6 tid=0x02f75400 nid=0x14c4 runnable [0x0315f000]\r\n java.lang.Thread.State: RUNNABLE\r\n\tat java.net.SocketInputStream.socketRead0(Native Method)\r\n\tat java.net.SocketInputStream.read(SocketInputStream.java:129)\r\n\tat oracle.net.ns.Packet.receive(Packet.java:239)\r\n\tat oracle.net.ns.DataPacket.receive(DataPacket.java:92)\r\n\tat oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:172)\r\n\tat oracle.net.ns.NetInputStream.read(NetInputStream.java:117)\r\n\tat oracle.net.ns.NetInputStream.read(NetInputStream.java:92)\r\n\tat oracle.net.ns.NetInputStream.read(NetInputStream.java:77)\r\n\tat oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1023)\r\n\tat oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:999)\r\n\tat oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:584)\r\n\tat oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)\r\n\tat oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:774)\r\n\tat oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:849)\r\n\tat oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)\r\n\tat oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)\r\n\t- locked <0x27ee0390> (a oracle.jdbc.driver.T4CConnection)\r\n\tat oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)\r\n\tat slowQuery.run(slowQuery.java:36)\r\n\tat java.lang.Thread.run(Thread.java:619)\r\n<\/pre>\n...while the other nine threads that show the following...<\/p>\n
\r\n\"Thread-9\" prio=6 tid=0x02fb7400 nid=0x15f8 waiting for monitor entry [0x033df000]\r\n java.lang.Thread.State: BLOCKED (on object monitor)\r\n\tat oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1348)\r\n\t- waiting to lock <0x27ee0390> (a oracle.jdbc.driver.T4CConnection)\r\n\tat oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:386)\r\n\tat slowQuery.run(slowQuery.java:36)\r\n\tat java.lang.Thread.run(Thread.java:619)\r\n <\/pre>\nThe 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).<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n