sql server中高並發情況下 同時執行select和update語句死結問題 （二）

標籤：

        SQL Server死結使我們經常遇到的問題，資料庫操作的死結是不可避免的，本文並不打算討論死結如何產生，重點在於解決死結。希望對您學習SQL Server死結方面能有所協助。

        死結對於DBA或是資料庫開發人員而言並不陌生,它的引發多種多樣,一般而言,資料庫應用的開發人員在設計時都會有一定的考量進而盡量避免死結的產生.但有時因為一些特殊應用情境如高頻查詢,高並發查詢下由於資料庫設計的潛在問題,一些不易捕捉的死結可能出現從而影響業務.這裡為大家介紹由於設計問題引起的鍵尋找死結及相關的解決辦法.

       這裡我們在測試的同時開啟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 intset @i=100while 1=1 begin update testklup set [email protected] where clskey=1 set @[email protected]+1 end</span></span>

開啟會話2 類比高頻select操作

----類比高頻select操作

<span style="font-size:18px;"><span style="font-size:18px;">declare @cont2 char(3000)while 1=1begin  select @cont2=cont2 from testklup where nlskey=1end</span></span>

此時開啟會話2執行一小段時間時我們就可以看到類似錯誤資訊:圖1-1

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

死結分析:可以看出由於讀進程(108)請求寫進程(79)持有的X鎖被阻塞的同時,寫進程(79)又申請讀進程(108)鎖持有的S鎖.讀執行計畫圖1-3,寫執行計畫圖1-4

(由於在預設隔離等級下(讀提交)讀申請S鎖只是瞬間過程,讀完立即釋放,不會等待事務完成),所以在並發,執行頻率不高的情形下不易出現.但我們類比的高頻情況使得S鎖獲得頻率非常高,此時就出現了僅僅兩個會話,一個讀,一個寫就造成了死結現象.

死結原因:讀操作中的鍵尋找造成的額外鎖(叢集索引)需求

解決方案:在瞭解了死結產生的原因後,解決起來就比較簡單了.

我們可以從以下幾個方面入手.

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 [email protected] 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>

結束語.我們在解決問題時,最好弄清問題的本質原因,通過問題點尋找出適合自己的環境的解決方案再實施.

sql server中高並發情況下 同時執行select和update語句死結問題 （二）