title: FOR UPDATE SKIP LOCKED not working properly
body: <p>We are using Oracle 11g Database. There is a main table which is accessed by multiple thread. We are fetching one record at a time from this main table. To ensure that record same record is not used by two thread, we are using</p>
  <blockquote>
  <p>SELECT FOR UPDATE SKIP LOCKED</p>
  </blockquote>
  <p>For some reason in Production environment, Intermittently same record is sent to Two thread/ Sessions. I have found this from the Application logs. Database is called by .NET application using WCF service. (.NET APPLICATION --&gt; WCF Service --&gt; GET DATA FROM DATABASE)</p>
  <p>To simulate this, I tried opening one Session from SQL Developer and One Session from Command prompt and fired below query</p>
  <p>SELECT A.ID,A.ORDER_DATA,B.SAID,B.BILLING_ACCOUNT FROM OVL_MASTER A,OVL_ACCOUNT_INFO B  WHERE  A.ID IN (SELECT MIN(ID) FROM OVL_MASTER WHERE STATUS =  (SELECT ID FROM OVL_PROCESS_STATUS WHERE status= &quot;S&quot;) AND IS_PROCESSING = 0)  AND A.ID = B.MASTER_ID FOR UPDATE SKIP LOCKED)</p>
  <p>Session 1 (SQL Developer) -&gt; Record fetched
  Session 2(Command prompt) --&gt; No record fetched.</p>
  <p>So this is expected. After firing ROLLBACK on session 1 , if I fire the same query in Session 2 , record is retrieved properly. However when application is calling the DB procedure, we are seeing this behavior where same record is fetched to multiple thread. This is happening only for few records but it is very strange.</p>
  <p>I am pasting my procedure for better understanding. COMMIT is executed after all the child tables are updated with respective entries.</p>
  <pre><code>create or replace PROCEDURE &quot;SP_OVL_GET_NEXT_RECORD&quot;( 
  PAR_CURRENT_STATUS IN VARCHAR2,
  
  
  PAR_MACHINE_NAME IN VARCHAR2,
    PAR_BOT_TYPE IN VARCHAR2,
    PAR_DATA_JSON OUT CLOB,
    PAR_MASTER_ID OUT NUMBER,
    PAR_SAID OUT VARCHAR2,
    PAR_SIEBEL_BILLINGACCOUNT OUT VARCHAR2
  
  )
  AS
  V_CNT number;
  
  BEGIN
  
  
  FOR X IN (
  SELECT A.ID,A.ORDER_DATA,B.SAID,B.BILLING_ACCOUNT FROM OVL_MASTER A,OVL_ACCOUNT_INFO B
    WHERE  A.ID IN (SELECT MIN(ID) FROM OVL_MASTER WHERE STATUS =
    (SELECT ID FROM OVL_PROCESS_STATUS WHERE status= par_current_status) AND IS_PROCESSING = 0)
    AND A.ID = B.MASTER_ID FOR UPDATE SKIP LOCKED)
  
  LOOP
      SELECT COUNT(ID) into V_CNT  FROM OVL_MASTER_CANCELLED WHERE MASTER_ID = X.ID;
      IF V_CNT&gt;0 THEN
        UPDATE OVL_MASTER SET STATUS = FN_GET_OVL_STATUS_ID('PROCESS_CANCELLED'),
        SUB_STATUS = FN_GET_OVL_STATUS_ID('PROCESS_CANCELLED'),
        UPDATED_DATE=SYSDATE
        WHERE ID=X.ID;
  
      ELSE
        UPDATE OVL_MASTER SET IS_PROCESSING = 1 WHERE ID = X.ID ;
        PAR_DATA_JSON := X.ORDER_DATA;
        PAR_MASTER_ID := X.ID;
        PAR_SAID := X.SAID;
        PAR_SIEBEL_BILLINGACCOUNT := X.BILLING_ACCOUNT;
  
        UPDATE OVL_MASTER_STATUS_MAPPING SET STAGE_STATUS = 'IN PROGRESS' WHERE STAGE = FN_GET_OVL_STATUS_ID(PAR_CURRENT_STATUS) AND
                MASTER_ID = X.ID;
  
  
       
        SP_INSERT_OVL_BOT_MASTER(PAR_MACHINE_NAME,PAR_BOT_TYPE,X.ID);
      END IF;
      EXIT;
  END LOOP;
  COMMIT;
  EXCEPTION
  WHEN OTHERS THEN ROLLBACK;
  SP_INSERT_OVL_ERROR_LOGS(null,'DB_ERROR','ERROR OCCURED IN SP_OVL_GET_NEXT_RECORD : '||SQLERRM);
  END;
  </code></pre>