如何减少SQL Server死锁发生

  死锁是指在某组资源中,两个或两个以上的线程在执行过程中,在争夺某一资源时而造成互相等待的现象,若无外力的作用下,它们都将无法推进下去,死时就可能会产生死锁,这些永远在互相等待的进程称为死锁线程。简单的说,进程A等待进程B释放他的资源,B又等待A释放他的资源,这样互相等待就形成死锁。

  如在数据库中,如果需要对一条数据进行修改,首先数据库管理系统会在上面加锁,以保证在同一时间只有一个事务能进行修改操作。如事务1的线程 T1具有表A上的排它锁,事务2的线程T2 具有表B上的排它锁,并且之后需要表A上的锁。事务2无法获得这一锁,因为事务1已拥有它。事务2被阻塞,等待事务1。然后,事务1需要表B的锁,但无法获得锁,因为事务2将它锁定了。事务在提交或回滚之前不能释放持有的锁。因为事务需要对方控制的锁才能继续操作,所以它们不能提交或回滚,这样数据库就会发生死锁了。

  如在编写存储过程的时候,由于有些存储过程事务性的操作比较频繁,如果先锁住表A,再锁住表B,那么在所有的存储过程中都要按照这个顺序来锁定它们。如果无意中某个存储过程中先锁定表B,再锁定表A,这可能就会导致一个死锁。而且死锁一般是不太容易被发现的。

  如果服务器上经常出现这种死锁情况,就会降低服务器的性能,所以应用程序在使用的时候,我们就需要对其进行跟踪,使用sp_who和sp_who2来确定可能是哪些用户阻塞了其他用户,我们还可以用下面的存储过程来跟踪具体的死锁执行的影响:

  create procedure sp_who_lock

  as

  begin

  declare @spid int,@bl int,@intTransactionCountOnEntry

  int,@intRowcount

  int,@intCountProperties

  int,@intCounter

  int create table

  #tmp_lock_who

  (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 RETURN

  @@ERRORinsert into

  #tmp_lock_who(spid,bl) select

  0 ,blockedfrom (select * from sysprocesses where

  blocked>0 )

  a where not exists(select * from (select * from sysprocesses where blocked>0 )

  b where a.blocked=spid)union select spid,blocked from sysprocesses where

  blocked>0IF

  @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数select

  @intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF

  @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0select

  '现在没有阻塞和死锁信息'

  as message-- 循环开始while @intCounter <= @intCountPropertiesbegin-- 取第一条记录select

  @spid = spid,@bl = blfrom #tmp_lock_who where id = @intCounter beginif @spid =0 select

  '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,

  其执行的SQL语法如下'elseselect

  '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' +

  '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,

  当前进程执行的SQL语法如下'DBCC INPUTBUFFER (@bl )end --

  循环指针下移set @intCounter = @intCounter + 1enddrop table #tmp_lock_who

  return 0

  我们只需要通过在查询分析器里面执行sp_who_lock,就可以具体捕捉到执行的堵塞进程,这时我们就可以对对应的SQL语句或者存储过程进行性能上面的改进及设计。

  所以我们在数据库设计的时候,虽然不能完全避免死锁,但可以使死锁的数量尽量减少。增加事务的吞吐量并减少系统开销,因为只有很少的事务,所以就得遵循下面的原则:

  按同一顺序访问对象

  如果所有并发事务按同一顺序访问对象,则发生死锁的可能性会降低。在写SQL语句或存储过程的时候,就需要按照顺序在两个并发事务中先获得表A上的锁,然后获得表B上的锁,当第一个事务完成之前,另一个事务被阻塞在表A上。第一个事务提交或回滚后,第二个事务继续进行,而不能在语句里面写先获得表B上的锁,然后再获得表A的锁。

  避免事务中的用户交互

  避免编写包含用户交互的事务,因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度,例如答复应用程序请求参数的提示。例如,如果事务正在等待用户输入,而用户就去做别的事了,则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量,因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况,访问同一资源的其它事务也会被阻塞,等待该事务完成。

  保持事务简短并在一个批处理中

  在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。保持事务在一个批处理中,可以最小化事务的网络通信往返量,减少完成事务可能的延迟并释放锁。

  使用低隔离级别

  确定事务是否能在更低的隔离级别上运行。执行提交读允许事务读取另一个事务已读取(未修改)的数据,而不必等待第一个事务完成。使用较低的隔离级别(例如提交读)而不使用较高的隔离级别(例如可串行读)可以缩短持有共享锁的时间,从而降低了锁定争夺。

  使用绑定连接

  使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。次级连接所获得的任何锁可以象由主连接获得的锁那样持有,反之亦然,因此不会相互阻塞。

  下面有一些对死锁发生的一些建议:

  1)对于频繁使用的表使用集簇化的索引;

  2)设法避免一次性影响大量记录的T-SQL语句,特别是INSERT和UPDATE语句;

  3)设法让UPDATE和DELETE语句使用索引;

  4)使用嵌套事务时,避免提交和回退冲突;

  5)对一些数据不需要及时读取更新值的表在写SQL的时候在表后台加上(nolock),如:Select * from tableA(nolock)

时间: 2024-11-27 22:11:35

