SQLServer中的死锁的介绍

原文: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)来实现对表的查询,但是可能会差生脏读。

 总结

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

时间: 02-28

SQLServer中的死锁的介绍的相关文章

sqlserver中select造成死锁

项目上线,准备验收前出现了一个严重的问题:很多select语句作为死锁的牺牲,大部分报表无法打开.这个问题影响范围很大所有的报表都无法访问,而我们的报表是放在电视上面轮播的,电视放在工厂里面,所以出现问题后,整个工厂都知道了. 解决这个问题比较曲折,首先是写SAP接口的同事发现了问题:SAP一直在传错误数据导致产量表被锁住.修改SAP传输的错误数据后,这个死锁的问题没有出现了.但是我查看生产环境服务器日志的时候,发现这个问题依然存在,由于客户没有提这个问题,我也就是没有理由要求花时间修改了,因为

SQL Server中解决死锁的新方法介绍

SQL Server中解决死锁的新方法介绍 数据库操作的死锁是不可避免的,本文并不打算讨论死锁如何产生,重点在于解决死锁,通过SQL Server 2005, 现在似乎有了一种新的解决办法. 将下面的SQL语句放在两个不同的连接里面,并且在5秒内同时执行,将会发生死锁. <ccid_nobr> <ccid_code>use Northwindbegin tran insert into Orders(CustomerId) values(@#[email protected]#)

sqlserver 中EXEC和sp_executesql使用介绍

sqlserver 中EXEC和sp_executesql使用介绍 MSSQL为我们提供了两种动态执行SQL语句的命令,分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势,它提供了输入输出接口,而EXEC没有.还有一个最大的好处就是利用sp_executesql,能够重用执行计划,这就大大提供了执行性能(对于这个我在后面的例子中会详加说明),还可以编写更安全的代码.EXEC在某些情况下会更灵活.除非您有令人信服的理由使用EXEC,否侧尽量使用sp_execu

sqlserver 中死锁相关问题

死锁定义: 所谓死锁就是两个线程或多个线程在拥有一部分资源的同时还需要拥有其他资源, 但是其他资源被其他线程占有,每个线程为了获得其他线程占有的资源都处于一个相互等待 的状态,这个时候如果没有外界力量破坏这种相互等待的状态或是某个(些)线程自动放弃已经占有的资源, 那么所有的线程都无法完成任务,这个时候系统处于一个僵死状态.这就是所谓的死锁. sqlserver自身有个锁监视器(Lock monitor),一旦发现死锁,SqlServer会kill掉一个其中一个线程, 使另外一个(些)继续完成任

【SqlServer】解析SqlServer中的事务

在这篇Blog中,笔者将会解析阐述SqlServer中的事务,希望可以对你有所帮助. 1.事务是什么 事务就是单个逻辑单元执行的一系列操作.事务都具有ACID特性:原子性(Atomicity).一致性(Consistency).隔离性(Isolation)和持久性(Durability). 原子性(Atomicity):事务必须是原子工作单元:对于其数据修改,要么全都执行,要么全都不执行. 一致性(Consistency):事务在完成时,必须使所有的数据都保持一致状态. 在相关数据库中,所有规则

SQLServer中间接实现函数索引或者Hash索引

本文出处:http://www.cnblogs.com/wy123/p/6617700.html SQLServer中没有函数索引,在某些场景下查询的时候要根据字段的某一部分做查询或者经过某种计算之后做查询,如果使用函数或者其他方式作用在字段上之后,就会限制到索引的使用,不过我们可以间接地实现类似于函数索引的功能.另外一个就是如果查询字段较大或者字段较多的时候,所建立的索引就显得有点笨重,效率也不高,就需要考虑使用一个较小的"替代性"字段做等价替换,类似于Hash索引,本文粗浅地介绍两

asp.net中缓存的使用介绍一

asp.net中缓存的使用介绍一 介绍: 在我解释cache管理机制时,首先让我阐明下一个观念:IE下面的数据管理.每个人都会用不同的方法去解决如何在IE在管理数据.有的会提到用状态管理,有的提到的cache管理,这里我比较喜欢cache管理,因为本人比较喜“cache”这个词.但是状态管理和cache管理这两个在概念和意义上是不同的,下面就让我们来讨论下两都在各个方面的不同之处. 虽然cache管理并不存在于Windows程序,但在web环境中已经得到巨大的应用.自从HTTP变成无协议以来,在

深入浅出SQL Server中的死锁 【转】

简介 死锁的本质是一种僵持状态,是多个主体对于资源的争用而导致的.理解死锁首先需要对死锁所涉及的相关观念有一个理解. 一些基础知识 要理解SQL Server中的死锁,更好的方式是通过类比从更大的面理解死锁.比如说一个经典的例子就是汽车(主体)对于道路(资源)的征用,如图1所示. 图1.对于死锁的直观理解 在图1的例子中,每队汽车都占有一条道路,但都需要另外一队汽车所占有的另一条道路,因此互相阻塞,谁都无法前行,因此造成了死锁.由这个简单的例子可以看出,发生死锁需要四个必要条件,如下: 1)互斥

Sqlserver中一直在用又经常被忽略的知识点一

已经有快2个月没有更新博客了,实在是因为最近发生了太多的事情,辞了工作,在湘雅医院待了一个多月,然后又新换了工作...... 在平时的工作中,Sqlserver中许多知识点是经常用到的,但是有时候我们往往忽略了它们,在过去的一年里,一直使用的是Mysql,现在又开始接触Sqlserver了,所以就把一些常用又容易忽视的Sqlserver知识点总结一点,以便备忘之用. 所有的操作都将基于Northwind数据库来进行操作. SET NOCOUNT ON 介绍 在存储过程中,经常用到SET NOCO