單一會話引發的死鎖

客戶環境中出現了ORA-60死鎖錯誤，檢查日誌發現，持有鎖和等待鎖的是同一個會話。

一般來說構成死鎖至少需要兩個會話，而當前的問題是一個會話引發的：

Wed Nov 23 10:19:46 2011
ORA-00060: Deadlock detected. More info in file /oracle/admin/db1/udump/db1_ora_3408686.trc.

對應的詳細資訊：

*** 2011-10-29 10:11:28.970
*** SERVICE NAME:(db1) 2011-10-29 10:11:28.960
*** SESSION ID:(5562.45) 2011-10-29 10:11:28.960
DEADLOCK DETECTED ( ORA-00060 )
[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  process session holds waits
TX-000c0016-000499ad        16    5562     X             16    5562           X
session 5562: DID 0001-0010-00000092 session 5562: DID 0001-0010-00000092
Rows waited on:
Session 5562: obj - rowid = 00009050 - AAAJBQAAWAAArQ6AAG
  (dictionary objn - 36944, file - 22, block - 177210, slot - 6)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

可以看到，等待的和持有鎖的是同一個會話。

根據trace資訊記錄的物件，發現問題是自治事務導致的。

在主事務中如果更新了部分記錄，這是啟動自治事務更新同樣的記錄，就會造成死鎖，下面透過一個簡單的例子模擬了這個錯誤的產生：

SQL> create table t (id number, name varchar2(30));

Table created.

SQL> insert into t select rownum, tname from tab;

4 rows created.

SQL> commit;

Commit complete.

SQL> create or replace procedure p_test as
2 pragma autonomous_transaction;
3 begin
4 update t set name = name where id = 1;
5 commit;
6 end;
7 /

Procedure created.

SQL> begin
2 update t set name = name where id = 1;
3 p_test;
4 end;
5 /

begin
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "TEST.P_TEST", line 4
ORA-06512: at line 3

在使用自治事務的時候要避免當前事務鎖定的記錄和自治事務中鎖定的記錄相互衝突。