ORA-60 Deadlock Detected With PARALLEL Hint
(Doc ID 412778.1)
Last updated on JULY 03, 2023
Applies to:
PL/SQL - Version 9.2.0.8 and laterInformation in this document applies to any platform.
Symptoms
When running the same procedure (elab_prova) by several sessions at the same time,
there are some records update more than one time and ORA-000060 (deadlock) occurs.
When the procedure is running with the same conditions
but without the PARALLEL hint no more than on update per row and no deadlock raised.
Sample:
Create table PROVA (c number,d number,e number);
declare
x number := 0;
begin
FOR i IN 1..50000 LOOP
insert into prova values (i, i, x);
END LOOP;
end;
/
create or replace PROCEDURE elab_prova(maxrow NUMBER) IS
CURSOR cCustomer IS SELECT /*+ FULL(tcd) PARALLEL(tcd,2)*/
c,d,e,rowid as "ROWID" from prova tcd WHERE tcd.e = 0 AND rownum <= maxrow FOR UPDATE;
rCustomer cCustomer%ROWTYPE;
TYPE TBlkCustomerC IS VARRAY(2000) OF rCustomer.C%TYPE;
TYPE TBlkCustomerD IS VARRAY(2000) OF rCustomer.D%TYPE;
TYPE TBlkAccountE IS VARRAY(2000) OF rCustomer.E%TYPE;
TYPE TBlkRowid IS VARRAY(2000) OF rCustomer.ROWID%TYPE;
blkCustomerC TBlkCustomerC;
blkCustomerD TBlkCustomerD;
blkAccountE TBlkAccountE;
blkRowid TBlkRowid;
sql_err number;
sql_mess varchar2(1000);
iCnt NUMBER(10);
BEGIN
iCnt := maxrow;
WHILE iCnt = maxrow LOOP
OPEN cCustomer;
FETCH cCustomer BULK COLLECT INTO blkCustomerC, blkCustomerD, blkAccountE, blkRowid LIMIT maxrow;
iCnt := cCustomer%BULK_ROWCOUNT(3);
CLOSE cCustomer;
FOR w IN 1 .. iCnt LOOP
dbms_output.put_line('updating '||blkRowid(w));
END LOOP;
FORALL j IN 1 .. iCnt
UPDATE PROVA SET E = 1 WHERE ROWID = blkRowid(j);
COMMIT;
FOR i IN 1 .. iCnt
LOOP
UPDATE PROVA t SET t.e = 2 WHERE t.ROWID = blkRowid(i);
COMMIT;
END LOOP;
END LOOP;
END;
/Session1: exec elab_prova(100); Session2: exec elab_prova(100);
ORA-00060: deadlock detected while waiting for resource
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |