在SQL Server的應(yīng)用開發(fā)過程(尤其是二次開發(fā))中可能由于開發(fā)人員對表的結(jié)構(gòu)不夠了解,造成開發(fā)過程中使用了不合理的方式造成數(shù)據(jù)庫引擎未按預(yù)定執(zhí)行,以致影響業(yè)務(wù).這是非常值得注意的.這次為大家介紹由于隱式數(shù)據(jù)類型轉(zhuǎn)換而造成的死鎖及相應(yīng)解決方案.
現(xiàn)實中有些程序員/數(shù)據(jù)庫開發(fā)者會根據(jù)數(shù)據(jù)庫的處理機制實現(xiàn)一些應(yīng)用,如搶座應(yīng)用,可能會對事務(wù)中的查詢加一些列的Hint以細(xì)化粒度,實現(xiàn)應(yīng)用的同時使得影響最低,但也有可能因為一些小細(xì)節(jié)的欠缺而引發(fā)錯誤,從而造成糟糕的用戶體驗.如下面這個例子
生成測試數(shù)據(jù)
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 跟蹤死鎖相關(guān)信息
然后分別在兩個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
?
問題分析
從死鎖圖中看既然更新既然擁有了自己的鍵鎖為何要其它會話的呢?很明顯,可能期望的鎖粒度擴大了.
進而分析任意一個會話的執(zhí)行計劃語句發(fā)現(xiàn)了異常,最后的更新出現(xiàn)了隱式數(shù)據(jù)類型轉(zhuǎn)換,以至于做了額外的聚集表掃描過程,致使執(zhí)行更新過程需要所有鍵的U鎖,從而引發(fā)了死鎖.
如圖1-2
?
??????????????????????????????????????????? 圖1-2
?
為什么會出現(xiàn)隱式轉(zhuǎn)換呢,通過檢查執(zhí)行的代碼發(fā)現(xiàn)"declare @ID nvarchar(10) "
?而表testlock中ID的定義是varchar(10) 問題就出在這里.
?
這里介紹一個小的知識點: 數(shù)據(jù)類型優(yōu)先級
當(dāng)運算符表達式中數(shù)據(jù)類型不同時,按照類型的優(yōu)先級低優(yōu)先級的向高優(yōu)先級的數(shù)據(jù)類型轉(zhuǎn)換.當(dāng)然如果兩個數(shù)據(jù)類型不支持隱式轉(zhuǎn)換則失敗報錯.
通過數(shù)據(jù)類型優(yōu)先級列表發(fā)現(xiàn)nvarchar是高于varchar的,所以varchar將向nvarchar轉(zhuǎn)換,進而使優(yōu)化器選擇了意料之外的執(zhí)行計劃,從而引發(fā)了死鎖
如圖1-3
?
??????????? 圖1-3
?
詳細(xì)參考
https://msdn.microsoft.com/zh-cn/library/ms190309.aspx
?
解決
找到問題的根源了,解決起來也就簡單了,我們只需將查詢中定義的declare @ID nvarchar(10)
調(diào)整為 varchar 即可(甚至char,通過優(yōu)先級列表可知,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
我們可以看到相應(yīng)的執(zhí)行計劃發(fā)生了改變,我們期待的執(zhí)行計劃出現(xiàn)了.如圖1-4
?
???????????????????????????????? 圖1-4
?
至此,問題解決.
注意: 雖然有數(shù)據(jù)優(yōu)先級,但建議大家在做開發(fā)時,定義的變量要與目標(biāo)表的數(shù)據(jù)類型一致,從根源上避免隱式轉(zhuǎn)換.
結(jié)語: 一個小小的字符當(dāng)真是可以引發(fā)血案,在做應(yīng)用開發(fā)中我們需要知道每個字符的深刻含義.
?
有陣子沒寫博客了,家里有個小孩,目前時間不算充裕,但我會堅持下去的,各位的同學(xué)的支持就是我的動力!最后給大家拜個早年,祝大家羊年大吉,錢途無量!
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
