6.7 監(jiān)控和解決鎖定爭用

當某個會話請求一行或一個對象上的鎖定，但是由于其他會話已經(jīng)獲取了該行或?qū)ο笊系呐潘i而無法獲得鎖定時，這個會話將被掛起。這種現(xiàn)象稱為鎖定爭用。在所有會話都排隊等待獲取鎖定時，鎖定爭用會導(dǎo)致數(shù)據(jù)庫的性能急劇惡化。某些作為正常活動結(jié)果的鎖定爭用可能無法避免，例如不同用戶需要訪問相同數(shù)據(jù)的應(yīng)用。然而在許多情況下，鎖定爭用是由程序和系統(tǒng)設(shè)計導(dǎo)致的。

Oracle數(shù)據(jù)庫提供了若干用于檢測鎖定爭用的實用程序，并且有可能解決突發(fā)的鎖定爭用問題。“死鎖(deadlock)”是鎖定爭用的一種特殊情況，并且通常由數(shù)據(jù)庫本身自動解決。

6.7.1 鎖定爭用的原因

在各種業(yè)務(wù)中，經(jīng)常會出現(xiàn)多個用戶同時需要對相同記錄進行寫訪問的情況。如果這個因素會限制系統(tǒng)的性能，那么唯一的解決方案是通過重新設(shè)計業(yè)務(wù)流程來開發(fā)一個更有效的業(yè)務(wù)模型。然而，除了業(yè)務(wù)數(shù)據(jù)處理過程中必需的某些鎖定之外，應(yīng)用程序設(shè)計中還會存在一些導(dǎo)致鎖定問題更嚴重的錯誤。

運行時間很長的事務(wù)會導(dǎo)致某些問題。用戶更新某行但是未提交變更就是一種顯著的情況。用戶可能在沒有結(jié)束事務(wù)時就出去就餐。此時，如果用戶使用諸如SQL*Plus之類的工具訪問數(shù)據(jù)庫，那么DBA無法阻止這種情況的發(fā)生。不過，使用編寫得當?shù)能浖?yīng)當不會發(fā)生這種情況。這樣的軟件應(yīng)當注意正好在更新操作之前施加鎖定，在操作結(jié)束后(使用COMMIT或ROLLBACK命令)立即解除這個鎖定。

編寫得不太好的批處理過程，如果編寫為長事務(wù)，也會帶來問題。考慮財務(wù)軟件中的名目賬項總賬。在會計術(shù)語中，從邏輯上看，某個財務(wù)軟件的名目賬項總賬不能為八、九月份各半月的賬目之和，因此月末結(jié)轉(zhuǎn)就是一個業(yè)務(wù)事務(wù)，這個事務(wù)可能會影響數(shù)千個表中的幾百萬條記錄，并且需要花費幾個小時才能完成。如果回滾例程編寫為一個用COMMIT結(jié)尾的事務(wù)，幾百萬條記錄就要鎖定好幾個小時——但在會計術(shù)語中，這是應(yīng)該的。優(yōu)秀的程序設(shè)計應(yīng)分組更新記錄，定期執(zhí)行，來避免這個問題，程序員還必須模擬事務(wù)的讀取一致性，處理過程中途失敗的情形。如果它是一個事務(wù)，就沒有問題，數(shù)據(jù)庫會回滾。如果它涉及許多小事務(wù)，它們就必須管理名目賬項總賬是八、九月份各半月的賬目之和的情形。這些考慮不應(yīng)出問題。程序員應(yīng)記住長事務(wù)對系統(tǒng)有效性的影響，并據(jù)此設(shè)計系統(tǒng)。

第三方用戶進程產(chǎn)品可能會帶來過高的鎖定級別。例如，某些應(yīng)用程序開發(fā)工具經(jīng)常會執(zhí)行SELECT...FOR UPDATE命令，從而避免重新查詢數(shù)據(jù)以及檢查變更的必要性。其他某些產(chǎn)品無法進行記錄層次上的鎖定。此時，如果用戶希望更新一行，那么這些工具會鎖定一組行(大約數(shù)十條，甚至數(shù)百條)。如果應(yīng)用程序使用諸如這些產(chǎn)品之類的工具進行編寫，那么Oracle數(shù)據(jù)庫會簡單地執(zhí)行被告知的操作，這將在業(yè)務(wù)進行期間帶來許多不必要的鎖定。如果察覺到這些軟件應(yīng)用了過多不必要的鎖定，就可以查看其是否具有修改這種行為的配置選項。

