sql server的死锁及处理方法

【转】处理sql server的死锁--第一篇--检测死锁--如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？--这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。

SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。

use mastergocreate procedure sp_who_lockasbegindeclare @spid int,@bl int,@intTransactionCountOnEntry int,@intRowcount int,@intCountProperties int,@intCounter intcreate table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 RETURN @@ERRORinsert into #tmp_lock_who(spid,bl) select 0 ,blockedfrom (select * from sysprocesses where blocked>0 ) awhere not exists(select * from (select * from sysprocesseswhere blocked>0 ) bwhere a.blocked=spid)union select spid,blocked from sysprocesses where blocked>0IF @@ERROR<>0 RETURN @@ERROR-- 找到临时表的记录数select @intCountProperties = Count(*),@intCounter = 1from #tmp_lock_whoIF @@ERROR<>0 RETURN @@ERRORif @intCountProperties=0select '现在没有阻塞和死锁信息' as message-- 循环开始while @intCounter <= @intCountPropertiesbegin-- 取第一条记录select @spid = spid,@bl = blfrom #tmp_lock_who where Id = @intCounterbeginif @spid =0select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'elseselect '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'DBCC INPUTBUFFER (@bl )end-- 循环指针下移set @intCounter = @intCounter + 1enddrop table #tmp_lock_whoreturn 0end--杀死锁和进程--如何去手动的杀死进程和锁？最简单的办法，重新启动服务。

但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。

use mastergoif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[p_killspid]GOcreate proc p_killspid@dbname varchar(200) --要关闭进程的数据库名asdeclare @sql nvarchar(500)declare @spid nvarchar(20)declare #tb cursor forselect spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)open #tbfetch next from #tb into @spidwhile @@fetch_status=0beginexec('kill '+@spid)fetch next from #tb into @spidendclose #tbdeallocate #tbgo--用法exec p_killspid 'newdbpy'--查看锁信息--如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。

--查看锁信息create table #t(req_spid int,obj_name sysname)declare @s nvarchar(4000),@rid int,@dbname sysname,@id int,@objname sysnamedeclare tb cursor forselect distinct req_spid,dbname=db_name(rsc_dbid),rsc_objidfrom master..syslockinfo where rsc_type in(4,5)open tbfetch next from tb into @rid,@dbname,@idwhile @@fetch_status=0beginset @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'exec sp_executesql @s,N'@objname sysname out,@id int',@objname out,@idinsert into #t values(@rid,@objname)fetch next from tb into @rid,@dbname,@idendclose tbdeallocate tbselect 进程id=a.req_spid,数据库=db_name(rsc_dbid),类型=case rsc_type when 1 then 'NULL 资源（未使用）'when 2 then '数据库'when 3 then '文件'when 4 then '索引'when 5 then '表'when 6 then '页'when 7 then '键'when 8 then '扩展盘区'when 9 then 'RID（行ID)'when 10 then '应用程序'end,对象id=rsc_objid,对象名=b.obj_name,rsc_indidfrom master..syslockinfo a left join #t b on a.req_spid=b.req_spidgodrop table #t--第二篇------------------版本1 ------------------------------------ /parable-myth/archive/2007/10/15/153010.html sqlserver 解除死锁if exists (select*from dbo.sysobjects where id =object_id(N'[dbo].[p_lockinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure[dbo].[p_lockinfo]GOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGO/*--处理死锁查看当前进程,或死锁进程,并能自动杀掉死进程因为是针对死的,所以如果有死锁进程,只能查看死锁进程当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程感谢: caiyunxia,jiangopen 两位提供的参考信息--邹建2004.4--*//*--调用示例exec p_lockinfo--*/create proc p_lockinfo@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock bit=1--如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示asdeclare@count int,@s nvarchar(1000),@i intselect id=identity(int,1,1),标志,进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,登陆时间=login_time,打开事务数=open_tran, 进程状态=status,工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,域名=nt_domain,网卡地址=net_addressinto #t from(select标志='死锁的进程',spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=a.spid,s2=0from master..sysprocesses a join (select blocked from master..sysprocesses group by blocked)b on a.spid=b.blocked where a.blocked=0union allselect'|_牺牲品_>',spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1=blocked,s2=1from master..sysprocesses a where blocked<>0)a order by s1,s2select@count=@@rowcount,@i=1if@count=0and@show_spid_if_nolock=1begininsert #tselect标志='正常的进程',spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_addre ssfrom master..sysprocessesset@count=@@rowcountif@count>0begincreate table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))if@kill_lock_spid=1begindeclare@spid varchar(10),@标志varchar(10)while@i<=@countbeginselect@spid=进程ID,@标志=标志from #t where id=@iinsert #t1 exec('dbcc inputbuffer('+@spid+')')if@标志='死锁的进程'exec('kill '+@spid)set@i=@i+1endendelsewhile@i<=@countbeginselect@s='dbcc inputbuffer('+cast(进程ID as varchar)+')'from #t whereid=@iinsert #t1 exec(@s)set@i=@i+1endselect a.*,进程的SQL语句=b.EventInfofrom #t a join #t1 b on a.id=b.idendGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO----------版本2：我改写的(KILL 死锁时间超过15秒的死锁进程---------------/*exec p_lockinfo 0,1;*/alter proc p_lockinfo@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示@show_spid_if_nolock bit=1--如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示declare@count int,@s nvarchar(1000),@i intselect id=identity(int,1,1),标志,进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,等待时间=waittime,登陆时间=login_time,打开事务数=open_tran, 进程状态=status,工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess, 域名=nt_domain,网卡地址=net_addressinto #t from(select标志='死锁的进程',spid,kpid,a.blocked,dbid,uid,loginame,cpu,waittime,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address,s1=a.spid,s2=0from master..sysprocesses a join (select blocked from master..sysprocesses group by blocked)b on a.spid=b.blocked where a.blocked=0union allselect'|_牺牲品_>',spid,kpid,blocked,dbid,uid,loginame,cpu,waittime,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address,s1=blocked,s2=1from master..sysprocesses a where blocked<>0)a order by s1,s2select@count=@@rowcount,@i=1if@count=0and@show_spid_if_nolock=1begininsert #tselect标志='正常的进程',spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,waittime,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_addre ssfrom master..sysprocessesset@count=@@rowcountendif@count>0begincreate table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(4000))if@kill_lock_spid=1begindeclare@spid varchar(10),@标志varchar(10), @等待时间intwhile@i<=@countbeginselect@spid=进程ID,@标志=标志, @等待时间=等待时间from #t where id=@i insert #t1 exec('dbcc inputbuffer('+@spid+')')if@标志='死锁的进程'and@等待时间>=15000exec('kill '+@spid)set@i=@i+1endendelsewhile@i<=@countbeginselect@s='dbcc inputbuffer('+cast(进程ID as varchar)+')'from #t whereid=@iinsert #t1 exec(@s)set@i=@i+1endselect a.*,进程的SQL语句=b.EventInfofrom #t a join #t1 b on a.id=b.idendGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO================================================ ================================================ ====锁知识未整理。