SQL Server阻塞與鎖
在討論阻塞與加鎖之前，需要先理解一些核心概念：併發性、事務、隔離級別、阻塞鎖及死鎖。
併發性是指多個進程在相同時間訪問或者更改共享數據的能力。一般情況而言，一個系統在互不干擾的情況下可以激活的併發用戶的進程數越多，該系統的併發性就越強。就像通常所說的系統性能表現，系統同時處理的併發用戶數越多，說明系統的性能越強。當正在更改數據的進程阻止其他進程讀取該數據時，或者當讀取數據的進程阻止其它進程更改該數據時，併發行會減弱。另外，當多個進程試圖同時更改相同數據時，且無法在不犧牲數據的一致性的前提下都能成功時，併發性也會受到影響。在SQL Server中提供了兩種方法對併發性進行有效的管理：悲觀控制法和樂觀控制法。悲觀控制法認為不同進程將會試圖同時讀寫相同的數據，並且通過鎖來阻塞另一個進程對正在使用的數據進行訪問，即當一個進程讀數據時，需要通過加鎖防止其它進程對它進行寫操作，當一個進程對寫數據時，需要通過加鎖防止其它進程對它進行讀操作，這樣，讀數據進程阻塞寫數據進程，寫數據進程阻塞讀數據進程。樂觀控制方法假設系統中很少數據在讀進程和寫進程中發生交叉的情況，因此不需要對數據進行加鎖操作，這意味著讀進程不會阻塞寫進程，寫進程不會阻塞讀進程。
SQL Server默認設置是悲觀式併發控制，因此為了提高應用程序的併發性，需要對資源加鎖和解鎖，在SQL Server 2005發佈後，可以採用樂觀式的策略，之所以可以採用這種策略，是因為SQL Server可以使用數據行版本控制，允許讀進程讀取的數據是寫進程開始進行修改之前的版本。在進行併發容易產生以下幾種情況的異常：丟失更新、髒讀、不可重複讀、幻影。
1) 丟失更新（Lost Updates）
丟失更新是指兩個進程對同一個數據進行讀取操作，並且同時試圖對該數據值進行更新時，這很容易出現更新初始值錯誤的現象。例如：一個人找兩個朋友借錢，這個人銀行卡帳中的餘額為5000塊，結果這兩個朋友同時向這張銀行卡存款，假設朋友A向這張銀行卡帳戶中存5000塊，朋友B向這張銀行卡帳戶中存6000塊，當朋友A存款完成後，銀行的出納員將銀行卡帳戶中的錢修改為10000塊（5000+5000），當朋友B存款完成後，銀行的出納員將銀行卡帳戶中的錢修改為11000塊（5000+6000），由於出現更新丟失，導致損失了5000塊，這個結果顯然無法接受。
2) 髒讀（Dirty Reads）
髒讀是指對提交的數據進行讀取操作，一個進程對數據進行了修改，但在提交修改後的數據時取消了當前的操作，即沒有更新成功，但另一個進程已經讀取了修改後的值並開始使用它。例如：你有一套房產，最近由於資金緊張，想將該處房產賣掉，這樣房產中介公司的經紀人A則將該信息寫入到公司資源池中（這樣公司所有的房產銷售人員都可以看該信息），但過了一些時間通過另外的方式籌到了這筆資金，你又不打算賣掉這處房產，所以通知經紀人A取消該房產信息，但此時經紀人B已經找到了買家，此時經紀人B讀取到的就是髒數據。
3) 不可重複讀（Non-Repeatable Reads）
不可重複讀是指一個進程在同一個事務中對數據進行了兩次讀取操作，這樣第二次讀到的數據很可能與第一次讀到的數據不一致。例如：超市統計當前8臺收銀機收銀總和，將每臺收銀機的金額讀取出來並相加得到總和，之後再次逐臺收銀機相加檢查統計出來的總和，結果發現這兩次計算出來的結果並不相同，因為在第一次統計結束後，有的收銀機再次收銀了，所以第二次計算出來的結果與第一次計算出來的結果不同。
4) 幻影（Phantoms）
幻影是指一個處理插入或刪除一行數據的事務，在處理該事務時，該數據行在另一個事務可讀取的範圍內。例如：在中國人才熱線上去搜索軟件測試相關職位，假設可以搜索到20家公司，於是說準備好簡歷，向這20家公司投簡歷，然後再按E-mail地址進行搜索，但搜索發現有25個E-mail地址，那麼第一次搜索中沒有找到的職位就是幻影數據行。
事務是數據庫併發控制的基本單元，可以執行一個或多個動作。事務分為顯式事務和隱式事務，顯式事務是顯式的開始一個事務並顯式的滾回或提交事務，除了顯式的事務還有隱式的了，隱式事務是數據庫自己根據情況完成的事務處理，如單獨的select、update、delete、select語句。在關係數據管理系統（Relational Database Management System，RDBMS）中，事務必須滿足ACID屬性。
1) 原子性（Atomicity）
一個事務是一個整體所有的動作都發生並都被執行，要麼全部執行成功，如果在執行過程中有一個動作失敗，那麼事務中之前所有的動作都必須回滾，就當之前的動作沒有執行。例如：做一個入庫操作，在這個事務裡，審核入庫單和修改庫存作為一個整體，要不單據變成審核過同時庫存增加相應的值，要不就是單據未審核同時庫存不變。
2) 一致性（Consistency）
一致性是指事務不能違背數據庫的完整性規則，當事務處理結束後，數據庫必須處於一個一致的狀態。例如：庫存的值不能為負數，sex的字段必須是male或female。
3) 隔離性（Isolation）
SQL Server在設計時應該注意其必須有為很多併發用戶供服務，但在從用戶的角度來看，數據集必須看像去就像該用戶是系統中唯一的用戶，每個事務都必須是完全自包含的，並且所作的修改必須對於任何其它事務是不可讀，SQL Server中關於事務的隔離有5種不同的級別，下面的內容中會詳細介紹。
4) 持久性（Durability）
持久性是指事務提交後，必須持久的保存，即使事務處理完成後系統發生故障，如果在事務執行的過程中發生故障那麼事務就會全部撤銷，當SQL Server提交事務成功時，在提交成功的消息返回給用戶之前，需要將相關的信息寫入到事務日誌中。
SQL Server中關於事務的隔離有5種不同的級別：讀取未提交、讀取提交、可重複讀、可串行化和快照；
1) 讀取未提交（Read unconnitted）
這是最低隔離，它可以允許髒讀、不可重複讀和幻影，如果一個事務已經開始寫數據，則另外一個數據不允許同時進行寫操作，只允許對該事務進行讀操作，如果不介意髒讀並且希望以可能的最輕量級接觸來讀取數據，則可以使用該隔離級別，在讀取數據時，這種方式在數據上不加任何鎖。
2) 讀取提交（Read connitted）
這種隔離方式不允許髒讀，但仍可能存在不可重複讀和幻影讀取數據的事務允許其它事務繼續訪問該行數據，但未提交的寫事務則會禁止其它事務訪問該行，這是SQL Server的默認隔離級別，通常可以在性能和業務需求之間提供最佳平衡。當讀操作的語句執行完成後，所持有的鎖都會被釋放，即使是在事務內部也是如此。
3) 可重複讀（Repeatable read）
該隔離可以防止髒讀以及不可重複讀，但是幻影仍然可能發生，讀取數據的事務將會禁止寫事務，但允許讀事務，如果是寫事務則禁止任何其它的事務，如果在事務持續期間保持讀鎖，以防止其它事務修改數據，那麼實現可重複讀也是有可能的。
4) 可串行化（Serializable）
該隔離方式要求事務序列化執行，對數據只能進行串行化訪問，並且在事務持續一直保持著鎖定狀態，這樣可以有效的鎖定那此雖然不存在但位於鍵範圍內的數據行，防止所有的副作用，該隔離程度是最高級別的，不允許高併發性執行。
5) 快照（Snapshot）
快照隔離級別是唯一可用的樂觀的隔離級別，其使用的是數據行版本控制，而不是鎖定，這意味著在一個事務中，由於讀一致性可以通過行版本控制實現，因此同樣的數據總是可以像在序列化級別上一樣被讀取而不必為防止來自其他事務的更改而被鎖定。但是仍然允許更新衝突的發生，這在事務串行化運行中是不會發生的，這種衝突發生在一個快照事務內部即將改變的數據被另外一個事務併發更改時，在執行過程中SQL Server能夠自動進行檢測，如果發生衝突，則快照事務進行回滾，以防止丟失更新。
關於隔離級別與可能的異常關係見表。
如果希望更改默認事務隔離級別，可以使用命令：
set transaction isolation level 隔離級別名
go
例如：如果希望將隔離級別更改為repeatable read（可重複讀），使用的命令如下：
set transaction isolation level repeatable read
go
無論那種模型，鎖在併發控制中是必需的，SQL Server會自動處理鎖，在SQL Server中有三種基本的粒度級別：行鎖、頁鎖和表鎖。
行鎖（row-level lock）：是指鎖定一個數據頁或索引頁中的一行數據。行是可以鎖定的最小空間，行級鎖佔用的數據資源最少，所以在事務的處理過程中，允許其它事務繼續操縱同一個表或者同一個頁的其它數據，大大降低了其它事務等待處理的時間，提高了系統的併發性。
頁鎖（page lock）：用於鎖定一個頁，一個頁由8KB的數據或索引信息組成。在事務的操縱過程中，無論事務處理數據的多少，每一次都鎖定一頁，在這個頁上的數據不能被其它事務操縱，頁級鎖鎖定的資源比行級鎖鎖定的數據資源多，在頁級鎖中，即使是一個事務只操縱頁上的一行數據，那麼該頁上的其它數據行也不能被其它事務使用。
表鎖（table lock）：用於鎖定包含數據與索引的整個表。用於表級別的鎖定。事務在操縱某一個表的數據時，鎖定了這個數據所在的整個表，其它事務不能訪問該表中的其它數據，當事務處理的數據量比較大時，一般使用表級鎖，表級鎖的特點是使用比較少的系統資源，但是卻佔用比較多的數據資源。
在執行過程中獲得每種鎖均佔用一定數量的系統資源，但並不是使用粒度越小的鎖越好，在某個臨界點上，使用一個較大粒度的鎖會更使用多個小粒度的鎖更有效率，當達到該臨界點時，SQL Server將鎖升級為表鎖，這個過程被稱為鎖升級。當事務中某個語句在一個對象上使用的鎖的數量超過5000時，SQL Server將試著升級到表鎖，SQL Server還會在出現內存壓力時試圖升級鎖，升級時都會升級到表鎖，一般情況下，查詢優化器會在創建執行計劃時選擇最合適的鎖粒度，因此不會經常發生鎖升級。
SQL Server支持的鎖定模式包括：共享鎖、排他鎖、更新鎖、架構鎖和意向鎖。
共享鎖（S）：用於保護資源，只允許對其進行只讀數據操作，當資源上存在共享（S）鎖時，其它事務均不能修改數據。共享鎖是非獨佔的，即允許多個併發事務讀取該鎖定的資源，默認情況下，數據被讀取後，SQL Server立即釋放共享鎖。例如，執行查詢“SELECT * FROM TEST”時，首先鎖定第一頁，讀取之後，釋放對第一頁的鎖定，然後鎖定第二頁。這樣，就允許在讀操作過程中，修改未被鎖定的第一頁。但是，事務隔離級別連接選項設置和SELECT語句中的鎖定設置都可以改變SQL Server的這種默認設置。例如，“SELECT * FROM TEST HOLDLOCK”就要求在整個查詢過程中，保持對錶的鎖定，直到查詢完成才釋放鎖定。
排他鎖（X）：是數據修改時需要申請的鎖，例如插入、更新或刪除，確保不能同時對同一資源進行多個更新，只能對數據加上一個排他鎖。如果事務T對數據A加上排他鎖後，則其它事務不能再對A加任何類型的鎖，獲准排他鎖的事務既能讀數據，又能修改數據。
其它的鎖模式要麼是以上兩者的混合，要麼用來對上述的兩種鎖模式提供堅持，主要包括：更新鎖、架構鎖和意向鎖。
更新鎖（U）：防止常見形式的死鎖，當一個進程需要在更新之前讀取數據時需要申請更新鎖，每次只有一個事務可以獲得資源上的更新鎖，如果事務修改資源，則更新鎖將轉換為排他鎖。更新鎖不會阻塞共享鎖。
架構鎖：在執行依賴於表架構的操作時使用。架構鎖的類型是架構修改 (Sch-M) 和架構穩定性 (Sch-S)。執行表的數據定義語言操作時（如添加列或除去表）時，使用架構修改(Sch-M)鎖，當編譯查詢時，使用架構穩定性(Sch-S)鎖，架構穩定性(Sch-S)鎖不阻塞任何事務鎖，包括排它鎖。因此在編譯查詢時，其它事務（包括在表上有排它鎖的事務）都能繼續運行，但不能在表上執行數據定義語言操作。
意向鎖：建立鎖層次結構，這些鎖指示事務正在處理層次結構中較低級別的某些資源，而不是所有資源，較低級別的資源將具有共享鎖、更新鎖或排他鎖。意向鎖說明SQL Server有在資源的低層獲得共享鎖或排它鎖的意向。例如，表級的共享意向鎖說明事務意圖將排它鎖釋放到表中的頁或者行。意向鎖又可以分為共享意向鎖、獨佔意向鎖和共享式獨佔意向鎖。共享意向鎖說明事務意圖在共享意向鎖所鎖定的低層資源上放置共享鎖來讀取數據，獨佔意向鎖說明事務意圖在共享意向鎖所鎖定的低層資源上放置排它鎖來修改數據。共享式排它鎖說明事務允許其他事務使用共享鎖來讀取頂層資源，並意圖在該資源低層上放置排它鎖。
通常有兩種情況會導致加鎖問題：阻塞鎖和死鎖。阻塞鎖是當一個進程對資源進行了加鎖，但該資源已經被另一個進程鎖定，這就產生了鎖衝突，即出現了阻塞，當然SQL Server在處理併發時本身就是採用該方式對資源地行處理，但是如果第一個進程需要等待很長時間才能獲得鎖的話，那麼顯然就產生了性能問題，在工作中還可能產生一條阻塞進程鏈，當統計中發現阻塞鎖非常高的時候，那麼阻塞鎖就成了影響性能的問題了。那麼進程在等待多久獲得鎖資源才是合理的呢？SQL Server有一個鎖超時的原理，默認情況下SQL Server鎖定超時的時間為兩秒鐘，除了嘗試訪問數據並有可能發生超時以外，在對資源進行鎖定之前，沒有辦法測試該資源是否已被鎖定。LOCK_TIMEOUT設置允許應用程序設置語句等待被阻塞資源的最長等待時間，當語句等待的時間超過LOCK_TIMEOUT設置的時間時，將自動取消阻塞的語句，並將錯誤消息 SSCE_M_LOCKTIMEOUT返回給應用程序。如果應用程序不捕獲錯誤，它可以繼續執行，但將不會知道事務中的單個語句已被取消。由於事務中後面的語句可能依賴於未執行的語句，因此可能會出現錯誤。若要設置會話的當前LOCK_TIMEOUT設置，請執行SET LOCK_TIMEOUT語法，如下列代碼示例所示：
SET LOCK_TIMEOUT 2000
死鎖是指當兩個進程各自擁有一個鎖，而這個鎖是對方繼續運行所需要的，這樣就會出現兩個進程相互阻止對方運行的情況，這就會出現死鎖，如果對這種情況不進行處理的話，那麼就將無限期的等待下去，在SQL Server中內置了死鎖探測，每5秒鐘鎖監視器就會檢查死鎖狀態，如果發現死鎖，這個檢查就會變的很頻繁，SQL Server通常選擇終止回滾成本較低的進程來解決死鎖問題。如果是在SQL Server Mobile中則使用鎖定超時處理死鎖，因為SQL Server Mobile中沒有死鎖探測器，如果一個事務試圖鎖定某個資源但無法在鎖定超時時間內實現鎖定，就會出現錯誤。鎖定超時可確保一個事務不會無限期地等待由另一個事務控制的資源，可以使用LOCK_TIMEOUT修改鎖定超時。如果一個死鎖涉及到兩個事務，則其中一個事務會等待資源超時，同時會出現錯誤。超時的事務仍然處於活動狀態；它不會被提交或回滾。另一個事務於是獲得了所需的鎖，然後繼續執行
閱讀更多 川石信息 的文章
