怎样解除死锁,已经有了一个存储过程,不符合我的要求,希望各位能帮我改一改(100分)

怎样解除死锁的存储过程
if exists (select * from sysobjects where id = object_id('dbo.sp_Kill_Process') and sysstat & 0xf = 4)
drop procedure dbo.sp_Kill_Process
GO

CREATE PROCEDURE sp_Kill_Process
AS
BEGIN

declare @lkdbnm varchar(32),@lkdbid smallint,@lkobjid int,@UserDBID smallint,@usrdbnm varchar(32),@User_ID smallint, @grpid smallint, @stmt varchar(255)

/* ===========================================================*/
/* build temp table with processes and their associated locks */
/* ===========================================================*/
select
'FullName' = case when p.spid > 9 then lo.name else 'System' end,
'UserID'= p.uid,
'UName' = ' ',
'ProcessID'= p.spid,
'Status'= p.status,
'DBID' = p.dbid,
'DbName'= d.name,
'Command'= p.cmd,
'Host'= p.hostname,
'Application'= p.program_name,
'Blocking' = case when l.type >= 256 then 100 else 0 end,
'Blockedby'= p.blocked,
'LockType' = l.type,
'LKObjDBID' = l.dbid,
'LKObjDB' = ' ',
'LKObjID' = l.id,
'LKObj' = case when l.id = 0 then
(select name from master..spt_values where number = l.type and type = 'SFL') else ' ' end,
'LockedPage' = l.page,'GroupID' = p.gid,'GName' = ' ','CPUUsage' = p.cpu,
'PhysicalIO' = p.physical_io,'HostProcess' = p.hostprocess
into #LockInfo from master..sysprocesses p, master..syslocks l,master..syslogins lo (nolock),master..sysdatabases d (nolock)
where p.spid = l.spid and p.suid = lo.suid and p.dbid= d.dbid
and d.name = 'CSV_database' --and p.cpu>10000 --and (RTRIM(p.program_name) = '' or (p.program_name like '%CSV%'))

/* ======================= */
/* flag blocking processes */
/* ======================= */

update #LockInfo set Blocking = 1 where ProcessID in (select Blockedby from #LockInfo where Blockedby > 0)

/* =============================================*/
/* get list of locked object IDs */
/* =============================================*/

