SQL2005中的事務(wù)與鎖定（七）

2019-11-03 08:36:10

字體：大中小

供稿：網(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)載請(qǐng)注明出處，更多請(qǐng)關(guān)注：http://blog.csdn.net/happyflystone

--       關(guān)鍵字：　鎖升級(jí)、禁止升級(jí)　行鎖與頁鎖動(dòng)態(tài)管理鎖定　死鎖

------------------------------------------------------------------------

    上一篇對(duì)鎖定資源進(jìn)行了深入，并對(duì)實(shí)體類型、鎖的本質(zhì)、生命周期等相關(guān)知識(shí)進(jìn)行了討論，這一篇我們再深入對(duì)敏感話題鎖的升級(jí)及死鎖進(jìn)行梳理。我們經(jīng)常看見有人發(fā)帖數(shù)據(jù)庫死鎖了，那死鎖有哪些類型，如果查看？如何處理？。。。。。。。

6、鎖升級(jí) 禁止升級(jí)　

       鎖定粒度是一個(gè)查詢或更新所鎖定的最小數(shù)據(jù)，粒度不同數(shù)據(jù)庫的性能和并發(fā)能力是此消彼長的，怎么來理解呢？鎖定的粒度越小并發(fā)的用戶數(shù)越多，這是顯而易的，如果這時(shí)發(fā)生一種情況，根據(jù)業(yè)務(wù)規(guī)律要鎖定大量的記錄行來進(jìn)行更新，在保持并發(fā)用戶的前提下，我們鎖定的記錄的行鎖或鍵鎖就很多，我們知道鎖定不是免費(fèi)的午餐，是要付出代價(jià)的，管理的鎖定多越多系統(tǒng)資源開銷就越大。還記得我們在前面介紹過鎖塊吧，鎖塊是一個(gè)64/128(128是64位操作系統(tǒng))字節(jié)的內(nèi)存塊，另外對(duì)每一個(gè)申請(qǐng)或正持有鎖塊的進(jìn)程還要準(zhǔn)備一個(gè)32/64(64是64位操作系統(tǒng))字節(jié)的內(nèi)存塊來描述這些進(jìn)程，在這兒我們確定一個(gè)前提：不管鎖定粒度的大小，每一個(gè)鎖定都占用幾乎同樣的系統(tǒng)開銷。好，比如我們要進(jìn)行10W行數(shù)據(jù)更新，為了并發(fā)我們都采用行鎖來鎖定，按照鎖塊的定義那么我們就得需要64B * 100000+N*32B= 6400000B +32NB(理論更新我們?nèi)=1相對(duì)于6400000可以忽略)> 6.4M的RAM來管理這些行鎖，假設(shè)并發(fā)進(jìn)程(當(dāng)然是不同資源上的)數(shù)量是X，那么當(dāng)前數(shù)據(jù)庫就得要X*6.4M的RAM用于管理鎖定，顯然這種對(duì)RAM需求的上升是系統(tǒng)無法忍受，不可能無限制的滿足的這種增長，那么SQLSERVER得用一種辦法來防止系統(tǒng)使用太多的內(nèi)存來追蹤鎖定并且提高鎖定的效率。這個(gè)任務(wù)交給了鎖管理器，它負(fù)責(zé)平衡資源的使用(當(dāng)然還負(fù)責(zé)從特定操作的開始到結(jié)束保持連續(xù)、邏輯完整性)，這時(shí)管理器就采取鎖定升級(jí)這一明智造擇，從行鎖或鍵鎖或頁鎖升級(jí)為表級(jí)鎖定，比較6.4M和96B，顯然獲取一個(gè)表級(jí)鎖定比持有許多行或鍵鎖更有意義。

       鎖升級(jí)的意義是顯而易見，使得鎖定開銷下降并避免系統(tǒng)資源耗盡。在結(jié)構(gòu)引擎里我們提及鎖管理器，系統(tǒng)分配給鎖管理器的內(nèi)存是有限的，鎖的升級(jí)保證了鎖定占用內(nèi)存維持一個(gè)合理的限度。

       鎖升級(jí)發(fā)生的時(shí)機(jī)：

1、  在一個(gè)對(duì)象上一個(gè)查詢或更新持有鎖的數(shù)量超過閥值。SQL2005缺省是5000個(gè)鎖(記得SQL6.0只有200個(gè)，但是我們要記住SQL6.0只有頁面鎖定哦)。

