ORA-00054: 资源繁忙并在 oracle 过程中使用 NOWAIT 获取

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

提问by Andrew

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.

我通过java线程代码并行运行许多程序。我在我的过程中使用了异常处理以捕获任何错误结果。当我看到 java 调试器日志时，我可以看到很少有程序抛出错误ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired。我使用了几个共同的表，也被其他程序使用。我不知道这个问题出现的原因。

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;

回答by Paul

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

您的代码中唯一可能导致这种情况的是TRUNCATE.

TRUNCATEis not DML. It's DDL operation. It aquires table lock. Because you are doing some DML in other sessions (inserting particular rows), session performing TRUNCATEcannot aquire table lock. Somewhere internally NOWAITis specified, so the exception is raised.

TRUNCATE不是 DML。这是 DDL 操作。它获得表锁。因为您正在其他会话中执行一些 DML（插入特定行），所以会话执行TRUNCATE无法获得表锁定。在内部NOWAIT指定了某个地方，因此引发了异常。

I believe, you should not use TRUNCATEhere. It is DDL, it performs commit so it's not transactional. Obviously, it is not safe to use it in multisession environment.

我相信，你不应该TRUNCATE在这里使用。它是 DDL，它执行提交，因此它不是事务性的。显然，在多会话环境中使用它是不安全的。

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

您可以使用临时表。您存储在其中的所有数据仅存在于您当前的会话中。这样你就不需要使用TRUNCATE.

DDL for them will look something like this.

他们的 DDL 看起来像这样。

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

回答by Tony Andrews

TRUNCATE TABLEis a DDL command, and if unable to lock the table to perform the truncate, Oracle will raise ORA-00054. So if one session is using the table, another session cannot truncate it.

TRUNCATE TABLE是一个 DDL 命令，如果无法锁定表来执行截断，Oracle 将引发 ORA-00054。因此，如果一个会话正在使用该表，则另一个会话无法截断它。