http://book.csdn.net/bookfiles/82/index.html6.2.6 死結如果你有兩個會話，每個會話都持有另一個會話想要的資源，此時就會出現死結（deadlock）。例如，如果我的資料庫中有兩個表A和B，每個表中都只有一行，就可以很容易地展示什麼是死結。我要做的只是開啟兩個會話（例如，兩個SQL*Plus會話）。在會話A中更新表A，並在會話B中更新表B。現在，如果我想在會話B中更新表A，就會阻塞。會話A已經鎖定了這一行。這不是死結；只是阻塞而已。我還沒有遇到過死結，因為會話A還有機會提交或復原，這樣會話B就能繼續。如果我再回到會話A，試圖更新表B，這就會導致一個死結。要在這兩個會話中選擇一個作為“犧牲品”，讓它的語句復原。例如，會話B中對錶A的更新可能復原，得到以下錯誤：想要更新表B的會話A還阻塞著，Oracle不會復原整個事務。只會復原與死結有關的某條語句。會話B仍然鎖定著表B中的行，而會話A還在耐心地等待這一行可用。收到死結訊息後，會話B必須決定將表B上未執行的工作提交還是復原，或者繼續走另一條路，以後再提交。一旦這個會話執行提交或復原，另一個阻塞的會話就會繼續，好像什麼也沒有發生過一樣。Oracle認為死結很少見，而且由於如此少見，所以每次出現死結時它都會在伺服器上建立一個追蹤檔案。這個追蹤檔案的內容如下：顯然，Oracle認為這些應用死結是應用自己導致的錯誤，而且在大多數情況下，Oracle的這種看法都是正確的。不同於許多其他的RDBMS，Oracle中極少出現死結，甚至可以認為幾乎不存在。通常情況下，必須人為地提供條件才會產生死結。根據我的經驗，導致死結的頭號原因是外鍵未加索引（第二號原因是表上的位元影像索引遭到並發更新，這個內容將在第11章討論）。在以下兩種情況下，Oracle在修改父表後會對子表加一個全表鎖：q 如果更新了父表的主鍵（倘若遵循關聯式資料庫的原則，即主鍵應當是不可變的，這種情況就很少見），由於外鍵上沒有索引，所以子表會被鎖住。q 如果刪除了父表中的一行，整個子表也會被鎖住（由於外鍵上沒有索引）。在Oracle9
i及以上版本中，這些全表鎖都是短期的，這意味著它們僅在DML操作期間存在，而不是在整個事務期間都存在。即便如此，這些全表鎖還是可能（而且確實會）導致很嚴重的鎖定問題。下面說明第二點[2]，如果用以下命令建立了兩個表：然後執行以下語句：到目前為止，還沒有什麼問題。但是如果再到另一個會話中，試圖刪除第一條父記錄：此時就會發現，這個會話立即被阻塞了。它在執行刪除之前試圖對錶C加一個全表鎖。現在，別的會話都不能對C中的任何行執行DELETE、INSERT或UPDATE（已經開始的會話可以繼續[3]，但是新會話將無法修改C）。更新主索引值也會發生這種阻塞。因為在關聯式資料庫中，更新主鍵是一個很大的禁忌，所以更新在這方面一般沒有什麼問題。在我看來，如果開發人員使用能產生SQL的工具，而且這些工具會更新每一列，而不論終端使用者是否確實修改了那些列，此時更新主鍵就會成為一個嚴重的問題。例如，假設我們使用了Oracle Forms，並為表建立了一個預設布局。預設情況下，Oracle Forms會產生一個更新，對我們選擇要顯示的表中的每一列進行修改。如果在DEPT表中建立一個預設布局，包括3個欄位，只要我們修改了DEPT表中的任何列，Oracle Forms都會執行以下命令：在這種情況下，如果EMP表有DEPT的一個外鍵，而且在EMP表的DEPTNO列上沒有任何索引，那麼更新DEPT時整個EMP表都會被鎖定。如果你使用了能產生SQL的工具，就一定要當心這一點。即便主索引值沒有改變，執行前面的SQL語句後，子表EMP也會被鎖定。如果使用Oracle Forms，解決方案是把這個表的UPDATE CHANGED COLUMNS ONLY屬性設定為YES。這樣一來，Oracle Forms會產生一條UPDATE語句，其中只包含修改過的列（而不包括主鍵）。刪除父表中的一行可能導致子表被鎖住，由此產生的問題更多。我已經說過，如果刪除表P中的一行，那麼在DML操作期間，子表C就會鎖定，這樣能避免事務期間對C執行其他更新（當然，這有一個前提，即沒有人在修改C；如果確實已經有人在修改C，刪除會等待）。此時就會出現阻塞和死結問題。通過鎖定整個表C，資料庫的並發性就會大幅下降，以至於沒有人能夠修改C中的任何內容。另外，出現死結的可能性則增加了，因為我的會話現在“擁有”大量資料，直到提交時才會交出。其他會話因為C而阻塞的可能性也更大；只要會話試圖修改C就會被阻塞。因此，我開始注意到，資料庫中大量會話被阻塞，這些會話持有另外一些資源的鎖。實際上，如果其中任何阻塞的會話鎖住了我的會話需要的資源，就會出現一個死結。在這種情況下，造成死結的原因是：我的會話不允許別人訪問超出其所需的更多資源（在這裡就是一個表中的所有行）。如果有人抱怨說資料庫中存在死結，我會讓他們運行一個指令碼，查看是不是存在未加索引的外鍵，而且在99%的情況下都會發現表中確實存在這個問題。只需對外鍵加索引，死結（以及大量其他的競爭問題）都會煙消雲散。下面的例子展示了如何使用這個指令碼來找出表C中未加索引的外鍵：這個指令碼將處理外鍵約束，其中最多可以有8列（如果你的外鍵有更多的列，可能就得重新考慮一下你的設計了）。首先，它在前面的查詢中建立一個名為CONS的內聯視圖（inline view）。這個內聯視圖將約束中適當的列名從行轉置到列，其結果是每個約束有一行，最多有8列，這些列分別取值為約束中的列名。另外，這個視圖中還有一個列COL_CNT，其中包含外鍵約束本身的列數。對於這個內聯視圖中返回的每一行，我們要執行一個關聯子查詢（correlated subquery），檢查當前所處理表上的所有索引。它會統計出索引中與外鍵約束中的列相匹配的列數，然後按索引名分組。這樣，就能產生一組數，每個數都是該表某個索引中匹配列的總計。如果原來的COL_CNT大於所有這些數，那麼表中就沒有支援這個約束的索引。如果COL_CNT小於所有這些數，就至少有一個索引支援這個約束。注意，這裡使用了NVL2函數，我們用這個函數把列名列表“粘到”一個用逗號分隔的列表中。這個函數有3個參數：A、B和C。如果參數A非空，則返回B；否則返回參數C。這個查詢有一個前提，假設約束的所有者也是表和索引的所有者。如果另一位使用者對錶加索引，或者表在另一個模式中（這兩種情況都很少見），就不能正確地工作。所以，這個指令碼展示出，表C在列X上有一個外鍵，但是沒有索引。通過對X加索引，就可以完全消除這個鎖定問題。除了全表鎖外，在以下情況下，未加索引的外鍵也可能帶來問題：q 如果有ON DELETE CASCADE，而且沒有對子表加索引：例如，EMP是DEPT的子表，DELETE DEPTNO = 10應該CASCADE（級聯）至EMP[4]。如果EMP中的DEPTNO沒有索引，那麼刪除DEPT表中的每一行時都會對EMP做一個全表掃描。這個全表掃描可能是不必要的，而且如果從父表刪除多行，父表中每刪除一行就要掃描一次子表。q 從父表查詢子表：再次考慮EMP/DEPT例子。利用DEPTNO查詢EMP表是相當常見的。如果頻繁地運行以下查詢（例如，產生一個報告），你會發現沒有索引會使查詢速度變慢：n select * from dept, empn where emp.deptno = dept.deptno and dept.deptno = :X;那麼，什麼時候不需要對外鍵加索引呢？答案是，一般來說，當滿足以下條件時不需要加索引：q 沒有從父表刪除行。q 沒有更新父表的惟一鍵/主索引值（當心工具有時會無意地更新主鍵！）。q 沒有從父表連接子表（如DEPT連接到EMP）。如果滿足上述全部3個條件，那你完全可以跳過索引，不需要對外鍵加索引。如果滿足以上的某個條件，就要當心加索引的後果。這是一種少有的情況，即Oracle“過分地鎖定了”資料。
