从Java类执行Oracle过程时线程被阻塞 [英] BLOCKED thread while executing Oracle procedure from Java class
问题描述
在运行独立的Java应用程序时遇到问题.
I had encountered an issue while running a standalone Java application.
此应用程序连接到Oracle 10g数据库并执行存储过程，该过程返回游标对象.问题是，在执行该过程之后，控制权不会返回到Java类.
This application connects to a Oracle 10g database and executes a stored procedure which returns a cursor object. Issue is, after executing the procedure, control is not returning back to Java class.
以下是所使用的代码.
Following is the code used.
public String execStoredProcedure(){
Connection l_Connection = null;
CallableStatement l_CStatement = null;
ResultSet l_ResultSet = null;
try{
l_Connection1 = getConnection();
l_CStatement = l_Connection.prepareCall("{call " + retrievestprName() + "(?,?)}");
l_CStatement.registerOutParameter(2, -10);
l_CStatement.setString(1, ApplicationProperties.loggedUser);
this.m_Logger.debug("before");
l_CStatement.execute();
this.m_Logger.debug("after");
l_ResultSet = (ResultSet)l_CStatement.getObject(2);
}catch (SQLException se){
//se.printStackTrace();
}
}
我观察到该过程已在db服务器上完全执行，并且在AWR报告中未发现任何问题.
I had observed that the procedure is executing completely on db server and no issues found in AWR reports.
我随机遇到此问题，无法找到确切原因.在这种情况下，此处找到了一篇不错的文章.请帮助我解决这个问题.
I am encountering this issue randomly and unable to trace the exact cause. Found a good article here for this scenario. Please help me to overcome this issue.
问题发生期间进行的线程转储.
Thread dump taken during issue occurrence.
bash-3.2$ jstack -F 3390
Attaching to process ID 3390, please wait...
Debugger attached successfully.
Server compiler detected.
JVM version is 20.65-b04
Deadlock Detection:
No deadlocks found.
Thread t@27: (state = BLOCKED)
- java.net.SocketInputStream.socketRead0(java.io.FileDescriptor, byte[], int, int, int) @bci=0 (Interpreted frame)
- java.net.SocketInputStream.read(byte[], int, int) @bci=84, line=129 (Interpreted frame)
- oracle.net.ns.Packet.receive() @bci=25 (Interpreted frame)
- oracle.net.ns.DataPacket.receive() @bci=1 (Interpreted frame)
- oracle.net.ns.NetInputStream.getNextPacket() @bci=48 (Interpreted frame)
- oracle.net.ns.NetInputStream.read(byte[], int, int) @bci=33 (Compiled frame)
- oracle.net.ns.NetInputStream.read(byte[]) @bci=5 (Interpreted frame)
- oracle.net.ns.NetInputStream.read() @bci=5 (Interpreted frame)
- oracle.jdbc.driver.T4CMAREngine.unmarshalUB1() @bci=6, line=978 (Interpreted frame)
- oracle.jdbc.driver.T4CMAREngine.unmarshalSB1() @bci=1, line=950 (Interpreted frame)
- oracle.jdbc.driver.T4C8Oall.receive() @bci=54, line=434 (Interpreted frame)
- oracle.jdbc.driver.T4CCallableStatement.doOall8(boolean, boolean, boolean, boolean) @bci=547, line=180 (Interpreted frame)
- oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(boolean) @bci=10, line=869 (Interpreted frame)
- oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout() @bci=316, line=1080 (Interpreted frame)
- oracle.jdbc.driver.OraclePreparedStatement.executeInternal() @bci=100, line=2904 (Interpreted frame)
- oracle.jdbc.driver.OraclePreparedStatement.execute() @bci=19, line=2995 (Interpreted frame)
- oracle.jdbc.driver.OracleCallableStatement.execute() @bci=58, line=4119 (Interpreted frame)
- com.sdgsoftware.managedinterface.staticimport.bulkimport.BulkImportOracleImpl.execStoredProcedure() @bci=453, line=299 (Interpreted frame)
- com.sdgsoftware.managedinterface.staticimport.bulkimport.BulkImportOracleImpl.executeBulkInsert(java.lang.String, com.sdgsoftware.managedinterface.main.gui.StatusMessages) @bci=514, line=207 (Interpreted frame)
- com.sdgsoftware.managedinterface.schedulingimpl.main.DataImportImpl.doBulkImort(int, java.lang.String, java.lang.String, java.lang.String) @bci=49, line=46 (Interpreted frame)
- com.sdgsoftware.managedinterface.schedulingimpl.main.ScheduledImportImpl.doBulkImport(int, java.lang.String) @bci=77, line=697 (Interpreted frame)
- com.sdgsoftware.managedinterface.schedulingimpl.main.ScheduledImportImpl.doImport(int, java.lang.String) @bci=118, line=582 (Interpreted frame)
- com.sdgsoftware.managedinterface.schedulingimpl.main.ScheduledImportImpl.process() @bci=739, line=410 (Interpreted frame)
- com.sdgsoftware.managedinterface.schedulingimpl.main.ScheduledImportImpl.run() @bci=1, line=282 (Interpreted frame)
- java.lang.Thread.run() @bci=11, line=680 (Interpreted frame)
Thread t@2: (state = BLOCKED)
Thread t@26: (state = IN_JAVA)
Error occurred during stack walking:
java.lang.NullPointerException
at sun.jvm.hotspot.tools.StackTrace.run(StackTrace.java:78)
at sun.jvm.hotspot.tools.StackTrace.run(StackTrace.java:45)
at sun.jvm.hotspot.tools.JStack.run(JStack.java:60)
at sun.jvm.hotspot.tools.Tool.start(Tool.java:221)
at sun.jvm.hotspot.tools.JStack.main(JStack.java:86)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at sun.tools.jstack.JStack.runJStackTool(JStack.java:118)
at sun.tools.jstack.JStack.main(JStack.java:84)
Thread t@21: (state = BLOCKED)
Thread t@20: (state = BLOCKED)
- java.lang.Object.wait(long) @bci=0 (Interpreted frame)
- java.lang.ref.ReferenceQueue.remove(long) @bci=44, line=118 (Interpreted frame)
- java.lang.ref.ReferenceQueue.remove() @bci=2, line=134 (Interpreted frame)
- java.lang.ref.Finalizer$FinalizerThread.run() @bci=16, lne=171 (Interpreted frame)
Thread t@19: (state = BLOCKED)
- java.lang.Object.wait(long) @bci=0 (Interpreted frame)
- java.lang.Object.wait() @bci=2, line=485 (Interpreted frame)
- java.lang.ref.Reference$ReferenceHandler.run() @bci=46, line=116 (Interpreted frame)
推荐答案
在您发布的同一链接中，建议设置套接字超时以避免此问题:
From that same link you posted, it recommends setting socket timeouts to avoid this problem:
为了防止并减少此类服务提供商不稳定的影响，关键的解决方案是实施适当的超时时间
In order to prevent and reduce the impact of such instability of your service providers, the key solution is to implement proper timeout
您应该这样做.
您可能还希望更改应用程序的行为，以便在连接打开的服务器上花费更少的时间，从而导致此问题开始.
You'll probably also want to change the behavior of your app so that less time is spent on the server with an open connection, causing this problem to begin with.
这篇关于从Java类执行Oracle过程时线程被阻塞的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
