SQL Server 隐式转换引发的躺枪死锁-软件工程师需知_SQL

SQL Server 隐式转换引发的躺枪死锁-程序员需知

在SQL Server的应用开发过程(尤其是二次开发)中可能由于开发人员对表的结构不够了解,造成开发过程中使用了不合理的方式造成数据库引擎未按预定执行,以致影响业务.这是非常值得注意的.这次为大家介绍由于隐式数据类型转换而造成的死锁及相应解决方案.

现实中有些程序员/数据库开发者会根据数据库的处理机制实现一些应用,如抢座应用,可能会对事务中的查询加一些列的Hint以细化粒度,实现应用的同时使得影响最低,但也有可能因为一些小细节的欠缺而引发错误,从而造成糟糕的用户体验.如下面这个例子

生成测试数据

code

create table testlock(ID varchar(10) primary key clustered,col1 varchar(20),col2 char(200))go----------create test tabledeclare @i intset @i = 1while @i < 100begininsert into testlockselect right(replicate('0',10)+ cast(@i as varchar(10)),10),'aaa','fixchar'set @i = @i+1endgo----------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 ascselect  @IDwaitfor delay '00:00:20'update testlock set col1 = 'bbb' where id = @IDcommit 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 ascselect  @IDwaitfor delay '00:00:20'update testlock set col1 = 'bbb' where id = @IDcommit tran

我们可以看到相应的执行计划发生了改变,我们期待的执行计划出现了.如图1-4

图1-4

至此,问题解决.

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

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

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

17楼湛天: 支持，建表时使用数据类型一般都是 nvarchar 。，头像是东灵山

16楼听风吹雨: 高总牛逼

15楼害怕飞的鸟: 从没注意过这个事情。

14楼_cc: 支持一下

13楼D、boy: mark

12楼CareySon: 碉堡了

11楼Sonnyxue: 我也在SQL SERVER 2008 R2中测试了，不过没有出现死锁，2014中出现了死锁，不知为啥呀

10楼老玉米: 太牛了!!!

9楼Lvanhades666: 学习了在技术内幕书中看到过，但是没有注意它

8楼victor596: SQL SERVER 2008中测试过，不会死锁呀

7楼疯狂小怪: jd有大量的血案....

6楼wy123: 我觉得微软这个玩意早晚得优化，不能被动地去做隐式转换，，应该将查询条件变量值的类型先转换为所在列的字段类型，转换成功继续执行查询，不成功直接报错，，应该是主动让查询去遵循表结构的字段类型，而不是被动做类型转换区适应查询，这不是活生生的例子吗，查询给出的类型不对，最后弄的执行计划都出问题了，就像是去饭店吃饭，想吃啥，只能从菜单上去点菜，而不能随便说出一个菜品，我再照你说的去做，厨师做不好或者做不出来，还的挨骂，太被动了

5楼James-yu: 楼主应该在文章开头说明里面添加:“SQL SERVER 索引的数据类型与参数数据类型不符，如varchar字段类型对nvarchar参数，将引发索引扫描，降低查询效率”

4楼GrayskyF: 先顶再说，等会再慢慢细看。

3楼Paddy_Duck: 不错~呱唧呱唧~~

2楼oliver_001: 不错，很有帮助，受教了。

1楼uestc小田: 搞了半天没有还原出来。2008 r2