对一个加锁的表进行解锁
来源:程序员人生 发布时间:2014-03-06 12:03:11 阅读次数:3138次
点评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 dbo
sysobjects where id = object_id(N
[dbo]
[p_lockinfo]
) and OBJECTPROPERTY(id
N
IsProcedure
) =
)
drop procedure [dbo]
[p_lockinfo]
GO
/*
处理死锁
查看当前进程
或死锁进程
并能自动杀掉死进程
因为是针对死的
所以如果有死锁进程
只能查看死锁进程
当然
你可以通过参数控制
不管有没有死锁
都只查看死锁进程
感谢: caiyunxia
jiangopen 两位提供的参考信息
邹建
*/
/*
调用示例
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 标志=
死锁的进程
spid
kpid
a
blocked
dbid
uid
loginame
cpu
login_time
open_tran
status
hostname
program_name
hostprocess
nt_domain
net_address
s
=a
spid
s
=
from master
sysprocesses a join (
select blocked from master
sysprocesses group by blocked
)b on a
spid=b
blocked where a
blocked=
union all
select
|_牺牲品_>
spid
kpid
blocked
dbid
uid
loginame
cpu
login_time
open_tran
status
hostname
program_name
hostprocess
nt_domain
net_address
s
=blocked
s
=
from master
sysprocesses a where blocked<>
)a order by s
s
select @count=@@rowcount
@i=
if @count=
and @show_spid_if_nolock=
begin
insert #t
select 标志=
正常的进程
spid
kpid
blocked
dbid
db_name(dbid)
uid
loginame
cpu
login_time
open_tran
status
hostname
program_name
hostprocess
nt_domain
net_address
from master
sysprocesses
set @count=@@rowcount
end
if @count>
begin
create table #t
(id int identity(
)
a nvarchar(
)
b Int
EventInfo 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语句=b
EventInfo
from #t a join #t
b on a
id=b
id
end
go
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