oracle鎖問題模拟實驗

1.行級死鎖。

1.1主鍵、唯一索引的死鎖（會話交叉插入相同的主鍵值）

a.建立一張表，設定主鍵（或建立唯一索引）後插入一個值，然後不要COMMIT，另一個會話插入另一個值，也不要COMMIT，然後再把這兩個插入的值互相交換一下，在兩個會話中分别插入，死鎖就會産生。

因為過程簡單，直接上圖了，我以scott使用者開了會話。1會話建表t_deadlock,插入第一條資料不送出，此時在2會話中插入第二條，不送出。再繼續插入1會話中對應值。模拟産生的阻塞。

b.PLSQL下查詢：

SELECT NVL(A.SQL_ID, A.PREV_SQL_ID) SQL_ID,

       A.BLOCKING_SESSION,

       A.SID,

       A.SERIAL#,

       A.LOGON_TIME,

       A.EVENT

  FROM GV$SESSION A

 WHERE A.SID IN (20,74)

 ORDER BY A.LOGON_TIME;
           

可以看到2的會話BLOCKING（阻塞）了sid20的會話。

c.此時在1會話中繼續插入2會話第一次插入的值，模拟死鎖。

如圖：2會話報ORA-00060 deadlock。

d.繼續查阻塞情況。

e.查alert日志

f.查trc檔案

Oracle對于死鎖有自己的内部處理機制會。當看到trace檔案時，需要确認一下産生鎖的類型，是兩行還是一行，是TX還是TM，如果隻有一行那麼說明是同一個SESSION，可能是自治事務引起的死鎖。

g.繼續送出2會話

會話2執行送出後，會話1就會報錯，違反唯一限制。這裡展現了ORACLE解除死鎖。9I以後Oracle檢查到60錯誤，會自動解鎖，并記錄trace。

附圖

1.2 外鍵的死鎖（外鍵未加索引）

外鍵未加索引很容易導緻死鎖。在以下兩種情況下，Oracle在修改父表後會對子表加一個全表鎖：

1. 如果更新了父表的主鍵，由于外鍵上沒有索引，是以子表會被鎖住。

2. 如果删除了父表中的一行，由于外鍵上沒有索引，整個子表也會被鎖住。

   總之，就是更新或者删除父表的主鍵，都會導緻對其子表加一個全表鎖。

   外鍵的死鎖可以這樣通俗的了解：有兩個表A和B：A是父表，B是子表。如果沒有在B表中的外鍵加上索引，那麼A表在更新或者删除主鍵時，都會在表B上加一個全表鎖。這是為什麼呢？因為我們沒有給外鍵加索引，在更新或者删除A表主鍵的時候，需要檢視子表B中是否有對應的記錄，以判斷是否可以更新删除。那如何查找呢？當然隻能在子表B中一條一條找了，因為我們沒有加索引嗎。既然要在子表B中一條一條地找，那就得把整個子表B都鎖定了。由此就會導緻以上一系列問題。

   模拟：

   建t_deadlock_p 主表

   

   插入對應值

   

   建立子表t_d_f 并插入值。

   

   1會話執行相關删除操作，給子表和父表第一行加行級鎖。

   

   檢視鎖情況，此時沒有鎖。

   

   會話2執行另一個删除操作，子表、父表加行級鎖。

   

   由于第一次删除主表語句時候，觸發了對子表的全表掃描，對子表加了表級鎖，故第二個主表删除語句出現等待。

   

   BLOCK為1表示阻塞了其它的鎖。

   此時在會話1下删除id為2的記錄，2會話報死鎖了。

   

   檢視阻塞情況

   

   trc檔案對鎖的類型都交代清楚了。

   

   再次模拟，先給外鍵加索引。

   

   模拟之前的删除操作，順利完成。

   

   未發現阻塞會話。

   

   1.3位圖索引死鎖

   表上的位圖索引遭到并發更新也很容易産生死鎖。在有位圖索引存在的表上面，其實很容易就引發阻塞與死鎖。這個阻塞不是發生在表上面，而是發生在索引上。因為位圖索引鎖定的範圍遠遠比普通的b-tree索引鎖定的範圍大。

   模拟，先建表并插入資料。

   

   在ID列上建bitmap index的話，所有ID＝1的會放到一個位圖中，所有ID=2的是另外一個位圖，而在執行DML操作的時候，鎖定的将是整個位圖中的所有行，而不僅僅是DML涉及到的行。由于鎖定的粒度變粗，bitmap index更容易導緻死鎖的發生。

   建立位圖索引語句。

   

   做更新操作，會話1：所有ID=1的行都被鎖定,會話2：所有ID=2的行都被鎖定

   

   會話1：此時會話被阻塞

   

   檢視阻塞

   

   會話2：會話被阻塞

   

   此時的會話層隻檢測到鎖等待

   

   但alert 檢測到deadlock，這個阻塞不是發生在表上面，而是發生在索引上。

   

   死鎖發生的根本原因是對于資源的排他鎖定順序不一緻。上面的試驗中，session1對于bitmap index中的2個位圖是先鎖定ID=1的位圖，然後請求ID=2的位圖，而在此之前ID=2的位圖已經被session2鎖定。session2則先鎖定ID=2的位圖，然後請求ID=2的位圖，而此前ID=1的位圖已經被session1鎖定。于是，session1等待session2釋放ID=2的位圖上的鎖，session2等待session1釋放ID=1的位圖上的鎖，死鎖就發生了

而如果我們建立的是普通的B*Tree index，重複上面的試驗則不會出現任何的阻塞和死鎖，這是因為鎖定的隻是DML操作涉及到的行，而不是所有ID相同的行。

1.4常見事務引發的死鎖

如果你有兩個會話，每個會話都持有另一個會話想要的資源，此時就會出現死鎖（deadlock）。例如，如果我的資料庫中有兩個表A和B，每個表中都隻有一行，就可以很容易地展示什麼是死鎖。我要做的隻是打開兩個會話（例如，兩個SQL*Plus會話）。在會話A中更新表A，并在會話B中更新表B。現在，如果我想在會話B中更新表A，就會阻塞。會話A已經鎖定了這一行。這不是死鎖；隻是阻塞而已。因為會話A還有機會送出或復原，這樣會話B就能繼續。如果我再回到會話A，試圖更新表B，這就會導緻一個死鎖。要在這兩個會話中選擇一個作為“犧牲品”，讓它的語句復原。

想要更新表B的會話A還阻塞着，Oracle不會復原整個事務。隻會復原與死鎖有關的某條語句。會話B仍然鎖定着表B中的行，而會話A還在耐心地等待這一行可用。收到死鎖消息後，會話B必須決定将表B上未執行的工作送出還是復原，或者繼續走另一條路，以後再送出。一旦這個會話執行送出或復原，另一個阻塞的會話就會繼續，好像什麼也沒有發生過一樣。

模拟一.兩個表之間不同順序互相更新操作引起的死鎖

略

模拟二：同一張表删除和更新之間引起的死鎖

略

1.5自治事務引發的死鎖

一般來說構成死鎖至少需要兩個會話，而自治事務是一個會話可能引發死鎖。

自治事務死鎖情景:存儲過程INSERT表A,然後INSERT表B;其中INSERT表A觸發TRIGGER T,T也INSERT表B,T是自治事務(AT),AT試圖擷取對B的鎖,結果B已經被主事務所HOLD,這裡會報出來ORA-00060 – 等待資源時檢查到死鎖.

解決方法:去掉了T中的PRAGMA AUTONOMOUS_TRANSACTION聲明,保持和存儲過程事務一緻.