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中运行如下语句

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

 

至此,问题解决.

注意:虽然有数据优先级,但建议大家在做开发时,定义的变量要与目标表的数据类型一致,从根源上避免隐式转换.

结语:一个小小的字符当真是可以引发血案,在做应用开发中我们需要知道每个字符的深刻含义.

 

后记:博客内容发表后有热心的朋友@Yaoquan.Luo,@victor596,@uestc小田,@Sonnyxue 测试发现在SQL2008R2中并未有死锁出现,由此给大家带来的困惑深表歉意.这里为大家解释下原因

SQL Server优化器特性-动态检索

有阵子没写博客了,家里有个小孩,目前时间不算充裕,但我会坚持下去的,各位的同学的支持就是我的动力!最后给大家拜个早年,祝大家羊年大吉,钱途无量!