【网学网提醒】：网学会员为大家收集整理了解决sqlserver死锁提供大家参考，希望对大家有所帮助!
SQLServer自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁,但没有这里介绍的方法好用。
usemaster
go
createproceduresp_who_lock
as
begin
declare@spidint,@blint,
@intTransactionCountOnEntryint,
@intRowcountint,
@intCountPropertiesint,
@intCounterint
createtable#tmp_lock_who(
idintidentity(1,1),
spidsmallint,
blsmallint)
IF@@ERROR<>0RETURN@@ERROR
insertinto#tmp_lock_who(spid,bl)select0,blocked
from(select*fromsysprocesseswhereblocked>0)a
wherenotexists(select*from(select*fromsysprocesseswhereblocked>0)b
wherea.blocked=spid)
unionselectspid,blockedfromsysprocesseswhereblocked>0
IF@@ERROR<>0RETURN@@ERROR
–找到临时表的记录数
select@intCountProperties=Count(*),@intCounter=1
from#tmp_lock_who
IF@@ERROR<>0RETURN@@ERROR
if@intCountProperties=0
select'现在没有阻塞和死锁信息'asmessage
–循环开始
while@intCounter<=@intCountProperties
begin
–取第一条记录
select@spid=spid,@bl=bl
from#tmp_lock_whowhereId=@intCounter
begin
if@spid=0
select'引起数据库死锁的是:'+CAST(@blASVARCHAR(10))+'进程号,其执行的SQL语法如下'
else
select'进程号SPID：'+CAST(@spidASVARCHAR(10))+'被'+'进程号SPID：'+CAST(@blASVARCHAR(10))+'阻塞,其当前进程执行的SQL语法如下'
DBCCINPUTBUFFER(@bl)
end
–循环指针下移
set@intCounter=@intCounter+1
end
droptable#tmp_lock_who
return0
end
杀死锁和进程
如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。
usemaster
go
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_killspid]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_killspid]
GO
createprocp_killspid
@dbnamevarchar(200)–要关闭进程的数据库名
as
declare@sqlnvarchar(500)
declare@spidnvarchar(20)
declare#tbcursorfor
selectspid=cast(spidasvarchar(20))frommaster..sysprocesseswheredbid=db_id(@dbname)
open#tb
fetchnextfrom#tbinto@spid
while@@fetch_status=0
begin
exec('kill'+@spid)
fetchnextfrom#tbinto@spid
end
close#tb
deallocate#tb
go
–用法
execp_killspid'newdbpy'
查看锁信息
如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。
–查看锁信息
createtable#t(req_spidint,obj_namesysname)
declare@snvarchar(4000)
,@ridint,@dbnamesysname,@idint,@objnamesysname
declaretbcursorfor
selectdistinctreq_spid,
dbname=db_name(rsc_dbid),rsc_objid
frommaster..syslockinfowherersc_typein(4,5)
opentb
fetchnextfromtbinto@rid,@dbname,@id
while@@fetch_status=0
begin
set@s='select@objname=namefrom['+@dbname+']..sysobjectswhereid=@id'
execsp_executesql@s,N'@objnamesysnameout,@idint',@objnameout,@id
insertinto#tvalues(@rid,@objname)
fetchnextfromtbinto@rid,@dbname,@id
end
closetb
deallocatetb
select进程id=a.req_spid
,数据库=db_name(rsc_dbid)
,类型=casersc_typewhen1then'NULL资源（未使用）'
when2then'数据库'
when3then'文件'
when4then'索引'
when5then'表'
when6then'页'
when7then'键'
when8then'扩展盘区'
when9then'RID（行ID)'
when10then'应用程序'
end
,对象id=rsc_objid
,对象名=b.obj_name
,rsc_indid
frommaster..syslockinfoaleftjoin#tbona.req_spid=b.req_spid
go
droptable#t
