起因：生產資料庫突然產生死鎖，查詢trace發現是insert語句造成的，經過向大神諮詢和論壇發帖詢問，獲得相關資訊。

參考： ... AD%BB%E9%94%81.html

原因：當ORACLE執行insert等DML語句時，會首先自動在所要操作的表上申請一個TM鎖，當TM鎖獲得後，再自動申請TX型別的鎖。當兩個或多個會話在表的同一條記錄上執行DML語言時，第一個會話在記錄上加鎖，其它的會話處於等待狀態，一直到第一個會話提交後TX鎖釋放，其它的會話才可以加鎖。考慮是因為兩個insert語句同時試圖向一個表中插入PK或unique值相同的資料，而造成其中會話被阻塞，等待其它會話提交或回滾，因而造成死鎖。這種情況，只要其中任何一個session提交，另外一個就會報出ORA-00001：違反唯一性約束條件，死鎖終止；或者其中一個session回滾，另外一個即可正常執行。(--引惜分飛部落格)

模擬：

session 1

insert into t2 values(1,'aaa');

session 2

insert into t2 values(2,'aaa');

session 1

insert into t2 values(2,'aaa');

session 2

insert into t2 values(1,'aaa');

錯誤浮現

SQL> insert into t2 values(2,'aaa');

insert into t2 values(2,'aaa')

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

trace檔案：

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-0001001d-00000310 34 42 X 32 49 S

TX-000a0002-0000030b 32 49 X 34 42 S

session 42: DID 0001-0022-00000008 session 49: DID 0001-0020-00000008

session 49: DID 0001-0020-00000008 session 42: DID 0001-0022-00000008

Rows waited on:

Session 42: no row

Session 49: no row

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

Session 49:

sid: 49 ser: 11 audsid: 240013 user: 80/SCOTT

flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x40009) -/-/INC

pid: 32 O/S info: user: oracle, term: UNKNOWN, ospid: 1798

image: oracle@sol11node151 (TNS V1-V3)

client details:

O/S info: user: oracle, term: pts/1, ospid: 1794

machine: sol11node151 program: sqlplus@sol11node151 (TNS V1-V3)

application name: SQL*Plus, hash value=3669949024

current SQL:

insert into t2 values(1,'aaa')

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

Information for THIS session:

----- Current SQL Statement for this session (sql_id=1qrs504d5jrg1) -----

insert into t2 values(2,'aaa')

insert引起的死鎖，起因主鍵衝突

相關文章