exec ('declare c1 cursor for select distinct LockedDBName = d.name, LKObjID , LKObjDBID from #LockInfo, master..sysdatabases d
where LKObjDBID = d.dbid and LKObjID > 0 FOR READ ONLY')

open c1

fetch c1 into @lkdbnm, @lkobjid, @lkdbid

while @@fetch_status >= 0
begin
/* set database.owner.name of locked objects in #info */
select @stmt ='Update #LockInfo set LKObj=' + '''' + @lkdbnm + '''' + '+ ''.'' + u.name + ''.'' + o.name' +
',LKObjDB=' + ''''+@lkdbnm + '''' + ' from '+@lkdbnm+'..sysobjects o,'+@lkdbnm+'..sysusers u ' +
'where o.id='+convert(char(10),@lkobjid)+
' and LKObjID=o.id' +
' and u.uid=o.uid and LKObjDBID=' + convert(char,@lkdbid)
exec(@stmt )
/* next please */
fetch c1 into @lkdbnm, @lkobjid, @lkdbid
end
deallocate c1

/* =============================================*/
/* resolve UserID, GroupID for all processes */
/* get a list of each database we need to visit */
/* =============================================*/

exec ('declare c1 cursor for select distinct DBID, DbName from #LockInfo FOR READ ONLY')

open c1

fetch c1 into @UserDBID, @usrdbnm

while @@fetch_status >= 0
begin
/* set user/group in #LockInfo */
select @stmt = 'Update #LockInfo set GName=g.name,UName=u.name from '+@usrdbnm+'..sysusers u,'+
@usrdbnm+'..sysusers g,#LockInfo '+ 'where u.uid=UserID and g.uid=GroupID and DBID=' + convert(char(10),@UserDBID)
exec(@stmt )
/* next please */
fetch c1 into @UserDBID, @usrdbnm
end
deallocate c1

/* ====== return the #LockInfo table ===== */
/*
SELECT
--FullName ,
ProcessID,

Status,
--DName = DbName,
Command,
Host,
Application,
Blockedby,
LockType,
LKObj,
GName = GName,
CPUUsage,
PhysicalIO,
HostProcess,
UName,
Blocking
FROM #LockInfo (nolock) ORDER BY LKObj, ProcessID
*/

declare @KillString char(100),
@ProcessID smallint

declare c2 cursor for select distinct ProcessID from #LockInfo where Blocking = 1 FOR READ ONLY
open c2
fetch c2 into @ProcessID
while @@fetch_status >= 0
begin
select @KillString = 'Kill ' + Convert(char,@ProcessID )
--select @KillString

exec( @KillString )

select @KillString = "master..xp_cmdshell 'echo "
+'"Kill ' + Convert(char(2),@ProcessID )
+' 时间:' + Convert(char,GetDate()) + '">>c:/csv/log/'
+convert(char(4),DATEPART(year,getdate())) + convert(char(2),DATEPART(month,getdate())) + convert(char(2),DATEPART(day,getdate())) + '.txt'
+ " '"
--select @KillString
exec(@KillString)

fetch c2 into @ProcessID
end
deallocate c2

END
GO
－－－－－－－－－－－－－－－－－－---------------------------
要求：
写成一个存储过程，这个存储过程有一个参数，参数是数据库名。
功能：
用户连到这个数据有被死锁的就帮我把它杀了。
---------------------------------------------------------------
希望各位能帮我完成，先谢谢了。

我试过，上面的这个储存过程有问题，可作如下简化，但下面这个存储过程最好建在非@dbname库中，否则每次执行时自已会产生一个进程号。
CREATE PROCEDURE sp_Kill_Process
@dbname char(256)
AS
declare @killid int
declare tmpkilldb cursor for
select distinct P.spid FROM master..sysprocesses p ,master..sysdatabases d (nolock),master..syslocks l WHERE p.dbid= d.dbid and d.name=@dbname and p.spid = l.spid
open tmpkilldb

fetch tmpkilldb into @killid
while @@fetch_status >= 0
begin
declare @KillString char(256)
select @KillString ='Kill ' + Convert(char(2),@killid )
exec(@KillString)
fetch tmpkilldb into @killid
end
deallocate tmpkilldb

to whbest
如果有死锁才把它杀掉呀，哪一个字段是显示死锁的状态呀。

我没有去研究，应该是超过多长时间就算是死锁。不过一般是出现死锁后才去执行解锁程序，上面的过程已能基本解问题了！死锁一般来讲应该很少出现，否则就要注意优化数据库的调用。

如果有很多用户连到这个数据库，这样会影响别的访问数据库呀。

根所上面原的过程，我分析了一下，在上面给定的简化程序中加一个条件
p.blocked>0

不过我没有进行验证，请自行检测，如可行，请通知我!

to whbest
先谢了，不应该把被堵死的杀掉，因该查找blocked字段对应spid字段的值。
再请大家一个问题。
如下表：
spid blocked text
1 0 1
2 0 2
3 0 3
4 0 4
5 4 5
6 4 6
54 0 7
56 54 8
57 54 9
查询结果:
spid blocked text
4 0 4
54 0 7
------------------------
查找字段spi的值等于Blocked字段大于0的记录
SQL语句怎样写，写出来之后就可以把堵塞者杀掉

自己搞定了，语句如下：
select * from sysprocesses where spid in (select distinct blocked from sysprocesses where blocked>0)

谢谢whbest的帮助。
怎样解除死锁
if exists (select * from sysobjects where id = object_id('dbo.sp_Kill_Process') and sysstat & 0xf = 4)
drop procedure dbo.sp_Kill_Process
GO
CREATE PROCEDURE sp_Kill_Process
@dbname char(256)
AS
declare @killid int
declare tmpkilldb cursor for
select distinct p.spid FROM master..sysprocesses p ,master..sysdatabases d (nolock),master..syslocks l
where p.dbid= d.dbid and d.name=@dbname and p.spid = l.spid and (p.spid in (select distinct blocked from sysprocesses where blocked>0))
open tmpkilldb

fetch tmpkilldb into @killid
while @@fetch_status >= 0
begin
declare @KillString char(256)
select @KillString ='Kill ' + Convert(char(2),@killid )
exec(@KillString)
--print @KillString
fetch tmpkilldb into @killid
end
deallocate tmpkilldb

接受答案了.