作者介紹
李華榮，網名小麥苗，長期專注oracle資料庫研究，具有交通、金融、政府、移動、醫療、電信等行業的運維經驗。興趣愛好廣泛，熱衷技術分享，對sql語句優化、備份恢複、資料庫巡檢、資料庫監控等有深入的鑽研，熟悉dg、rac，ogg及aix，hp-ux，linux等主流作業系統平台。個人公衆号：xiaomaimiaolhr。
前段時間工作上碰到了一個很奇怪的死鎖問題，一般來說，由業務發出來的sql是不太可能會産生死鎖的，不過确确實實産生了，而且還是itl死鎖！于是借此機會，也把死鎖可能出現的情況都分類總結了一下，分享給大家，歡迎探讨交流。
先看下大綱：
死鎖的概念及其trace檔案
死鎖的分類
行級死鎖的模拟
itl的概念、itl結構
itl引發的死鎖處理
itl死鎖的模拟
一、死鎖的概念及其trace檔案
1什麼是死鎖？
所謂死鎖，是指兩個或兩個以上的程序在執行過程中，因争奪資源而造成的一種互相等待的現象，若無外力作用，它們都将無法推進下去。此時稱系統處于死鎖狀态或系統産生了死鎖，這些永遠在互相等待的程序稱為死鎖程序。oracle對于“死鎖”是要做處理的，而不是不聞不問。
2死鎖的trace檔案
oracle中産生死鎖的時候會在alert告警日志檔案中記錄死鎖的相關資訊，無論單機還是rac環境都有deadlock這個關鍵詞，而且當發生死鎖時都會生成一個trace檔案，這個檔案名在alert檔案中都有記載。由于在rac環境中，是由lmd（lock manager daemon）程序統一管理各個節點之間的鎖資源的，是以，rac環境中trace檔案是由lmd程序來生成的。
在rac環境中，告警日志的形式如下所示：
在單機環境中，告警日志的形式如下所示：
通常來講，對于單機環境，當有死鎖發生後，在trace檔案中會看到如下的日志資訊：
圖 2-1 單機環境下的死鎖
當看到trace檔案時，需要确認一下産生鎖的類型，是兩行還是一行，是tx還是tm，如果隻有一行那麼說明是同一個session，可能是自治事務引起的死鎖。
對于rac環境，當有死鎖發生後，在trace檔案中會看到如下的日志資訊：
圖 2-2 rac環境下的死鎖
3死鎖的檢測時間
死鎖的監測時間是由隐含參數_lm_dd_interval來控制的，在oracle 11g中，隐含參數_lm_dd_interval的值預設為10，而在oracle 10g中該參數預設為60，機關為秒。
可以看到該隐含參數的值為10。
二、死鎖的分類
有人的地方就有江湖，有資源阻塞的地方就可能有死鎖。oralce中最常見的死鎖分為：行級死鎖（row-level deadlock）和塊級死鎖（block-level deadlock），其中，行級死鎖分為①主鍵、唯一索引的死鎖（會話交叉插入相同的主鍵值），②外鍵未加索引，③表上的位圖索引遭到并發更新，④常見事務引發的死鎖（例如，兩個表之間不同順序互相更新操作引起的死鎖；同一張表删除和更新之間引起的死鎖），⑤自治事務引發的死鎖。塊級死鎖主要指的是itl（interested transaction list）死鎖。
死鎖分類圖如下所示：
圖 2-3 死鎖的分類圖
1行級死鎖
行級鎖的死鎖一般是由于應用邏輯設計的問題造成的，其解決方法是通過分析trace檔案定位出造成死鎖的sql語句、被互相鎖住資源的對象及其記錄等資訊，提供給應用開發人員進行分析，并修改特定或一系清單的更新（update）順序。
以下模拟各種行級死鎖的産生過程，版本都是11.2.0.4。
主鍵、唯一索引的死鎖（會話交叉插入相同的主鍵值）
主鍵的死鎖其本質是唯一索引引起的死鎖，這個很容易模拟出來的，建立一張表，設定主鍵（或建立唯一索引）後插入一個值，然後不要commit，另一個會話插入另一個值，也不要commit，然後再把這兩個插入的值互相交換一下，在兩個會話中分别插入，死鎖就會産生。
會話2，sid為156：
156阻塞了191會話，即會話1阻塞了會話2。
會話1再次插入資料：
此時，去會話2看的時候，已經報出了死鎖的錯誤：
此時的阻塞已經發生了變化：
告警日志：
其内容可以看到很經典的一段：
這就是主鍵的死鎖，模拟完畢。
此時，若是會話2執行送出後，會話1就會報錯，違反唯一限制：
外鍵的死鎖（外鍵未加索引）
外鍵未加索引很容易導緻死鎖。在以下兩種情況下，oracle在修改父表後會對子表加一個全表鎖：
如果更新了父表的主鍵，由于外鍵上沒有索引，是以子表會被鎖住。
如果删除了父表中的一行，由于外鍵上沒有索引，整個子表也會被鎖住。
總之，就是更新或者删除父表的主鍵，都會導緻對其子表加一個全表鎖。
如果父表存在删除記錄或者更改外鍵列的情形，那麼就需要在子表上為外鍵列建立索引。
外鍵的死鎖可以這樣通俗的了解：有兩個表a和b：a是父表，b是子表。如果沒有在b表中的外鍵加上索引，那麼a表在更新或者删除主鍵時，都會在表b上加一個全表鎖。這是為什麼呢？因為我們沒有給外鍵加索引，在更新或者删除a表主鍵的時候，需要檢視子表b中是否有對應的記錄，以判斷是否可以更新删除。那如何查找呢？當然隻能在子表b中一條一條找了，因為我們沒有加索引嗎。既然要在子表b中一條一條地找，那就得把整個子表b都鎖定了。由此就會導緻以上一系列問題。
實驗過程：
會話1：首先建立子表和父表
會話1執行一個删除操作，這時候在子表和父表上都加了一個row-x(sx)鎖。
查詢會話1的鎖資訊：
block為0表示沒有阻塞其它的鎖。
會話2：執行另一個删除操作，發現這時候第二個删除語句等待
block為1表示阻塞了其它的鎖。
會話1執行删除語句，死鎖發生。
檢視内容：
復原會話建立外鍵列上的索引：
重複上面的步驟會話1删除子表記錄：
---會話1：
---會話2：
所有的删除操作都可以成功執行，也沒有阻塞的生成，重點就是在外鍵列上建立索引。
位圖(bitmap)索引死鎖
表上的位圖索引遭到并發更新也很容易産生死鎖。在有位圖索引存在的表上面，其實很容易就引發阻塞與死鎖。這個阻塞不是發生在表上面，而是發生在索引上。因為位圖索引鎖定的範圍遠遠比普通的b-tree索引鎖定的範圍大。
那麼在id列上建bitmap index的話，所有id＝1的會放到一個位圖中，所有id=2的是另外一個位圖，而在執行dml操作的時候，鎖定的将是整個位圖中的所有行，而不僅僅是dml涉及到的行。由于鎖定的粒度變粗，bitmap index更容易導緻死鎖的發生。
會話1：此時所有id=1的行都被鎖定
會話2：此時所有id=2的行都被鎖定
會話1：此時會話被阻塞
會話2：會話被阻塞
再回到session 1，發現系統檢測到了死鎖的發生
内容：
死鎖發生的根本原因是對于資源的排他鎖定順序不一緻。上面的試驗中，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相同的行。
常見事務引發的死鎖
如果你有兩個會話，每個會話都持有另一個會話想要的資源，此時就會出現死鎖（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、建立兩個簡單的表a和b，每個表中僅僅包含一個字段id。
2、每張表中僅初始化一條資料
3、在第一個會話session1中更新表a中的記錄“1”為“10000”，不送出；在第二個會話session2中更新表b中的記錄“2”為“20000”，不送出
session1的情況：
session2的情況：
此時，沒有任何問題發生。ok，現在注意一下下面的現象，我們再回到會話session1中，更新表b的記錄，此時出現了會話阻塞，更新hang住不能繼續。
這裡出現了“鎖等待”（“阻塞”）的現象，原因很簡單，因為在session2中已經對這條資料執行過update操作沒有送出表示已經對該行加了行級鎖。
我們可以通過v$session視圖看到，執行個體2的195阻塞了執行個體2的133會話，即本實驗中的session2阻塞了session1。
6、接下來再執行一條sql後死鎖就會産生了：在session2中，更新表a的記錄
sys@raclhr2> update a set id = 10000 where id = 1;
這裡還是長時間的等待，但是這裡發生了死鎖，這個時候我們去第一個會話session1中看一下，原先一直在等待的sql語句報了如下的錯誤：
若此時查詢v$session視圖可以看到執行個體2的133阻塞了執行個體2的195會話，即本實驗中的session1阻塞了session2，和剛剛的阻塞情況相反，說明oracle做了自動處理：
更進一步：檢視一下alert警告日志檔案發現有如下的記錄：
若是單機環境，報警日志為：
可以看到該檔案是由lmd程序生成的，為rac的特有程序，完成cachefusion的作用，再進一步：看看系統自動生成的trace檔案中記錄了什麼：
若是單機環境比較明顯：
注意trace檔案中的一行如下提示資訊，說明一般情況下都是應用和人為的，和oracle同學沒有關系：
the following deadlock is not an oracle error. it is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc sql.
模拟二：同一張表删除和更新之間引起的死鎖
造成死鎖的原因就是多個線程或程序對同一個資源的争搶或互相依賴。這裡列舉一個對同一個資源的争搶造成死鎖的執行個體。
會話1更新第一條記錄：
會話2删除第二條記錄：
接下來會話1更新第二條記錄，這是就産生了阻塞：
會話2删除第一條記錄：
檢視會話1：
自治事務引發的死鎖
一般來說構成死鎖至少需要兩個會話，而自治事務是一個會話可能引發死鎖。
自治事務死鎖情景:存儲過程insert表a,然後insert表b;其中insert表a觸發trigger t，t也insert表b,t是自治事務(at),at試圖擷取對b的鎖,結果b已經被主事務所hold，這裡會報出來ora-00060 – 等待資源時檢查到死鎖。
解決方法：去掉了t中的pragma autonomous_transaction聲明,保持和存儲過程事務一緻。
模拟一：更新
在主事務中如果更新了部分記錄，這時若自治事務更新同樣的記錄，就會造成死鎖，下面通過一個簡單的例子模拟了這個錯誤的産生：
在使用自治事務的時候要避免目前事務鎖定的記錄和自治事務中鎖定的記錄互相沖突。
模拟二：插入
主事務和自治事務插入的是同一個主鍵值也會引起死鎖。
可以看到，等待的和持有鎖的是同一個會話，根據trace資訊記錄的對象，發現問題是自治事務導緻的。
2塊級死鎖
塊級死鎖其實指的就是itl死鎖。
itl簡介
itl（interested transaction list）是oracle資料塊内部的一個組成部分，用來記錄該塊所有發生的事務，有的時候也叫itl槽位。如果一個事務一直沒有送出，那麼，這個事務将一直占用一個itl槽位，itl裡面記錄了事務資訊、復原段的入口和事務類型等等。如果這個事務已經送出，那麼，itl槽位中還儲存有這個事務送出時候的scn号。itl的個數受表的存儲參數initrans控制，在一個塊内部，預設配置設定了2個itl的個數，如果這個塊内還有空閑空間，那麼oracle是可以利用這些空閑空間再配置設定itl。如果沒有了空閑空間，那麼，這個塊因為不能配置設定新的itl，是以，就可能發生itl等待。如果在并發量特别大的系統中，那麼最好配置設定足夠的itl個數，或者設定足夠的pctfree，保證itl能擴充，但是pctfree有可能是被行資料給消耗掉的，例如update，是以，也有可能導緻塊内部的空間不夠而導緻itl等待，出現了itl等待就可能導緻itl死鎖。
itl結構
如果dump一個塊（指令：alter system dump datafile x block xxx;），那麼在dump檔案中就可以看到itl資訊：
1）itl： itl事務槽編号，itl事務槽号的流水編号
2）xid：代表對應的事務id（transac[x]tion identified），在復原段事務表中有一條記錄和這個事務對應。xid由三列使用十六進制編碼的數字清單示，分别是：undo segment number +transaction table slot number+ wrap,即由undo段号+undo槽号+undo槽号的覆寫次數三部分組成，即usn.slot.sqn，這裡0x0008.002.000009e9轉換為10進制為8.2.2537，從下邊的查詢出的結果是相對應的。
3）uba：(undo block address)，該事務對應的復原段位址，記錄了最近一次的該記錄的前鏡像（修改前的值）。uba組成：undo塊位址(undo檔案号和資料塊号)+復原序列号+復原記錄号。多版本一緻讀是oracle保證讀操作不會被事務阻塞的重要特性。當server process需要查詢一個正在被事務修改，但是尚未送出的資料時，就根據itl上的uba定位到對應undo前鏡像資料位置。這裡的uba為：0x00c0108b.04ac.24，其中00c0108b（16進制）=0000 0000 1100 0000 0001 0000 1000 1011（2進制，共32位，前10位代表檔案号，後22位代表資料塊号）=檔案号為3，塊号為4235：（10進制）；04ac（16進制）=1196（10進制）；24（16進制）=36（10進制）。這個結果和v$transaction查詢出來的結果一緻。
select ubafil 復原段檔案号,ubablk 資料塊号,ubasqn 復原序列号,ubarec 復原記錄号 from v$transaction ; --檢視uba
4）flag：事務标志位，即目前事務槽的狀态資訊。這個标志位就記錄了這個事務的操作狀态，各個标志的含義分别是：
5）lck：表示這個事務所影響的行數，鎖住了幾行資料，對應有幾個行鎖。我們看到01号事物槽lck為3，因為該事物槽中的事物flag為u，證明該事物已經送出，但是鎖還沒有清除。再比如對于下邊這個itl：
我們看到01号事物槽lck為0，因為該事物槽中的事物flag為c，證明該事物已經送出，鎖也被清楚掉了，該事物槽可以被重用了。02号事物槽lck為1，是因為我對第一行做了一個更新，并且沒有送出，flag為“----”說明該事物是活動的。
6）scn/fsc：commit scn或者快速送出（fast commit fsc）的scn。 scn=scn of commited tx; fsc=free space credit(bytes)每條記錄中的行級鎖對應itl條目lb，對應于itl清單中的序号，即那個事務在該記錄上産生的鎖。一個事物隻有在送出之後才會在itl事物槽中記錄scn。
itl個數
itl的個數，受參數initrans控制，最大itl個數受maxtrans控制（11g已廢棄maxtrans），在一個塊内部，預設配置設定了2個itl的個數。itl是block級的概念，一個itl占用塊46b的空間，參數initrans意味着塊中除去block header外一部分存儲空間無法被記錄使用（46b*initrans），當塊中還有一定的free space時，oracle可以使用free space建構itl供事務使用，如果這個塊内還有空閑空間，那麼oracle是可以利用這些空閑空間并再配置設定itl。如果沒有了空閑空間（free space），那麼，這個塊因為不能配置設定新的itl，是以就可能發生itl等待，即enq: tx - allocate itl entry等待事件。注意：10g以後maxtrans參數被廢棄，預設最大支援255個并發。
如果在并發量特别大的系統中，最好配置設定足夠的itl個數，其實它并浪費不了太多的空間，或者，設定足夠的pctfree，保證itl能擴充，但是pctfree有可能是被行資料給消耗掉的，如update，是以，也有可能導緻塊内部的空間不夠而導緻itl等待。
對于表（資料塊）來說，initrans這個參數的預設值是1。對于索引（索引塊）來說，這個參數預設值是2。
itl等待表現出的等待事件為“tx - allocate itl entry”，根據mos（troubleshooting waits for 'enq: tx - allocate itl entry' (doc id 1472175.1)提供的解決辦法，需要修改一些參數，sql如下，這裡假設使用者名為tlhr，表名為tlhrbokbal，表上的索引名為pk_tlhrbokbal：
itl引起的死鎖案例處理
我們首先建立一張表t_itl_lhr，這裡指定pctfree為0，initrans為1，就是為了觀察到itl的真實等待情況，然後我們給這些塊内插入資料，把塊填滿，讓它不能有空間配置設定。
我們檢查資料填充的情況：
可以發現，這2000條資料分布在3個塊内部，其中有2個塊(94953和94954)填滿了，一個塊（94955）是半滿的。因為有2個itl槽位，我們需要拿2個滿的資料塊，4個程序來模拟itl死鎖：
會話1：
會話2：
會話3：
會話4：
這個時候系統不存在阻塞，
以上4個程序把2個不同塊的4個itl槽位給消耗光了，現在的情況，就是讓他們互相鎖住，達成死鎖條件，回到會話1，更新塊94954，注意，以上4個操作，包括以下的操作，更新的根本不是同一行資料，主要是為了防止出現的是行鎖等待。
會話1出現了等待。
會話3發現出現了等待。
我們查詢阻塞的具體情況：
可以看到，會話1被會話4阻塞了，會話3被會話2阻塞了。
注意，如果是9i，在這裡就報死鎖了，但是在10g裡面，這個時候，死鎖是不會發生的，因為這裡的會話1還可以等待會話4釋放資源，會話3還可以等待會話2釋放資源，隻要會話2與會話4釋放了資源，整個環境又活了，那麼我們需要把這兩個程序也塞住。
出現的是行鎖等待。
會話2，注意，我們也不是更新的同一行資料：
會話2出現了等待，具體阻塞情況：
我做了幾次實驗，會話2執行完sql後，會話3到這裡就報出了死鎖，但有的時候并沒有産生死鎖，應該跟系統的阻塞順序有關，若沒有産生死鎖，我們可以繼續會話4的操作。
會話4，注意，我們也不是更新的同一行資料：
會話4發現出現了等待。
雖然，以上的每個更新語句，更新的都不是同一個資料行，但是，的确，所有的程序都被阻塞住了，那麼，死鎖的條件也達到了，等待一會（這個時間有個隐含參數來控制的：_lm_dd_interval），我們可以看到，會話2出現提示，死鎖：
報出死鎖之後的阻塞情況：
我們可以在會話2上繼續執行步驟三中的sql，依然會産生死鎖。生成死鎖後，在告警日志中有下邊的語句：
其中的内容有非常經典的一段global wait-for-graph(wfg)：
該實驗過程可能有點複雜，我畫個圖來說明整個實驗過程：
<b></b>
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-10-27</b>