上篇說SQL Server應(yīng)用模式之OLTP系統(tǒng)性能分析。五種角度分析sql性能問題。本章依然是SQL性能 五種角度其一“阻塞與死鎖”

這里通過連接在sysprocesses里字段值的組合來分析阻塞源頭，可以把阻塞分為以下5種常見的類型（見表）。waittype,open_tran,status,都是sysprocesses里的值，“自我修復(fù)？”列的意思,就是指阻塞能不能自動(dòng)消失。

 5種常見的阻塞類型

下面詳細(xì)介紹這些類型產(chǎn)生的原因，以及解決方法

解決方法：

要解決這一類阻塞，數(shù)據(jù)庫管理員需要和數(shù)據(jù)庫應(yīng)用設(shè)計(jì)人員合作，共同解決以下問題。

1. 語句本身有沒有可優(yōu)化的空間？
   這里包括修改語句本身降低復(fù)雜度、修改表格設(shè)計(jì)、調(diào)整索引等。
2. SQL Server整體性能如何？是不是有資源瓶頸影響了語句執(zhí)行速度？
   當(dāng)SQL Server 遇到諸如內(nèi)存、硬盤讀寫、CPU等資源瓶頸是，原來能很快完成的語句有可能會(huì)花很長時(shí)間。
3. 如果語句天生就很復(fù)雜，無法調(diào)優(yōu)（很多處理報(bào)表的語句就是這樣），就須考慮怎樣把這一類應(yīng)用（一般就是數(shù)據(jù)倉庫應(yīng)用）從OLTP系統(tǒng)中隔離出來。

這一類阻塞的特征，就是問題連接早就進(jìn)入了空閑狀態(tài)（sysprocesses.status=’sleeping’和sysprocesses.cmd=’AWAITING COMMAND’），但是，如果檢查sysprocesses.open_tran，就會(huì)發(fā)現(xiàn)它不為0，以及事務(wù)沒有提交。這類問題很多都是因?yàn)閼?yīng)用端遇到一個(gè)執(zhí)行超時(shí)，或者其他原因，當(dāng)時(shí)執(zhí)行的語句被提前終止了，但是連接還保留著。應(yīng)用沒有跟隨發(fā)來的事務(wù)提交或回滾指令，導(dǎo)致一個(gè)事務(wù)被遺留在SQL Server里。

遇到這類問題，許多使用者會(huì)誤以為是SQL Server端什么地方?jīng)]有處理好。其實(shí)，執(zhí)行超時(shí)（command timeout）完全是一個(gè)客戶端的行為。當(dāng)客戶端應(yīng)用向SQL Server發(fā)來語句執(zhí)行請(qǐng)求時(shí)，自己會(huì)有一個(gè)執(zhí)行超時(shí)設(shè)置。一般ADO或ADO.NET的連接超時(shí)時(shí)限是30秒。如果30秒以內(nèi)SQL Server沒有完成語句返回任何結(jié)果，客戶端就會(huì)發(fā)送一個(gè)Attention的消息給SQL Server，告訴SQL Server它不想繼續(xù)等下去了。SQL Server收到這個(gè)消息后，會(huì)終止當(dāng)前正在運(yùn)行的語句（或批處理）。但是，為了維護(hù)客戶端的邏輯，SQL Server默認(rèn)不會(huì)自動(dòng)回滾或提交這個(gè)連接已經(jīng)打開的事務(wù)，而是等待客戶端的后續(xù)決定。如果客戶端不發(fā)來回滾或提交指令，SQL Server會(huì)永遠(yuǎn)的把這個(gè)事務(wù)保持下去，直到客戶端斷開連接為止。

這里可以用下面這個(gè)實(shí)驗(yàn)來模擬這個(gè)問題。在Management Studio里創(chuàng)建一個(gè)連接到SQL Server，運(yùn)行下面的批處理語句：

use sqlnexus
        go
BEGIN TRAN
SELECT *
FROM ReadTrace.tblInterestingEvents
WITH(HOLDLOCK) SELECT 
    * 
FROM sysobjects s1,sysobjects
    s2 COMMIT TRAN

由于使用了HOLDLOCK參數(shù)，第一句SELECT會(huì)在運(yùn)行結(jié)束后，在表格上維持一個(gè)TAB的S鎖。如果批處理全部完成，這個(gè)鎖會(huì)在提交事務(wù)的時(shí)候釋放。但是第二句的SELECT會(huì)執(zhí)行很久。請(qǐng)?jiān)诘却?～4秒鐘以后取消執(zhí)行。然后運(yùn)行下面的語句，檢查open_tran和鎖的情況。

