ORACLE 死鎖分析過程

ORACLE 死鎖分析
關於死鎖一般3種處理方式
1、事前預測
2、資源分級
3、事後檢測釋放


我知道的ORACLE MYSQL都是採用第三種在行鎖級別上的話。
這裡分析一個ORACLE死鎖，首先一個死鎖肯定會生成一個TRACE檔案，這裡會記錄很多資訊如：
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0058000f-0000b473       649    1204     X            651    1252           X
TX-0019001c-0004e0b0       651    1252     X            649    1204           X


這裡給出了程式和會話id
Rows waited on:
  Session 1204: obj - rowid = 0003D942 - AAA9lCAAEAADgaNAAI
  (dictionary objn - 252226, file - 4, block - 919181, slot - 8)
  Session 1252: obj - rowid = 0003D942 - AAA9lCAAEAADgaNAAa
  (dictionary objn - 252226, file - 4, block - 919181, slot - 26)
這裡給出導致死鎖的行


同時給出了最後觸發死鎖會話 1252的語句
----- Information for the OTHER waiting sessions -----
Session 1252:
  sid: 1252 ser: 35883 audsid: 7170593 user: 235/FEECORESV
    flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 651 O/S info: user: oracle, term: UNKNOWN, ospid: 13035
    image: oracle@oratest11
  client details:
    O/S info: user: sky, term: unknown, ospid: 1234
    machine: autobots program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  UPDATE *******
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=3vh5sc7pgtrjy) -----
UPDATE *******


那麼到這裡我們大概能夠分析出
A:1204拿到AAA9lCAAEAADgaNAAa 行鎖    B:1252 拿到 AAA9lCAAEAADgaNAAI 行鎖
C:1204需要AAA9lCAAEAADgaNAAI 則等待   D:1252 需要  AAA9lCAAEAADgaNAAa 則觸發死鎖1204回滾


那麼隨後trace給出1204 C這一步等待時間和事物資訊
SO: 0xee1fcd10, type: 4, owner: 0xf031e750, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0xf031e750, name=session, file=ksu.h LINE:12624, pg=0
    (session) sid: 1204 ser: 2443 trans: 0xe9221180, creator: 0xf031e750
              flags: (0x100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x40009) -/-/INC
              DID: , short-term DID: 
              txn branch: (nil)
              oct: 6, prv: 0, sql: 0xf25d2278, psql: 0xc4346788, user: 235/FEECORESV
    ksuxds FALSE at location: 0
    service name: SYS$USERS
Current Wait Stack:
     0: waiting for 'enq: TX - row lock contention'
        name|mode=0x54580006, usn<<16 | slot=0x19001c, sequence=0x4e0b0
        wait_id=33 seq_num=34 snap_id=1
        wait times: snap=3.001739 sec, exc=3.001739 sec, total=3.001739 sec
        wait times: max=infinite, heur=3.001739 sec
        wait counts: calls=1 os=1
        in_wait=1 iflags=0x15a0
隨後給出了導致他等待會話的等待資訊，這裡不給出。當然隨後還有很多類容，但是關鍵就是如上
但是這裡並沒有一個顯示的事物執行的過程，如果要看到完整的語句我們需要日誌挖掘，我挖掘出來的如下：

1204：
set transaction read write;
select * from TEST.TEST where ROWID = 'AAA9lCAAEAADgaNAAa' for update;
commit;
1252：
set transaction read write;
select * from TEST.TEST where ROWID = 'AAA9lCAAEAADgaNAAI' for update;
update TEST.TEST set "STATUS" = 'SUCCESS', "DETAIL" = '執行成功', "RAW_UPDATE_TIME" = TO_TIMESTAMP('19-SEP-16 01.27.25.714715 PM') where "IDENTITY" = '39319' and "STATUS" = 'PROCESSING' and "DETAIL" IS NULL and "RAW_UPDATE_TIME" = TO_TIMESTAMP('19-SEP-16 01.27.24.611036 PM') and ROWID = 'AAA9lCAAEAADgaNAAa';
commit;

這樣能夠清楚的看到1204的update並沒有執行，而由於觸發了deadlock回滾掉了。