解決Oracle資料庫死鎖

　 　介紹

　　本文我們嘗試總結在多個使用者併發情況下，如何識別和解決刪除操作期間發生的死鎖問題，在開始之前，我們先簡單描述一下什麼是死鎖以及什麼東西會導致死鎖。

　 　死鎖

　　在任何資料庫中發生死鎖都是不愉快的，即使是在一個特殊的情況下發生也是如此，它們會減小應用程式的接受程度(ACCEPTANCE)，因此避免並正確解釋死鎖是非常重要的。

　　當兩個或更多使用者相互等待鎖定的資料時就會發生死鎖，發生死鎖時，這些使用者被卡住不能繼續處理業務，Oracle自動檢測死鎖並解決它們(透過回滾一個包含在死鎖中的語句實現)，釋放掉該語句鎖住的資料，回滾的會話將會遇到Oracle錯誤“ORA-00060：等待資源時檢測到死鎖”。

　　 是什麼導致了死鎖?

　　明確地鎖定表是為了保證讀/寫一致性，未建立索引的外來鍵約束，在相同順序下表不會鎖住，沒有為資料段分配足夠的 儲存引數(主要是指INITTRANS，MAXTRANS和PCTFREE引數)很容易引發突發鎖和死鎖，原因是多種多樣的，需要重新逐步審查。

　　 識別死鎖

　　當Oracle資料庫檢測到死鎖時(Oracle錯誤訊息：ORA-00060)，相應的訊息就寫入到警告日誌檔案中(alert.log)，另外還會在USER_DUMP_DEST目錄下建立一個跟蹤檔案，分析警告日誌檔案和跟蹤檔案是非常耗時的。

　　下面是一個警告日誌檔案示例：

　　Mon Aug 07 09:14:42 2007

　　ORA-000060: Deadlock detected. More info in file

　　e:\oracle\admin\GEDEON\udump\ORA01784.TRC.

　　下面是從跟蹤檔案中節選出來的片段，從其中我們可以看出是哪個語句創造了死鎖，相關的語句和被鎖定的資源已經標記為粗體。

　　/users/ora00/log/odn_ora_1097872.trc

　　Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

　　With the Partitioning, OLAP and Oracle Data Mining options

　　JServer Release 9.2.0.8.0 - Production

　　ORACLE_HOME = /soft/ora920

　　System name:AIX

　　Node name:beaid8

　　Release:2

　　Version:5

　　Machine:00C95B0E4C00

　　Instance name: ODN

　　Redo thread mounted by this instance: 1

　　Oracle process number: 17

　　Unix process pid: 1097872, image: oracle@beaid8 (TNS V1-V3)

　　*** 2007-06-04 14:41:04.080

　　*** SESSION ID:(10.6351) 2007-06-04 14:41:04.079

　　DEADLOCK DETECTED ( ORA-00060 )

　　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. The following

　　information may aid in determining the deadlock:

　　Deadlock graph:

　　---------Blocker(s)-------- ---------Waiter(s)---------

　　Resource Name process session holds waits process session holds waits

　　TM-00001720-00000000 17 10 SX 16 18 SX SSX

　　TM-0000173a-00000000 16 18 SX 17 10 SX SSX

　　session 10: DID 0001-0011-00000002session 18: DID 0001-0010-00000022

　　session 18: DID 0001-0010-00000022session 10: DID 0001-0011-00000002

　　Rows waited on:

　　Session 18: obj - rowid = 00001727 - AAABcnAAJAAAAAAAAA

　　(dictionary objn - 5927, file - 9, block - 0, slot - 0)

　　Session 10: obj - rowid = 00001727 - AAABcnAAJAAAAAAAAA

　　(dictionary objn - 5927, file - 9, block - 0, slot - 0)

　　Information on the OTHER waiting sessions:

　　Session 18:

　　pid=16 serial=2370 audsid=18387 user: 21/ODN

　　O/S info: user: mwpodn00, term: unknown, ospid: , machine: beaida

　　program: JDBC Thin Client

　　application name: JDBC Thin Client, hash value=0

　　Current SQL Statement:

　　DELETE FROM ODNQTEX WHERE EX_ID = :B1

　　End of information on OTHER waiting sessions.

　　Current SQL statement for this session:

　　DELETE FROM ODNQTFN WHERE FN_ID_EXIGENCE_EX = :B1

　　----- PL/SQL Call Stack -----

　　object line object

　　handle number name

　　7000000135f7fd8 34 procedure ODN.ODNQPDR

　　7000000135f89f0 16 procedure ODN.ODNQPZB

　　我們可以使用企業管理器來決定保留所有的鎖還是釋放掉它們，為了便於說明，我們開啟2個sqlplus例項會話(在此期間同時發生了死鎖)來一起調式，當每個語句執行完畢後，我們看到鎖仍然保留下來了，它可以幫助我們識別出是哪個資源引起的死鎖。

　　下面列出了可以幫助我們監視鎖的檢視：

　　可以透過查詢V$LOCK字典檢視來確定鎖，如：
        
         select * from v$lock ;

　　下面的SQL查詢可以用於確定鎖住資料庫物件的鎖：

　　select

　　c.owner,

　　c.object_name,

　　c.object_type,

　　b.sid,

　　b.serial#,

　　b.status,

　　b.osuser,

　　b.machine

　　from

　　v$locked_object a ,

　　v$session b,

　　dba_objects c

　　where

　　b.sid = a.session_id