SELECT @@TRANCOUNT
GO sp_lock GO

通過結(jié)果（見圖）可以得知：

(1)     批處理被取消的時(shí)候，“COMMIT TRAN”這條語句沒有被執(zhí)行到。SQL Server沒有對(duì)“BEGIN TRAN”開啟的那個(gè)事務(wù)做任何處理，只保持其活動(dòng)的狀態(tài)。

(2)     第一句SELECT帶來的鎖由于事務(wù)沒有結(jié)束，所以鎖還保持著（objID=85575343, Type=TAB, Mode=IS）。

現(xiàn)在，如果有其他連接要修改ReadTrace.tblInterestingEvents這張表，就會(huì)被阻塞住。

解決辦法：

1. 應(yīng)用程序本身必須意識(shí)到審核語句都有可能遇到意外終止情況，做好錯(cuò)誤處理工作。這些工作包括

　　a)   在做SQL Server調(diào)用的時(shí)候，必須加上錯(cuò)誤捕捉和處理語句

　　SQL Server客戶端驅(qū)動(dòng)程序（包括ODBC和OLE DB）當(dāng)語句執(zhí)行遇到意外終止（包括超時(shí)）的時(shí)候，都會(huì)向應(yīng)用返回錯(cuò)誤信息?？蛻舳嗽诓蹲降藉e(cuò)誤信息時(shí)。除了做記錄以外（這對(duì)問題定位非常有幫助），還要運(yùn)行下面這句話，把沒有提交的事務(wù)回滾掉。

IF @@TRANCOUNT>0 ROLLBACK TRAN

　　有些程序員會(huì)問，我在T-SQL批處理里已經(jīng)寫了T-SQL層面的錯(cuò)誤捕捉和處理語句（IF @@ERROR<>0 ROLLBACK TRAN）,還有必要讓應(yīng)用程序再做一遍么？需要意識(shí)到的是，有些異常（比如超時(shí)）終止的是整個(gè)T-SQL批處理的執(zhí)行，而不僅僅是當(dāng)前語句。所以當(dāng)這些異常發(fā)生的時(shí)候，T-SQL層面錯(cuò)誤捕捉和處理語句很可能也一起被取消了。它們不能發(fā)揮想象中的作用。在應(yīng)用程序里的錯(cuò)誤捕捉和處理語句是必不可少的。

　　b)   設(shè)置連接屬性“SET SACT_ABORT ON”

　　當(dāng)SET SACT_ABORT為ON時(shí)，如果執(zhí)行T-SQL語句產(chǎn)生運(yùn)行錯(cuò)誤，整個(gè)事務(wù)將會(huì)終止并回滾

　　當(dāng)SET SACT_ABORT為OFF時(shí)，處理方法不是唯一的。有時(shí)只回滾產(chǎn)生錯(cuò)誤的T-SQL語句，而事務(wù)將繼續(xù)進(jìn)行處理。如果錯(cuò)誤很嚴(yán)重，及時(shí)SET SACT_ABORT 為OFF，也可能回滾整個(gè)事務(wù)。OFF是默認(rèn)設(shè)置。

　　如果沒有辦法很快規(guī)范應(yīng)用程序的錯(cuò)誤捕捉和處理語句，一個(gè)最快的方法就是在每個(gè)連接建立以后，或者是容易出問題的存儲(chǔ)過程的開頭，運(yùn)行“SET XACT_ABORT ON”，讓SQL Server幫助應(yīng)用程序回滾事務(wù)。

　　c)   考慮是否要關(guān)閉連接池

　　一般的SQL Server應(yīng)用都會(huì)使用連接池來得到良好的性能。如果有一個(gè)連接忘記把事務(wù)關(guān)閉就推出連接，那么這個(gè)連接會(huì)被交還給連接池，但是這個(gè)時(shí)候事務(wù)不會(huì)被清理。客戶端驅(qū)動(dòng)程序會(huì)在這個(gè)連接下一次被重用的時(shí)候（又有新的用戶要建立連接），發(fā)一句sp_reset_connection命令清理當(dāng)前連接上次遺留下來的所有對(duì)象，包括回滾未提交的事務(wù)。如果連接交還給連接池以后很久都沒有被重用，那它的事務(wù)就會(huì)持續(xù)長時(shí)間，引起阻塞。有些Java程序使用的驅(qū)動(dòng)程序，提供連接池功能，但是不提供連接重用時(shí)的事務(wù)清理功能。這樣的連接池對(duì)應(yīng)用開發(fā)質(zhì)量要求很高，比較容易發(fā)生阻塞。

    如果不能很快的實(shí)施建議a)和b)，把連接池關(guān)閉能縮短食事務(wù)持續(xù)時(shí)間，也能從一定程度上緩解阻塞問題。

