sql server 死锁总结

deadlocks（死锁） 所谓死锁: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程. 由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。 一种情形，此时执行程序中两个或多个线程发生永久堵塞（等待），每个线程都在等待被其他线程占用并堵塞了的资源。例如，如果线程A锁住了记录1并等待记录2，而线程B锁住了记录2并等待记录1，这样两个线程就发生了死锁现象。 计算机系统中,如果系统的资源分配策略不当，更常见的可能是程序员写的程序有错误等，则会导致进程因竞争资源不当而产生死锁的现象。 产生死锁的原因主要是： （1） 因为系统资源不足。 （2） 进程运行推进的顺序不合适。 （3） 资源分配不当等。 如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁 产生死锁的四个必要条件： （1） 互斥条件：一个资源每次只能被一个进程使用。 （2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。 （3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。 （4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之 一不满足，就不会发生死锁。 死锁的解决：预防和解除 1。死锁的预防：1）一次封锁法：就是要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行。但是出现这样的问题：因为一次给全部数据加锁，从而降低系统的并发度，同时数据库中数据是不断变化的，原来不要求封锁的数据在执行过程中可能会变成封锁对象。2）顺序封锁法，预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实行封锁，问题：数据库中数据对象多，且不断的变化，维护困难，成本高；事务的封锁请求可以随着事务的执行而动态的决定。 2。检测死锁 企业管理器－>可以在[管理]->[当前活动] 里看到阻塞信息(有时SQL Server企业管理器会因为锁太多而没有响应). 设定跟踪1204: USE MASTER DBCC TRACEON (1204,-1) 显示当前启用的所有跟踪标记的状态: DBCC TRACESTATUS(-1) 取消跟踪1204: DBCC TRACEOFF (1204,-1) 在设定跟踪1204后,会在数据库的日志文件里显示SQL Server数据库死锁时一些信息。但那些信息很难看懂,需要对照SQL Server联机丛书仔细来看。根据PAG锁要找到相关数据库表的方法: DBCC TRACEON (3604) DBCC PAGE (db_id,file_id,page_no) DBCC TRACEOFF (3604) 可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。 写了一个系统存储过程sp_who_lock.sql。代码如下： if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[sp_who_lock] GO /**//******************************************************** // 说明 : 查看数据库里阻塞和死锁情况 ********************************************************/ use master go 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 @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (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>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end 需要的时候直接调用: sp_who_lock 就可以查出引起死锁的进程和SQL语句. 我们还可以设置锁的超时时间(单位是毫秒), 来缩短死锁可能影响的时间范围: 例如: use master seelct @@lock_timeout set lock_timeout 900000 -- 15分钟 seelct @@lock_timeout ********************************************************************************************************* 常看到死锁的问题,一般都是KILL进程,但如果不查出引起死锁的原因,死锁会时常发生 可以通过查找引起死锁的的操作,就可以方便的解决死锁,现将日常解决问题的方法总结,也许对大家有帮助 1/死锁发生时,通过如下语法,查询出引起死锁的操作 use master go declare @spid int,@bl int DECLARE s_cur CURSOR FOR select 0 ,blocked from (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>0 OPEN s_cur FETCH NEXT FROM s_cur INTO @spid,@bl WHILE @@FETCH_STATUS = 0 begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) FETCH NEXT FROM s_cur INTO @spid,@bl end CLOSE s_cur DEALLOCATE s_cur exec sp_who2 2/查找程序/数据库,此t_sql语法在什么地方使用 3/分析找到的,并解决问题 EG： /* ------------------------------------------------------- 引起数据库死锁的是: 71进程号,其执行的SQL语法如下 EventType Parameters EventInfo -------------- ---------- ------------------------------------------------ Language Event 0 select * from test insert test values(1,2) （所影响的行数为 1 行） DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ------------------------------------------------------------------------------ 进程号SPID：64被进程号SPID：71阻塞,其当前进程执行的SQL语法如下 EventType Parameters EventInfo -------------- ---------- ------------------------------------------------ Language Event 0 select * from test insert test values(1,2) （所影响的行数为 1 行） DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ------------------------------------------------------------------------------ 进程号SPID：65被进程号SPID：64阻塞,其当前进程执行的SQL语法如下 EventType Parameters EventInfo -------------- ---------- -------------------------------------------------------------------------------------------------- Language Event 0 begin tran select * from test with (holdlock) waitfor time '12:00' select * from test commit （所影响的行数为 1 行） DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 ------------------------------------------------------------------------------ 进程号SPID：73被进程号SPID：64阻塞,其当前进程执行的SQL语法如下 EventType Parameters EventInfo -------------- ---------- -------------------------------------------------------------------------------------------------- Language Event 0 begin tran select * from test with (holdlock) waitfor time '12:00' select * from test commit （所影响的行数为 1 行） DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。 */ *************************************************************************************************** 解决方法： 　　让用户（即先读后写类型的操作),在select 时就是用Update lock 　　语法如下： select * from table1 with(updlock) where ....