SQLServer中的死锁的介绍

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

简介

     什么是死锁？

我认为，死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。

什么又是阻塞？

阻塞是由于资源不足引起的排队等待现象。比如同时两个进程去更新一个表。

这里我们可以把阻塞作为死锁的必要条件。下面我们先理解一下死锁和阻塞再来看一下我最近遇到一个问题以及解决思路。

SQLServer中的死锁

     对应到SQL Server中，当在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁；

这些资源可能是：单行(RID，堆中的单行)、索引中的键(KEY，行锁)、页(PAG，8KB)、区结构(EXT，连续的8页)、堆或B树(HOBT) 、表(TAB，包括数据和索引)、文件(File，数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)。

下面我简单举一个例子来说明一下死锁的原理：

如图，按步骤执行：

1. begin tran
update test1 set aaa=1

2.

begin tran

update test2 set aaa=1

update test1 set bbb=2

3.再次执行图1中的Update test2 set bbb=2

执行完成后发现数据并未插入，且一直处于running状态

这个时候我们通过语句查询死锁的进程和语句。得到如下结果：

很容易发现发生死锁的语句，也可以使用 SQL Server Profiler 分析死锁: 将 Deadlock graph 事件类添加到跟踪。此事件类使用死锁涉及到的进程和对象的 XML 数据填充跟踪中的 TextData 数据列。SQL Server 事件探查器 可以将 XML 文档提取到死锁 XML  文件中，以后可在 SQL Server Management Studio 中查看该文件。如图：

接下来我们说一下如何处理死锁

      1.临时解决方案，先Kill 掉死锁的进程，只是暂时解决这个问题。

2.SQL Server自动选择一条SQL作死锁牺牲品：当死锁发生时，锁监视器线程执行死锁检查，数据库引擎 选择运行回滚开销最小的事务的会话作为死锁牺牲品，返回1205 错误，回滚死锁牺牲品的事务并释放该事务持有的所有锁，使其他线程的事务可以请求资源并继续运行。

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

3.使用SET LOCK_TIMEOUT timeout_period(单位为毫秒)来设定请求超时。

4.在SQLServer 和程序两个方面都可以做代码上修正，这里不在详细描述，主要是通过发现死锁等待一段时间后再次尝试的方式来解决。

预防和避免死锁

     1.尽量减少事务执行的时间。

2.在合理的范围内降低隔离级别。

3.同一个事务内尽量避免出现循环对同一个表的处理。

4.同一个事务内较少用户交互，即锁的竞争。

5.尽量保证逻辑处理的顺序比如对表的处理都按照一个顺序进行。

6.对于需要各种逻辑处理的表，可以通过增加索引的方式来减少锁的竞争。

7.尽量减少非聚集索引的include 的列，也能减少外键死锁的发生。

8.同一个对象尽量采用select 在update 前来使用。

9.对于实时性要求不高的可以使用with(nolock)来实现对表的查询，但是可能会差生脏读。

 总结

      本文简单的介绍了死锁的原因，如何解决和预防。当然任何事情都是双刃剑，还要我们根据实际情况来合理减少死锁和阻塞的发生；对于不同隔离界别锁带来的问题可以看一下我之前的一篇关于锁的介绍。希望对死锁发生预防和解决有一定的帮助。