Reset thread showing runnable state but its hung for a long time

Question

We encounter an unusual problem in our application, and during the last month our application has reached an unrecoverable state, it was restored after restarting the application.

Reference Information. Our application makes a database query to get some information, and this database is located on a separate node page.

Problematic case: when a thread dump was analyzed, we see that all threads are in a start state, which retrieves data from the database, but it did not complete even after 20 minutes.

Restart the application, as expected, all threads are restored. And CPU usage was also normal.

Lower dump stream

ThreadPool: 2: 47 "prio = 3 tid = 0x0000000007334000 nid = 0x5f runnable [0xfffffd7fe9f54000] java.lang.Thread.State: RUNNABLE at oracle.jdbc.driver.T2CStatement.t2cParseExecuteDesc .executeForDescribe (T2CPreparedStatement.java∗18) in oracle.jdbc.driver.T2CPreparedStatement.executeForRows (T2CPreparedStatement.java:764) in ora

All threads in the same state. 

Questions:

• what could be the cause of this condition?
• how to recover in this case?

Answer 1

He is probably waiting for network data from the database server. Java threads waiting (blocked) for I / O are described by the JVM as being in the RUNNABLE state, although they are blocked programmatically.

Answer 2

As mentioned earlier, inline methods are always executed because the JVM does not know / does not care about them.

Oracle drivers on the client side do not have a socket timeout by default. This means that if you have problems with the network, the client low-level socket can get stuck there forever, resulting in a maxxed out connection pool. You can also check network traffic to the Oracle server to make sure that it even sends data or not.

When using the thin client, you can install oracle.jdbc.ReadTimeout , but I do not know how to do this for the thick (oci) client that you use, I am not familiar with it.

What to do? Learn how you can specify a read wait time for the ojdbc thick driver and watch out for connection timeout exceptions that will clearly signal network problems. If you can change the source, you can wrap the calls and repeat the session when you catch the SQLExceptions associated with the timeout.

To fix the problem quickly, terminate the connection on the Oracle server manually.

It is worth checking the conflict of the session, perhaps this request blocks these sessions. If you find it, you will see which database object is causing the problem.

Answer 3

Native methods always remain in the RUNNABLE state (normal if you do not change the state of the native method itself, but this is not taken into account).

The method can be blocked on IO, any other pending event, or just a long intensive processor operation ... or an infinite loop. You can make your choice.

how to recover in this case?

remove the connection to oracle.

Answer 4

Is the system or JVM suspended? If configuration and, if possible, reduce the number of threads / concurrent connections.

A thread simply discards processor cycles while waiting for I / O. Yes, your processor, unfortunately, is constantly busy with threads that are waiting for a response from the database.

Answer 5

• Does your code execute a transaction manually? If then, perhaps some of the code did not commit () after changing the data. Or, maybe someone ran a query to modify data directly through PLSQL or didn’t do something else, and this makes all read operations work.

• When you were faced with the fact that the "freeze" and the database recovered from the status, did you check the data if some of them were thrown back? Ask about it since you said: "It was restored after restarting the application." This happens when the JDBC driver changed the material but did not commit it, and a timeout occurred ... The DB operation will be canceled. (may vary depending on configuration)