2. 分析為什么連接會(huì)遇到異常終止

　　這里又得談到錯(cuò)誤信息記錄了。有了錯(cuò)誤信息，就可以判定是超時(shí)問題，還是其他SQL Server錯(cuò)誤。如果是超時(shí)問題，可按照第一種阻塞進(jìn)行處理。

    還有一種孤兒事務(wù)的來源，是連接開啟了隱式事務(wù)（implicit transaction）而沒有加入及時(shí)提交事務(wù)的機(jī)制。如果連接處于隱式事務(wù)模式（SET IMPLICIT_TRANSACTIONS ON），并且連接當(dāng)前不再事務(wù)中，則執(zhí)行下列任何一條語句都會(huì)開啟一個(gè)新的事務(wù)。

對(duì)于因?yàn)榇嗽O(shè)置為ON而自動(dòng)打開的事務(wù)，SQL Server會(huì)自動(dòng)幫你打開事務(wù)，但是不會(huì)自動(dòng)幫你提交。用戶必須在該事務(wù)結(jié)束后將其顯式提交或回滾。否則，當(dāng)用戶斷開連接時(shí)，事務(wù)及其包含的所有數(shù)據(jù)更改將被回滾。事務(wù)提交后，執(zhí)行上述任意一條語句又會(huì)啟動(dòng)一個(gè)新事務(wù)。隱式事務(wù)模式將始終生效，知道連接執(zhí)行SET IMPLICIT_TRANSACTIONS OFF語句使連接恢復(fù)為自動(dòng)提交模式。在自動(dòng)提交模式下，所有單個(gè)語句在成功完成時(shí)將被提交，不會(huì)有事務(wù)遺留。

為什么會(huì)有連接要開啟隱式事務(wù)呢？除了程序員有意為之以外，很多是客戶端數(shù)據(jù)庫連接驅(qū)動(dòng)，或者空間為了實(shí)現(xiàn)它的事務(wù)功能（注意不是SQL Server通過T-SQL語句直接提供的）而選用這個(gè)機(jī)制。如果應(yīng)用程序出現(xiàn)意外，或者腳本沒有處理好，會(huì)有應(yīng)用層事務(wù)未提交的現(xiàn)象。在SQL Server里也體現(xiàn)為一個(gè)孤兒事務(wù)。嚴(yán)格約束應(yīng)用層對(duì)事務(wù)的使用，直接使用SQL Server里面的事務(wù)，是避免這種問題出現(xiàn)的好方法。

         語句在SQL Server內(nèi)執(zhí)行總時(shí)間不僅包含SQL Server的執(zhí)行時(shí)間，還包含把結(jié)果集發(fā)給客戶端的時(shí)間。如果結(jié)果集比較大，SQL Server會(huì)分幾次打包發(fā)出，每發(fā)一次，都要等待客戶端的確認(rèn)。只有確認(rèn)以后，SQL Server才會(huì)發(fā)送下一個(gè)結(jié)果集包。所有結(jié)果都發(fā)完以后，SQL Server才認(rèn)為語句執(zhí)行完畢，釋放執(zhí)行申請(qǐng)的資源（包括鎖資源）。

         如果處于某種原因，客戶端應(yīng)用處理結(jié)果非常緩慢甚至沒有相應(yīng)，或者干脆不理睬SQL Server發(fā)送結(jié)果集的請(qǐng)求，則SQL Server會(huì)耐心的等待，因此會(huì)導(dǎo)致語句長時(shí)間執(zhí)行而發(fā)生阻塞。

解決方法：

1. 在設(shè)計(jì)程序時(shí)，一定要慎重返回大結(jié)果集。這種行為不僅會(huì)對(duì)SQL Server和網(wǎng)絡(luò)帶來很大負(fù)擔(dān)，對(duì)應(yīng)用程序本身來講，也要花很多資源去處理結(jié)果集。如果最終用戶只需要部分結(jié)果集就可以，則在發(fā)送SQL Server指令的時(shí)候就要指定好。要避免居于不管三七二十一所有數(shù)據(jù)都要，而結(jié)果集只取走開頭一部分去展示這樣的行為發(fā)生。
2. 如果應(yīng)用程序的確須返回大結(jié)果集，例如一些報(bào)表系統(tǒng)，則要考慮報(bào)表數(shù)據(jù)庫和生產(chǎn)數(shù)據(jù)庫分開。
3. 如果1和2在短期內(nèi)不能實(shí)現(xiàn)，可以和最終用戶協(xié)商，返回大結(jié)果集的連接使用READ UNCOMMITTED事務(wù)隔離級(jí)別。這樣查詢語句就不會(huì)申請(qǐng)S鎖了。

