Failed to update a row in oracle sql database from java jdbc, java code stuck at update

Question

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)

Answer 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.