2、  鎖資源占用的內(nèi)存超過AWE或常規(guī)內(nèi)存的40%，40%是一個(gè)約數(shù)。

時(shí)機(jī)一滿足SQLSERVER就會(huì)嘗試鎖升級(jí)，當(dāng)然升級(jí)不一定會(huì)成功，當(dāng)失敗后在同一個(gè)對(duì)象上的鎖資源再次上升到一定程度時(shí)升級(jí)會(huì)再次發(fā)生，如果升級(jí)成功SQLSERVER會(huì)釋放對(duì)象上先前獲得的行、鍵、分頁鎖定。升級(jí)失敗發(fā)生當(dāng)另外一個(gè)進(jìn)程對(duì)表有行或頁有排它鎖定時(shí)。

鎖升級(jí)潛在的危險(xiǎn)：

1、  鎖升級(jí)的結(jié)果一定是一個(gè)完全表級(jí)鎖定，也就是不可能出現(xiàn)行鎖升級(jí)為頁鎖的，最細(xì)的行級(jí)鎖升級(jí)的直接結(jié)果一定是表鎖定。

2、  鎖升級(jí)可能造成意外的阻塞(這個(gè)應(yīng)該是很好理解的)

3、  鎖升級(jí)成功后無法降級(jí)

禁止升級(jí) 我們知道鎖升級(jí)是有潛在的危險(xiǎn)，并且這種升級(jí)的結(jié)果是不可能現(xiàn)降級(jí)除非事務(wù)結(jié)束。所以升級(jí)不是對(duì)所有的應(yīng)用都是一件好事，MS提供了兩個(gè)開關(guān)項(xiàng)：1211和1224，我們可以通過設(shè)置跟蹤標(biāo)識(shí)來禁止升級(jí)。

7、行鎖、頁鎖

       7.0之前的版本鎖定的最小粒度就是頁鎖，提醒大家一下那時(shí)的頁面最小單位是2K，如果細(xì)心部署一定程度上是可以滿足夠大的呑吐量和可以接受的響應(yīng)時(shí)間。然后7.0后把分頁從2KB提升為8KB時(shí)(為什么要提升呢？嘿嘿，留一個(gè)疑問給大家)，這種頁面鎖定對(duì)并發(fā)能力是一種挑戰(zhàn)，也就是鎖定的范圍是7.0之前的4倍，這時(shí)并發(fā)及響應(yīng)時(shí)間都成一個(gè)問題。SQL2005完全實(shí)現(xiàn)行級(jí)鎖定，顯然這對(duì)并發(fā)響應(yīng)是可喜的，可是正如我在鎖升級(jí)里給大家算的一筆帳，在有限可利用的鎖定資源前提下，大量行級(jí)鎖定的代價(jià)還是讓人無法接受的，特別在極限的狀態(tài)下。

       我們知道鎖定操作是一個(gè)密集型操作，一個(gè)鎖定不僅要看到內(nèi)存的損耗，還要看到SQLSERVER管理這些鎖定對(duì)其本身來也是一種負(fù)荷。雖然SQL內(nèi)部使用閂或自旋鎖來降低這種負(fù)荷，但我們很容易可以想像管理一個(gè)分頁鎖定比管理N個(gè)行級(jí)鎖定(假設(shè)頁面內(nèi)有N行記錄)更輕松、更有效率。

       比較行鎖和頁鎖，行鎖降低了并發(fā)沖突但是資源的損耗也是顯然的，頁鎖減少必須存在鎖的數(shù)量及管理這些鎖定的資源損耗但是以并發(fā)能下降為代價(jià)的。到底哪個(gè)更合適，恐怕不是一句兩句能說完的，因?yàn)獒槍?duì)不同應(yīng)用、不同行業(yè)、不同并發(fā)模型、不同隔離兩都各有各的優(yōu)勢。

       在SQLSERVER2005可以用sp_indexoption來控制索引的鎖定單位。關(guān)于這個(gè)設(shè)置我們可以看看聯(lián)機(jī)幫助，但是一定要注意它只針對(duì)索引所以對(duì)堆表無法控制分頁鎖定。

