SQL Server死锁总结

1、sql server死锁总结1. 死锁原理 按照操作系统中的定义：死锁是指在一组进程中的各个进程均占有不会释放的资源，但因相互申请被其他进程所站用不会释放的资源而处于的一种永远等待状态。 死锁的四个须要条件： 互斥条件(mutual clusion)：资源不能被分享，只能由一个进程用法。 哀求与保持条件(hold and wait)：已经得到资源的进程可以再次申请新的资源。 非剥夺条件(no pre-emption)：已经分配的资源不能从相应的进程中被强制地剥夺。 循环等待条件(circular wait)：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。 对应到sql

2、server中，当在两个或多个任务中，假如每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永远堵塞，从而浮现死锁；这些资源可能是：单行(rid，堆中的单行)、索引中的键(key，行锁)、页(pag，8kb)、区结构(ext，延续的8页)、堆或b树(hobt) 、表(tab，包括数据和索引)、文件(file，数据库文件)、应用程序专用资源(app)、元数据(metadata)、分配单元(alloion_unit)、囫囵数据库(db)。一个死锁示例如下图所示： 解释：t1、t2表示两个任务；r1和r2表示两个资源；由资源指向任务的箭头(如r1- t1，r2- t2)表示该资源被改任务所持有

3、；由任务指向资源的箭头(如t1- s2，t2- s1)表示该任务正在哀求对应目标资源； 其满足上面死锁的四个须要条件： (1).互斥：资源s1和s2不能被分享，同一时光只能由一个任务用法； (2).哀求与保持条件：t1持有s1的同时，哀求s2；t2持有s2的同时哀求s1； (3).非剥夺条件：t1无法从t2上剥夺s2，t2也无法从t1上剥夺s1； (4).循环等待条件：上图中的箭头构成环路，存在循环等待。 2. 死锁排查 (1). 用法sql server的系统存储过程sp_和sp_lock，可以查看当前数据库中的锁状况；进而按照objectid(objid)(sql server 2005)

4、/ object_name(objid)(sql server 2000)可以查看哪个资源被锁，用dbcc ld(blk)，可以查看最后一条发生给sql server的sql语句； create table who(sp int, ecid int, us nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_id int); create table lock(spid int, dpid int, objid i

5、nt, indld int, type nvarchar(20), resource nvarchar(50), mode nvarchar(10), status nvarchar(10) ); insert into who exec sp_who active -看哪个引起的堵塞，blk insert into lock exec sp_lock -看锁住了那个资源id，objid declare dbname nvarchar(20); set dbname='nameofdatabase' select who.* from who where dbname=dbna

6、me select lock.* from lock join who on who.spid=lock.spid and dbname=dbname; -最后发送到sql server的语句 declare crsr cursor for select blk from who where dbname=dbname and blk 0; declare blk int; open crsr; fetch next from crsr into blk; while (fetch_status = 0) begin; dbcc inputbuffer(blk); fetch next fro

7、m crsr into blk; end; close crsr; deallocate crsr; -锁定的资源 select who.spid,hostname,objid,type,mode,object_name(objid) as objname from lock join who on who.spid=lock.spid and dbname=dbname where objid 0; drop table who; drop table lock; (2). 用法 sql server pror 分析死锁: 将 deadlock graph 大事类添加到跟踪。此大事类用法死锁

8、涉及到的进程和对象的 xml 数据填充跟踪中的 textdata 数据列。sql server 大事探查器 可以将 xml 文档提取到死锁 xml (.xdl) 文件中，以后可在 sql server management studio 中查看该文件。 3. 避开死锁 上面1中列出了死锁的四个须要条件，我们只要想方法破其中的随意一个或多个条件，就可以避开死锁发生，普通有以下几种办法(from sql server 2005联机丛书)： (1).按同一挨次拜访对象。(注：避开浮现循环) (2).避开事务中的用户交互。(注：削减持有资源的时光，较少锁竞争) (3).保持事务简短并处于一个批处理中。

9、(注：同(2)，削减持有资源的时光) (4).用法较低的隔离级别。(注：用法较低的隔离级别（例如已提交读）比用法较高的隔离级别（例如可序列化）持有分享锁的时光更短，削减锁竞争) (5).用法基于行版本控制的隔离级别：2005中支持快照事务隔离和指定read_committed隔离级别的事务用法行版本控制，可以将读与写操作之间发生的死锁几率降至最低： set allow_snapshot_isolation on -事务可以指定 snapshot 事务隔离级别; set read_committed_snapshot on -指定 read_committed 隔离级别的事务将用法行版本控制而不

