如何快速发现和处理sqlserver数据库的死锁_sqlora

http://blog.sina.com.cn/u/5592732073

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

如何快速发现和处理sqlserver数据库的死锁

(2018-11-24 12:52:33)

分类： oracle

首先在sqlserver的查询分析器中查看特定数据库被阻塞的进程

select * from sysprocesses where dbid in (select dbid from sysdatabases where name='www_rztong_com_cn') and blocked>0

然后查看阻塞超时设置

SELECT @@LOCK_TIMEOUT

再次根据阻塞的进程ID查看阻塞语句

dbcc inputbuffer(108)

上面的108就是阻塞进程的ID。根据发现的语句可以用来分析是程序代码的哪部分造成了这个死锁，从而得以解决。

如果事情紧急，可以立即杀死阻塞进程，从而终结死锁的情况

kill 108

在实际操作中可能会出现kill 108执行不了的情况，这个时候处理就比较麻烦，需要是数据库处于独占情况下才能处理

---------------------

作者：sxf359

来源：CSDN

原文：https://blog.csdn.net/sxf359/article/details/54669866

前些天写一个存储过程，存储过程中使用了事务，后来我把一些代码注释掉来进行调试找错，突然发现一张表被锁住了，原来是创建事务的代码忘记注释掉。本文表锁住了的解决方法。其实不光是上面描述的情况会锁住表，还有很多种场景会使表放生死锁，解锁其实很简单，下面用一个示例来讲解： 1 首先创建一个测试用的表：


CREATE TABLE Test
(
    TID INT IDENTITY(1,1)
)

2 执行下面的SQL语句将此表锁住：


SELECT * FROM Test WITH (TABLOCKX)

3 通过下面的语句可以查看当前库中有哪些表是发生死锁的：


SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName
FROM  sys.dm_tran_locks
WHERE resource_type='OBJECT '

4 上面语句执行结果如下：

http://blog.fwhyy.com/wp-content/uploads/2011/08/2011-08-06_203648.png

spid ：被锁进程ID。
tableName：发生死锁的表名。

5 只需要使用kill关键字来杀掉被锁的进程ID就可以对表进行解锁：


KILL 52

SQL Server死锁的分析、处理与预防

1、基本原理

所谓“死锁”，在操作系统的定义是：在一组进程中的各个进程均占有不会释放的资源，但因互相申请被其他进程所站用不会释放的资源而处于的一种永久等待状态。

定义比较抽象，下图可以帮助你比较直观的理解死锁：

https://images0.cnblogs.com/i/554322/201403/161807255437528.x-png

出现死锁需要满足几个必要条件：

a）互斥：进程独占资源，资源不共享；

b）请求与保持：已经得到资源的进程可以再次申请新资源；

c）不剥夺：已分配的资源不能被其它进程强制剥夺；

d）环路等待：几个进程组成环路，都在相互等待正被占用的资源；

对应到SQL Server中，在2个或多个任务中（insert、update、delete、select、alter table或Tran事务等等），如果每个任务锁定了其它任务想要锁定的资源，会造成这些任务永久阻塞，从而出现死锁。这些资源可能是：单行数据（RID、HEAP堆中的行）、索引中的键（KEY，行锁）、页（Page，8KB）、区（Extent，8个连续页）、堆或B树、表（Table，数据和索引）、文件（File，数据库文件）、整个数据库（DataBase）。

如果系统中的资源不足或者资源分配策略不当，会导致因进程间的资源争用产生死锁现象。但更多的可能是程序员的程序有问题。“锁”有多种方式，如意向锁、共享锁、排他锁等等。锁还有多种粒度，如行锁、表锁。

了解了死锁产生的原因，就可以最大可能的避免与预防死锁。只要上述4个必要条件中有1个不满足，就不会发生死锁。所以，在系统设计、实现阶段就可以在资源分配与占用、资源访问顺序等方面采取必要措施。

2、一个例子

直面死锁，来看一个例子：如图1所示，新建一个查询窗口，并利用事务的原子特性和update语句的排他锁特性把2个表中的记录锁住；如图2所示，再次新建一个查询窗口，2条很简单的SQL语句长时间仍没有执行完成。

https://images0.cnblogs.com/i/554322/201403/161810378242685.x-png

https://images0.cnblogs.com/i/554322/201403/161810466219663.x-png

3、检测与排查

3.1 通过Profile工具看死锁

Profile是SQL Server自带的跟踪分析工具，开启Profile来捕捉死锁信息可以更直观的看到相关信息。

https://images0.cnblogs.com/i/554322/201403/161811314651729.x-png

3.2 通过系统存储过程看死锁

sp_who和sp_lock是SQL Server的2个系统存储过程，可以用它们来查询数据库中的锁情况。sp_who提供有关的数据库实例中当前用户、会话和进程的信息，如下图，我们看到spid=56的会话（UPDATE语句）被spid=54的会话阻塞。

