上篇說SQL Server應(yīng)用模式之OLTP系統(tǒng)性能分析。五種角度分析sql性能問題。本章依然是SQL性能 五種角度其一“阻塞與死鎖”
這里通過連接在sysprocesses里字段值的組合來分析阻塞源頭，可以把阻塞分為以下5種常見的類型（見表）。waittype,open_tran,status,都是sysprocesses里的值，“自我修復(fù)？”列的意思,就是指阻塞能不能自動消失。
5種常見的阻塞類型
| 類型 | waittype | open_tran | status | 自我修復(fù) | 原因/其他特征 |
| 1 | 不為0 | >=0 | runnable | 是的，當(dāng)語句運行結(jié)束后 | 語句運行的時間比較長，運行時需等待某些系統(tǒng)資源（如硬盤讀寫、CPU或內(nèi)存等）。 |
| 2 | 0x0000 | >0 | sleeping | 不能，但是如果運行 KILL語句，這個鏈接能夠很容易被終止 | 可能客戶端遇到了一個語句執(zhí)行超時，或者主動取消了上一語句的執(zhí)行，但是沒有回滾開啟的事務(wù)，在SQL Trace里能夠看到一個Attention事件 |
| 3 | 0x0000 0x0800 0x0063 | >=0 | runnable | 不能。知道客戶端吧所有結(jié)果都主動取走，或者主動斷開連接，可以運行KILL語句去終止它，但是可能要花長達30秒 | 客戶端沒有及時把所有結(jié)果都取走，這時可能open_tran=0，事務(wù)隔離級別也為默認（READ COMMITTED），但這個連接還會持有鎖資源 |
| 4 | 0x0000 | >0 | rollback | 是的 | 在SQL Trace里能夠看到這個SPID已經(jīng)發(fā)來了一個Attention事件，說明客戶端已經(jīng)遇到了超時，或者主動要求回滾事務(wù) |
| 5 | 各種值都有可能 | >=0 | runnable | 不能，直到客戶端取消語句運行或者主動斷開連接。可以運行KILL語句終止它，但是可能要花長達30秒 | 應(yīng)用程序運行中產(chǎn)生死鎖，在SQL Server中以阻塞形式體現(xiàn)。Sysprocesses里阻塞和被阻塞的連接hostname值是一樣的 |
下面詳細介紹這些類型產(chǎn)生的原因，以及解決方法
解決方法：
要解決這一類阻塞，數(shù)據(jù)庫管理員需要和數(shù)據(jù)庫應(yīng)用設(shè)計人員合作，共同解決以下問題。
這一類阻塞的特征，就是問題連接早就進入了空閑狀態(tài)（sysprocesses.status=’sleeping’和sysprocesses.cmd=’AWAITING COMMAND’），但是，如果檢查sysprocesses.open_tran，就會發(fā)現(xiàn)它不為0，以及事務(wù)沒有提交。這類問題很多都是因為應(yīng)用端遇到一個執(zhí)行超時，或者其他原因，當(dāng)時執(zhí)行的語句被提前終止了，但是連接還保留著。應(yīng)用沒有跟隨發(fā)來的事務(wù)提交或回滾指令，導(dǎo)致一個事務(wù)被遺留在SQL Server里。
遇到這類問題，許多使用者會誤以為是SQL Server端什么地方?jīng)]有處理好。其實，執(zhí)行超時（command timeout）完全是一個客戶端的行為。當(dāng)客戶端應(yīng)用向SQL Server發(fā)來語句執(zhí)行請求時，自己會有一個執(zhí)行超時設(shè)置。一般ADO或ADO.NET的連接超時時限是30秒。如果30秒以內(nèi)SQL Server沒有完成語句返回任何結(jié)果，客戶端就會發(fā)送一個Attention的消息給SQL Server，告訴SQL Server它不想繼續(xù)等下去了。SQL Server收到這個消息后，會終止當(dāng)前正在運行的語句（或批處理）。但是，為了維護客戶端的邏輯，SQL Server默認不會自動回滾或提交這個連接已經(jīng)打開的事務(wù)，而是等待客戶端的后續(xù)決定。如果客戶端不發(fā)來回滾或提交指令，SQL Server會永遠的把這個事務(wù)保持下去，直到客戶端斷開連接為止。
這里可以用下面這個實驗來模擬這個問題。在Management Studio里創(chuàng)建一個連接到SQL Server，運行下面的批處理語句：
use sqlnexus goBEGIN TRANSELECT *FROM ReadTrace.tblInterestingEventsWITH(HOLDLOCK) SELECT * FROM sysobjects s1,sysobjects s2 COMMIT TRAN
由于使用了HOLDLOCK參數(shù)，第一句SELECT會在運行結(jié)束后，在表格上維持一個TAB的S鎖。如果批處理全部完成，這個鎖會在提交事務(wù)的時候釋放。但是第二句的SELECT會執(zhí)行很久。請在等待3～4秒鐘以后取消執(zhí)行。然后運行下面的語句，檢查open_tran和鎖的情況。
SELECT @@TRANCOUNTGO sp_lock GO
通過結(jié)果（見圖）可以得知：
(1) 批處理被取消的時候，“COMMIT TRAN”這條語句沒有被執(zhí)行到。SQL Server沒有對“BEGIN TRAN”開啟的那個事務(wù)做任何處理，只保持其活動的狀態(tài)。
(2) 第一句SELECT帶來的鎖由于事務(wù)沒有結(jié)束，所以鎖還保持著（objID=85575343, Type=TAB, Mode=IS）。
現(xiàn)在，如果有其他連接要修改ReadTrace.tblInterestingEvents這張表，就會被阻塞住。
解決辦法：
1. 應(yīng)用程序本身必須意識到審核語句都有可能遇到意外終止情況，做好錯誤處理工作。這些工作包括
a) 在做SQL Server調(diào)用的時候，必須加上錯誤捕捉和處理語句
SQL Server客戶端驅(qū)動程序（包括ODBC和OLE DB）當(dāng)語句執(zhí)行遇到意外終止（包括超時）的時候，都會向應(yīng)用返回錯誤信息?？蛻舳嗽诓蹲降藉e誤信息時。除了做記錄以外（這對問題定位非常有幫助），還要運行下面這句話，把沒有提交的事務(wù)回滾掉。
IF @@TRANCOUNT>0 ROLLBACK TRAN
有些程序員會問，我在T-SQL批處理里已經(jīng)寫了T-SQL層面的錯誤捕捉和處理語句（IF @@ERROR<>0 ROLLBACK TRAN）,還有必要讓應(yīng)用程序再做一遍么？需要意識到的是，有些異常（比如超時）終止的是整個T-SQL批處理的執(zhí)行，而不僅僅是當(dāng)前語句。所以當(dāng)這些異常發(fā)生的時候，T-SQL層面錯誤捕捉和處理語句很可能也一起被取消了。它們不能發(fā)揮想象中的作用。在應(yīng)用程序里的錯誤捕捉和處理語句是必不可少的。
b) 設(shè)置連接屬性“SET SACT_ABORT ON”
當(dāng)SET SACT_ABORT為ON時，如果執(zhí)行T-SQL語句產(chǎn)生運行錯誤，整個事務(wù)將會終止并回滾
當(dāng)SET SACT_ABORT為OFF時，處理方法不是唯一的。有時只回滾產(chǎn)生錯誤的T-SQL語句，而事務(wù)將繼續(xù)進行處理。如果錯誤很嚴重，及時SET SACT_ABORT 為OFF，也可能回滾整個事務(wù)。OFF是默認設(shè)置。
如果沒有辦法很快規(guī)范應(yīng)用程序的錯誤捕捉和處理語句，一個最快的方法就是在每個連接建立以后，或者是容易出問題的存儲過程的開頭，運行“SET XACT_ABORT ON”，讓SQL Server幫助應(yīng)用程序回滾事務(wù)。
c) 考慮是否要關(guān)閉連接池
一般的SQL Server應(yīng)用都會使用連接池來得到良好的性能。如果有一個連接忘記把事務(wù)關(guān)閉就推出連接，那么這個連接會被交還給連接池，但是這個時候事務(wù)不會被清理?？蛻舳蓑?qū)動程序會在這個連接下一次被重用的時候（又有新的用戶要建立連接），發(fā)一句sp_reset_connection命令清理當(dāng)前連接上次遺留下來的所有對象，包括回滾未提交的事務(wù)。如果連接交還給連接池以后很久都沒有被重用，那它的事務(wù)就會持續(xù)長時間，引起阻塞。有些Java程序使用的驅(qū)動程序，提供連接池功能，但是不提供連接重用時的事務(wù)清理功能。這樣的連接池對應(yīng)用開發(fā)質(zhì)量要求很高，比較容易發(fā)生阻塞。
如果不能很快的實施建議a)和b)，把連接池關(guān)閉能縮短食事務(wù)持續(xù)時間，也能從一定程度上緩解阻塞問題。
2. 分析為什么連接會遇到異常終止
這里又得談到錯誤信息記錄了。有了錯誤信息，就可以判定是超時問題，還是其他SQL Server錯誤。如果是超時問題，可按照第一種阻塞進行處理。
還有一種孤兒事務(wù)的來源，是連接開啟了隱式事務(wù)（implicit transaction）而沒有加入及時提交事務(wù)的機制。如果連接處于隱式事務(wù)模式（SET IMPLICIT_TRANSACTIONS ON），并且連接當(dāng)前不再事務(wù)中，則執(zhí)行下列任何一條語句都會開啟一個新的事務(wù)。
| ALTER TABLE | FETCH | REVOKE |
| CREATE | GRANT | SELECT |
| DELETE | INSERT | TRUNCATE_TABLE |
| DROP | OPEN | UPDATE |
對于因為此設(shè)置為ON而自動打開的事務(wù)，SQL Server會自動幫你打開事務(wù)，但是不會自動幫你提交。用戶必須在該事務(wù)結(jié)束后將其顯式提交或回滾。否則，當(dāng)用戶斷開連接時，事務(wù)及其包含的所有數(shù)據(jù)更改將被回滾。事務(wù)提交后，執(zhí)行上述任意一條語句又會啟動一個新事務(wù)。隱式事務(wù)模式將始終生效，知道連接執(zhí)行SET IMPLICIT_TRANSACTIONS OFF語句使連接恢復(fù)為自動提交模式。在自動提交模式下，所有單個語句在成功完成時將被提交，不會有事務(wù)遺留。
為什么會有連接要開啟隱式事務(wù)呢？除了程序員有意為之以外，很多是客戶端數(shù)據(jù)庫連接驅(qū)動，或者空間為了實現(xiàn)它的事務(wù)功能（注意不是SQL Server通過T-SQL語句直接提供的）而選用這個機制。如果應(yīng)用程序出現(xiàn)意外，或者腳本沒有處理好，會有應(yīng)用層事務(wù)未提交的現(xiàn)象。在SQL Server里也體現(xiàn)為一個孤兒事務(wù)。嚴格約束應(yīng)用層對事務(wù)的使用，直接使用SQL Server里面的事務(wù)，是避免這種問題出現(xiàn)的好方法。
語句在SQL Server內(nèi)執(zhí)行總時間不僅包含SQL Server的執(zhí)行時間，還包含把結(jié)果集發(fā)給客戶端的時間。如果結(jié)果集比較大，SQL Server會分幾次打包發(fā)出，每發(fā)一次，都要等待客戶端的確認。只有確認以后，SQL Server才會發(fā)送下一個結(jié)果集包。所有結(jié)果都發(fā)完以后，SQL Server才認為語句執(zhí)行完畢，釋放執(zhí)行申請的資源（包括鎖資源）。
如果處于某種原因，客戶端應(yīng)用處理結(jié)果非常緩慢甚至沒有相應(yīng)，或者干脆不理睬SQL Server發(fā)送結(jié)果集的請求，則SQL Server會耐心的等待，因此會導(dǎo)致語句長時間執(zhí)行而發(fā)生阻塞。
解決方法：
這種情況常是由第一類情況衍生來的。有時候數(shù)據(jù)庫管理員發(fā)現(xiàn)一個連接阻塞住了別人，為了解決問題，會讓連接主動退出或強制退出（輕質(zhì)退出應(yīng)用，或者直接在SQL Server端KILL連接）。對于大部分情況，這些措施會消除阻塞。但是要記住的是，不管是在客戶端退出，還是要服務(wù)器端KILL，為了維護數(shù)據(jù)庫事務(wù)的一致性，SQL Server都會對連接還沒有來得及完成提交的事務(wù)做回滾動作。SQL Server要找到所有當(dāng)前事務(wù)修改過的記錄，把它們改回原來的狀態(tài)。所以，如果一個DELETE、INSERT或UPDATE已經(jīng)運行了一個小時，可能回滾也需要一個小時，在這個過程中，阻塞還會延續(xù)，我們只能等待。
有些用戶可能等不及，直接重啟SQL Server。當(dāng)SQL Server關(guān)閉的時候，回滾動作會被中斷，SQL Server會被很快關(guān)掉，但是這個回滾動作在下次SQL Server重啟的時候會重新開始（數(shù)據(jù)庫做恢復(fù)的時候）。重啟的時候如果回滾不能很快結(jié)束，整個數(shù)據(jù)庫都不可用，可能會帶來更嚴重的后果。
解決方法：
最好的方法是在工作時間盡量不要做這種大的修改操作。這些操作盡量安排在半夜或者周末的時間完成。如果操作已經(jīng)做了很久，最好耐心等它做完。如果一定要在有工作負荷的時候做，最好把一個大操作分成若干小操作分步完成。
一個客戶端的應(yīng)用在運行過程中會使用到許多資源，包括線程資源，信號量資源，內(nèi)存資源，IO資源等，SQL Server也是資源之一。如果發(fā)生死鎖的兩端不全是SQL Server，SQL Server的死鎖判斷機制可能不起作用。這時如果應(yīng)用端沒有處理好，可能會永遠等下去。而SQL Server內(nèi)部的表現(xiàn)可能僅僅是一個阻塞。但是這個阻塞不會自動消除。這樣的阻塞對SQL Server的性能會產(chǎn)生很大影響。
下面我們舉兩個這種應(yīng)用端死鎖的例子。
1) 在應(yīng)用的一個線程中開啟不止一個數(shù)據(jù)庫連接而產(chǎn)生的死鎖（見圖）。
假設(shè)應(yīng)用有一個線程有這樣的邏輯：
● 開始運行
● 建立數(shù)據(jù)庫連接A，調(diào)用存儲過程ProcA。打開結(jié)果集A。
● 建立數(shù)據(jù)庫連接B，調(diào)用存儲過程ProcB。打開結(jié)果集B。
● 輪流讀取結(jié)果集A、B，整合輸出最終結(jié)果。
● 關(guān)閉結(jié)果集A、B，關(guān)機連接A、B。
● 結(jié)束運行
在正常情況下這樣的設(shè)計看上去沒有問題，但是實際上很脆弱。因為在線程內(nèi)部，這個邏輯是線程執(zhí)行的。假設(shè)存儲過程ProcA是一個事務(wù)，在返回結(jié)果集之前因為一些操作申請了一些排他鎖，而ProcB為了返回結(jié)果又要用到這些鎖，那會發(fā)生什么情況呢？
發(fā)生的情況會是連接A在等線程把連接B上的結(jié)果讀出來，再來處理結(jié)果集A，而連接B等待連接A完成事務(wù)后再釋放鎖。雙方相互等待，產(chǎn)生思索。
1) 兩個線程間的死鎖（見圖）。
如果應(yīng)用有兩個線程，每個線程各開一個數(shù)據(jù)庫連接，那上面的邏輯不會出問題。因為運行ProcA的那個線程會先做完，釋放阻塞住連接B的鎖，讓B也能夠接著跑完。但是假設(shè)有下列邏輯：
線程A：建立數(shù)據(jù)庫連接A，不斷讀取表格A，按條取出記錄，做一定處理后發(fā)給線程B的輸入緩存。
線程B：建立數(shù)據(jù)庫連接B，從輸入緩存讀取數(shù)據(jù)，依據(jù)收到的記錄對表格A進行修改。
這個邏輯會產(chǎn)生什么問題呢？我們知道表格修改會在表上申請一些排他鎖。如果線程A正在讀取這條記錄，修改動作會被阻塞住。這個時候線程B就會進入等待狀態(tài)。但是線程A需要線程B輸入緩存清空后才能寫入。如果線程B還沒來得及清空，它也不得不等待，這時候也會產(chǎn)生死鎖（在SQL Server里是一個阻塞）。
解決方法:
復(fù)雜的程序還可能會出現(xiàn)其他的死鎖形式。為了避免這種死鎖，要在應(yīng)用調(diào)用SQL Server的時候設(shè)置執(zhí)行超時，并寫好錯誤處理機制（參見阻塞原因2）。一旦死鎖發(fā)生，SQL Server的操作在等待一段時間后會因為超時而放棄，并釋放出SQL Server內(nèi)部的資源，解決死鎖。
小結(jié)：應(yīng)更多從程序設(shè)計著手解決阻塞問題
很多用戶有一種誤解，認為阻塞是一個數(shù)據(jù)庫問題。當(dāng)阻塞問題發(fā)生的時候，都希望從數(shù)據(jù)庫層面找到方法，一勞永逸地解決問題?？墒牵枞旧硎菫榱送瓿墒聞?wù)的隔離，是應(yīng)用程序向SQL Server提出的要求。所以很多時候，光從數(shù)據(jù)庫端努力是不能解決阻塞問題的。在應(yīng)用程序?qū)用嬉惨龊芏喙ぷ鳌＠鐟?yīng)用在做連接的時候選擇什么樣的隔離級別，事務(wù)開始和結(jié)束的時間點選擇，連接的建立和回收機制，指令復(fù)雜度的控制等。應(yīng)用程序還應(yīng)該考慮到控制結(jié)果集大小，并及時從SQL Server端取走數(shù)據(jù)。還要考慮SQL Server指令執(zhí)行時間長短控制，以及發(fā)生超時或其他意外后的錯誤處理機制等。尤其是對高并發(fā)量、高響應(yīng)要求的關(guān)鍵業(yè)務(wù)系統(tǒng)，在設(shè)計應(yīng)用時必須要考慮好上面這些關(guān)鍵因素。對于關(guān)鍵的業(yè)務(wù)邏輯，必須逐個審查，保證應(yīng)用選擇的是能夠滿足業(yè)務(wù)需求的最低隔離級別，事務(wù)的大小已經(jīng)控制到了最小的粒度。而運行的語句，也要有良好的數(shù)據(jù)庫設(shè)計，保證它不會隨著數(shù)據(jù)庫的增大和用戶量的增多，占用更多的資源和運行時間。如果做不到這幾點，就會容易發(fā)生應(yīng)用在用戶量比較少，或者數(shù)據(jù)庫比較小的初始階段性能不錯，但是當(dāng)用戶量增長或數(shù)據(jù)量增大以后性能越來越慢的問題。
