I have a clustered, multi threaded application which uses c3p0 as connection pool to connect oracle database. My oracle driver is ojdbc6.jar...
I don't know whether the problem is related to c3p0 or ojdbc6.jar...
Below are the problem symptoms:
1. Lets say I have 10 threads in machine. This hanging problem is occured in every computer, but in someone all threads are hanged, in others some threads are hanged.. Hanging reason is not machine depended
2. Threads are hanged while going to different databases, so hanging is not related to database
3. In some cases threads are hanged on dual queries ( light-simple queries), hanging is not related to query time..
4. I have set queryTimeOut to 5 sn but threads hanged at socketRead0 method and cancelling is not working. At that point I want to ask a question. Who manages the query cancelling process. Does c3p0 spawns a thread for this cancelling issues or database manages the query cancelling or ojdbc?
And why my queries are not cancelled although I have set queryTimeOut?
5. My application is working well until "something" happens. After that point my threads start hanging... They are not hanged at the same time... ( someone is hanged 1 hour later)
Please give any idea about the origin of the problem
Related thread dump:
at java.net.SocketInputStream.socketRead0(Ljava/io/FileDescriptor;[BIII)I (Native Method)
at java.net.SocketInputStream.read([BII)I (SocketInputStream.java:129)
at oracle.net.ns.Packet.receive()V (Packet.java:300)
at oracle.net.ns.DataPacket.receive()V (DataPacket.java:106)
at oracle.net.ns.NetInputStream.getNextPacket()V (NetInputStream.java:315)
at oracle.net.ns.NetInputStream.read([BII)I (NetInputStream.java:260)
at oracle.net.ns.NetInputStream.read([B)I (NetInputStream.java:185)
at oracle.net.ns.NetInputStream.read()I (NetInputStream.java:102)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket()V (T4CSocketInputStreamWrapper.java:124)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read()I (T4CSocketInputStreamWrapper.java:80)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1()S (T4CMAREngine.java:1137)
at oracle.jdbc.driver.T4CTTIfun.receive()V (T4CTTIfun.java:290)
at oracle.jdbc.driver.T4CTTIfun.doRPC()V (T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(ZZZZZLoracle/jdbc/internal/OracleStatement$SqlKind;I[BI[Loracle/jdbc/driver/Accessor;I[Loracle/jdbc/driver/Accessor;I[B[C[SILoracle/jdbc/driver/DBConversion;[B[[Ljava/io/InputStream;[[[B[[Loracle/jdbc/oracore/OracleTypeADT;Loracle/jdbc/driver/OracleStatement;[B[C[S[Loracle/jdbc/driver/T4CTTIoac;[I[I[ILoracle/jdbc/driver/NTFDCNRegistration;)V (T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(ZZZZZ)V (T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe()V (T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe()V (OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout()V (OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal()I (OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery()Ljava/sql/ResultSet; (OraclePreparedStatement.java:3628)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery()Ljava/sql/ResultSet; (OraclePreparedStatementWrapper.java:1493)
at oracle.jdbc.OracleDatabaseMetaData.getTables(Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;[Ljava/lang/String;)Ljava/sql/ResultSet; (OracleDatabaseMetaData.java:3077)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnectionNoQuery(Ljava/sql/Connection;[Ljava/lang/Throwable;)I (DefaultConnectionTester.java:185)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(Ljava/sql/Connection;Ljava/lang/String;[Ljava/lang/Throwable;)I (DefaultConnectionTester.java:62)
at com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(Ljava/sql/Connection;Ljava/lang/Throwable;Ljava/lang/String;[Ljava/lang/Throwable;)I (DefaultConnectionTester.java:143)
at com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(Ljava/sql/Connection;Ljava/lang/Throwable;Ljava/lang/String;)I (AbstractConnectionTester.java:82)
at com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(Ljava/lang/Throwable;)Ljava/sql/SQLException; (NewPooledConnection.java:437)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; (NewProxyStatement.java:52)
I don't know the solution and I off hand I suspect no one else will either. I suggest running some experiments to narrow down the problem. Here are some suggestions:
1: You have a clustered, multi threaded application. Disable the cluster and only use one server. If the problem goes away, its likely cluster related.
2: If it doesn't go away, next step (still using one server), is to temporarily change the multi thread application to a single thread and see if the problem goes away. If it doesn't, its likely not a cluster or multi thread issue.
3: If it doesn't go away, keep the above two changes in place and only allow one database to be read/write to and see if that works.
4: Put debug code at key locations in your code and send info to the log file with timestamps. When it hangs, read the log file. For example: put a 'opening socket #3' before you open a specific socket, and 'closing socket #3' after you close it. If the first statement is in the log file and not the second statement, your problem is there.
5: Check the other log files such as on the database side and not just the Java log file. Perhaps it may provide additional information.
Th cp30 product is doing something dumb, thinking it's clever. It is calling a DatabaseMetaData call
to test the connection, thinking it's not doing a query, but it is in fact causing a complicated query
to the DBMS.
SOmething bad though is happening either at the DBMS or between the DBMS and driver. This
is a query, so the DBMS response should not hang (no locks). I would start by upgrading your
dirver version to the latest appropriate for your DBMS version. If that doesn't make the problem
go away, I would suggest a DBA see what the DBMS thinks is going on with those sessions.
There is no clientside debug that is likely to help. Maybe cp30 has an alternative for testing
connections, like an explicit "select 1 from dual" query...
I suppose that's possible but ojdbc6.jar supports back to 9.0.1
JDBC 11.2.0 drivers can talk to RDBMS
OP - just in case you are using an old Oracle version here is the official jdbc and Oracle db version support page
I had a similar situation in production (see please http://perfstories.wordpress.com/2012/05/15/yet-another-hanging-java-net-socketinputstream-socketread0/).
I'd recommend you to set -Doracle.jdbc.ReadTimeout=65432 in milliseconds (besides queryTimeout).