8、動(dòng)態(tài)管理鎖定

       SQL造擇鎖定類型、粒度是基于行數(shù)、可能掃描的頁面數(shù)、分頁上的行數(shù)，隔離級(jí)別、進(jìn)行的何種操作、可使用的系統(tǒng)資源等因素的影響，根據(jù)這些影響因素SQLSERVER選擇一種合適的鎖定模式這個(gè)過程稱動(dòng)態(tài)鎖定策略(我發(fā)現(xiàn)策略在MS很流行)，數(shù)據(jù)庫引擎(還有印象我有引擎結(jié)構(gòu)中介紹的存儲(chǔ)引擎吧)動(dòng)態(tài)的管理粒度和鎖定模式，控制鎖定與系統(tǒng)資源的最佳成本效率。一個(gè)范圍內(nèi)的鎖定所要使用的系統(tǒng)資源肯定小，但是系統(tǒng)的并發(fā)性也就降低，如果選擇小范圍內(nèi)的鎖定，那管理鎖定所使用的系統(tǒng)資源上升，然而并發(fā)性能卻得到了淋漓發(fā)揮。

       一般情況我們可使用系統(tǒng)缺省設(shè)置(行級(jí)鎖定是系統(tǒng)缺省的)，讓系統(tǒng)決定是否要進(jìn)行鎖定的升級(jí)。這樣一來簡化我們對(duì)庫鎖定的管理，系統(tǒng)根據(jù)實(shí)際情況平衡負(fù)載。

9、死鎖

首先，我們得清楚死鎖與等待是兩回事。等待是當(dāng)前進(jìn)程所需要的資源讓另一個(gè)進(jìn)程排它了，只要另外一個(gè)進(jìn)程釋放，當(dāng)時(shí)進(jìn)程就可以繼續(xù)執(zhí)行(當(dāng)然如果另外這個(gè)進(jìn)程已經(jīng)死鎖那會(huì)進(jìn)入無限期等待，但是這種情況一般不會(huì)發(fā)生，因?yàn)镾QLSERVER會(huì)干預(yù)死鎖的。另外我們還有一個(gè)鎖定超時(shí)設(shè)置，這方面大家可以看聯(lián)機(jī)叢書)。而死鎖是發(fā)生在兩個(gè)進(jìn)程間，在沒有人為干預(yù)兩個(gè)鎖定的進(jìn)程是都無法繼續(xù)工作的一種困境。另外一個(gè)顯著的地方就是死鎖一旦發(fā)生，SQLSERVER就會(huì)干預(yù)進(jìn)來，我們所能感知比如接收到1205號(hào)錯(cuò)誤，健壯的應(yīng)用系統(tǒng)會(huì)人工干預(yù)1205錯(cuò)誤，恰當(dāng)?shù)闹匦绿峤慌幚恚?dāng)1205錯(cuò)誤發(fā)生沒有終止的進(jìn)程獲得相應(yīng)的資源并處理自己的事務(wù)直至釋放資源，其實(shí)這種人為的干預(yù)潛在的又為死鎖提供一個(gè)外在環(huán)境。當(dāng)然我們前面寫的一個(gè)過程也可以查詢到相應(yīng)的鎖定信息。

接著，死鎖是無法完全避免的。在一個(gè)并發(fā)的多用戶系統(tǒng)，鎖定、線程、內(nèi)存、并行查詢、MARS中死鎖的發(fā)生是正常的、可以預(yù)見的，也是必然的。在我們能力范圍內(nèi)只能盡可能的在應(yīng)用端或服務(wù)器上恰當(dāng)?shù)奶幚硭梨i，使得這種無法完全避免的事件給系統(tǒng)帶來的影響降到最低。也就是我們應(yīng)該明白：死鎖是無法完全避免，但是我可以降低發(fā)生的次數(shù)。

第三，死鎖是一種末日，沒有人為干預(yù)時(shí)永遠(yuǎn)退不出這種狀態(tài)。一個(gè)并發(fā)的多用戶系統(tǒng)這種競爭資源的可能性是很大的，一有競爭就會(huì)有“矛盾”發(fā)生，雙方等待對(duì)方釋放自己所需要的資源，必然成了無限期等待，這種等待就是我們所說的死鎖。我們通過上面的介紹知道這時(shí)SQLSERVER鎖管理器會(huì)干預(yù)這個(gè)過程，試想如果沒有SQLSERVER鎖管理器的干預(yù)那么兩個(gè)進(jìn)程一根筯的結(jié)果就是無限期等待，對(duì)于應(yīng)用系統(tǒng)來說就是一個(gè)末日。SQLSERVER2005更是提供了豐富的鎖有關(guān)元數(shù)據(jù)，可以很方便的偵察出鎖定信息，SQLSERVER鎖管理器干預(yù)的結(jié)果就是根據(jù)犧牲品的優(yōu)先等級(jí)及回滾代價(jià)，把優(yōu)先級(jí)低和代價(jià)最小的進(jìn)程當(dāng)作犧牲品，殺掉這個(gè)進(jìn)程并拋出1205錯(cuò)誤。

