sql server 2005 2709 出錯(cuò) 是什么意思

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

我們提供的服務(wù)有：成都做網(wǎng)站、成都網(wǎng)站建設(shè)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、新市ssl等。為數(shù)千家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的新市網(wǎng)站制作公司

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

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

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

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

2、 鎖資源占用的內(nèi)存超過(guò)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ì)象上先前獲得的行、鍵、分頁(yè)鎖定。升級(jí)失敗發(fā)生當(dāng)另外一個(gè)進(jìn)程對(duì)表有行或頁(yè)有排它鎖定時(shí)。

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

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

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

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

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

7、行鎖、頁(yè)鎖

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

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

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

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

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

SQL造擇鎖定類型、粒度是基于行數(shù)、可能掃描的頁(yè)面數(shù)、分頁(yè)上的行數(shù)，隔離級(jí)別、進(jìn)行的何種操作、可使用的系統(tǒng)資源等因素的影響 ，根據(jù)這些影響因素SQLSERVER選擇一種合適的鎖定模式這個(gè)過(guò)程稱動(dòng)態(tài)鎖定策略(我發(fā)現(xiàn)策略在MS很流行)，數(shù)據(jù)庫(kù)引擎(還有印象我有引擎結(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í)。這樣一來(lái)簡(jiǎn)化我們對(duì)庫(kù)鎖定的管理，系統(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)入無(wú)限期等待，但是這種情況一般不會(huì)發(fā)生，因?yàn)镾QLSERVER會(huì)干預(yù)死鎖的。另外我們還有一個(gè)鎖定超時(shí)設(shè)置 ，這方面大家可以看聯(lián)機(jī)叢書(shū))。而死鎖是發(fā)生在兩個(gè)進(jìn)程間，在沒(méi)有人為干預(yù)兩個(gè)鎖定的進(jìn)程是都無(wú)法繼續(xù)工作的一種困境。另外一個(gè)顯著的地方就是死鎖一旦發(fā)生，SQLSERVER就會(huì)干預(yù)進(jìn)來(lái)，我們所能感知比如接收到1205號(hào)錯(cuò)誤，健壯的應(yīng)用系統(tǒng)會(huì)人工干預(yù)1205錯(cuò)誤，恰當(dāng)?shù)闹匦绿峤慌幚?，?dāng)1205錯(cuò)誤發(fā)生沒(méi)有終止的進(jìn)程獲得相應(yīng)的資源并處理自己的事務(wù)直至釋放資源，其實(shí)這種人為的干預(yù)潛在的又為死鎖提供一個(gè)外在環(huán)境。當(dāng)然我們前面寫(xiě)的一個(gè)過(guò)程也可以查詢到相應(yīng)的鎖定信息。

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

第三，死鎖是一種末日，沒(méi)有人為干預(yù)時(shí)永遠(yuǎn)退不出這種狀態(tài)。一個(gè)并發(fā)的多用戶系統(tǒng)這種競(jìng)爭(zhēng)資源的可能性是很大的，一有競(jìng)爭(zhēng)就會(huì)有“矛盾”發(fā)生，雙方等待對(duì)方釋放自己所需要的資源，必然成了無(wú)限期等待，這種等待就是我們所說(shuō)的死鎖。我們通過(guò)上面的介紹知道這時(shí)SQLSERVER鎖管理器會(huì)干預(yù)這個(gè)過(guò)程，試想如果沒(méi)有SQLSERVER鎖管理器的干預(yù)那么兩個(gè)進(jìn)程一根筯的結(jié)果就是無(wú)限期等待，對(duì)于應(yīng)用系統(tǒng)來(lái)說(shuō)就是一個(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í)死鎖及不明死鎖。

轉(zhuǎn)SQLSERVER 會(huì)不會(huì)自動(dòng)加鎖

[SQL]提升查詢效率與避免LOCK發(fā)生nolock: 可能把沒(méi)有提交事務(wù)的數(shù)據(jù)也顯示出來(lái)，可能會(huì)產(chǎn)生臟讀readpast： 會(huì)把被鎖住的行不顯示出來(lái)

