sql server中高并發情況下 同時執行select和update語句死鎖問題 （二）
來源：程序員人生 發布時間：2015-01-13 09:06:23 閱讀次數：5782次
SQL Server死鎖使我們常常遇到的問題，數據庫操作的死鎖是不可避免的，本文其實不打算討論死鎖如何產生，重點在于解決死鎖。希望對您學習SQL Server死鎖方面能有所幫助。
死鎖對DBA或是數據庫開發人員而言其實不陌生,它的引發多種多樣,1般而言,數據庫利用的開發者在設計時都會有1定的考量進而盡可能避免死鎖的產生.但有時由于1些特殊利用場景如高頻查詢,高并發查詢下由于數據庫設計的潛伏問題,1些不容易捕捉的死鎖可能出現從而影響業務.這里為大家介紹由于設計問題引發的鍵查找死鎖及相干的解決辦法.
這里我們在測試的同時開啟trace profiler跟蹤死鎖視圖(locks:deadlock graph).(固然也能夠開啟跟蹤標記,或利用擴大事件(xevents)等捕捉死鎖)
創建測試對象code
<span style="font-size:18px;"><span style="font-size:18px;">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'
</span></span>
開啟會話1 摹擬高頻update操作
----摹擬高頻update操作
<span style="font-size:18px;"><span style="font-size:18px;">declare @i int
set @i=100
while 1=1
begin
update testklup set cont1=@i
where clskey=1
set @i=@i+1
end</span></span>
開啟會話2 摹擬高頻select操作
----摹擬高頻select操作
<span style="font-size:18px;"><span style="font-size:18px;">declare @cont2 char(3000)
while 1=1
begin
select @cont2=cont2 from testklup where nlskey=1
end</span></span>
此時開啟會話2履行1小段時間時我們就能夠看到類似毛病信息:圖1⑴
而在我們開啟的跟蹤中捕捉到了以下的死鎖圖.圖1⑵
死鎖分析:可以看出由于讀進程(108)要求寫進程(79)持有的X鎖被阻塞的同時,寫進程(79)又申請讀進程(108)鎖持有的S鎖.讀履行計劃圖1⑶,寫履行計劃圖1⑷
(由于在默許隔離級別下(讀提交)讀申請S鎖只是瞬間進程,讀完立即釋放,不會等待事務完成),所以在并發,履行頻率不高的情形下不容易出現.但我們摹擬的高頻情況使得S鎖取得頻率非常高,此時就出現了僅僅兩個會話,1個讀,1個寫就造成了死鎖現象.
死鎖緣由:讀操作中的鍵查找釀成的額外鎖(聚集索引)需求
解決方案:在了解了死鎖產生的緣由后,解決起來就比較簡單了.
我們可以從以下幾個方面入手.
a 消除額外的鍵查找鎖需的鎖
b 讀操作時取消獲得鎖
a.1我們可以創建覆蓋索引使select語句中的查詢列包括在指定索引中
<span style="font-size:18px;"><span style="font-size:18px;">CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup] ([nlskey] ASC) INCLUDE ( [cont2])</span></span>
a.2 根據查詢需求,分步履行,通過聚集索引獲得查詢列,避免鍵查找.
<span style="font-size:18px;"><span style="font-size:18px;">declare @cont2 char(3000) declare @clskey intwhile 1=1 begin select @clskey=clskey from testklup where nlskey=1 select @cont2=cont2 from testklup where clskey=@clskey end</span></span>
b 通過改變隔離級別,使用樂觀并發模式,讀操作時源行無需鎖
<span style="font-size:18px;"><span style="font-size:18px;"> declare @cont2 char(3000)
while 1=1
begin
select @cont2=cont2 from testklup with(nolock) where nlskey=1
end </span></span>
結束語.我們在解決問題時,最好弄清問題的本質緣由,通過問題點尋覓出合適自己的環境的解決方案再實行.
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