第四，死鎖大體分為三類：cycle死鎖、conversion死鎖、應(yīng)用級(jí)死鎖及不明死鎖。

Cycle死鎖：是進(jìn)程雙方持有的排它性資源是另外一方想要的資源。比如說進(jìn)程A擁有TA的表級(jí)排它鎖這時(shí)它又想申請(qǐng)TB的排它鎖，同時(shí)進(jìn)程B先擁有TB的排它鎖定也想申請(qǐng)TA的表級(jí)排它鎖定，這是進(jìn)程A想要TB的排它鎖但是已經(jīng)讓進(jìn)程B鎖定，進(jìn)程B想要的TA鎖定也已經(jīng)讓進(jìn)程B鎖定，這時(shí)死鎖發(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上的鎖定，行號(hào)為20和25的記錄我們可以看到分別獲得了排它鎖定（我僅說明RID,在表和頁上也會(huì)相應(yīng)的鎖定），因?yàn)椴樵円恍袌?zhí)行，那么5S后，它想更新TA，這時(shí)阻塞發(fā)生，我們從記錄26可以看到SPID＝53的進(jìn)程想要獲取表TA上行的更新鎖定被SPID＝52的進(jìn)程阻塞，而進(jìn)入等待狀，注意這時(shí)不是死鎖哦，是等待哦，緊接查詢二5S過了開始執(zhí)行更新TB上的記錄行，顯然這個(gè)時(shí)候TB給進(jìn)程53排它鎖定，這是死鎖發(fā)生，因?yàn)檫M(jìn)程52等待53釋放TB上的鎖定，進(jìn)程53在等待52釋放資源，進(jìn)入了抱死狀態(tài)，這就是cycle鎖定。這時(shí)我們會(huì)SQLSERVER參與了干預(yù)，查詢二拋出錯(cuò)誤并回滾事務(wù)：

消息1205，級(jí)別13，狀態(tài)45，第8 行

事務(wù)(進(jìn)程ID 52)與另一個(gè)進(jìn)程被死鎖在鎖資源上，并且已被選作死鎖犧牲品。請(qǐng)重新運(yùn)行該事務(wù)。

為什么說它回滾呢，這是死鎖發(fā)生時(shí)SQLSERVER自動(dòng)處理的，不信你最后提交查詢二的commit語句，你會(huì)發(fā)現(xiàn)報(bào)如下錯(cuò)（3902）：

消息3902，級(jí)別16，狀態(tài)1，第1 行

COMMIT TRANSACTION 請(qǐng)求沒有對(duì)應(yīng)的BEGIN TRANSACTION。

conversion死鎖：轉(zhuǎn)換死鎖發(fā)生在不同進(jìn)程在查詢相同的數(shù)據(jù)后準(zhǔn)備嘗試更新剛才查詢的數(shù)據(jù)時(shí)，這時(shí)大家都持相同數(shù)據(jù)的共享鎖定并都準(zhǔn)備升級(jí)為更新鎖，但是都因?yàn)閷?duì)方不釋放共享鎖定而無法獲取更新鎖定，這是死鎖發(fā)生，我們稱這個(gè)為轉(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錯(cuò)誤

commit tran

圖conversion1：

圖conversion2：

查詢二死鎖1205信息：

消息1205，級(jí)別13，狀態(tài)45，第7 行

事務(wù)(進(jìn)程ID 52)與另一個(gè)進(jìn)程被死鎖在鎖資源上，并且已被選作死鎖犧牲品。請(qǐng)重新運(yùn)行該事務(wù)。

應(yīng)用級(jí)死鎖及不明死鎖：這類死鎖有點(diǎn)特殊，除了綁定外我們可能通過動(dòng)態(tài)管理視圖或函數(shù)是無法偵測到鎖定信息，顯然鎖管理器可能也無法干預(yù)它。我們所能看到可能是一個(gè)長期占用的資源鎖定，而這種鎖定潛在造成更多死鎖發(fā)生。比如說同一個(gè)應(yīng)用程序的多線程間、應(yīng)用程序調(diào)用外部程序等如果最終再與數(shù)據(jù)庫發(fā)生聯(lián)系，那顯然還是數(shù)據(jù)庫兩個(gè)進(jìn)程間的死鎖，如果與非數(shù)據(jù)庫聯(lián)系，那么這種依賴于其它程序的狀態(tài)鎖定只能是無限期的等待，除非人為干預(yù)SQLSERVER是干預(yù)不了的。

我們把SQLSERVER鎖管理器無法偵測的死鎖稱不明死鎖，這類死鎖可以借助SSIS來模擬，比如我們要完成一項(xiàng)任務(wù)就是把未導(dǎo)出的數(shù)據(jù)通過SSIS生成文件，并把已經(jīng)導(dǎo)出的數(shù)據(jù)做上標(biāo)識(shí)。我們用一個(gè)過程模擬插入數(shù)據(jù)，并在事務(wù)提交前啟動(dòng)SSIS包完成數(shù)據(jù)導(dǎo)出并修改標(biāo)識(shí)，這時(shí)我們?nèi)绻胍谝粋€(gè)事務(wù)里完成所有工作那是不可能的，會(huì)造成事務(wù)一直運(yùn)行，并且我們鎖定管理器也無法偵測。

