CODEWITHSUNDEEP
javadatabaseoracle-databasetransactions
Lakshitha Udara
Lakshitha Udara

Reputation: 153

Oracle Select for update not working and sometimes hangs after while

Im doing a Oracle Select for Update with java and it works on times and sometimes it hangs with the session and cannot remove the locked session (have to manually kill the session ) this works fine for most of the scenarios but when I deployed it in two servers ( web service ) and request them both at once this happens , I can't understand whether it's a problem with my code , my code 

 public boolean checkJobStatus(long taskId)
{
    Connection con = null;
    PreparedStatement selectForUpdate = null;
    String lastJobStatus = null;
    boolean runNow = false;
    try
    {
        con = conPool.getConnection();
        con.setAutoCommit(false);
        selectForUpdate  = con.prepareStatement("SELECT LAST_JOB_STATUS FROM ADM_JOB WHERE TASK_ID = ? FOR UPDATE ");
        selectForUpdate.setLong(1, taskId);
        ResultSet resultSet = selectForUpdate.executeQuery();

        while(resultSet.next())
        {
            if (resultSet.getObject("LAST_JOB_STATUS") == null)
            {
                lastJobStatus = ScheduledJob.STATUS_FAILED;
            }
            else
            {
                lastJobStatus = resultSet.getString("LAST_JOB_STATUS");
            }
        }

     if(ScheduledJob.STATUS_RUNNING.equalsIgnoreCase(lastJobStatus) || ScheduledJob.STATUS_STARTED.equalsIgnoreCase(lastJobStatus))
     {
         runNow = false;
         // commit n update setting autocommit to true
         selectForUpdate = con.prepareStatement("UPDATE ADM_JOB SET LAST_JOB_STATUS =? WHERE TASK_ID = ?");
         selectForUpdate.setString(1, lastJobStatus);
         selectForUpdate.setLong(2, taskId);
         selectForUpdate.executeUpdate();
     }
     else
     {
         runNow =true;
         // commit n update setting autocommit to true
         selectForUpdate  = con.prepareStatement("UPDATE ADM_JOB SET LAST_JOB_STATUS =? WHERE TASK_ID = ?");
         selectForUpdate.setString(1, ScheduledJob.STATUS_STARTED);
         selectForUpdate.setLong(2, taskId);
         selectForUpdate.executeUpdate();

         con.commit();
         con.setAutoCommit(true);
     }

    } catch (SQLException e)
    {
        Logger.getLogger( "" ).log(Level.SEVERE, "Error in getting database connection", e);
        try
        {
            con.rollback();   // rolling back the row lock in case of a exception
        } catch (SQLException e1)
        {
            e1.printStackTrace();
        }
    }
    finally
    {
        DBUtility.close( selectForUpdate  );
        DBUtility.close( con );
    }


    return runNow;
}

Upvotes: 1

Views: 1090

Answers (1)

Simimmo
Simimmo

Reputation: 668

Commit occurs only in the else branch. If this condition doesn't happen, transaction is not closed, so a second thread hangs up forever on the select for update.

Upvotes: 2

Related Questions