oracle deadlock itself

遇到一个奇怪的死锁报错，死锁的blockers和waiters都是同一个会话

 

在网上搜资料时发现有三种情况会导致deadlock itself

http://www.dba-oracle.com/t_session_deadlock_itself.htm

 

在官方文档查询相关资料，找到文档

ORA-00060 Single-Resource Deadlock Occurs on Autonomous Transaction (文档 ID 1511700.1)

Autonomous Transaction can cause locking (文档 ID 224305.1)

其中有该报错的复现方法（见下文）

 

报错的原因为：

The deadlock occurs because the MAIN and LOCAL procedures are trying to modify
the same records. Since the LOCAL procedure is autonomous, it cannot see that
the MAIN procedure has acquired a lock on the row it is trying to access. The
MAIN procedure also cannot see that the LOCAL procedure has acquired a lock on
the row it needs.

 

解决方法：改代码。。。

 

如何复现该报错

 

Example of Autonomous Deadlock

 -- Login to scott/tiger , create table and populate --

sqlplus scott/tiger

CREATE TABLE test_emp AS SELECT * FROM emp;

INSERT INTO test_emp

VALUES      (9999,

             'SCOTT',

             99,

             99,

             SYSDATE,

             10000,

             0,

             10);

COMMIT;

 

-- Create Procedure using autonomous transaction --

CREATE OR replace PROCEDURE Proc_a

AS

  PRAGMA autonomous_transaction;

BEGIN

    UPDATE test_emp

    SET    sal = sal + 5000

    WHERE  empno = 9999;

    COMMIT;

END;

/

 

-- Create Main Procedure --

CREATE OR REPLACE PROCEDURE Main_p

AS

BEGIN

    UPDATE test_emp

    SET    comm = 5000

    WHERE  empno = 9999;

    proc_a;

    COMMIT;

END;

/

 

-- Call Main Procedure --

SQL> exec main_p

BEGIN main_p; END;

 

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at "SCOTT.PROC_A", line 5

ORA-06512: at "SCOTT.MAIN_P", line 4

ORA-06512: at line 1

 

从trace file可以看到

 

With an autonomous transaction, there is only one session involved and the deadlock graph contains only one row where both blocker and waiter are the same session as shown in the following example:

As well as the deadlock graph, the trace file contains PROCESS STATE information. The information below shows different sections of the trace file that may be of interest:

 

关于自治事务

 

自治事务与死锁

 

https://blog.csdn.net/pan_tian/article/details/7675800