Oracle LOCK內部機制及最佳實務系列(五)給出一個導致死結的SQL樣本

給出一個導致死結的SQL樣本

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

LEO1@LEO1> create table p1 ( x int primary key );       我們建立一個p1表，設定x欄位為主鍵
Table created.
LEO1@LEO1> insert into leo1.p1 values(10);                 138會話插入的是10
1 row created.
LEO2@LEO1> insert into leo1.p1 values(20);                 156會話插入的是20
1 row created.
LEO1@LEO1> 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獨佔鎖定，但當前也沒有阻塞會話
LEO1@LEO1> select object_name from dba_objects where object_id=73470;            看p1表上存在正常的TM  TX鎖，都沒有阻塞到對方的會話
OBJECT_NAME
--------------------------------------------------------------------------------
P1
LEO1@LEO1> insert into leo1.p1 values(20);              此時我在138會話上再插入20，發現hang住了不能前進，這是什麼原因呢？我們看看v$lock視圖
LEO1@LEO1> 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會話的事務還沒有完成還是一個未決狀態

LEO2@LEO1> insert into leo1.p1 values(10);               我在156會話上也插入10，這時死結的效果就出來了
LEO1@LEO1> 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
LEO1@LEO1> 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宗旨：沒有並發就沒有鎖，一個人操作資料庫是不會產生鎖的


Leonarding
2012.11.28
天津&winter
分享技術~成就夢想
Blog：www.leonarding.com
 

本文出自 “leonarding Blog” 部落格，請務必保留此出處http://leonarding.blog.51cto.com/6045525/1073554