对一个加锁的表进行解锁

　　点评use master 必须在master数据库中创建 go if exists (select * from dbosysobjects where id = object_id(N[dbo][p_lockinfo]) and OBJECTPROPERTY(id NIsProcedure) = ) drop procedure [dbo][p_lockinfo] GO /*处理死锁 查看当前进程或死锁进程并能自

use master 必须在master数据库中创建
go
if exists (select * from dbosysobjects where id = object_id(N[dbo][p_lockinfo]) and OBJECTPROPERTY(id NIsProcedure) = )
drop procedure [dbo][p_lockinfo]
GO
/*处理死锁
查看当前进程或死锁进程并能自动杀掉死进程
因为是针对死的所以如果有死锁进程只能查看死锁进程
当然你可以通过参数控制不管有没有死锁都只查看死锁进程
感谢: caiyunxiajiangopen 两位提供的参考信息
邹建 */
/*调用示例
exec p_lockinfo
*/
create proc p_lockinfo
@kill_lock_spid bit= 是否杀掉死锁的进程 杀掉 仅显示
@show_spid_if_nolock bit= 如果没有死锁的进程是否显示正常进程信息 显示 不显示
as
declare @count int@s nvarchar()@i int
select id=identity(int)标志
进程ID=spid线程ID=kpid块进程ID=blocked数据库ID=dbid
数据库名=db_name(dbid)用户ID=uid用户名=loginame累计CPU时间=cpu
登陆时间=login_time打开事务数=open_tran 进程状态=status
工作站名=hostname应用程序名=program_name工作站进程ID=hostprocess
域名=nt_domain网卡地址=net_address
into #t from(
select 标志=死锁的进程
spidkpidablockeddbiduidloginamecpulogin_timeopen_tran
statushostnameprogram_namehostprocessnt_domainnet_address
s=aspids=
from mastersysprocesses a join (
select blocked from mastersysprocesses group by blocked
)b on aspid=bblocked where ablocked=
union all
select |_牺牲品_>
spidkpidblockeddbiduidloginamecpulogin_timeopen_tran
statushostnameprogram_namehostprocessnt_domainnet_address
s=blockeds=
from mastersysprocesses a where blocked<>
)a order by ss
select @count=@@rowcount@i=
if @count= and @show_spid_if_nolock=
begin
insert #t
select 标志=正常的进程
spidkpidblockeddbiddb_name(dbid)uidloginamecpulogin_time
open_transtatushostnameprogram_namehostprocessnt_domainnet_address
from mastersysprocesses
set @count=@@rowcount
end
if @count>
begin
create table #t(id int identity()a nvarchar()b IntEventInfo nvarchar())
if @kill_lock_spid=
begin
declare @spid varchar()@标志 varchar()
while @i<=@count
begin
select @spid=进程ID@标志=标志 from #t where id=@i
insert #t exec(dbcc inputbuffer(+@spid+))
if @标志=死锁的进程 exec(kill +@spid)
set @i=@i+
end
end
else
while @i<=@count
begin
select @s=dbcc inputbuffer(+cast(进程ID as varchar)+) from #t where id=@i
insert #t exec(@s)
set @i=@i+
end
select a*进程的SQL语句=bEventInfo
from #t a join #t b on aid=bid
end
go