2019獨角獸企業重金招聘Python工程師標準>>>

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

?5種常見的阻塞類型

類型	waittype	open_tran	status	自我修復	原因/其他特征
1	不為0	>=0	runnable	是的，當語句運行結束后	語句運行的時間比較長，運行時需等待某些系統資源（如硬盤讀寫、CPU或內存等）。
2	0x0000	>0	sleeping	不能，但是如果運行 KILL語句，這個鏈接能夠很容易被終止	可能客戶端遇到了一個語句執行超時，或者主動取消了上一語句的執行，但是沒有回滾開啟的事務，在SQL Trace里能夠看到一個Attention事件
3	0x0000 0x0800 0x0063	>=0	runnable	不能。知道客戶端吧所有結果都主動取走，或者主動斷開連接，可以運行KILL語句去終止它，但是可能要花長達30秒	客戶端沒有及時把所有結果都取走，這時可能open_tran=0，事務隔離級別也為默認（READ COMMITTED），但這個連接還會持有鎖資源
4	0x0000	>0	rollback	是的	在SQL Trace里能夠看到這個SPID已經發來了一個Attention事件，說明客戶端已經遇到了超時，或者主動要求回滾事務
5	各種值都有可能	>=0	runnable	不能，直到客戶端取消語句運行或者主動斷開連接。可以運行KILL語句終止它，但是可能要花長達30秒	應用程序運行中產生死鎖，在SQL Server中以阻塞形式體現。Sysprocesses里阻塞和被阻塞的連接hostname值是一樣的

下面詳細介紹這些類型產生的原因，以及解決方法

類型1：由于語句運行時間太長而導致的阻塞，語句本身在正常運行中，只須等待某些系統資源。

解決方法：

要解決這一類阻塞，數據庫管理員需要和數據庫應用設計人員合作，共同解決以下問題。

語句本身有沒有可優化的空間？?
這里包括修改語句本身降低復雜度、修改表格設計、調整索引等。

SQL Server整體性能如何？是不是有資源瓶頸影響了語句執行速度？?
當SQL Server 遇到諸如內存、硬盤讀寫、CPU等資源瓶頸是，原來能很快完成的語句有可能會花很長時間。

如果語句天生就很復雜，無法調優（很多處理報表的語句就是這樣），就須考慮怎樣把這一類應用（一般就是數據倉庫應用）從OLTP系統中隔離出來。

類型2：由于一個未按預期提交的事務導致的阻塞

這一類阻塞的特征，就是問題連接早就進入了空閑狀態（sysprocesses.status=’sleeping’和sysprocesses.cmd=’AWAITING COMMAND’），但是，如果檢查sysprocesses.open_tran，就會發現它不為0，以及事務沒有提交。這類問題很多都是因為應用端遇到一個執行超時，或者其他原因，當時執行的語句被提前終止了，但是連接還保留著。應用沒有跟隨發來的事務提交或回滾指令，導致一個事務被遺留在SQL Server里。

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

這里可以用下面這個實驗來模擬這個問題。在Management Studio里創建一個連接到SQL Server，運行下面的批處理語句：

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

由于使用了HOLDLOCK參數，第一句SELECT會在運行結束后，在表格上維持一個TAB的S鎖。如果批處理全部完成，這個鎖會在提交事務的時候釋放。但是第二句的SELECT會執行很久。請在等待3～4秒鐘以后取消執行。然后運行下面的語句，檢查open_tran和鎖的情況。

SELECT @@TRANCOUNT GO sp_lock GO

通過結果（見圖）可以得知：

(1)???? 批處理被取消的時候，“COMMIT TRAN”這條語句沒有被執行到。SQL Server沒有對“BEGIN TRAN”開啟的那個事務做任何處理，只保持其活動的狀態。

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

現在，如果有其他連接要修改ReadTrace.tblInterestingEvents這張表，就會被阻塞住。

解決辦法：

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

a)?? 在做SQL Server調用的時候，必須加上錯誤捕捉和處理語句

SQL Server客戶端驅動程序（包括ODBC和OLE DB）當語句執行遇到意外終止（包括超時）的時候，都會向應用返回錯誤信息。客戶端在捕捉到錯誤信息時。除了做記錄以外（這對問題定位非常有幫助），還要運行下面這句話，把沒有提交的事務回滾掉。

IF @@TRANCOUNT>0 ROLLBACK TRAN

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

b)?? 設置連接屬性“SET SACT_ABORT ON”

當SET SACT_ABORT為ON時，如果執行T-SQL語句產生運行錯誤，整個事務將會終止并回滾

當SET SACT_ABORT為OFF時，處理方法不是唯一的。有時只回滾產生錯誤的T-SQL語句，而事務將繼續進行處理。如果錯誤很嚴重，及時SET SACT_ABORT 為OFF，也可能回滾整個事務。OFF是默認設置。

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

c)?? 考慮是否要關閉連接池

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

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

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

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

??? 還有一種孤兒事務的來源，是連接開啟了隱式事務（implicit transaction）而沒有加入及時提交事務的機制。如果連接處于隱式事務模式（SET IMPLICIT_TRANSACTIONS ON），并且連接當前不再事務中，則執行下列任何一條語句都會開啟一個新的事務。

