SQL Server 隐式轉換引發的躺槍死鎖-程式員需知

在SQL Server的應用開發過程(尤其是二次開發)中可能由于開發人員對表的結構不夠了解,造成開發過程中使用了不合理的方式造成資料庫引擎未按預定執行,以緻影響業務.這是非常值得注意的.這次為大家介紹由于隐式資料類型轉換而造成的死鎖及相應解決方案.

現實中有些程式員/資料庫開發者會根據資料庫的處理機制實作一些應用,如搶座應用,可能會對事務中的查詢加一些列的Hint以細化粒度,實作應用的同時使得影響最低,但也有可能因為一些小細節的欠缺而引發錯誤,進而造成糟糕的使用者體驗.如下面這個例子

生成測試資料

code

create table testlock
(ID varchar(10) primary key clustered,
col1 varchar(20),
col2 char(200))
go----------create test table

declare @i int
set @i = 1
while @i < 100
begin
insert into testlock
select right(replicate('0',10)+ cast(@i as varchar(10)),10),'aaa','fixchar'
set @i = @i+1
end
go----------generate test data      

此時我們打開trace profiler 跟蹤死鎖相關資訊

然後分别在兩個session中運作如下語句

declare @ID nvarchar(10)

begin tran 

select  top 1 @ID = ID from testlock with(updlock, rowlock, readpast)
where col1 = 'aaa'
order by id asc

select  @ID

waitfor delay '00:00:20'

update testlock set col1 = 'bbb' where id = @ID

commit tran      

大約20s後我們可以從trace 中捕捉到死鎖了如圖1-1

                                                                        圖1-1

問題分析

從死鎖圖中看既然更新既然擁有了自己的鍵鎖為何要其它會話的呢?很明顯,可能期望的鎖粒度擴大了.

進而分析任意一個會話的執行計劃語句發現了異常,最後的更新出現了隐式資料類型轉換,以至于做了額外的聚集表掃描過程,緻使執行更新過程需要所有鍵的U鎖,進而引發了死鎖.

如圖1-2

                                            圖1-2

為什麼會出現隐式轉換呢,通過檢查執行的代碼發現"declare @ID nvarchar(10)"

 而表testlock中ID的定義是varchar(10) 問題就出在這裡.

這裡介紹一個小的知識點:資料類型優先級

當運算符表達式中資料類型不同時,按照類型的優先級低優先級的向高優先級的資料類型轉換.當然如果兩個資料類型不支援隐式轉換則失敗報錯.

通過資料類型優先級清單發現nvarchar是高于varchar的,是以varchar将向nvarchar轉換,進而使優化器選擇了意料之外的執行計劃,進而引發了死鎖

如圖1-3

            圖1-3

詳細參考

https://msdn.microsoft.com/zh-cn/library/ms190309.aspx

解決

找到問題的根源了,解決起來也就簡單了,我們隻需将查詢中定義的declare @ID nvarchar(10)

調整為varchar即可(甚至char,通過優先級清單可知,char低于varchar.)

declare @ID varchar(10)

begin tran 

select  top 1 @ID = ID from testlock with(updlock, rowlock, readpast)
where col1 = 'aaa'
order by id asc

select  @ID

waitfor delay '00:00:20'

update testlock set col1 = 'bbb' where id = @ID

commit tran      

我們可以看到相應的執行計劃發生了改變,我們期待的執行計劃出現了.如圖1-4

                                 圖1-4

至此,問題解決.

注意:雖然有資料優先級,但建議大家在做開發時,定義的變量要與目标表的資料類型一緻,從根源上避免隐式轉換.

結語:一個小小的字元當真是可以引發血案,在做應用開發中我們需要知道每個字元的深刻含義.

後記:部落格内容發表後有熱心的朋友@Yaoquan.Luo,@victor596,@uestc小田,@Sonnyxue 測試發現在SQL2008R2中并未有死鎖出現,由此給大家帶來的困惑深表歉意.這裡為大家解釋下原因

SQL Server優化器特性-動态檢索

有陣子沒寫部落格了,家裡有個小孩,目前時間不算充裕,但我會堅持下去的,各位的同學的支援就是我的動力!最後給大家拜個早年,祝大家羊年大吉,錢途無量!

Involuntary DBA