Oracle – ORA-00054: resource busy and acquire with NOWAIT in oracle procedure

commitexception handlinglockingoraclestored-procedures

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 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. 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. 

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 < 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 >= 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 < to_char(SYSDATE - numtodsinterval (  KPI_DEF_ROW.BUSINESS_CHK_PERIOD_VAL,KPI_DEF_ROW.BUSINESS_CHECK_PERIOD_UNIT_ID ), 'YYYYMMDDHH24MISS')||'0000'
 and SO.ID > 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 < SYSDATE - numtodsinterval (  KPI_DEF_ROW.BUSINESS_CHK_PERIOD_VAL,KPI_DEF_ROW.BUSINESS_CHECK_PERIOD_UNIT_ID )
and TSM.CREATE_DATE > 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->');
RAISE;

COMMIT;


END EXT_10024_ACTIVATE_OPTION;

Best Answer

The only thing in your code that can cause this is the TRUNCATE.

TRUNCATE is not DML. It's a DDL operation. It acquires a full table lock. Because you are doing some DML in other sessions (inserting particular rows), the session performing TRUNCATE cannot acquire the table lock. Somewhere internally NOWAIT is specified, so the exception is raised.

I believe you should reconsider using TRUNCATE here. It is DDL, it performs commit so it's not transactional. Obviously, it is not safe to use it in multi user environment.

You could use TEMPORARY TABLES. All the data you store in them only exists in your current session. This way you don't need to use TRUNCATE.

DDL will look something like this.

CRATE GLOBAL TEMPORARY TABLE TEMP_ACTIVATE_OPTION(
  ID   NUMBER,
  ... -- your columns
)
ON COMMIT DELETE ROWS;
Related Topic