ORA-00060: deadlock detected while waiting for resource using DBA_PARALLEL_EXECUTE_CHUNKS

Question

I am getting ORA-00060: deadlock detected while waiting for resources using DBA_PARALLEL_EXECUTE_CHUNKS. It is not happening daily but once or twice a week . I am using the Merge statement to update and insert the records . Is there a way to avoid this error ?

 DBMS_PARALLEL_EXECUTE.RUN_TASK (task_name        => l_task_name,
                                      sql_stmt         => l_sql,
                                      language_flag    => DBMS_SQL.NATIVE,
                                      parallel_level   => 4);


Trace file /u01/XYZ.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
Build label:    RDBMS_19.17.0.0.0DBRU_LINUX.X64_220924
ORACLE_HOME:    /u01/app/oracle/product/19c/dbhome_1
System name:    Linux
Release:    3.10.0-1160.92.1.el7.x86_64
Version:    #1 SMP Thu May 18 11:23:40 UTC 2023
Oracle process number: 230

*** 2023-08-15T20:55:11.362843-04:00
*** SESSION ID:(1696.21009) 2023-08-15T20:55:11.362865-04:00
*** CLIENT ID:() 2023-08-15T20:55:11.362874-04:00
*** SERVICE NAME:(SYS$USERS) 2023-08-15T20:55:11.362879-04:00
*** MODULE NAME:(DBMS_SCHEDULER) 2023-08-15T20:55:11.362885-04:00
*** ACTION NAME:(TASK$_34836_1) 2023-08-15T20:55:11.362891-04:00
*** CLIENT DRIVER:() 2023-08-15T20:55:11.362896-04:00
 
2023-08-15 20:55:11.361*:ksq.c@13204:ksqdld_hdr_dump(): 
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]
 
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
 
Deadlock graph:
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-0004001D-0053AC1A-00000000-00000000        230    1696     X        21009     279    6509           S  39534
TX-005C0016-0023CB44-00000000-00000000        279    6509     X        39534     230    1696           S  21009
 
----- Information for waiting sessions -----
Session 1696:
Holds resource TX-0004001D-0053AC1A-00000000-00000000 acquired 46 seconds ago.
  sid: 1696 ser: 21009 audsid: 50982032 user: 106/USER
    flags: (0x8310041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 230 O/S info: user: oracle, term: UNKNOWN, ospid: 560
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 560
    application name: DBMS_SCHEDULER, hash value=2478762354
    action name: TASK$_34836_1, hash value=3488044395
  current SQL:
  MERGE INTO T1 USING (SELECT * FROM S_T S WHERE ROWID BETWEEN :B2 AND :B1 ) S ON (T1.ID = S.ID) 
  WHEN MATCHED THEN UPDATE SET ....
Session 6509:
Holds resource TX-005C0016-0023CB44-00000000-00000000 acquired 41 seconds ago.
  sid: 6509 ser: 39534 audsid: 50982034 user: 106/USER
    flags: (0x8310041) USR/- flags2: (0x40009) -/-/INC
    flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
  pid: 279 O/S info: user: oracle, term: UNKNOWN, ospid: 599
  client details:
    O/S info: user: oracle, term: UNKNOWN, ospid: 599
    application name: DBMS_SCHEDULER, hash value=2478762354
    action name: TASK$_34836_2, hash value=2438080590
  current SQL:
    MERGE INTO T1 USING (SELECT * FROM S_T S WHERE ROWID BETWEEN :B2 AND :B1 ) S ON (T1.ID = S.ID) 
  WHEN MATCHED THEN UPDATE SET ....
----- End of information for waiting sessions -----

Thanks in Advance

Answer 1

It seems it must be the case that you have the same values for ID appearing in multiple rowid ranges. This would be a problem even if you weren't having deadlocks, as you'd still have contention and unnecessary waits.

I would suggest that you break up your work by ranges for values of S_T.ID instead of S_T.ROWID. You would use DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL to do this.

Then your merge becomes:

MERGE INTO T1 USING 
  (SELECT * FROM S_T S WHERE S.ID BETWEEN :B2 AND :B1) S ON (T1.ID = S.ID) 
WHEN MATCHED THEN UPDATE SET ....

Assuming T1.ID is a unique key, that should eliminate both the contention and the deadlocks.

Answer 2

If you rewrite your MERGE to only modify rows that actually have a change, your volume of redo will probably be much lower and won't pose a problem for your redo shipping to standby, allowing you to do the work in a single transaction. This will work much better than dba_parallel_execute_chunks and being a single session will greatly reduce the chances of a deadlock:

ALTER SESSION ENABLE PARALLEL DML;

MERGE /*+ parallel(t1,16) */ INTO T1 
USING (SELECT /*+ parallel(16) */ 
              T1.ROWID row_id,
              S_T.*
         FROM S_T,
              T1
        WHERE S_T.ID = T1.ID(+)
          AND (T1.ID IS NULL OR -- new records
               NOT (S_T.COL1 = T1.COL1 AND -- compare nonnullable cols
                    NVL(S_T.COL2,' ') = NVL(T1.COL2,' ') AND -- nullable strings
                    NVL(S_T.COL3,-1) = NVL(T1.COL3,-1) -- nullable numbers, etc.
                   )
               )
       ) S ,
 ) S ON (T1.ROWID = S.row_id)  -- use ROWID obtained above
  WHEN MATCHED THEN UPDATE SET T1.COl1 = S.COl1,
                               T1.COL2 = S.COL2,
                               T1.COL3 = S.COL3, 
                               ...
  WHEN NOT MATCHED THEN INSERT ...

In addition to this rewrite, you'll want to ensure that:

1. There are no triggers on the table. This would disable parallel dml and could contribute to a deadlocking situation depending on what it's doing.
2. There are no foreign keys on the table. This would disable parallel dml and could contribute to a deadlocking situation.
3. If this is Exadata, you aren't using HCC compression (without the expensive ACO license to enable row level locking, HCC compression units (CUs) have only one lock bit per CU, drastically limiting concurrency. Concurrent updates will frequently deadlock in this situation).
4. Your data blocks have enough free space for ITL expansion. Make sure they aren't compressed with PCTFREE 0 without override INITRANS to a much higher value than the default.