Oracle LOCK內部機制及最佳實踐系列(五)給出一個導致死鎖的SQL示例

給出一個導致死鎖的SQL示例。

死鎖定義：從廣義上講包括作業系統 應用程式 資料庫，如果2個程式（會話）相互持有對方的資源，都一直等待對方釋放，這種情況會造成死鎖。
誤解：會話的阻塞可不是死鎖，因為其中有一個會話還是可以繼續操作的。
釋放：Oracle會自動檢測死鎖並強制干預釋放

> create table p1 ( x int primary key );       我們新建一個p1表，設定x欄位為主鍵
Table created.
> insert into leo1.p1 values(10);                 138會話插入的是10
1 row created.
> insert into leo1.p1 values(20);                 156會話插入的是20
1 row created.
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK
---------- ---------- ---------- ---------- ---------- ---------- ----------
       138 TM              73470          0          3          0          0
       138 TX             327713    1124          6          0          0             138有一個TX排他鎖，但當前沒有阻塞會話
       156 TM              73470          0          3          0          0
       156 TX             589825      945          6          0          0             156也有一個TX排他鎖，但當前也沒有阻塞會話
> select object_name from dba_objects where object_id=73470;            看p1表上存在正常的TM  TX鎖，都沒有阻塞到對方的會話
OBJECT_NAME
--------------------------------------------------------------------------------
P1
> insert into leo1.p1 values(20);              此時我在138會話上再插入20，發現hang住了不能前進，這是什麼原因呢？我們看看v$lock檢視
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      73470          0          3          0          0
       138 TX     589825      945          0          4          0     這時138會話就繼續插入了，但這個插入動作是成功的沒有阻塞，而是由於138|156會話修改值的相同
       138 TX     327713    1124          6          0          0     違反了主鍵約束從而產生阻塞，實際是對修改值的相同產生了阻塞，所以申請的是4級鎖，而非6級鎖
       156 TM      73470          0          3          0          0
       156 TX     589825      945          6          0          1     156會話此時正在阻塞138會話，因為156會話的事務還沒有完成還是一個未決狀態

> insert into leo1.p1 values(10);               我在156會話上也插入10，這時死鎖的效果就出來了
> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      73470          0          3          0          0
       138 TX     327713    1124          6          0          1     138會話此時正在阻塞156會話，因為138會話的事務還沒有完成還是一個未決狀態
       156 TM      73470          0          3          0          0
       156 TX     327713    1124          0          4          0     實際上是對修改值的相同產生了阻塞，156會話正在申請4級鎖
       156 TX     589825      945          6          0          0
> insert into leo1.p1 values(20);               我們看一下138會話報錯，Oracle自動檢測死鎖並強制干預釋放
insert into leo1.p1 values(20)
                 *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource（等待資源時檢測到死鎖->釋放之）  這時請注意一下，只是釋放掉了第一個鎖定，但第二個鎖定還在等待，所以我們要手工釋放

小結：上面講到了好幾種鎖的機制，我們崇尚的思想就是先要想一想為什麼會出現鎖，不出現行不行，鎖的作用有哪些，這種啟發式的思路能夠讓我們記憶深刻。
LOCK作用：獨佔業務資源  保證讀一致性  維護事務完整性
LOCK宗旨：沒有併發就沒有鎖，一個人運算元據庫是不會產生鎖的



2012.11.28
天津&winter
分享技術~成就夢想
Blog：