Oracle parallel DML: Deadlock Detected: ORA-00060 Oracle Database Tips by Donald Burleson

Question:  I'm having trouble running a parallel DML statement.  Here is the code for Parallel DML, I'm running only one session which is current. I don't know how comes I got an error saying DEADLOCK DETECTED:

SQL> alter session enable parallel dml;

Session altered.

SQL>
SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> set timin on
SQL>
SQL>
SQL> UPDATE /*+ full(myobjects) parallel(myobjects, 8)*/ myobjects
  2  SET object_name = upper(object_name);
update /*+ full(myobjects) parallel(myobjects, 8)*/
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P015
ORA-00060: deadlock detected while waiting for resource

Answer: It's one of two issues:

• Deadly embrace - Competing DML tasks cause perpetual deadlock
   
• ITL shortage - More ITL's needed for parallel DML

ORA-00060 and Competing Updates

Do you have competing updates running?  This may be an age-old issue known as the perpetual embrace!  The docs note that a retry may work:

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock.  To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.

ORA-00060 and INITRANS Shortage

There can also be a ORA-00060 "deadlock detected" where the table and index INITRANS is set too low.  The "Interested Transaction List" and deadlocks caused by an ITL-shortage as described in MOSC note 62354.1.  The eBook "Oracle Space Management Handbook" also has notes on the internals of ITL.

You can also see ITL waits in a STATSPACK or AWR report, in the segments section we see:

Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.

Also, Arup Nanda has scripts for detecting ITL waits:

Select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   ' ' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p
where
   s.sid = l.sid and
   s.username <> ' ' and
   s.paddr = p.addr and
   l.type <> 'TM' and
   (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   object_name object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   sys.dba_objects o
where
   s.sid = l.sid and
   o.object_id = l.id1 and
   l.type = 'TM' and
   s.username <> ' ' and
   s.paddr = p.addr
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   '(Rollback='||rtrim(r.name)||')' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   v$rollname r
where
   s.sid = l.sid and
   l.type = 'TX' and
   l.lmode = 6 and
   trunc(l.id1/65536) = r.usn and
   s.username <> ' ' and
   s.paddr = p.addr
order by 5, 6;
 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.