第五，鎖管理器：在SQLSERVER中一有獨(dú)立線程周期性的檢查系統(tǒng)的死鎖，當(dāng)死鎖發(fā)生時(shí)，死鎖的檢查周期縮小到毫秒級(jí)，直到死鎖的頻率降低再次恢復(fù)到默認(rèn)的周期。如果偵測到鎖定，管理器會(huì)權(quán)衡回滾的代價(jià)，并依據(jù)是否已經(jīng)標(biāo)識(shí)為回滾引起不明狀態(tài)及犧牲品的優(yōu)先級(jí)別選擇犧牲品，殺掉進(jìn)程并發(fā)送1205錯(cuò)誤，這也就意思犧牲品所占的資源全部釋放，這樣其它相關(guān)的進(jìn)程可以繼續(xù)運(yùn)行。

最后提一上我們先前提到的閂及自旋鎖定，它們是預(yù)防死鎖而不是解決死鎖，這兩種輕量級(jí)的鎖定之所以能預(yù)防死鎖是因?yàn)镸S嚴(yán)謹(jǐn)?shù)目刂七_(dá)到不需要管理死鎖。

綜上所述，死鎖是無法完全避免的，對(duì)于SQLSERVER所能偵測的死鎖還是比較容易處理的，恰當(dāng)?shù)淖龊贸鲥e(cuò)后的處理使得對(duì)死鎖相關(guān)的用戶進(jìn)程的影響降到最低。所謂的愉當(dāng)就是接受到1205錯(cuò)誤時(shí)應(yīng)用程序應(yīng)能夠再次提交處理或提醒1205錯(cuò)誤的用戶進(jìn)行相應(yīng)處理。我們還可以做的一件事就是盡量回避死鎖，回避死鎖可以從下面幾點(diǎn)出發(fā)：

l         事務(wù)盡可能的短，鎖定時(shí)間就會(huì)短；

l         應(yīng)用程序做好死鎖發(fā)生后處理；

l         認(rèn)識(shí)索引的重要性；

l         保證業(yè)務(wù)規(guī)則及執(zhí)行順序的合理性、可實(shí)現(xiàn)性；

l         根據(jù)業(yè)務(wù)規(guī)則選擇合適隔離等級(jí)；

l         事務(wù)有始有終(dbcc opentran & set xaxt_abort on);

l         避免人為干擾事務(wù)的執(zhí)行(不要在事務(wù)中人機(jī)交互過程)；

l         慎用鎖定提示(Lock Hint)來改變鎖定的粒度；

l         正確認(rèn)識(shí)使用綁定；

鎖定機(jī)制是一個(gè)很復(fù)雜的過程，它保證了并發(fā)下資源的正確、有序使用，在了解鎖定的機(jī)制后對(duì)跟蹤解決死鎖是有相當(dāng)?shù)膸椭Ｏ旅嫖覀冊偈崂硪幌?005的行版本控制。

上一篇：如何在SQL Server 2005中為安裝程序增加計(jì)數(shù)器注冊表項(xiàng)值

下一篇：SQL Server2005 表分區(qū)三步曲