ORACLE死鎖(ORA-00060)故障排查的一般性手法

ORACLE死鎖(ORA-00060)故障排查的一般性手法

【背景】

今晨巡檢發現SAP PRD產生一個死鎖：

Sun Jun 29 10:30:36 2014

ORA-00060: Deadlock detected.

More info in file /oracle/PRD/saptrace/diag/rdbms/prd/PRD/trace/PRD_ora_2065024.trc.

網上找到一個不錯的troubleshooting的blog，雖然不是SAP系統產生的，但也有很不錯的參考價值，轉于此并進行一定的完善和處理，如下：

【故障現象】：

 兩個Java寫的后臺批處理同時執行時，發生了死鎖現象。(我的情況是SAP PRD環境，但底層數據庫一樣是Oracle。)

【排查手法】：

通過查詢視圖，找到被鎖住的對象v$locked_object，根據其locked_mode，判斷其鎖類型

查詢SQL語句：

SET PAGESIZE 2000 LINESIZE 1000;

col XIDUSN format 99;

col OBJECT_ID format 99999999;

col OWNER format a8;

col OBJECT_NAME format a25;

col SESSION_ID format 99999;

col ORACLE_USERNAME format a8;

col OS_USER_NAME format a8;

col PROCESS formaT 999999;

COL LOCKE format a4;

select l.xidusn, l.object_id, o.owner, o.object_name,

l.session_id, l.oracle_username, l.os_user_name, l.process,

decode(l.locked_mode, 0, '',

        1, 'NULL',

        2, '(SS)',

        3, '(SX)',

        4, '(S)',

        5, '(SSX)',

        6, '(X)',

        '???') locked_mode

from v$locked_object l, dba_objects o

where l.object_id = o.object_id;

判斷查詢結果，發現兩個Session對同一個表的數據行進行了排他。

用以下的語句對視圖v$sqltext進行查詢，可以得到當前正在執行的SQL語句，以及執行SQL語句的session

select username, osuser, machine, terminal, program,

    sid, serial#, status, sql_address, sql_text

from v$session ss, v$sqltext sq

where type = 'USER'

and ss.sql_address = sq.address

order by ss.sid, ss.serial#, sq.piece;

可以發現對同一表中的同一數據行進行更新的兩條SQL語句。

通過這兩條SQL語句，可以定位Java程序中導致問題的代碼。

【問題原因分析】：

     對Java代碼進行分析后，發現有一個按照數據行主鍵，逐行循環處理的操作。

不幸的是，循環前，從KeySet（）生成的主鍵列表沒有進行排序，導致每次循環的執行順序都是隨機的。

     例如，假設兩個session都想要對數據行A,B,C,D進行處理，很有可能session1先處理了A,B，

而此時session2剛好處理完了C,D。這樣，session1想要繼續處理的C,D由于正被session2給鎖住，所以無法繼續執行。

session2想要處理的A,B也被session1給鎖著，session2也無法繼續，兩個session最終都沒有辦法終止。

借助于ORACLE的TRACE文件

       Oracle發現死鎖后，會在alert_[SID].log文件中輸出如下的警告信息：ORA-00060: Deadlock detected.

并提示去查看相應的*.trc文件。通過分析*.trc文件可以看到死鎖的詳細情況，

下面是一個*.trc文件的例子:

*** 2012-01-09 20:11:22.379

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-000a0006-0000f48f        65     101     X             64     102           X

TX-0007000f-0000d8a3        64     102     X             65     101           X

session 101: DID 0001-0041-00000002 session 102: DID 0001-0040-00000002

session 102: DID 0001-0040-00000002 session 101: DID 0001-0041-00000002

注:這里明確指出了發生死鎖的兩個session的ID。

Rows waited on:

  Session 101: obj - rowid = 0008915A - AACJFaAAFAAEwq1AAA

  (dictionary objn - 561498, file - 5, block - 1247925, slot - 0)

  Session 102: obj - rowid = 0008915A - AACJFaAAFAAEwq1AAI

  (dictionary objn - 561498, file - 5, block - 1247925, slot - 8)

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

Session 102:

  sid: 102 ser: 2 audsid: 25260645 user: 87/USR_BAT flags: 0x41

  pid: 64 O/S info: user: ora_1, term: UNKNOWN, ospid: 5915

    image: oracle@pcXX

  client details:

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

    machine: pcXX program: JDBC Thin Client

    application name: JDBC Thin Client, hash value=2546894660

  current SQL:

UPDATE XXXX SET XXXX   注:這里是導致死鎖的SQL語句1

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

Information for THIS session:

*** 2012-01-09 20:11:22.530

----- Current SQL Statement for this session (sql_id=b0qn65w78t10b) -----

UPDATE XXXX SET XXXX  注:這里是導致死鎖的SQL語句2

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

※ log文件和trc文件的存放路徑，取決于Oracle的安裝路徑，可以借助于文件檢索功能。