測試庫死鎖診斷DEADLOCK DETECTED ( ORA-00060 )

 

2012年5月份的一天，開發同事找到我，說測試庫碰到了死鎖問題，要求幫忙看看。

1.死鎖問題及建議

死鎖：DEADLOCK DETECTED ( ORA-00060 )

現象：

Thu May 17 10:35:35 2012

ORA-00060: Deadlock detected. More info in file /oradata/diag/rdbms/orcl/orcl/trace/orcl_ora_893044.trc.

trace檔案資訊摘錄：

*** 2012-05-17 10:35:34.727

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

TM-0001226e-00000000       118     371    SX   SSX      117     310    SX   SSX

TM-0001226e-00000000       117     310    SX   SSX      118     371    SX   SSX

 

session 371: DID 0001-0076-00000002     session 310: DID 0001-0075-00000008

session 310: DID 0001-0075-00000008     session 371: DID 0001-0076-00000002

 

Rows waited on:

  Session 371: no row

  Session 310: no row

 

----- Information for the OTHER waiting sessions -----

Session 310:

  sid: 310 ser: 88 audsid: 2818451 user: 102/DATAMGR flags: 0x100045

  pid: 117 O/S info: user: oracle, term: UNKNOWN, ospid: 1212508

    image: oracle@localhost

  client details:

    O/S info: user: bisp, term: unknown, ospid: 1234

    machine: dmsvr.bisp program: JDBC Thin Client

    application name: JDBC Thin Client, hash value=2546894660

  current SQL:

  DELETE FROM FORECAST_KEYPOINT WHERE FORECAST_ID = :1

 

----- End of information for the OTHER waiting sessions -----

 

Information for THIS session:

 

----- Current SQL Statement for this session (sql_id=0jdjc3kdu7dnz) -----

DELETE FROM FORECAST_KEYPOINT WHERE FORECAST_ID = :1

****************************************************************************

Session371和session310發起的兩個事務分別包含上述標紅的sql語句，相互等待對方資源的釋放，造成死鎖；

首先注意到的是Deadlock graph中的資源佔有情況，可以看到兩個session都hold了一個SX型別的鎖，同時在等待SSX型別的鎖，而且引發的是一個刪除語句，並且這個表是系統的一個關鍵表，大部分的表的外來鍵都引用自此表的主鍵。因此猜測是碰到了外來鍵引發的死鎖。

建議1：

應用程式開發人員檢查表FORECAST_AIRPORT和表FORECAST_KEYPOINT之間的關係（如父表FORECAST_KEYPOINT、子表FORECAST_AIRPORT  具體參照哪一列），有哪些約束、索引等；

如果子表上的外來鍵約束列沒有建立索引，刪除父表記錄時不得不對子表加表級鎖，防止其他刪除操作對該表的操作，要解決這種死鎖問題則需要在子表的外來鍵約束裂傷建立相應的索引；

這樣，當對子表的外來鍵列新增索引後，死鎖可以被消除，因為這時刪除父表記錄不需要對子表加表級鎖。   （後面有模擬實驗可以驗證）

建立索引：create index index_name on schema.table_name(column_name);

create index index_name on pdmcompard.FORECAST_AIRPORT (FORECAST_ID);

create index index_name on datamgr.FORECAST_AIRPORT (FORECAST_ID);

建議2：

也有可能是迴圈刪除的問題（如果第1個建議沒有成功，可以試試此方法）
如果可以，改成delete from t_user where ID in (:1,:2...)

《調整業務邏輯，修改應用程式，合理分配資源》

2.附錄及實驗

表1 Oracle的TM鎖型別
鎖模式        鎖描述        解釋        SQL操作
0        none                  
1        NULL        空        Select
2        SS(Row-S)        行級共享鎖，其他物件只能查詢這些資料行        Select for update、Lock for update、Lock row share
3        SX(Row-X)        行級排它鎖，在提交前不允許做DML操作        Insert、Update、Delete、Lock row share
4        S(Share)        共享鎖        Create index、Lock share
5        SSX(S/Row-X)        共享行級排它鎖        Lock share row exclusive
6        X(Exclusive)        排它鎖        Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

試驗如下：
1、建立一個表，此表作為子表
SQL> create table fk_table as select * from user_objects;

Table created
2、建立一個表，此表作為父表
SQL> create table pk_table as select * from user_objects;

