title: ORA-00054: resource busy and acquire with NOWAIT in oracle procedure
body: <p>I am running many procedures parallel through java thread code. I have used exception handling in my procedures in order to catch any error results. When i see java debugger log i can see few of the procedures throws an error <code>ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired</code>. I am using few tables which are in common and used by other procedures as well. I dont know the reason why this issue is comming. </p>
  
  <pre><code>PROCEDURE                    "EXT_10024_ACTIVATE_OPTION"(IN_KPI_DEF_ID IN NUMBER DEFAULT 0) AS
  
  IN_EVENT_ID NUMBER;
  err_code VARCHAR(100);
  err_msg VARCHAR(100);
  IN_OBJECT_NAME VARCHAR2(100);
  
  CURSOR KPI_DEF_CUR IS
  Select KPI_DEF_ID,BUSINESS_CHECK_PERIOD_ID,BUS_CHK_PRD_ID_1,
  CASE WHEN BUSINESS_CHECK_PERIOD_UNIT_ID=11 THEN 'MINUTE'
  WHEN BUSINESS_CHECK_PERIOD_UNIT_ID=12 THEN 'HOUR'
  WHEN BUSINESS_CHECK_PERIOD_UNIT_ID=13 THEN 'DAY'
  WHEN BUSINESS_CHECK_PERIOD_UNIT_ID IS NULL THEN 'MINUTE'
  END AS BUSINESS_CHECK_PERIOD_UNIT_ID,
  CASE WHEN BUSINESS_CHK_PERIOD_VAL IS NULL THEN 0
  ELSE BUSINESS_CHK_PERIOD_VAL END AS BUSINESS_CHK_PERIOD_VAL,
  CASE WHEN BUS_CHK_PRD_UNIT_ID_1=11 THEN 'MINUTE'
  WHEN BUS_CHK_PRD_UNIT_ID_1=12 THEN 'HOUR'
  WHEN BUS_CHK_PRD_UNIT_ID_1=13 THEN 'DAY'
  WHEN BUS_CHK_PRD_UNIT_ID_1 IS NULL THEN 'MINUTE'
  END AS BUS_CHK_PRD_UNIT_ID_1,
  CASE WHEN BUS_CHK_PRD_VAL_1 IS NULL THEN 0
  ELSE BUS_CHK_PRD_VAL_1 END AS BUS_CHK_PRD_VAL_1,
  EVENT_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION where KPI_DEF_ID = IN_KPI_DEF_ID;
  
  BEGIN
  
  --delete the data from TEMP_SERVICE_OPTION and TEMP_SERVICE_OPTION_EXTRACTION
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_ACTIVATE_OPTION';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SERVICE_OPTION_EXTRACTION';
  EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_SERVICE_OPTION';
  
  DELETE FROM CAPTURED_DATA_ERROR WHERE EVENT_TIMESTAMP &lt; SYSDATE - 60 and EVENT_ID=10024;
  
  -- removed, retrieve  a new START_ID from source first, don't use the last id.
  -- SELECT LAST_TASK_ID INTO LAST_SO_ID FROM CAPTURING where DB_TABLE='TEMP_SERVICE_OPTION';
  --SELECT MIN(SO.ID) INTO LAST_SO_ID FROM SERVICE_OPTION@FONIC_RETAIL SO WHERE SO.ID &gt;= to_char(SYSDATE -1, 'YYYYMMDDHH24MISS')||'0000';
  
  Select EVENT_ID INTO IN_EVENT_ID FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION where KPI_DEF_ID = IN_KPI_DEF_ID;
  
  FOR KPI_DEF_ROW IN KPI_DEF_CUR
  LOOP
  
  BEGIN
  INSERT INTO TEMP_ACTIVATE_OPTION(ID,ICC,ASSIGNED_ANUMBER_ID,SERVICE_ID,PRODUCT_OPTION_ID,STATUS_ID,END_DATE,PRODUCT_TYPE_KEY)
  Select DISTINCT(SO.ID),SIM.ICC,SIM.ASSIGNED_ANUMBER_ID,SO.SERVICE_ID,SO.PRODUCT_OPTION_ID,SO.STATUS_ID,SO.END_DATE,SIM.PRODUCT_TYPE_KEY FROM
  SIMCARD@FONIC_RETAIL SIM
    JOIN SERVICE_OPTION@FONIC_RETAIL SO ON SO.SERVICE_ID=SIM.ASSIGNED_TO_SERVICE_ID
   where SO.STATUS_ID IN (20,40)
  and SO.ID &lt; to_char(SYSDATE - numtodsinterval (  KPI_DEF_ROW.BUSINESS_CHK_PERIOD_VAL,KPI_DEF_ROW.BUSINESS_CHECK_PERIOD_UNIT_ID ), 'YYYYMMDDHH24MISS')||'0000'
   and SO.ID &gt; to_char(SYSDATE - numtodsinterval ( KPI_DEF_ROW.BUS_CHK_PRD_VAL_1, KPI_DEF_ROW.BUS_CHK_PRD_UNIT_ID_1 ), 'YYYYMMDDHH24MISS')||'0000'
  and NOT EXISTS(SELECT ID from TEMP_ACTIVATE_OPTION T WHERE T.ID = SO.ID );
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND exception in EXT_10024_ACTIVATE_OPTION - KPI_DEF_ID:'||to_char(IN_KPI_DEF_ID));
  RAISE;
  
  END;
  
  commit;
  
  BEGIN
  INSERT INTO TEMP_SERVICE_OPTION_EXTRACTION(ID,ICC,ASSIGNED_ANUMBER_ID,SERVICE_ID,PRODUCT_OPTION_ID,STATUS_ID,END_DATE,A_NUMBER,PRODUCT_TYPE_KEY)
  Select DISTINCT(SO.ID),SO.ICC,SO.ASSIGNED_ANUMBER_ID,SO.SERVICE_ID,SO.PRODUCT_OPTION_ID,SO.STATUS_ID,SO.END_DATE,AN.A_NUMBER,SO.PRODUCT_TYPE_KEY FROM
  TEMP_ACTIVATE_OPTION SO JOIN
  PRODUCT_OPTION@FONIC_RETAIL PO ON SO.PRODUCT_OPTION_ID = PO.ID JOIN
  PRODUCT_CONFIG@FONIC_RETAIL PC ON PO.OPTION_KEY=PC.DEFAULT_PRODUCT_OPTIONS
  JOIN PRODUCT_TYPE@FONIC_RETAIL PT ON PC.ID = PT.PRODUCT_CONFIG_ID
  JOIN TEMP_ACTIVATE_OPTION SO ON SO.PRODUCT_TYPE_KEY=PT.KEY
  JOIN
  A_NUMBER@FONIC_RETAIL AN ON SO.ASSIGNED_ANUMBER_ID = AN.ID
  where SO.STATUS_ID IN (20,40)
  and NOT EXISTS(SELECT ID from TEMP_SERVICE_OPTION_EXTRACTION T WHERE T.ID = SO.ID );
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND exception in EXT_10024_ACTIVATE_OPTION - KPI_DEF_ID:'||to_char(IN_KPI_DEF_ID));
  RAISE;
  
  END;
  
  commit;
  
  BEGIN
  --SELF_REGISTRATION ACTIVATE OPTION
  INSERT INTO TEMP_SERVICE_OPTION(ID,SERVICE_ID,PRODUCT_OPTION_ID,STATUS_ID,EVENT_TIMESTAMP,END_DATE,EVENT_ID,SUBSCRIPTION_ID,ORDER_NUMBER,A_NUMBER)
  Select DISTINCT(SO.ID),SO.SERVICE_ID,SO.PRODUCT_OPTION_ID,SO.STATUS_ID,to_date(substr(SO.ID, 1, 14), 'YYYYMMDDHH24MISS'),SO.END_DATE,
  IN_EVENT_ID
  ,TSM.SUBSCRIPTION_ID,TSM.ORDER_NUMBER,SO.A_NUMBER
  from TEMP_SERVICE_OPTION_EXTRACTION SO JOIN TMP_SOAP_MONITORING_IDS TSM
  ON SO.A_NUMBER = TSM.MSISDN
  where SO.STATUS_ID IN (20,40) and TSM.ORDER_TYPE='SELF_REGISTRATION' and
  TSM.CREATE_DATE &lt; SYSDATE - numtodsinterval (  KPI_DEF_ROW.BUSINESS_CHK_PERIOD_VAL,KPI_DEF_ROW.BUSINESS_CHECK_PERIOD_UNIT_ID )
  and TSM.CREATE_DATE &gt; SYSDATE - numtodsinterval ( KPI_DEF_ROW.BUS_CHK_PRD_VAL_1, KPI_DEF_ROW.BUS_CHK_PRD_UNIT_ID_1 )
  and NOT EXISTS(SELECT ID from TEMP_SERVICE_OPTION T WHERE T.ID = SO.ID )
  and TSM.WEB_SERVICE_NAME ='RatorWebShopService' and TSM.WEB_METHOD_NAME ='placeShopOrder';
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND exception in EXT_10024_ACTIVATE_OPTION - KPI_DEF_ID:'||to_char(IN_KPI_DEF_ID));
  RAISE;
  
  END;
  
  END LOOP;
  
  commit;
  
  --INSERT TEMP DATA INTO CAPTURED_DATA_01 TABLE
  Insert into CAPTURED_DATA_01(SUBSCRIPTION_ID,ENV_ID,BRAND_ID,BP_ID,EVENT_ID,ORDER_ID,STATUS_DESCRIPTION,STATUS_CODE,EVENT_TIMESTAMP)
  Select DISTINCT(DCR.SUBSCRIPTION_ID),BBE.ENV_ID,TSM.BRAND_ID,BBE.BP_ID,DCR.EVENT_ID,
  DCR.ORDER_NUMBER,
  CASE WHEN DCR.STATUS_ID=20 THEN 'OK'
  WHEN DCR.STATUS_ID=40 THEN 'ERROR'
  END,DCR.STATUS_ID,
  DCR.EVENT_TIMESTAMP from TEMP_SERVICE_OPTION DCR JOIN TMP_SOAP_MONITORING_IDS TSM ON TSM.SUBSCRIPTION_ID=DCR.SUBSCRIPTION_ID
  JOIN
  RATOR_MONITORING_CONFIGURATION.ENV_BRAND_BP_EVENT BBE ON BBE.EVENT_ID = DCR.EVENT_ID JOIN
  RATOR_MONITORING_CONFIGURATION.ENVIRONMENT ENV on BBE.ENV_ID=ENV.ENV_ID
  JOIN RATOR_MONITORING_CONFIGURATION.BRAND BR ON BBE.BRAND_ID = BR.BRAND_ID
  JOIN RATOR_MONITORING_CONFIGURATION.BUSINESS_PROCESS BP ON BBE.BP_ID = BP.BP_ID
  AND NOT EXISTS(SELECT CD.SUBSCRIPTION_ID FROM CAPTURED_DATA_01 CD WHERE CD.EVENT_ID = DCR.EVENT_ID AND CD.SUBSCRIPTION_ID = DCR.SUBSCRIPTION_ID);
  
  EXCEPTION WHEN OTHERS THEN
  err_code := SQLCODE;
  err_msg := SUBSTR(SQLERRM, 1, 200);
  
  DBMS_OUTPUT.PUT_LINE('OTHERS exception in EXT_10072_REQ_SENT_SPAIN - KPI_DEF_ID:'||to_char(IN_KPI_DEF_ID) || err_code || '----' || err_msg || 'OBJECT_NAME-&gt;');
  RAISE;
  
  COMMIT;
  
  
  END EXT_10024_ACTIVATE_OPTION;
  </code></pre>