深入淺出解析mssql在高頻,高并發(fā)訪問時鍵查找死鎖問題
死鎖對于DBA或是數(shù)據(jù)庫開發(fā)人員而言并不陌生,它的引發(fā)多種多樣,一般而言,數(shù)據(jù)庫應(yīng)用的開發(fā)者在設(shè)計時都會有一定的考量進(jìn)而盡量避免死鎖的產(chǎn)生.但有時因為一些特殊應(yīng)用場景如高頻查詢,高并發(fā)查詢下由于數(shù)據(jù)庫設(shè)計的潛在問題,一些不易捕捉的死鎖可能出現(xiàn)從而影響業(yè)務(wù).這里為大家介紹由于設(shè)計問題引起的鍵查找死鎖及相關(guān)的解決辦法.
這里我們在測試的同時開啟trace profiler跟蹤死鎖視圖(locks:deadlock graph).(當(dāng)然也可以開啟跟蹤標(biāo)記,或者應(yīng)用擴(kuò)展事件(xevents)等捕捉死鎖)
創(chuàng)建測試對象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'
開啟會話1 模擬高頻update操作
----模擬高頻update操作
declare @i int set @i=100 while 1=1 begin update testklup set cont1=@i where clskey=1 set @i=@i+1 end
開啟會話2 模擬高頻select操作
----模擬高頻select操作
declare @cont2 char(3000) while 1=1 begin select @cont2=cont2 from testklup where nlskey=1 end
此時開啟會話2執(zhí)行一小段時間時我們就可以看到類似錯誤信息:圖1-1
圖1-1
而在我們開啟的跟蹤中捕捉到了如下的死鎖圖.圖1-2
圖1-2
死鎖分析:可以看出由于讀進(jìn)程(108)請求寫進(jìn)程(79)持有的X鎖被阻塞的同時,寫進(jìn)程(79)又申請讀進(jìn)程(108)鎖持有的S鎖.讀執(zhí)行計劃圖1-3,寫執(zhí)行計劃圖1-4
(由于在默認(rèn)隔離級別下(讀提交)讀申請S鎖只是瞬間過程,讀完立即釋放,不會等待事務(wù)完成),所以在并發(fā),執(zhí)行頻率不高的情形下不易出現(xiàn).但我們模擬的高頻情況使得S鎖獲得頻率非常高,此時就出現(xiàn)了僅僅兩個會話,一個讀,一個寫就造成了死鎖現(xiàn)象.
圖1-3
圖1-4
死鎖原因:讀操作中的鍵查找造成的額外鎖(聚集索引)需求
解決方案:在了解了死鎖產(chǎn)生的原因后,解決起來就比較簡單了.
我們可以從以下幾個方面入手.
a 消除額外的鍵查找鎖需的鎖
b 讀操作時取消獲取鎖
a.1我們可以創(chuàng)建覆蓋索引使select語句中的查詢列包含在指定索引中
CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup] ([nlskey] ASC) INCLUDE ( [cont2])
a.2 根據(jù)查詢需求,分步執(zhí)行,通過聚集索引獲取查詢列,避免鍵查找.
declare @cont2 char(3000) declare @clskey int while 1=1 begin select @clskey=clskey from testklup where nlskey=1 select @cont2=cont2 from testklup where clskey=@clskey end
b 通過改變隔離級別,使用樂觀并發(fā)模式,讀操作時源行無需鎖
declare @cont2 char(3000) while 1=1 begin select @cont2=cont2 from testklup with(nolock) where nlskey=1 end
結(jié)束語.我們在解決問題時,最好弄清問題的本質(zhì)原因,通過問題點尋找出適合自己的環(huán)境的解決方案再實施.
相關(guān)文章
SQL Server 置疑、可疑、正在恢復(fù)等情況分析
有些時候當(dāng)你重啟了數(shù)據(jù)庫服務(wù),會發(fā)現(xiàn)有些數(shù)據(jù)庫變成了正在恢復(fù)、置疑、可疑等情況,這個時候DBA就會很緊張了,下面是一些在實踐中得到證明的方法2011-12-12SQL?Server導(dǎo)入.mdf方法的錯誤處理方法
導(dǎo)入mdf文件和ldf文件到數(shù)據(jù)庫是網(wǎng)站搬家或者是初學(xué)者學(xué)習(xí)源碼是必要的一步,下面這篇文章主要給大家介紹了關(guān)于SQL?Server導(dǎo)入.mdf方法的錯誤處理方法,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-05-05sqlserver復(fù)制數(shù)據(jù)庫的方法步驟(圖文)
這篇文章主要介紹了sqlserver復(fù)制數(shù)據(jù)庫的方法步驟(圖文),文中通過圖文示例介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04SQL Server誤區(qū)30日談 第11天 鏡像在檢測到故障后瞬間就能故障轉(zhuǎn)移
數(shù)據(jù)庫鏡像的故障轉(zhuǎn)移既可以自動發(fā)起,也可以手動發(fā)起2013-01-01SQL Server根據(jù)分區(qū)表名查找所在的文件及文件組實現(xiàn)腳本
這篇文章主要介紹了SQL Server根據(jù)分區(qū)表名查找所在的文件及文件組實現(xiàn)腳本,本文直接給出實現(xiàn)代碼,需要的朋友可以參考下2015-07-07