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