oracle ORA-00060：使用DBA_PARALLEL_EXECUTE_CHUNKS等待资源时检测到死锁

5lwkijsr 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(114)

我收到ORA-00060：使用DBA_PARALLEL_EXECUTE_CHUNKS等待资源时检测到死锁。它不是每天发生，而是一周一两次。我正在使用Merge语句更新和插入记录。有没有办法避免这种错误？

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

oracle

来源：https://stackoverflow.com/questions/77081994/ora-00060-deadlock-detected-while-waiting-for-resource-using-dba-parallel-execu

2条答案

按热度按时间

vltsax251#

如果您重写MERGE以仅修改实际发生更改的行，则重做的量可能会低得多，并且不会对重做发送到备用状态造成问题，从而允许您在单个事务中完成工作。这将比dba_parallel_execute_chunks更好地工作，并且作为单个会话将大大减少死锁的机会：

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 ...

除了重写之外，您还需要确保：
1.table上没有触发器。这将禁用并行dml，并可能导致死锁情况，具体取决于它在做什么。
1.表中没有外键。这将禁用并行dml，并可能导致死锁情况。
1.如果这是Exhibition，那么您就没有使用HCC压缩（如果没有昂贵的ACO许可证来启用行级锁定，HCC压缩单元（CU）每个CU只有一个锁定位，极大地限制了并发性。在这种情况下，并发更新经常会死锁）。
1.您的数据块有足够的空闲空间用于ITL扩展。确保它们没有被PCTFREE 0压缩，而没有将INITRANS重写为比默认值高得多的值。

赞(0）回复(0）举报 2023-10-16

wixjitnu2#

看起来一定是这样的情况，您在多个rowid范围中出现了相同的ID值。即使您没有遇到死锁，这也是一个问题，因为您仍然会遇到争用和不必要的等待。
我建议你将你的工作分解为S_T.ID而不是S_T.ROWID的值。你可以使用DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL来实现。
然后你的合并变成：

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 ....

假设T1.ID是一个唯一的密钥，这应该可以消除争用和死锁。

赞(0）回复(0）举报 2023-10-16

我来回答

oracle ORA-00060：使用DBA_PARALLEL_EXECUTE_CHUNKS等待资源时检测到死锁

2条答案

相关问题

热门标签

最新问答