sql处理数据库锁的存储过程分享
前端之家收集整理的这篇文章主要介绍了
sql处理数据库锁的存储过程分享，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
邹建 2004.4 @H_502_0@<div class="codetitle"><a style="CURSOR: pointer" data="47669" class="copybut" id="copybut47669" onclick="doCopy('code47669')"> 代码如下:
<div class="codebody" id="code47669"> @H_
502_0@/
--调用示例 @H_502_0@exec plockinfo1 @H502_0@--/ @H_
502_0@alter proc p
lockinfo1 @H502_0@@kill_lock_spid bit=1,--是否杀掉死锁的进程,1 杀掉,0 仅
显示 @H_
502_0@@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否
显示正常进程信息,1
显示,0
不显示 @H_
5020@as @H5020@declare @count int,@s nvarchar(max),@i int @H5020@select id=identity(int,1,1),标志,@H502_0@进程ID=spid,线程ID=kpid,块进程ID=blocked,
数据库ID=dbid,@H_
502_0@
数据库名=db_name(dbid),
用户ID=uid,
用户名=loginame,累计
cpu时间=
cpu,@H_
502_0@登陆时间=login_time,打开事务数=open
tran,进程状态=status,@H502_0@工作站名=hostname,应用程序名=program
name,工作站进程ID=hostprocess,@H502_0@域名=nt_domain,网卡地址=net
address @H5020@into #t from( @H5020@select 标志='死锁的进程',@H502_0@spid,kpid,a.blocked,dbid,uid,loginame,
cpu,login_time,open
tran,@H502_0@status,hostname,program_name,hostprocess,nt_domain,net
address,@H5020@s1=a.spid,s2=0 @H5020@from master..sysprocesses a join ( @H5020@select blocked from master..sysprocesses group by blocked @H5020@)b on a.spid=b.blocked where a.blocked=0 @H5020@union all @H502_0@select '|
牺牲品>',blocked,@H_
5020@s1=blocked,s2=1 @H5020@from master..sysprocesses a where blocked<>0 @H502_0@)a order by s1,s2 select @count=@@rowcount,@i=1 if @count=0 and @show_spid_if
nolock=1 @H5020@begin @H5020@insert #t @H502_0@select 标志='正常的进程',db
name(dbid),@H502_0@open_tran,status,net
address @H5020@from master..sysprocesses @H5020@set @count=@@rowcount @H5020@end if @count>0 @H5020@begin @H5020@create table #t1(id int identity(1,a nvarchar(max),b Int,EventInfo nvarchar(max)) @H502_0@if @kill_lock
spid=1 @H5020@begin @H5020@declare @spid varchar(max),@标志 varchar(max) @H5020@while @i<=@count @H5020@begin @H5020@select @spid=进程ID,@标志=标志 from #t where id=@i @H5020@insert #t1 exec('dbcc inputbuffer('+@spid+')') @H5020@if @标志='死锁的进程' exec('kill '+@spid) @H5020@set @i=@i+1 @H5020@end @H5020@end @H5020@else @H5020@while @i<=@count @H5020@begin @H5020@select @s='dbcc inputbuffer('+cast(进程ID as varchar(max))+')' from #t where id=@i @H5020@insert #t1 exec(@s) @H5020@set @i=@i+1 @H5020@end @H502_0@select a.*,进程的
sql语句=b.EventInfo @H_
5020@from #t a join #t1 b on a.id=b.id @H5020@end @H5020@go @H502_0@