最后，確保程序員了解數(shù)據(jù)庫的功能。一個常見的問題是可重復(fù)的讀取。考慮下面的例子：

        SQL> select * from regions;
         REGION_ID REGION_NAME
        ---------- -------------------------
                    1 Europe
                    2 Americas
                    3 Asia
                    4 Middle East and Africa
        SQL> select count(*) from regions;
          COUNT(*)
        ----------
                  5

這怎么可能？第一個查詢(詳細報表)顯示了4行，但第二個查詢(匯總報告)顯示了5行。問題是在第一個查詢期間，另一個會話插入并提交了第5行。解決該問題的一種方式是運行報表時鎖定表，使其他會話掛起。更專業(yè)的方式是使用SET TRANSACTION READ ONLY語句。這將確保(不施加任何鎖)會話不會在任何表上看到任何DML(無論提交與否)，直到用COMMIT或ROLLBACK中斷只讀事務(wù)為止。該機制基于撤消段的使用。

6.7.2 檢測鎖定爭用

可以通過多種視圖，來了解數(shù)據(jù)庫中的鎖定情況，Database Express為鎖定的監(jiān)控提供了一個圖形界面。鎖定爭用是由許多用戶同時訪問相同數(shù)據(jù)而導(dǎo)致的。設(shè)計不當?shù)能浖惯@種問題更加嚴重，但是大體上鎖定爭用是正常數(shù)據(jù)庫活動的一個部分。因此，DBA不可能完全解決鎖定爭用問題，而只能標識問題，并提醒系統(tǒng)和應(yīng)用程序設(shè)計人員，在設(shè)計數(shù)據(jù)結(jié)構(gòu)與程序時牢記鎖定爭用的影響。

鎖定爭用可以在V$SESSION視圖中查看。這個視圖給每個當前登錄的會話顯示了一行。唯一標識符是列的會話標識符(SID)。如果一個會話被另一個會話阻塞，阻塞會話的SID就會顯示在列BLOCKING_SESSION上。圖6-7顯示的查詢把V$SESSION加進來，使用了SID和BLOCKING_SESSION列。唯一列出的會話是被阻塞的會話，所有其他會話的BLOCKING_SESSION都是NULL，因此沒有包含進來。

在圖6-7中，用戶MILLER阻塞了另外兩個會話JW和KING。用戶SCOTT阻塞了用戶SYSTEM。為了在Database Express中標識鎖定爭用，請單擊Performance標簽，單擊Performance Hub鏈接，再單擊Current ADDM Findings標簽。這會顯示當前檢測到的問題，如果單擊Impact欄，就會顯示細節(jié)。圖6-8顯示了SID=256的會話阻塞另外兩個會話(SID=15和SID=251)的情形，且按行鎖定排隊。還可以理解隊列：會話SID=15被SID為251和256的會話阻塞，所以關(guān)閉256(這是推薦1)不足以釋放它。但是，關(guān)閉會話256會釋放會話251。這樣251就可以完成其工作，接著釋放會話15。所有會話都標識一個3方會話ID，它由實例號(總是1，因為這不是群集數(shù)據(jù)庫)、SID和SERIAL#組成。

6.7.3 解決鎖定爭用

大多數(shù)情況下，這種阻塞持續(xù)的時間非常短。阻塞會話會執(zhí)行其事務(wù)，接著，被阻塞的會話就可以工作了。不過在緊急情況下，DBA可以通過終止過長時間擁有過多鎖定的會話來解決鎖定爭用問題。強制終止某個會話時，這個會話擁有的任何鎖定都會在回滾其作用的事務(wù)的同時被釋放。此時，被鎖定的會話將被釋放并繼續(xù)執(zhí)行。

使用ALTER SYSTEM KILL SESSION命令可以終止一個會話。該命令用會話的SID和SERIAL#指定要終止的會話。SID在任意時刻都是唯一的，但用戶登錄和注銷時，會重用SID。SID和SERIAL#確保其在實例的整個生命周期都是唯一的，所以在終止會話時必須指定它們兩個。

        ALTER SYSTEM KILL SESSION ' sid , serial# ';

6.7.4 死鎖：一種特殊情況

我們可能遇到如下場景：兩個會話相互阻塞，這兩個會話都被掛起，每個會話都在等待另一個會話釋放其鎖定。這種場景稱為“死鎖(deadlock)”。死鎖是由不當?shù)脑O(shè)計所導(dǎo)致的，數(shù)據(jù)庫自身能夠自動解決死鎖問題。與死鎖相關(guān)的信息會寫入警報日志，并詳細記錄至某個跟蹤文件。作為日常的一部分監(jiān)視工作，我們將找出所發(fā)生的死鎖現(xiàn)象，并將發(fā)生死鎖的情況告知開發(fā)人員。

