SQL2005中的事務與鎖定（七）
2024-07-21 02:44:50
供稿：網友
SQL2005中的事務與鎖定（七）
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-10-21
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- APR 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-- 轉載請注明出處，更多請關注：http://blog.csdn.net/happyflystone
-- 關鍵字： 鎖升級、禁止升級 行鎖與頁鎖 動態管理鎖定 死鎖
------------------------------------------------------------------------
上一篇對鎖定資源進行了深入，并對實體類型、鎖的本質、生命周期等相關知識進行了討論，這一篇我們再深入對敏感話題鎖的升級及死鎖進行梳理。我們經?？匆娪腥税l帖數據庫死鎖了，那死鎖有哪些類型，如果查看？如何處理？。。。。。。。
6、鎖升級 禁止升級
鎖定粒度是一個查詢或更新所鎖定的最小數據，粒度不同數據庫的性能和并發能力是此消彼長的，怎么來理解呢？鎖定的粒度越小并發的用戶數越多，這是顯而易的，如果這時發生一種情況，根據業務規律要鎖定大量的記錄行來進行更新，在保持并發用戶的前提下，我們鎖定的記錄的行鎖或鍵鎖就很多，我們知道鎖定不是免費的午餐，是要付出代價的，管理的鎖定多越多系統資源開銷就越大。還記得我們在前面介紹過鎖塊吧，鎖塊是一個64/128(128是64位操作系統)字節的內存塊，另外對每一個申請或正持有鎖塊的進程還要準備一個32/64(64是64位操作系統)字節的內存塊來描述這些進程，在這兒我們確定一個前提：不管鎖定粒度的大小，每一個鎖定都占用幾乎同樣的系統開銷。好，比如我們要進行10W行數據更新，為了并發我們都采用行鎖來鎖定，按照鎖塊的定義那么我們就得需要64B * 100000+N*32B= 6400000B +32NB(理論更新我們取N=1相對于6400000可以忽略)> 6.4M的RAM來管理這些行鎖，假設并發進程(當然是不同資源上的)數量是X，那么當前數據庫就得要X*6.4M的RAM用于管理鎖定，顯然這種對RAM需求的上升是系統無法忍受，不可能無限制的滿足的這種增長，那么SQLSERVER得用一種辦法來防止系統使用太多的內存來追蹤鎖定并且提高鎖定的效率。這個任務交給了鎖管理器，它負責平衡資源的使用(當然還負責從特定操作的開始到結束保持連續、邏輯完整性)，這時管理器就采取鎖定升級這一明智造擇，從行鎖或鍵鎖或頁鎖升級為表級鎖定，比較6.4M和96B，顯然獲取一個表級鎖定比持有許多行或鍵鎖更有意義。
鎖升級的意義是顯而易見，使得鎖定開銷下降并避免系統資源耗盡。在結構引擎里我們提及鎖管理器，系統分配給鎖管理器的內存是有限的，鎖的升級保證了鎖定占用內存維持一個合理的限度。
鎖升級發生的時機：
1、 在一個對象上一個查詢或更新持有鎖的數量超過閥值。SQL2005缺省是5000個鎖(記得SQL6.0只有200個，但是我們要記住SQL6.0只有頁面鎖定哦)。
2、 鎖資源占用的內存超過AWE或常規內存的40%，40%是一個約數。
時機一滿足SQLSERVER就會嘗試鎖升級，當然升級不一定會成功，當失敗后在同一個對象上的鎖資源再次上升到一定程度時升級會再次發生，如果升級成功SQLSERVER會釋放對象上先前獲得的行、鍵、分頁鎖定。升級失敗發生當另外一個進程對表有行或頁有排它鎖定時。
鎖升級潛在的危險：
1、 鎖升級的結果一定是一個完全表級鎖定，也就是不可能出現行鎖升級為頁鎖的，最細的行級鎖升級的直接結果一定是表鎖定。
2、 鎖升級可能造成意外的阻塞(這個應該是很好理解的)
3、 鎖升級成功后無法降級
禁止升級 我們知道鎖升級是有潛在的危險，并且這種升級的結果是不可能現降級除非事務結束。所以升級不是對所有的應用都是一件好事，MS提供了兩個開關項：1211和1224，我們可以通過設置跟蹤標識來禁止升級。
7、行鎖、頁鎖
7.0之前的版本鎖定的最小粒度就是頁鎖，提醒大家一下那時的頁面最小單位是2K，如果細心部署一定程度上是可以滿足夠大的呑吐量和可以接受的響應時間。然后7.0后把分頁從2KB提升為8KB時(為什么要提升呢？嘿嘿，留一個疑問給大家)，這種頁面鎖定對并發能力是一種挑戰，也就是鎖定的范圍是7.0之前的4倍，這時并發及響應時間都成一個問題。SQL2005完全實現行級鎖定，顯然這對并發響應是可喜的，可是正如我在鎖升級里給大家算的一筆帳，在有限可利用的鎖定資源前提下，大量行級鎖定的代價還是讓人無法接受的，特別在極限的狀態下。
我們知道鎖定操作是一個密集型操作，一個鎖定不僅要看到內存的損耗，還要看到SQLSERVER管理這些鎖定對其本身來也是一種負荷。雖然SQL內部使用閂或自旋鎖來降低這種負荷，但我們很容易可以想像管理一個分頁鎖定比管理N個行級鎖定(假設頁面內有N行記錄)更輕松、更有效率。
比較行鎖和頁鎖，行鎖降低了并發沖突但是資源的損耗也是顯然的，頁鎖減少必須存在鎖的數量及管理這些鎖定的資源損耗但是以并發能下降為代價的。到底哪個更合適，恐怕不是一句兩句能說完的，因為針對不同應用、不同行業、不同并發模型、不同隔離兩都各有各的優勢。
在SQLSERVER2005可以用sp_indexoption來控制索引的鎖定單位。關于這個設置我們可以看看聯機幫助，但是一定要注意它只針對索引所以對堆表無法控制分頁鎖定。
8、動態管理鎖定
SQL造擇鎖定類型、粒度是基于行數、可能掃描的頁面數、分頁上的行數，隔離級別、進行的何種操作、可使用的系統資源等因素的影響 ，根據這些影響因素SQLSERVER選擇一種合適的鎖定模式這個過程稱動態鎖定策略(我發現策略在MS很流行)，數據庫引擎(還有印象我有引擎結構中介紹的存儲引擎吧)動態的管理粒度和鎖定模式，控制鎖定與系統資源的最佳成本效率。一個范圍內的鎖定所要使用的系統資源肯定小，但是系統的并發性也就降低，如果選擇小范圍內的鎖定，那管理鎖定所使用的系統資源上升，然而并發性能卻得到了淋漓發揮。
一般情況我們可使用系統缺省設置(行級鎖定是系統缺省的)，讓系統決定是否要進行鎖定的升級。這樣一來簡化我們對庫鎖定的管理，系統根據實際情況平衡負載。
9、死鎖
首先，我們得清楚死鎖與等待是兩回事。等待是當前進程所需要的資源讓另一個進程排它了，只要另外一個進程釋放，當時進程就可以繼續執行(當然如果另外這個進程已經死鎖那會進入無限期等待，但是這種情況一般不會發生，因為SQLSERVER會干預死鎖的。另外我們還有一個鎖定超時設置 ，這方面大家可以看聯機叢書)。而死鎖是發生在兩個進程間，在沒有人為干預兩個鎖定的進程是都無法繼續工作的一種困境。另外一個顯著的地方就是死鎖一旦發生，SQLSERVER就會干預進來，我們所能感知比如接收到1205號錯誤，健壯的應用系統會人工干預1205錯誤，恰當的重新提交批處理，當1205錯誤發生沒有終止的進程獲得相應的資源并處理自己的事務直至釋放資源，其實這種人為的干預潛在的又為死鎖提供一個外在環境。當然我們前面寫的一個過程也可以查詢到相應的鎖定信息。
接著，死鎖是無法完全避免的。在一個并發的多用戶系統，鎖定、線程、內存、并行查詢、MARS中死鎖的發生是正常的、可以預見的，也是必然的。在我們能力范圍內只能盡可能的在應用端或服務器上恰當的處理死鎖，使得這種無法完全避免的事件給系統帶來的影響降到最低。也就是我們應該明白：死鎖是無法完全避免，但是我可以降低發生的次數。
第三，死鎖是一種末日，沒有人為干預時永遠退不出這種狀態。一個并發的多用戶系統這種競爭資源的可能性是很大的，一有競爭就會有“矛盾”發生，雙方等待對方釋放自己所需要的資源，必然成了無限期等待，這種等待就是我們所說的死鎖。我們通過上面的介紹知道這時SQLSERVER鎖管理器會干預這個過程，試想如果沒有SQLSERVER鎖管理器的干預那么兩個進程一根筯的結果就是無限期等待，對于應用系統來說就是一個末日。SQLSERVER2005更是提供了豐富的鎖有關元數據，可以很方便的偵察出鎖定信息，SQLSERVER鎖管理器干預的結果就是根據犧牲品的優先等級及回滾代價，把優先級低和代價最小的進程當作犧牲品，殺掉這個進程并拋出1205錯誤。
第四，死鎖大體分為三類：cycle死鎖、conversion死鎖、應用級死鎖及不明死鎖。
Cycle死鎖：是進程雙方持有的排它性資源是另外一方想要的資源。比如說進程A擁有TA的表級排它鎖這時它又想申請TB的排它鎖，同時進程B先擁有TB的排它鎖定也想申請TA的表級排它鎖定，這是進程A想要TB的排它鎖但是已經讓進程B鎖定，進程B想要的TA鎖定也已經讓進程B鎖定，這時死鎖發生，下面我們來模擬一下：
create table ta(id int,col varchar(10))
create table tb(id int,col varchar(10))
go
查詢一：
BEGIN TRAN
UPDATE TB SET COL = 'A'
WAITFOR DELAY '00:00:05'
UPDATE TA SET COL = 'B'
--COMMIT TRAN
查詢二：
BEGIN TRAN
UPDATE TA SET COL = 'A'
WAITFOR DELAY '00:00:05'
EXEC SP_US_LOCKINFO –-在死鎖前獲取鎖的信息
UPDATE TB SET COL = 'B'
--COMMIT TRAN
執行順序先運行行查詢一立即切換查詢二執行，得死鎖前的鎖定信息，我們列出部分鎖定信息：
由上圖我們可以看得出，一開始查詢一和查詢二分別獲得了TB TA上的鎖定，行號為20和25的記錄我們可以看到分別獲得了排它鎖定（我僅說明RID,在表和頁上也會相應的鎖定），因為查詢一行執行，那么5S后，它想更新TA，這時阻塞發生，我們從記錄26可以看到SPID＝53的進程想要獲取表TA上行的更新鎖定被SPID＝52的進程阻塞，而進入等待狀，注意這時不是死鎖哦，是等待哦，緊接查詢二5S過了開始執行更新TB上的記錄行，顯然這個時候TB給進程53排它鎖定，這是死鎖發生，因為進程52等待53釋放TB上的鎖定，進程53在等待52釋放資源，進入了抱死狀態，這就是cycle鎖定。這時我們會SQLSERVER參與了干預，查詢二拋出錯誤并回滾事務：
消息1205，級別13，狀態45，第8 行
事務(進程ID 52)與另一個進程被死鎖在鎖資源上，并且已被選作死鎖犧牲品。請重新運行該事務。
為什么說它回滾呢，這是死鎖發生時SQLSERVER自動處理的，不信你最后提交查詢二的commit語句，你會發現報如下錯（3902）：
消息3902，級別16，狀態1，第1 行
COMMIT TRANSACTION 請求沒有對應的BEGIN TRANSACTION。
conversion死鎖：轉換死鎖發生在不同進程在查詢相同的數據后準備嘗試更新剛才查詢的數據時，這時大家都持相同數據的共享鎖定并都準備升級為更新鎖，但是都因為對方不釋放共享鎖定而無法獲取更新鎖定，這是死鎖發生，我們稱這個為轉換死鎖。好下面我們模擬一下conversion死鎖。
(約定：運行查詢一后在3S內運行查詢二)
查詢一：
select @@spid –-53
set transaction isolation level repeatable read
begin tran
select * from ta
waitfor delay '00:00:03'
update ta
set col = 'B'
where id = 1
exec sp_us_lockinfo -–結果見圖conversion1
commit tran
查詢二：
select @@spid –-52
set transaction isolation level repeatable read
begin tran
select * from ta
waitfor delay '00:00:05'
exec sp_us_lockinfo --結果見圖conversion2
update ta
set col = 'B'
where id = 1 –-系統檢測到死鎖，拋出1205錯誤
commit tran
圖conversion1：
圖conversion2：
查詢二死鎖1205信息 ：
消息1205，級別13，狀態45，第7 行
事務(進程ID 52)與另一個進程被死鎖在鎖資源上，并且已被選作死鎖犧牲品。請重新運行該事務。
應用級死鎖及不明死鎖：這類死鎖有點特殊，除了綁定外我們可能通過動態管理視圖或函數是無法偵測到鎖定信息，顯然鎖管理器可能也無法干預它。我們所能看到可能是一個長期占用的資源鎖定，而這種鎖定潛在造成更多死鎖發生。比如說同一個應用程序的多線程間、應用程序調用外部程序等如果最終再與數據庫發生聯系，那顯然還是數據庫兩個進程間的死鎖，如果與非數據庫聯系，那么這種依賴于其它程序的狀態鎖定只能是無限期的等待，除非人為干預SQLSERVER是干預不了的。
我們把SQLSERVER鎖管理器無法偵測的死鎖稱不明死鎖，這類死鎖可以借助SSIS來模擬，比如我們要完成一項任務就是把未導出的數據通過SSIS生成文件，并把已經導出的數據做上標識。我們用一個過程模擬插入數據，并在事務提交前啟動SSIS包完成數據導出并修改標識，這時我們如果想要在一個事務里完成所有工作那是不可能的，會造成事務一直運行，并且我們鎖定管理器也無法偵測。
第五，鎖管理器：在SQLSERVER中一有獨立線程周期性的檢查系統的死鎖，當死鎖發生時，死鎖的檢查周期縮小到毫秒級，直到死鎖的頻率降低再次恢復到默認的周期。如果偵測到鎖定，管理器會權衡回滾的代價，并依據是否已經標識為回滾引起不明狀態及犧牲品的優先級別選擇犧牲品，殺掉進程并發送1205錯誤，這也就意思犧牲品所占的資源全部釋放，這樣其它相關的進程可以繼續運行。
最后提一上我們先前提到的閂及自旋鎖定，它們是預防死鎖而不是解決死鎖，這兩種輕量級的鎖定之所以能預防死鎖是因為MS嚴謹的控制達到不需要管理死鎖。
綜上所述，死鎖是無法完全避免的，對于SQLSERVER所能偵測的死鎖還是比較容易處理的，恰當的做好出錯后的處理使得對死鎖相關的用戶進程的影響降到最低。所謂的愉當就是接受到1205錯誤時應用程序應能夠再次提交處理或提醒1205錯誤的用戶進行相應處理。我們還可以做的一件事就是盡量回避死鎖， 回避死鎖可以從下面幾點出發：
l 事務盡可能的短，鎖定時間就會短；
l 應用程序做好死鎖發生后處理；
l 認識索引的重要性；
l 保證業務規則及執行順序的合理性、可實現性；
l 根據業務規則選擇合適隔離等級；
l 事務有始有終(dbcc opentran & set xaxt_abort on);
l 避免人為干擾事務的執行(不要在事務中人機交互過程)；
l 慎用鎖定提示(Lock Hint)來改變鎖定的粒度；
l 正確認識使用綁定；
鎖定機制是一個很復雜的過程，它保證了并發下資源的正確、有序使用，在了解鎖定的機制后對跟蹤解決死鎖是有相當的幫助。下面我們再梳理一下2005的行版本控制。