所有Select加 With (NoLock)解決阻塞死鎖

在查詢語(yǔ)句中使用 NOLOCK 和 READPAST

處理一個(gè)數(shù)據(jù)庫(kù)死鎖的異常時(shí)候，其中一個(gè)建議就是使用 NOLOCK 或者 READPAST 。有關(guān) NOLOCK 和 READPAST的一些技術(shù)知識(shí)點(diǎn)：

對(duì)于非銀行等嚴(yán)格要求事務(wù)的行業(yè)，搜索記錄中出現(xiàn)或者不出現(xiàn)某條記錄，都是在可容忍范圍內(nèi)，所以碰到死鎖，應(yīng)該首先考慮，我們業(yè)務(wù)邏輯是否能容忍出現(xiàn)或者不出現(xiàn)某些記錄，而不是尋求對(duì)雙方都加鎖條件下如何解鎖的問(wèn)題。

NOLOCK 和 READPAST 都是處理查詢、插入、刪除等操作時(shí)候，如何應(yīng)對(duì)鎖住的數(shù)據(jù)記錄。但是這時(shí)候一定要注意NOLOCK 和 READPAST的局限性，確認(rèn)你的業(yè)務(wù)邏輯可以容忍這些記錄的出現(xiàn)或者不出現(xiàn)：

簡(jiǎn)單來(lái)說(shuō)：

NOLOCK 可能把沒(méi)有提交事務(wù)的數(shù)據(jù)也顯示出來(lái)．

READPAST 會(huì)把被鎖住的行不顯示出來(lái)

不使用 NOLOCK 和 READPAST ，在 Select 操作時(shí)候則有可能報(bào)錯(cuò)誤：事務(wù)(進(jìn)程 ID **)與另一個(gè)進(jìn)程被死鎖在 鎖 資源上，并且已被選作死鎖犧牲品。

下面就來(lái)演示這個(gè)情況。

為了演示兩個(gè)事務(wù)死鎖的情況，我們下面的測(cè)試都需要在SQL Server Management Studio中打開(kāi)兩個(gè)查詢窗口。保證事務(wù)不被干擾。

演示一 沒(méi)有提交的事務(wù)，NOLOCK 和 READPAST處理的策略：

查詢窗口一請(qǐng)執(zhí)行如下腳本：

CREATE TABLE t1 (c1 int IDENTITY(1,1), c2 int)

go

BEGIN TRANSACTION

insert t1(c2) values(1)

在查詢窗口一執(zhí)行后，查詢窗口二執(zhí)行如下腳本：

select count(*) from t1 WITH(NOLOCK)

select count(*) from t1 WITH(READPAST)

結(jié)果與分析：

查詢窗口二依次顯示統(tǒng)計(jì)結(jié)果為： 1、0

查詢窗口一的命令沒(méi)有提交事務(wù)，所以 READPAST 不會(huì)計(jì)算沒(méi)有提交事務(wù)的這一條記錄，這一條被鎖住了，READPAST 看不到；而NOLOCK則可以看到被鎖住的這一條記錄。

如果這時(shí)候我們?cè)诓樵兇翱诙袌?zhí)行：

select count(*) from t1 就會(huì)看到這個(gè)執(zhí)行很久不能執(zhí)行完畢，因?yàn)檫@個(gè)查詢遇到了一個(gè)死鎖。

清除掉這個(gè)測(cè)試環(huán)境，需要在查詢窗口一中再執(zhí)行如下語(yǔ)句：

ROLLBACK TRANSACTION

drop table t1

演示二：對(duì)被鎖住的記錄，NOLOCK 和 READPAST處理的策略

這個(gè)演示同樣需要兩個(gè)查詢窗口。

請(qǐng)?jiān)诓樵兇翱谝恢袌?zhí)行如下語(yǔ)句：

CREATE TABLE t2 (UserID int , NickName nvarchar(50))

go

insert t2(UserID,NickName) values(1,'郭紅俊')

insert t2(UserID,NickName) values(2,'蟈蟈俊')

go

BEGIN TRANSACTION

update t2 set NickName = '蟈蟈俊.net' where UserID = 2