10、是锁定。默认状况下(没有开启此选项，没有加with nolock提醒)，select语句会对哀求的资源加s锁(分享锁)；而开启了此选项后，select不会对哀求的资源加s锁。 注重：设置 read_committed_snapshot 选项时，数据库中只允许存在执行 alter database 的衔接。在 alter database 完成之前，数据库中决不能有其他打开的衔接。数据库不必一定要处于单用户模式中。 (6).用法绑定衔接。(注：绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话分享相同的事务和锁(但每个回话保留其自己的事务隔离级别)，并可以用法同一数

11、据，而不会有锁矛盾。可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin an)后，调用exec sp_getbindtoken token out;来取得token，然后传入另一个会话并执行exec sp_bindsession token来举行绑定(最后的示例中演示了绑定衔接)。 4. 死锁处理办法： (1). 按照2中提供的sql，查看那个spid处于wait状态，然后用 spid来干掉(即破坏死锁的第四个须要条件:循环等待)；固然这只是一种暂时解决计划，我们总不能在碰到死锁就在用户的生产环境上排查死锁、kil

12、l sp，我们应当考虑如何去避开死锁。 (2). 用法set lock_timeout out_period(单位为毫秒)来设定锁哀求超时。默认状况下，数据库没有超时期限(timeout_period 为-1，可以用select lock_timeout来查看该 ，即无限期等待)。当哀求锁超过timeout_period时，将返回错误。timeout_period 为0时表示根本不等待，一碰到锁就返回消息。设置锁哀求超时，破环了死锁的其次个须要条件(哀求与保持条件)。 服务器: 消息 1222，级别 16，状态 50，行 1 已超过了锁哀求超时时段。 (3). sql server内部有一个锁

13、监视器线程执行死锁检查，锁监视器对特定线程启动死锁搜寻时，会标识线程正在等待的资源；然后查找特定资源的全部者，并递归地继续执行对那些线程的死锁搜寻，直到找到一个构成死锁条件的循环。检测到死锁后，数据库引擎 挑选运行回滚开销最小的事务的会话作为死锁牺牲品，返回1205 错误，回滚死锁牺牲品的事务并释放该事务持有的全部锁，使其他线程的事务可以哀求资源并继续运行。 5. 两个死锁示例及解决办法 5.1 sql死锁 (1). 测试用的基础数据： create table lock1(c1 int default(0); create table lock2(c1 int default(0); ins

14、ert into lock1 values(1); insert into lock2 values(1); (2). 开两个查询窗口，分离执行下面两段sql -query 1 begin tran up lock1 set c1=c1 waitfor delay '00:01:00' select * from lock2 rollback tran; -query 2 begin tran update lock2 set c1=c1 waitfor delay '00:01:00' select * from lock1 rollback tran; 上面

15、的sql中有一句waitfor delay '00:01:00'，用于等待1分钟，以便利查看锁的状况。 (3). 查看锁状况 在执行上面的waitfor语句期间，执行其次节中提供的语句来查看锁信息： query1中，持有lock1中第一行(表中惟独一行数据)的行排他锁(rid:x)，并持有该行所在页的意向更新锁(pag:ix)、该表的意向更新锁(tab:ix)；query2中，持有lock2中第一行(表中惟独一行数据)的行排他锁(rid:x)，并持有该行所在页的意向更新锁(pag:ix)、该表的意向更新锁(tab:ix)； 执行完waitfor，query1查询lock2，哀求

16、在资源上加s锁，但该行已经被query2加上了x锁；query2查询lock1，哀求在资源上加s锁，但该行已经被query1加上了x锁；于是两个查询持有资源并互不相让，构成死锁。 (4). 解决方法 a). sql server自动挑选一条sql作死锁牺牲品：运行完上面的两个查询后，我们会发觉有一条sql能正常执行完毕，而另一个sql则报如下错误： 服务器: 消息 1205，级别 13，状态 50，行 1 事务（进程 id xx）与另一个进程已被死锁在 lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。 这就是上面第四节中介绍的锁监视器干活了。 b). 按同一挨次拜访对象：颠倒随

17、意一条sql中的update与select语句的挨次。例如修改其次条sql成如下： -query2 begin tran select * from lock1-在lock1上申请s锁 waitfor delay '00:01:00' update lock2 set c1=c1 -lock2:rid:x rollback tran; 固然这样修改也是有代价的，这会导致第一条sql执行完毕之前，其次条sql向来处于堵塞状态。单独执行query1或query2需要约1分钟，但假如开头执行query1时，马上同时执行query2，则query2需要2分钟才干执行完；这种按挨次哀求资