這種情況常是由第一類情況衍生來的。有時(shí)候數(shù)據(jù)庫管理員發(fā)現(xiàn)一個(gè)連接阻塞住了別人，為了解決問題，會(huì)讓連接主動(dòng)退出或強(qiáng)制退出（輕質(zhì)退出應(yīng)用，或者直接在SQL Server端KILL連接）。對(duì)于大部分情況，這些措施會(huì)消除阻塞。但是要記住的是，不管是在客戶端退出，還是要服務(wù)器端KILL，為了維護(hù)數(shù)據(jù)庫事務(wù)的一致性，SQL Server都會(huì)對(duì)連接還沒有來得及完成提交的事務(wù)做回滾動(dòng)作。SQL Server要找到所有當(dāng)前事務(wù)修改過的記錄，把它們改回原來的狀態(tài)。所以，如果一個(gè)DELETE、INSERT或UPDATE已經(jīng)運(yùn)行了一個(gè)小時(shí)，可能回滾也需要一個(gè)小時(shí)，在這個(gè)過程中，阻塞還會(huì)延續(xù)，我們只能等待。

有些用戶可能等不及，直接重啟SQL Server。當(dāng)SQL Server關(guān)閉的時(shí)候，回滾動(dòng)作會(huì)被中斷，SQL Server會(huì)被很快關(guān)掉，但是這個(gè)回滾動(dòng)作在下次SQL Server重啟的時(shí)候會(huì)重新開始（數(shù)據(jù)庫做恢復(fù)的時(shí)候）。重啟的時(shí)候如果回滾不能很快結(jié)束，整個(gè)數(shù)據(jù)庫都不可用，可能會(huì)帶來更嚴(yán)重的后果。

解決方法：

最好的方法是在工作時(shí)間盡量不要做這種大的修改操作。這些操作盡量安排在半夜或者周末的時(shí)間完成。如果操作已經(jīng)做了很久，最好耐心等它做完。如果一定要在有工作負(fù)荷的時(shí)候做，最好把一個(gè)大操作分成若干小操作分步完成。

一個(gè)客戶端的應(yīng)用在運(yùn)行過程中會(huì)使用到許多資源，包括線程資源，信號(hào)量資源，內(nèi)存資源，IO資源等，SQL Server也是資源之一。如果發(fā)生死鎖的兩端不全是SQL Server，SQL Server的死鎖判斷機(jī)制可能不起作用。這時(shí)如果應(yīng)用端沒有處理好，可能會(huì)永遠(yuǎn)等下去。而SQL Server內(nèi)部的表現(xiàn)可能僅僅是一個(gè)阻塞。但是這個(gè)阻塞不會(huì)自動(dòng)消除。這樣的阻塞對(duì)SQL Server的性能會(huì)產(chǎn)生很大影響。

下面我們舉兩個(gè)這種應(yīng)用端死鎖的例子。

1)  在應(yīng)用的一個(gè)線程中開啟不止一個(gè)數(shù)據(jù)庫連接而產(chǎn)生的死鎖（見圖）。

假設(shè)應(yīng)用有一個(gè)線程有這樣的邏輯：

    ●  開始運(yùn)行

    ●  建立數(shù)據(jù)庫連接A，調(diào)用存儲(chǔ)過程ProcA。打開結(jié)果集A。

    ●  建立數(shù)據(jù)庫連接B，調(diào)用存儲(chǔ)過程ProcB。打開結(jié)果集B。

    ●  輪流讀取結(jié)果集A、B，整合輸出最終結(jié)果。

    ●  關(guān)閉結(jié)果集A、B，關(guān)機(jī)連接A、B。

    ●  結(jié)束運(yùn)行

在正常情況下這樣的設(shè)計(jì)看上去沒有問題，但是實(shí)際上很脆弱。因?yàn)樵诰€程內(nèi)部，這個(gè)邏輯是線程執(zhí)行的。假設(shè)存儲(chǔ)過程ProcA是一個(gè)事務(wù)，在返回結(jié)果集之前因?yàn)橐恍┎僮魃暾?qǐng)了一些排他鎖，而ProcB為了返回結(jié)果又要用到這些鎖，那會(huì)發(fā)生什么情況呢？

