My Oracle Support Banner

ORA-60 Deadlock Detected With PARALLEL Hint (Doc ID 412778.1)

Last updated on MARCH 12, 2021

Applies to:

PL/SQL - Version 9.2.0.8 and later
Information 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

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
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.