3

I'm facing a problem, that for one particular record(randomly) in an Oracle SQL table, the updating and select is not working from Java JDBC code, The java control is waiting indefinitely at that update statement (application getting stuck there). I have attached stack trace at the bottom. This program has been executing without any issues for a few years now.

Code:

    public void updateRequestStatus(long req_id, int status){  
    int rowsAffected=0;
    Statement stmt = null;
    try{   
        stmt = objCon.createStatement();
        String strUpdQry="";
        '--- some java code
        '---
        strUpdQry= "UPDATE abcrequest SET status="+status+" where request_id="+req_id;
        this.logger.printString("Before executing Update Query :: with status: "+status+", Request id: "+req_id);

        rowsAffected=stmt.executeUpdate(strUpdQry);
        this.logger.printString("After executing Update Query:: "+status+", Request id: "+req_id);  
        this.objCon.commit();           
    }catch(SQLException sqle){
            this.lg.error("SQLException at :",sqle);
            this.logger.printString("SQLException occured  "+sqle.getMessage());
    }catch(Exception e){
            this.lg.error("Exception :",e);
            this.logger.printString("Exception occured in: "+e.getMessage());
    }       
    finally{
        closeStatement(stmt);
    }
}

Things we have tried

  1. We are able to execute update query from oracle SQLDeveloper session on the same record.

  2. When the application got stuck we verified related tables: V$LOCKED_OBJECT, v$LOCK, v$session_longops, dba_blockers, v$session, dba_waiters for finding any locks on table record that is making the java application to wait infinitely. But we couldn't find any.

  3. If we restart the application, even for the same record update or select is freezing. If we skip that record, other records are updating without any issues.

What factors are factors causing this?

stack trace

   java.lang.Thread.State: RUNNABLE
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at oracle.net.ns.Packet.receive(Unknown Source)
    at oracle.net.ns.NetInputStream.getNextPacket(Unknown Source)
    at oracle.net.ns.NetInputStream.read(Unknown Source)
    at oracle.net.ns.NetInputStream.read(Unknown Source)
    at oracle.net.ns.NetInputStream.read(Unknown Source)
    at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:931)
    at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
    - locked <0x09c62c78> (a oracle.jdbc.ttc7.TTC7Protocol)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:572)
    - locked <0x04b068d8> (a oracle.jdbc.driver.OracleStatement)
    - locked <0x09c35338> (a oracle.jdbc.driver.OracleConnection)
    at xxxxx.DBActions.xxxxx.getRequestAttributes(Unknown Source)

Edit-1: Added new stack trace with respect to ojdbc6.

java.lang.Thread.State: RUNNABLE
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at java.net.SocketInputStream.read(Unknown Source)
    at oracle.net.ns.Packet.receive(Packet.java:282)
    at oracle.net.ns.DataPacket.receive(DataPacket.java:103)
    at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:230)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
    at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
    at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
    at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
    at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1814)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1779)
    - locked <0x09ec40f0> (a oracle.jdbc.driver.T4CConnection)
    at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:277)
    at cas.com.database.MQDatabaseDts.updateProvRequestStatus(MQDatabaseDts.java:595)
    at cas.com.thread.NeoMQSenderThread.run(NeoMQSenderThread.java:188)
    at java.lang.Thread.run(Unknown Source)
Improve this question
15
  • 1
    which is the error your are getting. Also print the strUpdQry before calling the query and add the output to the question. Commented Nov 6, 2017 at 14:04
  • Is your application multithreaded? Are there other pending transactions involving that row at the same time? Did you tried changing transaction isolation? Commented Nov 6, 2017 at 14:07
  • Any software updates or patches run recently? DB updates not in sync with oracle java driver? Commented Nov 6, 2017 at 14:09
  • 2
    OT, but you should be using bind variables, not literals in your SQL statement. Commented Nov 6, 2017 at 14:20
  • @DavideCavestro Thanks for your comment. the application is not multithreaded, and there are no pending transactions involving that row at the same time from java code, and verified the same from V$lock, V$LOCKED_OBJECT tables, and if i skip that record, other records are updating without any issue, Note: This issue is getting for some random records. Commented Nov 6, 2017 at 15:00

2 Answers 2

Reset to default
1

You should call objCon.isValid() before executing this statement because this issue looks like your connection has been disconnected under the covers. If the connection isn't valid, get a new one.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

It seems like it's not a connection issue, As even if we process the same record(having issue) the code is freezing and if we skip that and process next record, update and select are working fine.
Then I suggest you upgrade your driver to a more recent one. The fact that you have "TTC7Protocol" in the exception stack trace indicates that you're using a driver that's more than 10 years old. There is a good chance that upgrading the driver will fix the issue.
As per your suggestion we have updated to ojdbc6 and issue is still replicating, we have got one clue a] in v$session there is some session_id in blocking_session column with the JDBC session_id, we could not find any locks. b] I have updated the new stack wrt ojdbc6. please suggest a solution.
Are you using multiple threads that can concurrently execute this updateRequestStatus method and share the same connection? If so you might need to revisit your logic to be sure that you don't have 2 threads that execute this method concurrently as you may end up in a deadlock. One quick fix would be to synchronize the method.
We are using only one thread for this entire process, Could you please explain what the stack tells. Is the JDBC waiting for sending or receiving... And what are other probable issues?
|
1

The above issue is because of concurrency issues in that Oracle version, that is causing the java sessions getting blocked.

The issue is resolved when updated to the latest Oracle DB version in live.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.