ALTER TABLE	FETCH	REVOKE
CREATE	GRANT	SELECT
DELETE	INSERT	TRUNCATE_TABLE
DROP	OPEN	UPDATE

對于因為此設置為ON而自動打開的事務，SQL Server會自動幫你打開事務，但是不會自動幫你提交。用戶必須在該事務結束后將其顯式提交或回滾。否則，當用戶斷開連接時，事務及其包含的所有數據更改將被回滾。事務提交后，執行上述任意一條語句又會啟動一個新事務。隱式事務模式將始終生效，知道連接執行SET IMPLICIT_TRANSACTIONS OFF語句使連接恢復為自動提交模式。在自動提交模式下，所有單個語句在成功完成時將被提交，不會有事務遺留。

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

類型3：由于客戶端沒有及時把結果集取出而導致的語句長時間運行。

?????????語句在SQL Server內執行總時間不僅包含SQL Server的執行時間，還包含把結果集發給客戶端的時間。如果結果集比較大，SQL Server會分幾次打包發出，每發一次，都要等待客戶端的確認。只有確認以后，SQL Server才會發送下一個結果集包。所有結果都發完以后，SQL Server才認為語句執行完畢，釋放執行申請的資源（包括鎖資源）。

???????? 如果處于某種原因，客戶端應用處理結果非常緩慢甚至沒有相應，或者干脆不理睬SQL Server發送結果集的請求，則SQL Server會耐心的等待，因此會導致語句長時間執行而發生阻塞。

解決方法：

在設計程序時，一定要慎重返回大結果集。這種行為不僅會對SQL Server和網絡帶來很大負擔，對應用程序本身來講，也要花很多資源去處理結果集。如果最終用戶只需要部分結果集就可以，則在發送SQL Server指令的時候就要指定好。要避免居于不管三七二十一所有數據都要，而結果集只取走開頭一部分去展示這樣的行為發生。

如果應用程序的確須返回大結果集，例如一些報表系統，則要考慮報表數據庫和生產數據庫分開。

如果1和2在短期內不能實現，可以和最終用戶協商，返回大結果集的連接使用READ UNCOMMITTED事務隔離級別。這樣查詢語句就不會申請S鎖了。

類型4：阻塞的源頭連接一直處于rollback狀態。

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

有些用戶可能等不及，直接重啟SQL Server。當SQL Server關閉的時候，回滾動作會被中斷，SQL Server會被很快關掉，但是這個回滾動作在下次SQL Server重啟的時候會重新開始（數據庫做恢復的時候）。重啟的時候如果回滾不能很快結束，整個數據庫都不可用，可能會帶來更嚴重的后果。

解決方法：

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

類型5：應用程序運行中產生死鎖，在SQL Server中以阻塞形式體現。

一個客戶端的應用在運行過程中會使用到許多資源，包括線程資源，信號量資源，內存資源，IO資源等，SQL Server也是資源之一。如果發生死鎖的兩端不全是SQL Server，SQL Server的死鎖判斷機制可能不起作用。這時如果應用端沒有處理好，可能會永遠等下去。而SQL Server內部的表現可能僅僅是一個阻塞。但是這個阻塞不會自動消除。這樣的阻塞對SQL Server的性能會產生很大影響。

下面我們舉兩個這種應用端死鎖的例子。

1)? 在應用的一個線程中開啟不止一個數據庫連接而產生的死鎖（見圖）。

假設應用有一個線程有這樣的邏輯：

????●? 開始運行

????●? 建立數據庫連接A，調用存儲過程ProcA。打開結果集A。

????●? 建立數據庫連接B，調用存儲過程ProcB。打開結果集B。

????●? 輪流讀取結果集A、B，整合輸出最終結果。

????●? 關閉結果集A、B，關機連接A、B。

????●? 結束運行

在正常情況下這樣的設計看上去沒有問題，但是實際上很脆弱。因為在線程內部，這個邏輯是線程執行的。假設存儲過程ProcA是一個事務，在返回結果集之前因為一些操作申請了一些排他鎖，而ProcB為了返回結果又要用到這些鎖，那會發生什么情況呢？

發生的情況會是連接A在等線程把連接B上的結果讀出來，再來處理結果集A，而連接B等待連接A完成事務后再釋放鎖。雙方相互等待，產生思索。

1)? 兩個線程間的死鎖（見圖）。

如果應用有兩個線程，每個線程各開一個數據庫連接，那上面的邏輯不會出問題。因為運行ProcA的那個線程會先做完，釋放阻塞住連接B的鎖，讓B也能夠接著跑完。但是假設有下列邏輯：

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

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

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

解決方法:

復雜的程序還可能會出現其他的死鎖形式。為了避免這種死鎖，要在應用調用SQL Server的時候設置執行超時，并寫好錯誤處理機制（參見阻塞原因2）。一旦死鎖發生，SQL Server的操作在等待一段時間后會因為超時而放棄，并釋放出SQL Server內部的資源，解決死鎖。

小結：應更多從程序設計著手解決阻塞問題

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

轉載于:https://my.oschina.net/u/200350/blog/671117

總結

以上是生活随笔為你收集整理的需要我们了解的SQL Server阻塞原因与解决方法 - sym_cn的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。