通常情況下，dead lock是由應用的不合理造成，應該由PD解決，而非DBA。
但何時為非通常情況呢？
今天遇到一個case，即為非通常情況。
而他的通常的標誌即為：在trc檔案裡的deadlock graph裡，看見有SX/SSX的TM lock出現，而不全是X mode的TX lock。
而這，常常是因為foreign key沒有index。（相信很多人都已經有這個經驗了）
但我今天遇到的更加特殊一點的情況是，deadlock graph裡，一個session等在SSX mode TM lock上，另一個等在X mode TX lock上。
而對於foreign key沒有index的情況，我們常常見到的是兩個sessions都等在SSX mode TM lock上。

我今天遇到的deadlock graph如下：
Deadlock graph:
                     ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name       process session holds waits  process session holds waits
TM-000298c4-00000000    143 3185 SX          149 3061 SX SSX
TX-000a0000-0006b43c    149 3061    X          143 3185          X

而如果是因為應用的不合理導致的行級別的衝突所產生的deadlock graph為：
                     ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name       process session holds waits  process session holds waits
TX-00090022-00000079       31 3274    X          29 3267          X
TX-000a0000-000010c0       29 3267    X          31 3274          X

而如果是通常的因為foreign key沒有index所產生的deadlock graph常常為：
                     ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name       process session holds waits  process session holds waits
TM-000047c5-00000000       31 3274 SX SSX    29 3267 SX SSX
TM-000047c5-00000000       29 3267 SX SSX    31 3274 SX SSX

所以，如果大家看到deadlock graph裡有出現等在SSX TM lock上時，最好先看看產生deadlock的兩條SQL的表裡有沒有foreign key的約束，如果有，確認是否在子表上有無index。
在確認了問題之後，然後帶著好奇，可以自己在QA環境做幾個實驗，模擬出完全一樣的deadlock graph。

於是，我根據trc檔案後面的資訊，發現產生deadlock的兩條SQL分別是兩條update 父表和子表的update語句。
透過N多實驗，終於模擬出透過兩條對父表和子表的update語句產生死鎖，併產生那種詭異的deadlock graph的方法了。

直接公佈答案：
create table b as select * from a;
alter table a add constraints apk primary key(object_id);
alter table b add constraint bfk foreign key (object_id) references a (object_id) on delete cascade;

首先b上在object_id列上沒有index。
--Session A:
delete from a where object_id=123;

--Session B:
delete from b where object_id=456;

--Session A:
update a set object_id=789 where object_id=789;(-- it will hung here)

--Session B:
update b set object_id=123 where object_id=123; (-- it will hung also, and dead lock happenes)

如上產生的trc檔案裡即有我遇見的case一模一樣的deadlock graph：
                     ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name       process session holds waits  process session holds waits
TX-000a0018-0000115e       29 3267    X          31 3274          X
TM-000047c5-00000000    31 3274 SX          29 3267 SX SSX
session 3267: DID 0001-001D-00000024 session 3274: DID 0001-001F-0000000B
session 3274: DID 0001-001F-0000000B session 3267: DID 0001-001D-00000024
Rows waited on:
Session 3274: obj - rowid = 000047C5 - AAAEfFAAFAAACDXABS
  (dictionary objn - 18373, file - 5, block - 8407, slot - 82)
Session 3267: no row
Information on the OTHER waiting sessions:
Session 3274:
  pid=31 serial=2735 audsid=20123 user: 36/HAOZHU_USER
  O/S info: user: oracle, term: pts/5, ospid: 9809, machine: xxx
         program: xxx[/email] (TNS V1-V3)
  application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  update b set object_id=123 where object_id=123
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update a set object_id=789 where object_id=789

如果我們加上index：
create index bidx on b(object_id);

--Session A:
delete from a where object_id=123;

--Session B:
delete from b where object_id=456;

--Session A:
update a set object_id=789 where object_id=789; (--it will NOT hung, and is successful.)

--Session B:
update b set object_id=123 where object_id=123; (-- it will hung also, but not dead lock)

  那麼不會出現deadlock，只會出現一般的lock。

  其實，對於這個case，除了foreign key沒有index這個問題之外，也不排除應用邏輯的問題。
也有可能在解決了SX/SSX mode TM lock的deadlock之後，應用也會產生X mode TX lock的deadlock也說不定。
畢竟，他也會產生上述比較危險的lock，如果Session A再在object_id=456的行上做dml，也會引發X mode TX deadlock。

綜上，deadlock是個挺有趣的東西。

[ 本帖最後由 viadeazhu 於 2010-4-2 21:25 編輯 ]

oracle deadlock with TM lock in SX/SSX mode

相關文章