sql 死锁查看

MSSQL(查找死锁)：

use master 钱塘个人空间 IV/rs l-WU
go 钱塘个人空间h;{7K-g&m&ukh:N
declare @spid int,@bl int 钱塘个人空间6|QFf+v f%D
DECLARE s_cur CURSOR FOR  钱塘个人空间!F#Y2]*W+_'Jz
select  0 ,blocked
;y+O(k0u!s0from (select * from sysprocesses where  blocked>0 ) a 
X.G"mx`]9E-A0where not exists(select * from (select * from sysprocesses where  blocked>0 ) b  where a.blocked=spid)
SQ#J` _ u0union 钱塘个人空间1Up1W8a kfb;?
select spid,blocked from sysprocesses where  blocked>0
[P/xIy)D8[(B,qM
\0OPEN s_cur
;hz9r-~x^+]8`0FETCH NEXT FROM s_cur INTO @spid,@bl
)B`4`:v$u0\w;nm|0WHILE @@FETCH_STATUS = 0 钱塘个人空间 ?9M,u'Rx`Ge'_3uLL#u
begin
(df~5J?3^$tz px8@0if @spid =0  钱塘个人空间7SB:P)Dj3])H
            select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' 钱塘个人空间z{9}:?4O
else 钱塘个人空间M5e
s%S?,Dw[1dP z'a
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' 钱塘个人空间vI
{ p#n,P
DBCC INPUTBUFFER (@bl )
%L*WOFa,?0FETCH NEXT FROM s_cur INTO @spid,@bl
1W%_ _(]M2]@}0end 钱塘个人空间/c,i6VX5o8h
n!p
CLOSE s_cur 钱塘个人空间/XHc3I.U-eb
DEALLOCATE s_cur

a`GBXM.a)ff6V0--邹建的
a7N V!z)R&a ft0create proc sp_lockinfo钱塘个人空间D,Su:|+E"y8sr
@kill_lock_spid bit=1,      --是否杀掉阻塞的进程,1 杀掉, 0 仅显示钱塘个人空间@K6\x)VE^
@show_spid_if_nolock bit=1, --如果没有阻塞的进程,是否显示正常进程信息,1 显示,0 不显示钱塘个人空间mIF#PlD6m2S$t
@dbname sysname=''          --如果为空,则查询所有的库,如果为null,则查询当前库,否则查询指定库钱塘个人空间%\/cVd0? c#K@ zO[
as
r+QFP\~6w{)|0set nocount on
6I6Wa2c?I0declare @count int,@s nvarchar(2000),@dbid int
x-Lo$qC5J W0if @dbname='' set @dbid=db_id() else set @dbid=db_id(@dbname)

select id=identity(int,1,1),标志,
^0[;z7k,sp K0进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,钱塘个人空间]RH JK}eU
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
9P(g8Wq V0登陆时间=login_time,打开事务数=open_tran,进程状态=status,钱塘个人空间S&~z(V2~9lh+O1C
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
Eo3X#H&KA0域名=nt_domain,网卡地址=net_address
Jj@z#x(s-D"r/e0into #t from(
*l0oP#T&Cwlbn8]l0select 标志='阻塞的进程',
r BVKg)|/w5_0spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,钱塘个人空间$b)s I(s7H#{i
status,hostname,program_name,hostprocess,nt_domain,net_address,
*`"x"n|6G6ob0s1=a.spid,s2=0
'qQ`*K@9J
S0from master..sysprocesses a join (钱塘个人空间 g/S]Z)yD3U
select blocked from master..sysprocesses
vgc3K'kn o2n3D{0where blocked>0钱塘个人空间o{2j7v1mzX
and(@dbid is null or dbid=@dbid)钱塘个人空间!s-X!V)I5_I8Y
group by blocked
8I }R*b5f!?!e\0)b on a.spid=b.blocked 钱塘个人空间iA0ky{
where a.blocked=0钱塘个人空间[U.|g \9}
and(@dbid is null or dbid=@dbid)
7jkYy%D8D+a0union all
u'kS` hE[0select '|_牺牲品_>',
nAk%@i2h0spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
!C,gn{m"D0status,hostname,program_name,hostprocess,nt_domain,net_address,
j'UV1m;k0K&w0s1=blocked,s2=spid钱塘个人空间t!C]*NdwO
from master..sysprocesses a
xNe TI9a0where blocked<>0钱塘个人空间;een%n JW \+LN0c
and(@dbid is null or dbid=@dbid)钱塘个人空间 K,i%[\Ja
)a order by s1,s2

select @count=@@rowcount

if @count=0 and @show_spid_if_nolock=1
b2Gb3Z$x&k,CtF0begin钱塘个人空间f6|(s6s~2d
insert #t钱塘个人空间/aMt:yB(Q
select 标志='正常的进程',钱塘个人空间/K8aH_s
[P
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,钱塘个人空间!C9{](?X]
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
TGs_'G$V,gb0from master..sysprocesses
2Y$Hs SJs7c-T0where @dbid is null or dbid=@dbid钱塘个人空间lzD_pGy(W
order by spid钱塘个人空间5u3YkS2y
set @count=@@rowcount
G%f]0d/fA]
dt0end

if @count>0
hQAs3CRl1Ws0begin
hFP.b)XeT6tT0create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
q+Xt)B8}0declare tb cursor local钱塘个人空间R9Mj%~`
for
B4T,^jk0select N'insert #t1 exec(''dbcc inputbuffer('+rtrim(进程ID)+')'')钱塘个人空间9@%\@#Ot E3]8P0Jt d
if @@rowcount=0 insert #t1(a) values(null)钱塘个人空间+?8jhM!kO/T h
'+case when @kill_lock_spid=1 and 标志=N'阻塞的进程'
(zd[:nb1z0then 'kill '+rtrim(进程ID) else '' end钱塘个人空间+gAh7?$~qL-z2\
from #t钱塘个人空间a7E1otbP
open tb
:Yld7pt0fetch tb into @s钱塘个人空间5jV:{^7a8vJ"t.K!]
while @@fetch_status=0
!G$b$Nu z | M-W0begin钱塘个人空间)@J4Peo5S#g k
exec(@s)
P
Xe M#Ho@#?v3S0fetch tb into @s钱塘个人空间`,s7N$Y9p}q^?
end钱塘个人空间 ZA"F$g&^m5]&_5t
close tb钱塘个人空间]0A+X3[$F
deallocate tb
y5@(nh^J)Gn3lb0select a.*,进程的SQL语句=b.EventInfo
O\'F
L1Vr/F%W`0from #t a join #t1 b on a.id=b.id
Lz\oc0CCC0order by a.ID
9TDW+i-p0end
UK'DtQ P'`e$j0set nocount off
K/eB1l(L!u$ta/m0go钱塘个人空间(de
y'hinY
v