SQL Server 高频高并发访问中的键查找死锁解析 - 文章 - 伯乐在线
& SQL Server 高频高并发访问中的键查找死锁解析
死锁对于DBA或是数据库开发人员而言并不陌生，它的引发多种多样。一般而言，数据库应用的开发者在设计时都会有一定的考量进而尽量避免死锁的产生。但有时因为一些特殊应用场景如高频查询，高并发查询下由于数据库设计的潜在问题，一些不易捕捉的死锁可能出现从而影响业务。这里为大家介绍由于设计问题引起的键查找死锁及相关的解决办法。
这里我们在测试的同时开启trace profiler跟踪死锁视图(locks:deadlock graph).(当然也可以开启跟踪标记,或者应用扩展事件(xevents)等捕捉死锁)
创建测试对象code
create table testklup
clskey int not null,
nlskey int not null,
int not null,
char(3000)
create unique clustered index inx_cls on testklup(clskey)
create unique nonclustered index inx_nlcs
on testklup(nlskey) include(cont1)
insert into testklup select 1,1,100,'aaa'
insert into testklup select 2,2,200,'bbb'
insert into testklup select 3,3,300,'ccc'
123456789101112131415
create table testklup(clskey int not null,nlskey int not null,cont1&&int not null,cont2&&char(3000))&create unique clustered index inx_cls on testklup(clskey)&create unique nonclustered index inx_nlcs&&on testklup(nlskey) include(cont1)&insert into testklup select 1,1,100,'aaa'insert into testklup select 2,2,200,'bbb'insert into testklup select 3,3,300,'ccc'
开启会话1 模拟高频update操作
----模拟高频update操作
declare @i int
set @i=100
update testklup set cont1=@i
where clskey=1
set @i=@i+1
----模拟高频update操作 declare @i intset @i=100while 1=1 begin &&update testklup set cont1=@i &&where clskey=1&&set @i=@i+1 end
开启会话2 模拟高频select操作
----模拟高频select操作
declare @cont2 char(3000)
select @cont2=cont2 from testklup where nlskey=1
----模拟高频select操作declare @cont2 char(3000)while 1=1begin&&&&select @cont2=cont2 from testklup where nlskey=1end
此时开启会话2执行一小段时间时我们就可以看到类似错误信息:图1-1
而在我们开启的跟踪中捕捉到了如下的死锁图.图1-2
死锁分析:可以看出由于读进程(108)请求写进程(79)持有的X锁被阻塞的同时,写进程(79)又申请读进程(108)锁持有的S锁.读执行计划图1-3,写执行计划图1-4
(由于在默认隔离级别下(读提交)读申请S锁只是瞬间过程,读完立即释放,不会等待事务完成),所以在并发,执行频率不高的情形下不易出现.但我们模拟的高频情况使得S锁获得频率非常高,此时就出现了仅仅两个会话,一个读,一个写就造成了死锁现象.
死锁原因:读操作中的键查找造成的额外锁(聚集索引)需求
解决方案:在了解了死锁产生的原因后,解决起来就比较简单了.
我们可以从以下几个方面入手.
a 消除额外的键查找锁需的锁
b 读操作时取消获取锁
a.1我们可以创建覆盖索引使select语句中的查询列包含在指定索引中
CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup]
([nlskey] ASC) INCLUDE ( [cont2])
CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup]([nlskey] ASC) INCLUDE ( [cont2])
a.2 根据查询需求,分步执行,通过聚集索引获取查询列,避免键查找.
declare @cont2 char(3000)
declare @clskey int
select @clskey=clskey from testklup where nlskey=1
select @cont2=cont2 from testklup where clskey=@clskey
declare @cont2 char(3000)declare @clskey intwhile 1=1begin&&&&select @clskey=clskey from testklup where nlskey=1&&&&&select @cont2=cont2 from testklup where clskey=@clskeyend
b 通过改变隔离级别,使用乐观并发模式,读操作时源行无需锁
declare @cont2 char(3000)
select @cont2=cont2 from testklup with(nolock) where nlskey=1
declare @cont2 char(3000)while 1=1begin&&&&select @cont2=cont2 from testklup with(nolock) where nlskey=1end
结束语.我们在解决问题时,最好弄清问题的本质原因,通过问题点寻找出适合自己的环境的解决方案再实施.
可能感兴趣的话题
关于伯乐在线博客
在这个信息爆炸的时代，人们已然被大量、快速并且简短的信息所包围。然而，我们相信：过多“快餐”式的阅读只会令人“虚胖”，缺乏实质的内涵。伯乐在线内容团队正试图以我们微薄的力量，把优秀的原创文章和译文分享给读者，为“快餐”添加一些“营养”元素。
新浪微博：
推荐微信号
（加好友请注明来意）
– 好的话题、有启发的回复、值得信赖的圈子
– 分享和发现有价值的内容与观点
– 为IT单身男女服务的征婚传播平台
– 优秀的工具资源导航
– 翻译传播优秀的外文文章
– 国内外的精选文章
– UI,网页，交互和用户体验
– 专注iOS技术分享
– 专注Android技术分享
– JavaScript, HTML5, CSS
– 专注Java技术分享
– 专注Python技术分享
& 2017 伯乐在线您所在的位置： &
SQL Server锁使用注意事项之如何避免死锁
SQL Server锁使用注意事项之如何避免死锁
以下的文章主要介绍的是SQL Server锁使用注意事项之如何避免死锁，以下是文章的具体介绍，望你浏览完以下的内容会有所收获。
本文主要向你介绍的是锁使用注意事项之如何避免死锁，当然此事项是SQL Server锁使中经常出现的，如果你是SQL Server锁的热捧者。那么下面的文章对于你而言一定很有意义。
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3 优化程序，检查并避免死SQL Server锁现象出现；
4 .对所有的脚本和SP都要仔细测试，在正是版本之前。
5 所有的SP都要有错误处理（通过@error）
6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁
498)this.width=498;' onmousewheel = 'javascript:return big(this)' style="width: 798 height: 598px" height="598" alt="SQL Server锁使用注意事项之如何避免死锁 " width="798" srcwidth="798" srcheight="598" zoomrate="1" src="/files/uploadimg/7210.jpg" />
上述的相关内容就是对SQL Server锁使用注意事项之如何避免死锁的描述，希望会给你带来一些帮助在此方面。
【编辑推荐】
【责任编辑： TEL：（010）】
关于的更多文章
数据库管理员的工作让很多人景仰和艳羡。其实这份看似光鲜的工作
数据库产品
数据库综合
数据库新闻
维基百科将切换到另外一款开源数据库MariaDB
这条路，有人说是一条不归路，走上来了，就要勇敢的走
MongoDB现在已经越来越受到重视，学习的人也越来越多
DBA是数据库管理员,英文是Database Administrator。一
本书为《Eclipse从入门到精通》一书的全新改版。本书以最新的Eclipse 3.2作为写作版本。全书分为5篇：起步篇介绍了Eclipse及相关
51CTO旗下网站安全检查中...
请打开浏览器的javascript，然后刷新浏览器
< 浏览器安全检查中...
还剩 5 秒&拒绝访问 | www. | 百度云加速
请打开cookies.
此网站 (www.) 的管理员禁止了您的访问。原因是您的访问包含了非浏览器特征(394c435b829a43cb-ua98).
重新安装浏览器，或使用别的浏览器数据库是一个多用户使用的共享资源，当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。
在实际应用中经常会遇到的与锁相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严重影响应用的正常执行。&
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。
加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。&
一. 事务之间对资源访问顺序的交替
出现原因：&一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
解决方法：&这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。
二. 并发修改同一记录
出现原因：&用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁由于比较隐蔽，但在稍大点的项目中经常发生。&
一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
解决方法：&a. 使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。&
b. 使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统，当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。&
c. SqlServer可支持更新锁&为解决死锁，SqlServer引入更新锁,它有如下特征：&(1) 加锁的条件：当一个事务执行update语句时，数据库系统会先为事务分配一把更新锁。&(2) 解锁的条件：当读取数据完毕，执行更新操作时，会把更新锁升级为独占锁。&(3) 与其他锁的兼容性：更新锁与共享锁是兼容的，也就是说，一个资源可以同时放置更新锁和共享锁，但是最多放置一把更新锁。这样，当多个事务更新相同的数据时，只有一个事务能获得更新锁，然后再把更新锁升级为独占锁，其他事务必须等到前一个事务结束后，才能获取得更新锁，这就避免了死锁。&(4) 并发性能：允许多个事务同时读锁定的资源，但不允许其他事务修改它。&
begin tran
select * from table(updlock) (加更新锁)
update table set column1='hello'
begin tran
select * from table(updlock)
update table set column1='world'
更新锁的意思是：“我现在只想读，你们别人也可以读，但我将来可能会做更新操作，我已经获取了从共享锁（用来读）到排他锁（用来更新）的资格”。一个事物只能有一个更新锁获此资格。&
T1执行select，加更新锁。&T2运行，准备加更新锁，但发现已经有一个更新锁在那儿了，只好等。&当后来有user3、user4…需要查询table表中的数据时，并不会因为T1的select在执行就被阻塞，照样能查询,提高了效率。
三. 索引不当导致全表扫描
出现原因：&如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。
解决方法：&SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
四.事务封锁范围大且相互等待
转自http://blog.csdn.net/qq_/article/details/
阅读(...) 评论()