如何减少SQL Server死锁发生的相关文章

如何捕获和记录SQL Server中发生的死锁

方法一:利用SQL Server代理(Alert+Job)具体步骤如下:1.首先使用下面的命令,将有关的跟踪标志启用.SQL code DBCC TRACEON (3605,1204,1222,-1) 说明:3605 将DBCC的结果输出到错误日志. 1204 返回参与死锁的锁的资源和类型,以及受影响的当前命令. 1222 返回参与死锁的锁的资源和类型,以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用). -1 以全局方式打开指

如何捕获和记录SQL Server中发生的死锁_Mysql

方法一:利用SQL Server代理(Alert+Job)具体步骤如下:1.首先使用下面的命令,将有关的跟踪标志启用.SQL code DBCC TRACEON (3605,1204,1222,-1)  说明:3605 将DBCC的结果输出到错误日志.1204 返回参与死锁的锁的资源和类型,以及受影响的当前命令.1222 返回参与死锁的锁的资源和类型,以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用).-1 以全局方式打开指定的

SQL Server 死锁案例分析

概述 当两个或者多个进程相互阻塞时,形成一个复杂的阻塞链,参与的进程都在等待其他进程放弃其获取到的锁,没有系统的干预,无法解开这个阻塞链,这种情况就是死锁. 有些死锁是意料之内的,可以说是故意为之的. 例如,为了一致性,使用死锁来防止丢失更新. 有一些死锁是意料之外的,由于缺少索引或者事务运行时间较长导致,我们遇到的大部分都是意料之外的死锁.  SQL Server内部有个死锁的检测机制,当发生死锁时,SQL Server根据会话优先级以及工作量进行评估, 选择终止其中一个事务,解开死锁,并且向

SQL Server死锁的分析

server SQL Server数据库发生死锁时不会像ORACLE那样自动生成一个跟踪文件.有时可以在[管理]->[当前活动] 里看到阻塞信息(有时SQL Server企业管理器会因为锁太多而没有响应). 设定跟踪1204: USE MASTERDBCC TRACEON (1204,-1) 显示当前启用的所有跟踪标记的状态: DBCC TRACESTATUS(-1) 取消跟踪1204: DBCC TRACEOFF (1204,-1) 在设定跟踪1204后,会在数据库的日志文件里显示SQL Se

sql server死锁的精辟解释

server sysprocessessysprocesses 表中保存关于运行在 Microsoft SQL Server 上的进程的信息.这些进程可以是客户端进程或系统进程.sysprocesses 只存储在 master 数据库中. 列名 数据类型 描述 spid smallint SQL Server 进程 ID. kpid smallint Microsoft Windows NT 4.0 线程 ID. blocked smallint 分块进程的进程 ID (spid). waitt

RDS SQL Server死锁(Deadlock)系列之五利用Extended Events获取死锁信息

问题引入 在过去很长一段时间,不断有客人会问道:"在事先没有任何跟踪或者监控部署的情况下,阿里云RDS SQL Server有没有办法获取到历史死锁信息,供我们分析?".在写到RDS SQL Server死锁系列文章之五时,我们就可以使用Extended Events来解决这个问题. 分析问题 Extended Events是微软从SQL Server 2008版本开始引入的,其中有一个默认事件会话是system_health,它的作用是用来收集SQL Server发生的错误信息,以X

RDS SQL Server死锁(Deadlock)系列之四利用Service Broker事件通知捕获死锁

问题引入 在前面三篇文章,我们分别谈到了使用DBCC命令捕获死锁:使用Profiler界面跟踪Deadlock Graph事件捕获死锁和使用脚本自动部署Profiler Trace捕获死锁.这篇文章介绍一个非常有意思的捕获死锁的方法:使用SQL Server Service Broker Event Notification来捕获死锁. Service Broker Event Notification Service Broker Event Notification即使用SQL Server

RDS SQL Server死锁(Deadlock)系列之二使用Profiler捕获死锁

问题引入 不管是RDS SQL Server还是自建SQL Server数据库,死锁的确是一个非常头疼的问题,上一篇文章我们已经谈到了使用DBCC捕获死锁.这篇文章是以阿里云RDS客户遇到的死锁问题为背景,分享死锁文章系列之二使用Profiler捕获死锁. Profiler捕获死锁 使用Profiler工具的Deadlock graph事件,可以非常方便直观的捕获死锁信息.方法是: 开启MSSQL Profiler:开始 -> 运行 -> 键入profiler 新建Deadlock Gra

RDS SQL Server死锁(Deadlock)系列之一使用DBCC捕获死锁

问题引入 在日常运维阿里云RDS SQL Server产品过程中,经常会被客户问道:"应用程序被死锁报错啦?影响很大,到底是哪个进程导致了死锁发生的啊?怎么解决啊?怎么办呀?".从客户一连串的问题中,我们深刻体会到了死锁问题的紧迫性和影响之大.授人予鱼而不如授人予渔,RDS SQL Server死锁系列文章就是为了帮助客人彻底解决死锁问题为初衷而诞生的.本篇文章是系列文章的开篇,主要是讨论如何使用DBCC来捕获死锁信息,内容包括: DBCC捕获死锁 死锁测试 死锁分析 解决方法