https://images0.cnblogs.com/i/554322/201403/161813148715639.x-png

sp_lock提供有关锁的信息，如下图。我们可以通过spid知道是哪个会话锁住了资源，可以通过ObjId知道被锁住的资源是什么。

https://images0.cnblogs.com/i/554322/201403/161814051992230.x-png

执行如下SQL脚本获取被锁资源和资源所属的数据库：

SELECT OBJECT_NAME(421666738) AS LockedResource,DB_NAME(11) AS DBName;
--------------------------------------------------------------
LockedResource                  DBName
--------------------------------------------------------------
tb_TE_SizeInformation           JYBGDB

执行如下脚本获取锁资源的会话正在执行的SQL脚本：

https://common.cnblogs.com/images/copycode.gif

DBCC INPUTBUFFER(54);
---------------------------------------------------------------------------
EventInfo                                  EventType                Parameters
---------------------------------------------------------------------------
--根据事务的原子性实现个必要条件中                Language Event           0             
--请求和等待 BEGIN TRAN
--update语句在数据行上加排他锁
--和其它所有锁不兼容
--实现个必要条件中的：互斥
UPDATE tb_TE_BrandInformation SET IsCompensate=0
UPDATE tb_TE_SizeInformation SET [Description]=''

https://common.cnblogs.com/images/copycode.gif

4、处理方式

4.1 SQL Server自动处理

“无为而治”。当数据库产生死锁时，SQL Server通过一个叫“锁监视器”的东西捕获死锁信息，并根据一定的规则自动选择一个SQL作为锁的牺牲品，并返回如下报错信息：

服务器: 消息 1205，级别 13，状态 50，行 1
事务（进程 ID xx）与另一个进程已被死锁在 lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。

如果你对数据库还不够了解，那建议你向其他有经验的人求助，在此之前不要轻易对数据库进行修改。

4.2 Kill会话

通过3.2中提到的系统存储过程可以获取到与死锁相关的信息。可以查询其中是哪个spid导致的死锁，并使用Kill spid的方法把它干掉。但是这只能是一种临时的解决方案，我们不可能一遇到死锁就在用户的生产环境里排查死锁、Kill sp。同样的道理，也不可能一遇到死锁就重启SQL Server服务，甚至重启数据库服务器。

SQL脚本：

Kill 54; --此处54即分析后得到的spid值

4.3 设定锁请求超时

默认情况下，数据库没有锁定超时期限。也就是说一个会话在申请新的资源时，如果这个资源已经被其它进程锁定，那么本会话会一直处于等待状态。这样无疑是有问题的。我们可以通过SQL命令来设定锁请求超时。也可以访问全局变量 @@LOCK_TIMEOUT 来查看这个值。

SET LOCK_TIMEOUT 20000; --单位是毫秒

当请求锁超过设定时间时，SQL Server将返回错误。我们的程序可以根据返回的错误来进行响应的处理，避免长时间的用户等待。

服务器: 消息 1222，级别 16，状态 50，行 1

已超过了锁请求超时时段。

当然，使用这种方式来处理所有的锁请求是不合适的，也是不负责任的。在多数情况下是我们的程序的设计、实现的问题导致了死锁。在处理过程中，我们既要治标，更要治本。

4.4 修改程序

在3.2的最后，我们通过系统存储过程和几个命令找到了锁定资源的SQL命令。以这次LL项目为例，我们发现是WEB管理系统上的一个统计报表（SELECT）在执行过程中长时间的那一个生产信息表锁定，导致现场各机台上位机系统想要插入新的生产记录（INSERT）时长时间等待。所以在现场项目组每次重新启动SQL Server服务或者重启数据库服务器2个小时以后，这个问题依然重复出现。

这个时候如果采用Kill掉这个统计报表请求的方式处理，结果和重启SQL Server服务、重启数据库服务器没有区别，2个小时后问题依旧。如果采用设定锁请求超时的方式处理，那么这个统计报表每次执行都不会获得想要的结果，而且每次执行也会锁定一定的时间导致现场上位机的等待。

这次我们的处理措施是：1）暂时禁用了WEB管理系统上的这个报表功能；2）重启了SQL Server服务；3）优化报表的SQL语句；4）启用报表功能。之后的一段时间没有再次出现这样的问题。

通过对这个报表的性能优化，这个问题算是解决的差不多了。但是经过事后了解，发现报表的性能问题并不在于开发人员的疏忽或水平不够。问题的根本在这个生产信息表的设计有问题。在一个数据量达到1000w级的表中，我们采用char(10)来保存日期值，虽然INSERT、UPDATE、DELETE时没有问题，但是在执行SELECT且这个日期值字段作为过滤条件时发生性能问题是必然的。经过测试，这个字段的数据类型改为datetime时的执行时间不到性能优化后的10%。

所以，不但是在开发阶段，早在设计阶段就已经有了性能隐患。