18、源从一定程度上降低了并发性。 c). select语句加with(nolock)提醒：默认状况下select语句会对查询到的资源加s锁(分享锁)，s锁与x锁(排他锁)不兼容；但加上with(nolock)后，select不对查询到的资源加锁(或者加sch-s锁，sch-s锁可以与任何锁兼容)；从而可以是这两条sql可以并发地拜访同一资源。固然，此办法适合解决读与写并发死锁的状况，但加with(nolock)可能会导致脏读。 select * from lock2 with(nolock) select * from lock1 with(nolock) d). 用法较低的隔离级别。sql se

19、rver 2000支持四种事务处理隔离级别(til)，分离为：read uncommitted、read committed、repeatable read、serializable；sql server 2005中增强了snapshot til。默认状况下，sql server用法read committed til，我们可以在上面的两条sql前都加上一句set transaction isolation level read uncommitted，来降低til以避开死锁；实际上，运行在read uncommitted til的事务，其中的select语句不对结果资源加锁或加sch-s锁，

20、而不会加s锁；但还有一点需要注重的是：read uncommitted til允许脏读，虽然加上了降低til的语句后，上面两条sql在执行过程中不会报错，但执行结果是一个返回1，一个返回2，即读到了脏数据，大概这并不是我们所期望的。 e). 在sql前加set lock_timeout timeout_period，当哀求锁超过设定的timeout_period时光后，就会终止当前sql的执行，牺牲自己，成全别人。 f). 用法基于行版本控制的隔离级别(sql server 2005支持)：开启下面的选项后，select不会对哀求的资源加s锁，不加锁或者加sch-s锁，从而将读与写操作之间发生

21、的死锁几率降至最低；而且不会发生脏读。啊 set allow_snapshot_isolation on set read_committed_snapshot on g). 用法绑定衔接(用法办法见下一个示例。) 5.2 程序死锁(sql堵塞) 看一个例子：一个典型的数据库操作事务死锁分析，根据我自己的理解，我觉得这应当算是c程序中浮现死锁，而不是数据库中的死锁；下面的代码模拟了该文中对数据库的操作过程： /略去的无关的code sqlconnection conn = new sqlconnection(connectionstring); conn.open(); sqltransact

22、ion tran = conn.begintransaction(); string sql1 = update lock1 set c1=c1 1 string sql2 = select * from lock1 exeenonquery(tran, sql1); /用法事务:事务中lock了table executenonquery(null, sql2); /新开一个connection来读取table public static void executenonquery(sqltransaction tran, string sql) sqlcommand cmd = new sql

23、command(sql); if (tran != null) cmd.connection = tran.connection; cmd.transaction = tran; cmd.executenonquery(); ee using (sqlconnection conn = new sqlconnection(connectionstring) conn.open(); cmd.connection = conn; cmd.executenonquery(); 执行到executenonquery(null, sql2)时抛出sql执行超时的异样，下图从数据库的角度来看该问题： 代

24、码从上往下执行，会话1持有了表lock1的x锁，且事务没有结束，回话1就向来持有x锁不释放；而会话2执行select操作，哀求在表lock1上加s锁，但s锁与x锁是不兼容的，所以回话2的被堵塞等待，不在等待中，就在等待中获得资源，就在等待中超时。从中我们可以看到，里面并没有浮现死锁，而只是select操作被堵塞了。也正由于不是数据库死锁，所以sql server的锁监视器无法检测到死锁。 我们再从c程序的角度来看该问题： c程序持有了表lock1上的x锁，同时开了另一个sqlconnection还想在该表上哀求一把s锁，图中已经构成了环路；太贪心了，结果自己把自己给锁死了。 虽然这不是一个数据

25、库死锁，但却是由于数据库资源而导致的死锁，上例中提到的解决死锁的办法在这里也基本适用，主要是避开读操作被堵塞，解决办法如下： a). 把select放在update语句前：select不在事务中，且执行完毕会释放s锁； b). 把select也放加入到事务中：executenonquery(tran, sql2); c). select加with(nolock)提醒：可能产生脏读； d). 降低事务隔离级别：select语句前加set transaction isolation level read uncommitted；同上，可能产生脏读； e). 用法基于行版本控制的隔离级别（同上例）。

26、 g). 用法绑定衔接：取得事务所在会话的token，然后传入新开的connection中；执行exec sp_bindsession token后绑定了衔接，最后执行exec sp_bindsession null;来取消绑定；最后需要注重的四点是： (1). 用法了绑定衔接的多个connection分享同一个事务和相同的锁，但各自保留自己的事务隔离级别； (2). 假如在sql3字符串的“exec sp_bindsession null”换成“commit tran”或者“rollback tran”，则会提交囫囵事务，最后一行c代码tran.commit()就可以不用执行了(执行会报错，由于事务已经结束了-,-)。 (3). 开启事务(begin tran)后，才可以调用exec sp_getbindtoken token out来取得token；假如不想再新开的connection中结束掉原有的事务，则在这个connection close之前，必需执行“exec sp_bindsess