在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中運行如下語句
code
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.)
?
code
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
?
至此,問題解決.
注意: 雖然有數據優先級,但建議大家在做開發時,定義的變量要與目標表的數據類型一致,從根源上避免隱式轉換.
結語: 一個小小的字符當真是可以引發血案,在做應用開發中我們需要知道每個字符的深刻含義.
?
有陣子沒寫博客了,家里有個小孩,目前時間不算充裕,但我會堅持下去的,各位的同學的支持就是我的動力!最后給大家拜個早年,祝大家羊年大吉,錢途無量!
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