　　and

　　a.object_id = c.object_id;

解決死鎖

　　安裝順序執行下面的修改，避免一致性訪問期間的死鎖問題：

　　設定事務一致性：我們需要確定一個隔離水平，在 儲存過程中可以使用“READ COMMITTED”或“SERIALIZABLE”，我們需要考慮兩件事情：

　　 設定事務可以在任何時間提交

　　 相關表中行的讀取順序

　　Oracle資料庫隔離模式透過行級鎖和Oracle資料庫多版本併發控制系統提供高階一致性和併發性(和高效能)，READ COMMITTED模式可以提供更多的併發性，因為沒有重複讀，SERIALIZABLE隔離水平提供了更好的一致性，透過保護非重複讀實現，在一個讀寫事務執行不止一次查詢時這很重要，然而，SERIALIZABLE模式需要應用程式檢查“不能連續訪問”的錯誤，這樣就可以在有許多訪問相同資料的一致性事務的環境中大大減少吞吐量。

　　在我們的例子中，我們需要使用READ COMMITTED隔離水平，原因如下：

　　我們需要獲取由另一個事務提交的查詢返回的行，不僅僅是獲取剛開始的事務返回的行。

　　如果我們將隔離水平設為SERIALIZABLE，將會獲得一個“事務無法按順序訪問”的錯誤，因為我們想要修改的資料已經被另一個事務修改了。

　　在儲存過程中我們放入下面的語句：

　　SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

　　明確鎖定資料：Oracle資料庫總是執行必要的鎖確保資料的併發性、完整性和語句級讀一致性，但在我們的例子中，我們需要獨佔訪問資源，為了處理它的語句，事務獨佔訪問資源，不要等待其他事務完成。

　　我將會用一個例子來解釋，在我們的案例中，需要刪除一個業務物件System，為了刪除這個物件，首先我們需要刪除System子表中的所有資料，現在進入其中一個子表，我們在子表上建立一個關於刪除的觸發器，這個觸發器更新主表System的資料並鎖住它，接下來進入刪除操作，當我們想刪除System表中的資料時，就會出現死鎖，因為它已經被前面子表上的觸發器給鎖住了，為了避免出現這種情況，在刪除操作開始之前，我們提供一個行獨佔鎖鎖住System表來解決這個問題。命令如下：

　　LOCK TABLE ODNQTSY IN ROW EXCLUSIVE MODE;

　　並行索引和查詢處理：Oracle資料庫使用索引增強SQL查詢的效能，這有助於我們執行DML操作，如插入、更新和刪除，做這些動作的時間將會極具減少了。

　　並行索引將會讓最佳化器思考執行並行查詢時使用索引，這是避免死鎖的一個方法，當索引重建後，Oracle將會允許多個DML操作發生在同一個索引上，在索引上啟用並行操作的語法如下：

　　ALTER INDEX PARALLEL;

　　使用並行查詢選項的最大好處是直接路徑讀取，因此需要的latch就更少了，同樣，並行執行大大減少了資料密集型業務的響應時間。

　　並行執行選項可以透過修改INIT.ORA檔案中的PARALLEL_AUTOMATIC_TUNING引數(設為TRUE)實現資料庫級別的啟用。

　　parallel_automatic_tuning=TRUE

　　外來鍵上的索引：外來鍵上如果沒有建立索引會引發兩個問題，第一個是如果你更新父記錄主鍵或刪除父記錄，子表的外來鍵沒有索引時，會引發表級鎖;第二個問題是效能。

　　如果你試圖刪除父錶行，或更新父/子關聯中父錶行的鍵值，而子表的外來鍵上沒有索引時，Oracle將會嘗試在子表上獲得一個共享行級獨佔鎖，接下來如果有其它會話要修改子表，它將不得不等待在它前面的SRX鎖(共享行級獨佔鎖)，這樣就形成了一個死鎖狀態。

　　下面的指令碼可以幫助我們識別沒有索引的外來鍵(FK)約束，從指令碼執行的輸出中，我們可以確定在外來鍵上建立索引將可以幫助我們改善效能，並且可以避免死鎖。

　　column columns format a20 word_wrapped

　　column table_name format a30 word_wrapped

　　select decode( b.table_name, NULL, '****', 'ok' ) Status,

　　a.table_name, a.columns, b.columns

　　from

　　( select substr(a.table_name,1,30) table_name,

　　substr(a.constraint_name,1,30) constraint_name,

　　max(decode(position, 1, substr(column_name,1,30),NULL)) ||

　　max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns

　　from user_cons_columns a, user_constraints b

　　where a.constraint_name = b.constraint_name

　　and b.constraint_type = 'R'

　　group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,

　　( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,

　　max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||

　　max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns

　　from user_ind_columns

　　group by substr(table_name,1,30), substr(index_name,1,30) ) b

　　where a.table_name = b.table_name (+)

　　and b.columns (+) like a.columns || '%'

　　外來鍵的級聯刪除：在我們的案例中，已經使用“級聯刪除選項(On Delete Cascade)”建立了一些外來鍵約束，這樣會引發死鎖問題，原因是在我們的刪除事務中，我們明確地刪除子表中的資料，然後再刪除主表中的資料，因此在子表上就已經存在一個鎖了，在刪除主表之前，我們想再刪除子表一次，因此導致的死鎖。

　　為了解決這個問題，我們移除了“級聯刪除選項(On Delete Cascade)”，這樣就修復了死鎖問題。