SqlServer隐式转换问题

最新推荐文章于 2024-06-13 16:55:00 发布

baobaojinjin

最新推荐文章于 2024-06-13 16:55:00 发布

阅读量992

点赞数

分类专栏： sqlserver性能调优文章标签：数据库

本文链接：https://blog.csdn.net/baobaojinjin/article/details/84737399

版权

sqlserver性能调优专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文探讨了在SQL Server中由于隐式数据类型转换引起的死锁问题，通过分析死锁原因，发现是由于声明的变量数据类型与表中字段不一致导致的。提供了解决方案，即确保变量数据类型与目标表匹配，以避免隐式转换，减少不必要的锁竞争，从而解决死锁问题。

摘要由CSDN通过智能技术生成

在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