在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 类型转换表,类型隐式转换和显示转换。
主要介绍了SQL Server 中的数据类型隐式转换问题,本文给大家介绍的非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
1.4 隐式转换(Implicit Conversion) 标量表达式 筛选表达式 1.5 基于CLR的用户定义类型 UDT理论简介 开发UDT 1.6 XML数据类型 关系数据库中的XML支持 什么时候应该使用XML代替关系表现形式? ...
在操作sqlserver时候用到了substring函数 SUBSTRING ( expression, start, length ) 参数 expression 字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。 start 整数或可以隐式...
SQL Server字符串处理函数大全 ...此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。
1.4 SQL Server 2000的新特性 1.4.1 改进了对Web的支持 1.4.2 改进了伸缩能力和可靠性 1.4.3 改进了开发和管理环境 1.5 样本数据库 1.5.1 样本数据库安装 1.5.2 样本数据库的设计目的和设计思想 1.5.3 数据库图解 ...
SQLSERVER 2012新增了两个字符串函数CONCAT和FORMAT。本文首先介绍一下CONCAT,CONCAT函数的作用是可以返回多个字符串拼接后的结果。 CONCAT 函数最多可以连接255个字符变量,当调用这个函数的时候需要至少接收两个...
1.4 SQL Server 2000的新特性 1.4.1 改进了对Web的支持 1.4.2 改进了伸缩能力和可靠性 1.4.3 改进了开发和管理环境 1.5 样本数据库 1.5.1 样本数据库安装 1.5.2 样本数据库的设计目的和设计思想 1.5.3 数据库图解 ...
一个简单的例子,如代码清单1所示。 1: SELECT * 2: FROM HumanResources.Employee 3: WHERE NationalIDNumber = 243322160 4: 5: SELECT * 6: FROM HumanResources.Employee ...
回顾:SQL2012中发生死锁的原因已经向大家解释了,因为隐式转换造成的表扫描扩大了锁规模.但在SQL2008R2中未有同样的现象出现,很显然锁规模没有扩大,原因在于SQL Server的优化器为我们做了额外的事情-动态检索 ...
因为如果数据类型与查询匹配,SQL Server需要先进行数据类型的隐式转换,以使它们能够匹配。 也有一些情况,即使为参照列设置了索引,SQL Server却不能使用此索引。因此,变量与列类型一致的情况下,您的查询可能会...
在这种情况下,SQLServer将会要么把where中的列,要么把参数的数据类型隐式转换为更高级或者更低级的数据类型。当作为被查询列被转换时(转换竞争中的牺牲者),将引起扫描(scan)来满足查询请求。让我们看看以下两...
2.1.1 sql server的结构 21 2.1.2 数据库访问标准化接口—odbc 22 2.1.3 使用查询分析器执行sql语句 22 2.2 transact-sql 24 2.2.1 transact-sql 概述 24 2.2.2 transact-sql的主要组成 25 2.2.3 transact-sql...
《精通SQ:结构化查询语言详解》全面讲解SQL语言,提供317个典型应用,读者可以随查随用,针对SQL Server和Oracle进行讲解,很有代表性。 全书共包括大小实例317个,突出了速学速查的特色。《精通SQ:结构化查询语言...
以SQL Server为工具,讲解SQL语言的应用,提供了近500个曲型应用,读者可以随查随用,深入讲解SQL语言的各种查询语句,详细介绍数据库设计及管理,详细讲解存储过程、解发器和游标等知识,讲解了SQL语言在高级语言中...
可用性组信息查找活动锁在计划缓存中查找密钥查找在PlanCache中查找长期运行的查询在PlanCache中查找最昂贵的查询在PlanCache中查找查询的计划在PlanCache中查找隐式转换查询在一段时间内测量IO延迟和吞吐量索引使用...
select语句中只能使用sql函数对字段进行操作(链接sql server),select 字段1 from...此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的
select语句中只能使用sql函数对字段进行操作(链接sql server),select 字段1 from...此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的