解决SQL 死锁的语句

ALTER     procedure [dbo].[sp_lock_check]
@spid1 int = NULL,               
@spid2 int = NULL               
as

set nocount on

if @spid1 is not NULL
begin
        select         convert (smallint, req_spid) As spid,
                loginame=rtrim(loginame),hostname ,t2.name,
                rsc_dbid As dbid,
                rsc_objid As ObjId,
                rsc_indid As IndId,
                substring (v.name, 1, 4) As Type,
                substring (rsc_text, 1, 16) as Resource,
                substring (u.name, 1, 8) As Mode,
                substring (x.name, 1, 5) As Status

        from         master.dbo.syslockinfo(nolock),
                master.dbo.spt_values v(nolock),
                master.dbo.spt_values x(nolock),
                master.dbo.spt_values u(nolock),
                master.dbo.sysprocesses t1(nolock),
                sysobjects t2(nolock)
        where   master.dbo.syslockinfo.rsc_type = v.number
                        and v.type = 'LR'
                        and master.dbo.syslockinfo.req_status = x.number
                        and x.type = 'LS'
                        and master.dbo.syslockinfo.req_mode + 1 = u.number
                        and u.type = 'L'
                        and t1.spid=convert (smallint, req_spid)
                        and req_spid in (@spid1, @spid2)
                        and rsc_objid =t2.Id
end
else
begin

        select         convert (smallint, req_spid) As spid,
                loginame=rtrim(loginame),hostname ,t2.name,
                rsc_dbid As dbid,
                rsc_objid As ObjId,
                rsc_indid As IndId,
                substring (v.name, 1, 4) As Type,
                substring (rsc_text, 1, 16) as Resource,
                substring (u.name, 1, 8) As Mode,
                substring (x.name, 1, 5) As Status

        from         master.dbo.syslockinfo(nolock),
                master.dbo.spt_values v(nolock),
                master.dbo.spt_values x(nolock),
                master.dbo.spt_values u(nolock),
                master.dbo.sysprocesses t1(nolock),
                sysobjects t2(nolock)

        where   master.dbo.syslockinfo.rsc_type = v.number
                        and v.type = 'LR'
                        and master.dbo.syslockinfo.req_status = x.number
                        and x.type = 'LS'
                        and master.dbo.syslockinfo.req_mode + 1 = u.number
                        and u.type = 'L'
                        and t1.spid=convert (smallint, req_spid)
                        and rsc_objid =t2.Id
        order by spid


        select         convert (smallint, req_spid) As spid,
                loginame=rtrim(loginame),hostname ,t2.name,
                rsc_dbid As dbid,
                rsc_objid As ObjId,
                rsc_indid As IndId,
                substring (v.name, 1, 4) As Type,
                substring (rsc_text, 1, 16) as Resource,
                substring (u.name, 1, 8) As Mode,
                substring (x.name, 1, 5) As Status
into #temp
        from         master.dbo.syslockinfo(nolock),
                master.dbo.spt_values v(nolock),
                master.dbo.spt_values x(nolock),
                master.dbo.spt_values u(nolock),
                master.dbo.sysprocesses t1(nolock),
                sysobjects t2(nolock)

        where   master.dbo.syslockinfo.rsc_type = v.number
                        and v.type = 'LR'
                        and master.dbo.syslockinfo.req_status = x.number
                        and x.type = 'LS'
                        and master.dbo.syslockinfo.req_mode + 1 = u.number
                        and u.type = 'L'
                        and t1.spid=convert (smallint, req_spid)
                        and rsc_objid =t2.Id
        order by spid

DECLARE dbcc_inputbuffer CURSOR
READ_ONLY
FOR select distinct spid from #temp

DECLARE @spid int
OPEN dbcc_inputbuffer

FETCH NEXT FROM dbcc_inputbuffer INTO @spid
WHILE (@@fetch_status <> -1)
BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
--                PRINT 'add user defined code here'
--                eg.

select @spid
dbcc inputbuffer(@spid)

        END
        FETCH NEXT FROM dbcc_inputbuffer INTO @spid
END

CLOSE dbcc_inputbuffer
DEALLOCATE dbcc_inputbuffer


end

return (0) -- sp_lock