死鎖（ora-00060）以及死鎖相關的知識點

最近碰到一個死鎖的問題：ora-00060 deadlock detected while waiting for resource  （ora-00060 等待資源時檢測到死鎖）

檢視udump（SQL> show parameter USER_DUMP_DEST;  檢視該目錄）下面的trace，發現如下日誌：

*** 2009-08-13 10:53:11.656

*** SERVICE NAME:(his3) 2009-08-13 10:53:11.593

*** SESSION ID:(130.3437) 2009-08-13 10:53:11.593

DEADLOCK DETECTED

[Transaction Deadlock]

Current SQL statement for this session:

UPDATE MY_KUCUN1 SET KUCUNSL = KUCUNSL - :B3 WHERE YINGYONGID = :B2 AND JIAGEID = :B1

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

33DF6A44      6002  package body HIS3KS.PKG_MY_JINXIAOCUN

2FD11B48         1  anonymous block

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-000b001c-00005019        38     130     X             19     106           X

TX-00010025-000100ee        19     106     X             38     130           X

session 130: DID 0001-0026-00008297    session 106: DID 0001-0013-00004EB8

session 106: DID 0001-0013-00004EB8   session 130: DID 0001-0026-00008297

Rows waited on:

Session 106: obj - rowid = 0000E1AB - AAAOGrAAXAAAJHQAAT

  (dictionary objn - 57771, file - 23, block - 37328, slot - 19)

Session 130: obj - rowid = 0000E1AB - AAAOGrAAXAAAJHQAAZ

  (dictionary objn - 57771, file - 23, block - 37328, slot - 25)

Information on the OTHER waiting sessions:

Session 106:

  pid=19 serial=671 audsid=309881 user: 64/HIS3KS

  O/S info: user: NT AUTHORITY/ANONYMOUS LOGON, term: MEDIINFO-QA2, ospid: 6708:6612, machine: WORKGROUP/MEDIINFO-QA2

            program: cicsas.exe

  application name: cicsas.exe, hash value=0

  Current SQL Statement:

  UPDATE MY_KUCUN1 SET KUCUNSL = KUCUNSL - :B3 WHERE YINGYONGID = :B2 AND JIAGEID = :B1

End of information on OTHER waiting sessions.

===================================================

幾個重要的資訊就是鎖的型別是X，說明是DML語句導致的行級排他鎖，涉及到兩個Session：130和106。130就是報錯ora-00060的Session。

下面是兩個Session涉及到的物件ID，三種顏色其實代表的都是同樣的意思。0000E1AB的10進位制就是57771

SQL> select dbms_rowid.rowid_object( 'AAAOGrAAXAAAJHQAAT' ) from dual;

DBMS_ROWID.ROWID_OBJECT('AAAOGrAAXAAAJHQAAT')

------------------------------

                        57771

SQL> select dbms_rowid.rowid_object( 'AAAOGrAAXAAAJHQAAZ' ) from dual;

DBMS_ROWID.ROWID_OBJECT('AAAOGrAAXAAAJHQAAZ')

------------------------------

                        57771

查詢物件：

SQL> SELECT t.owner,t.object_name,t.object_type FROM all_objects t WHERE t.data_object_id = 57771

以上就是該日誌檔案能夠提供的內容，可以從中分析在那個程式碼裡面產生的問題，下面是關於死鎖的知識:

1. 產生死鎖的典型原理：

session A locks row X

sesssion B locks row Y

session A tries to lock row Y

session B tries to lock row X

Session A fails after roughly 3 seconds with a deadlock ORA-00060

2.死鎖產生的二大原因

a. unindexed foreign keys in a system that issues a delete against the parent table OR updates the

parent primary key.

b. use of bitmap indexes on tables that are modifed "in real time".

the next big one:

c. application design flaw

the last, mostly rare - but can happen

d. itl deadlocks, undersized initrans

3. 死鎖產生後，其中一個佔用資源會被Oracle自動釋放，例如：

just set up a table with three rows (x=1,2,3)

in 3 sessions - update 1 and then 2 and then 3 (one in each session)

have session 3 update 1

have session 2 update 3

have session 1 update 2

one of them will be chosen as the deadlock victim and their STATEMENT (but not transaction) will be

rolled back, they have to figure out whether to go forward and eventually commit or rollback the

transaction, releasing one of the other sessions.

That means multiple way deadlocks are resolved by killing the transactions by oracle as if

deadlocks are between two transactions until all the deadlocks are cleared.

but in a distributed environment, things are different (the locks time out instead).

4. session deadlock itself   單個session也可以產生死鎖

create table t ( x int primary key );

insert into t values ( 1 );

declare

pragma autonomous_transaction;

begin

insert into t values ( 1 );

commit;

end;

/

that'll do it. all you need is two transactions and you have that ability.

參考資料：

http://**/viewthread.php?tid=140645

http://it.toolbox.com/blogs/confessions/how-to-use-oracle-25-understanding-deadlocks-17029

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4144238660486

http://asktom.oracle.com/pls/asktom/f?p=100:11:8973480827671230::::P11_QUESTION_ID:1068032649872