發(fā)生的情況會(huì)是連接A在等線程把連接B上的結(jié)果讀出來，再來處理結(jié)果集A，而連接B等待連接A完成事務(wù)后再釋放鎖。雙方相互等待，產(chǎn)生思索。

1)  兩個(gè)線程間的死鎖（見圖）。

如果應(yīng)用有兩個(gè)線程，每個(gè)線程各開一個(gè)數(shù)據(jù)庫連接，那上面的邏輯不會(huì)出問題。因?yàn)檫\(yùn)行ProcA的那個(gè)線程會(huì)先做完，釋放阻塞住連接B的鎖，讓B也能夠接著跑完。但是假設(shè)有下列邏輯：

線程A：建立數(shù)據(jù)庫連接A，不斷讀取表格A，按條取出記錄，做一定處理后發(fā)給線程B的輸入緩存。

線程B：建立數(shù)據(jù)庫連接B，從輸入緩存讀取數(shù)據(jù)，依據(jù)收到的記錄對(duì)表格A進(jìn)行修改。

這個(gè)邏輯會(huì)產(chǎn)生什么問題呢？我們知道表格修改會(huì)在表上申請(qǐng)一些排他鎖。如果線程A正在讀取這條記錄，修改動(dòng)作會(huì)被阻塞住。這個(gè)時(shí)候線程B就會(huì)進(jìn)入等待狀態(tài)。但是線程A需要線程B輸入緩存清空后才能寫入。如果線程B還沒來得及清空，它也不得不等待，這時(shí)候也會(huì)產(chǎn)生死鎖（在SQL Server里是一個(gè)阻塞）。

解決方法:

復(fù)雜的程序還可能會(huì)出現(xiàn)其他的死鎖形式。為了避免這種死鎖，要在應(yīng)用調(diào)用SQL Server的時(shí)候設(shè)置執(zhí)行超時(shí)，并寫好錯(cuò)誤處理機(jī)制（參見阻塞原因2）。一旦死鎖發(fā)生，SQL Server的操作在等待一段時(shí)間后會(huì)因?yàn)槌瑫r(shí)而放棄，并釋放出SQL Server內(nèi)部的資源，解決死鎖。

小結(jié)：應(yīng)更多從程序設(shè)計(jì)著手解決阻塞問題

很多用戶有一種誤解，認(rèn)為阻塞是一個(gè)數(shù)據(jù)庫問題。當(dāng)阻塞問題發(fā)生的時(shí)候，都希望從數(shù)據(jù)庫層面找到方法，一勞永逸地解決問題。可是，阻塞本身是為了完成事務(wù)的隔離，是應(yīng)用程序向SQL Server提出的要求。所以很多時(shí)候，光從數(shù)據(jù)庫端努力是不能解決阻塞問題的。在應(yīng)用程序?qū)用嬉惨龊芏喙ぷ?。例如?yīng)用在做連接的時(shí)候選擇什么樣的隔離級(jí)別，事務(wù)開始和結(jié)束的時(shí)間點(diǎn)選擇，連接的建立和回收機(jī)制，指令復(fù)雜度的控制等。應(yīng)用程序還應(yīng)該考慮到控制結(jié)果集大小，并及時(shí)從SQL Server端取走數(shù)據(jù)。還要考慮SQL Server指令執(zhí)行時(shí)間長短控制，以及發(fā)生超時(shí)或其他意外后的錯(cuò)誤處理機(jī)制等。尤其是對(duì)高并發(fā)量、高響應(yīng)要求的關(guān)鍵業(yè)務(wù)系統(tǒng)，在設(shè)計(jì)應(yīng)用時(shí)必須要考慮好上面這些關(guān)鍵因素。對(duì)于關(guān)鍵的業(yè)務(wù)邏輯，必須逐個(gè)審查，保證應(yīng)用選擇的是能夠滿足業(yè)務(wù)需求的最低隔離級(jí)別，事務(wù)的大小已經(jīng)控制到了最小的粒度。而運(yùn)行的語句，也要有良好的數(shù)據(jù)庫設(shè)計(jì)，保證它不會(huì)隨著數(shù)據(jù)庫的增大和用戶量的增多，占用更多的資源和運(yùn)行時(shí)間。如果做不到這幾點(diǎn)，就會(huì)容易發(fā)生應(yīng)用在用戶量比較少，或者數(shù)據(jù)庫比較小的初始階段性能不錯(cuò)，但是當(dāng)用戶量增長或數(shù)據(jù)量增大以后性能越來越慢的問題。