如何深入解析mssql在高頻高并發(fā)訪問(wèn)時(shí)鍵查找死鎖問(wèn)題

如何深入解析mssql在高頻高并發(fā)訪問(wèn)時(shí)鍵查找死鎖問(wèn)題，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

死鎖對(duì)于DBA或是數(shù)據(jù)庫(kù)開發(fā)人員而言并不陌生,它的引發(fā)多種多樣,一般而言,數(shù)據(jù)庫(kù)應(yīng)用的開發(fā)者在設(shè)計(jì)時(shí)都會(huì)有一定的考量進(jìn)而盡量避免死鎖的產(chǎn)生.但有時(shí)因?yàn)橐恍┨厥鈶?yīng)用場(chǎng)景如高頻查詢,高并發(fā)查詢下由于數(shù)據(jù)庫(kù)設(shè)計(jì)的潛在問(wèn)題,一些不易捕捉的死鎖可能出現(xiàn)從而影響業(yè)務(wù).這里為大家介紹由于設(shè)計(jì)問(wèn)題引起的鍵查找死鎖及相關(guān)的解決辦法.

這里我們?cè)跍y(cè)試的同時(shí)開啟trace profiler跟蹤死鎖視圖(locks:deadlock graph).(當(dāng)然也可以開啟跟蹤標(biāo)記,或者應(yīng)用擴(kuò)展事件(xevents)等捕捉死鎖)

創(chuàng)建測(cè)試對(duì)象code

create table testklup(clskey int not null,nlskey int not null,cont1 int not null,cont2 char(3000))create unique clustered index inx_cls on testklup(clskey)create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1)insert into testklup select 1,1,100,'aaa'insert into testklup select 2,2,200,'bbb'insert into testklup select 3,3,300,'ccc'

開啟會(huì)話1 模擬高頻update操作

----模擬高頻update操作

 declare @i intset @i=100while 1=1 begin  update testklup set cont1=@i  where clskey=1 set @i=@i+1 end

開啟會(huì)話2 模擬高頻select操作

----模擬高頻select操作

declare @cont2 char(3000)while 1=1begin  select @cont2=cont2 from testklup where nlskey=1end

此時(shí)開啟會(huì)話2執(zhí)行一小段時(shí)間時(shí)我們就可以看到類似錯(cuò)誤信息:圖1-1

圖1-1

而在我們開啟的跟蹤中捕捉到了如下的死鎖圖.圖1-2

圖1-2

死鎖分析:可以看出由于讀進(jìn)程(108)請(qǐng)求寫進(jìn)程(79)持有的X鎖被阻塞的同時(shí),寫進(jìn)程(79)又申請(qǐng)讀進(jìn)程(108)鎖持有的S鎖.讀執(zhí)行計(jì)劃圖1-3,寫執(zhí)行計(jì)劃圖1-4

(由于在默認(rèn)隔離級(jí)別下(讀提交)讀申請(qǐng)S鎖只是瞬間過(guò)程,讀完立即釋放,不會(huì)等待事務(wù)完成),所以在并發(fā),執(zhí)行頻率不高的情形下不易出現(xiàn).但我們模擬的高頻情況使得S鎖獲得頻率非常高,此時(shí)就出現(xiàn)了僅僅兩個(gè)會(huì)話,一個(gè)讀,一個(gè)寫就造成了死鎖現(xiàn)象.

圖1-3

圖1-4死鎖原因:讀操作中的鍵查找造成的額外鎖(聚集索引)需求

解決方案:在了解了死鎖產(chǎn)生的原因后,解決起來(lái)就比較簡(jiǎn)單了.

我們可以從以下幾個(gè)方面入手.

a 消除額外的鍵查找鎖需的鎖

b 讀操作時(shí)取消獲取鎖

a.1我們可以創(chuàng)建覆蓋索引使select語(yǔ)句中的查詢列包含在指定索引中

CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup]([nlskey] ASC) INCLUDE ( [cont2])

a.2 根據(jù)查詢需求,分步執(zhí)行,通過(guò)聚集索引獲取查詢列,避免鍵查找.

declare @cont2 char(3000)declare @clskey intwhile 1=1begin  select @clskey=clskey from testklup where nlskey=1  select @cont2=cont2 from testklup where clskey=@clskeyend

b 通過(guò)改變隔離級(jí)別,使用樂(lè)觀并發(fā)模式,讀操作時(shí)源行無(wú)需鎖

declare @cont2 char(3000)while 1=1begin  select @cont2=cont2 from testklup with(nolock) where nlskey=1end

看完上述內(nèi)容，你們掌握如何深入解析mssql在高頻高并發(fā)訪問(wèn)時(shí)鍵查找死鎖問(wèn)題的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！