請(qǐng)?jiān)诓樵兇翱诙袌?zhí)行如下腳本：

select * from t2 WITH(NOLOCK) where UserID = 2

select * from t2 WITH(READPAST) where UserID = 2

結(jié)果與分析：

查詢窗口二中， NOLOCK 對(duì)應(yīng)的查詢結(jié)果中我們看到了修改后的記錄，READPAST對(duì)應(yīng)的查詢結(jié)果中我們沒(méi)有看到任何一條記錄。 這種情況下就可能發(fā)生臟讀

sqlserver 2000 怎么自動(dòng)結(jié)束正在阻塞的事務(wù)

SQL code/*--處理死鎖

查看當(dāng)前進(jìn)程,或死鎖進(jìn)程,并能自動(dòng)殺掉死進(jìn)程

因?yàn)槭轻槍?duì)死的,所以如果有死鎖進(jìn)程,只能查看死鎖進(jìn)程

當(dāng)然,你可以通過(guò)參數(shù)控制,不管有沒(méi)有死鎖,都只查看死鎖進(jìn)程

--鄒建 2004.4--*/

/*--調(diào)用示例

exec p_lockinfo

--*/

create proc p_lockinfo

@kill_lock_spid bit=1, --是否殺掉死鎖的進(jìn)程,1 殺掉, 0 僅顯示

@show_spid_if_nolock bit=1 --如果沒(méi)有死鎖的進(jìn)程,是否顯示正常進(jìn)程信息,1 顯示,0 不顯示

as

declare @count int,@s nvarchar(1000),@i int

select id=identity(int,1,1),標(biāo)志,

進(jìn)程ID=spid,線程ID=kpid,塊進(jìn)程ID=blocked,數(shù)據(jù)庫(kù)ID=dbid,

數(shù)據(jù)庫(kù)名=db_name(dbid),用戶ID=uid,用戶名=loginame,累計(jì)CPU時(shí)間=cpu,

登陸時(shí)間=login_time,打開(kāi)事務(wù)數(shù)=open_tran, 進(jìn)程狀態(tài)=status,

工作站名=hostname,應(yīng)用程序名=program_name,工作站進(jìn)程ID=hostprocess,

域名=nt_domain,網(wǎng)卡地址=net_address

into #t from(

select 標(biāo)志='死鎖的進(jìn)程',

spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,

status,hostname,program_name,hostprocess,nt_domain,net_address,

s1=a.spid,s2=0

from master..sysprocesses a join (

select blocked from master..sysprocesses group by blocked

)b on a.spid=b.blocked where a.blocked=0

union all

select '|_犧牲品_',

spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,

status,hostname,program_name,hostprocess,nt_domain,net_address,

s1=blocked,s2=1

from master..sysprocesses a where blocked0

)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1

begin

insert #t

select 標(biāo)志='正常的進(jìn)程',

spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,

open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address

from master..sysprocesses

set @count=@@rowcount

end

if @count0

begin

create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))

if @kill_lock_spid=1

begin

declare @spid varchar(10),@標(biāo)志 varchar(10)

while @i=@count

begin

select @spid=進(jìn)程ID,@標(biāo)志=標(biāo)志 from #t where id=@i

insert #t1 exec('dbcc inputbuffer('+@spid+')')

if @標(biāo)志='死鎖的進(jìn)程' exec('kill '+@spid)

set @i=@i+1

end

end

else

while @i=@count

begin

select @s='dbcc inputbuffer('+cast(進(jìn)程ID as varchar)+')' from #t where id=@i

insert #t1 exec(@s)

set @i=@i+1

end

select a.*,進(jìn)程的SQL語(yǔ)句=b.EventInfo

from #t a join #t1 b on a.id=b.id

end

go

本文標(biāo)題：關(guān)于sqlserver犧牲品的信息
文章網(wǎng)址：http://chinadenli.net/article10/hohido.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、ChatGPT、網(wǎng)站建設(shè)、域名注冊(cè)、動(dòng)態(tài)網(wǎng)站、網(wǎng)站收錄

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)