Table created
3、建立父表的主鍵
SQL> alter table PK_TABLE add constraint pk_pktable primary key (OBJECT_ID);

Table altered
4、建立子表的外來鍵
SQL> alter table FK_TABLE add constraint fk_fktable foreign key (OBJECT_ID) references pk_table (OBJECT_ID);

Table altered
5、如下sql取自TOAD工具，用來顯示資料庫鎖的資訊
SELECT LK.SID,
SE.USERNAME,
SE.OSUSER,
SE.MACHINE,
DECODE(LK.TYPE,
'TX',
'Transaction',
'TM',
'DML',
'UL',
'PL/SQL User Lock',
LK.TYPE) LOCK_TYPE,
DECODE(LK.LMODE,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
TO_CHAR(LK.LMODE)) MODE_HELD,
DECODE(LK.REQUEST,
0,
'None',
1,
'Null',
2,
'Row-S (SS)',
3,
'Row-X (SX)',
4,
'Share',
5,
'S/Row-X (SSX)',
6,
'Exclusive',
TO_CHAR(LK.REQUEST)) MODE_REQUESTED,
TO_CHAR(LK.ID1) LOCK_ID1,
TO_CHAR(LK.ID2) LOCK_ID2,
OB.OWNER,
OB.OBJECT_TYPE,
OB.OBJECT_NAME,
LK.BLOCK,
SE.LOCKWAIT
FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE
WHERE LK.TYPE IN ('TM', 'UL')
AND LK.SID = SE.SID
AND LK.ID1 = OB.OBJECT_ID(+);

6、執行一個刪除操作，這時候在子表和父表上都加了一個Row-S(SX)鎖
delete from fk_table where object_id=94716;
delete from pk_table where object_id=94716;
7、執行另一個刪除操作，發現這時候第二個刪除語句等待
delete from fk_table where object_id=94702;
delete from pk_table where object_id=94702;
執行查詢語句，得到鎖資訊如下：
857 DML Row-S (SS) None 107220 0 BILL TABLE PK_TABLE 0 00000000D555A0E8
872 DML Row-X (SX) None 107220 0 BILL TABLE PK_TABLE 0
857 DML Row-X (SX) S/Row-X (SSX) 107219 0 BILL TABLE FK_TABLE 0 00000000D555A0E8
872 DML Row-X (SX) None 107219 0 BILL TABLE FK_TABLE 1
可以看到session 857在請求一個SSX型別的鎖，此時如果執行如下操作：
delete from pk_table where object_id=94716;
死鎖馬上發生，因為857 SESSION拿到了一個對FK_TABLE的行獨佔鎖，並在請求一個表共享鎖，而872 SESSION也拿到了一個FK_TABLE上的行獨佔鎖，並請求一個表共享鎖。此時兩個session誰都不會釋放獨佔鎖，並同時請求表的共享鎖，死鎖由此引發。因為死鎖引發的時候兩個session不是在等待對資料行進行加鎖，所以可以從trace檔案中發現等待的行都為no row，同時可以看到兩個session都hold了一個SX鎖，並且都在等待SSX鎖資源。同時trace檔案中還記錄了引發死鎖的sql。

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-0001a2d3-00000000 16 872 SX SSX 20 857 SX SSX
TM-0001a2d3-00000000 20 857 SX SSX 16 872 SX SSX
session 872: DID 0001-0010-000F5EA0 session 857: DID 0001-0014-001D7407
session 857: DID 0001-0014-001D7407 session 872: DID 0001-0010-000F5EA0
Rows waited on:
Session 857: no row
Session 872: no row

Current SQL Statement:
delete from pk_table where object_id=94716
8、當對子表的外來鍵列新增索引後，死鎖被消除，因為這時刪除父表記錄不需要對子表加表級鎖，這裡不再做測試。

結論：曾經有人討論過是否所有的資料庫設計都應該遵守正規化的規範，都把主外來鍵關係建立起來。也有人反對這樣做，因為這樣複雜的關係在OLTP系統中可能會成為災難，而提倡透過程式來保證資料的完整性，但程式發生bug導致資料不一致的情況時有發生。而且如果外來鍵設定為級聯刪除，則不加索引的外來鍵會使得對子表的記錄刪除走全表掃描。因此，對外來鍵的使用還是要慎重！