如果出現(xiàn)某個死鎖，那么將掛起兩個會話，但這種情況只是暫時的。其中一個會話將在幾秒鐘內(nèi)監(jiān)測到這個死鎖，并且會回滾導(dǎo)致死鎖問題的語句。上述操作將釋放另一個會話，同時返回消息“ORA-00060 Deadlock detected”。編程人員必須在異常處理子句中捕獲這條消息，從而采取適當?shù)膭幼鳌?/p>

需要強調(diào)的是，死鎖是一種程序設(shè)計錯誤。產(chǎn)生死鎖的原因是代碼試圖執(zhí)行在邏輯上不可行的操作。編寫合理的代碼不僅始終能夠按照不會導(dǎo)致死鎖的順序請求鎖定，而且還可以測試在請求鎖定之前是否存在矛盾的鎖定。

練習6-8 描述鎖定機制和數(shù)據(jù)并發(fā)管理

本練習的第一部分，首先使用SQL*Plus 制造問題，然后檢測和解決問題。第二部分學習如何處理死鎖。

下面是演示鎖定爭用的步驟：

(1) 使用SQL*Plus，以用戶SYSTEM的身份在三個會話中連接到數(shù)據(jù)庫。

(2) 在第一個會話上，創(chuàng)建一個表。

        create table lockdemo as select * from all_users;

(3) 在第二個會話上，更新一行。

        update lockdemo set user_id=99 where username='SYS';

(4) 在第三個會話上，執(zhí)行與步驟2相同的命令。會話會掛起。

(5) 在第一個會話上，運行如下查詢，以確定哪個會話被阻塞：

        select username, sid, blocking_session from v$session
        where blocking_session is not null;

然后運行如下查詢，來檢索阻塞會話的必要細節(jié)：

        select username, sid, serial# from v$session
        where sid=&blocking_session;

出現(xiàn)提示時，輸入阻塞會話的SID。

(6) 在第一個會話上，關(guān)閉阻塞會話。

        alter system kill session '&blocking_sid, &serial'

出現(xiàn)提示時，輸入阻塞會話的SID和SERIAL #。

(7) 整理。

A．在第二個會話上，嘗試運行任何SQL語句。收到消息“ORA-00028: your session has been killed。”

B．在第三個會話上，更新將成功完成。用COMMIT或ROLLBACK終止事務(wù)。

以下步驟演示了死鎖：

(1) 在第一個會話上，更新一行。

        update lockdemo set user_id=99 where username='SYS';

(2) 在第三個會話上，更新第二行。

        update lockdemo set user_id=99 where username='SYSTEM';

(3) 在第一個會話上，嘗試更新第二行。

        update lockdemo set user_id=99 where username='SYSTEM';

這個會話將掛起，因為該行已鎖定。

(4) 在第三個會話上更新第一行，建立死鎖。

        update lockdemo set user_id=99 where username='SYS';

這個會話將掛起，但在三秒內(nèi)，第一個會話會被釋放，并顯示信息“ORA-00060: deadlock requested while waiting for resource” 。

(5) 從警報日志和跟蹤文件中檢索死鎖的信息。

A．從操作系統(tǒng)提示行上，打開數(shù)據(jù)庫的跟蹤目錄中的警告日志。該位置可以用如下查詢確定：

        select value from v$diag_info where name='Diag Trace';

該文件是alert_SID.ora。文件中的最后一個條目如下(Windows例子)：

        Sat Jan 10 22:32:54 2015
        ORA-00060: Deadlock detected. More info in file
    D:\APP\ORACLE\diag\rdbms\coda\coda\trace\coda_ora_8364.trc.

B．用編輯器打開跟蹤文件。在文件的頂部是如下關(guān)鍵信息：

        *** 2015-01-10 22:32:54.817
        DEADLOCK DETECTED ( ORA-00060 )

       [Transaction Deadlock]
        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.

請注意，此消息將責任推給開發(fā)人員。在文件的后面，會找到參與會話的SID和它們執(zhí)行的、導(dǎo)致死鎖的語句。

(6) 整理。在第一個會話上，執(zhí)行一個ROLLBACK命令，回滾成功的更新。這將釋放第三個會話，在這里也可以執(zhí)行ROLLBACK。刪除LOCKDEMO表。