得到死锁进程的sql有时不对,甚至加nolock了也死锁,select*fromtwith(nolock)。用的论坛里找的p_lockinfo改了下把结果记录到表z_sql里了CREA
得到死锁进程的sql有时不对,甚至加nolock 了也死锁,select * from t with(nolock) 。用的论坛里找的p_lockinfo 改了下把结果记录到表z_sql里了
CREATE TABLE [dbo].[z_sql](
[zid] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[rq] [datetime] NULL CONSTRAINT [DF_z_sql_rq] DEFAULT (getdate()),
[id] [int] NOT NULL,
[标志] [varchar](50) NOT NULL,
[进程ID] [int] NOT NULL,
[线程ID] [int] NOT NULL,
[块进程ID] [int] NOT NULL,
[数据库ID] [int] NOT NULL,
[数据库名] [varchar](50) NULL,
[用户ID] [int] NOT NULL,
[用户名] [varchar](50) NOT NULL,
[累计CPU时间] [int] NOT NULL,
[登陆时间] [datetime] NOT NULL,
[打开事务数] [int] NOT NULL,
[进程状态] [varchar](50) NOT NULL,
[工作站名] [varchar](255) NOT NULL,
[应用程序名] [varchar](255) NOT NULL,
[工作站进程ID] [varchar](20) NOT NULL,
[域名] [varchar](255) NOT NULL,
[网卡地址] [varchar](50) NOT NULL,
[进程的SQL语句] [text] NULL,
CONSTRAINT [PK_z_sql] PRIMARY KEY CLUSTERED
(
[zid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
go
USE [master]
GO
/****** 对象: StoredProcedure [dbo].[p_lockinfo] 脚本日期: 12/16/2010 08:49:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[p_lockinfo]
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程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,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
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>0
begin
create table #t1(id int identity(1,1),a varchar(8000),b Int null,EventInfo varchar(8000) null)
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
insert z_sql
(id, 标志, 进程ID, 线程ID, 块进程ID, 数据库ID, 数据库名, 用户ID, 用户名, 累计CPU时间, 登陆时间, 打开事务数, 进程状态, 工作站名, 应用程序名, 工作站进程ID, 域名, 网卡地址, 进程的SQL语句)
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
select * from z_sql
end
12 个解决方案
参考:
/*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
--邹建 2004.4--*/
/*--调用示例
exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程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,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
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>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
嘿嘿 好像都一样呀 我用下来感觉这个存储过程没问题呀 关注一下 收藏
其它查询如与T表关联的,如update B set col=11 from B inner join T on B.id=T.id 把T表占用了.也有可能使用t表,占用了T表资源!
我仔细看了一下,这个过程的描述是不准确的。
这个过程检测的并不是死锁,而是阻塞;而阻塞并不一定会形成死锁。
sysprocesses是2000下的系统表,2005以后不建议再用(可用动态管理视图代替)。检测死锁是一个递归的过程,首先查询被阻塞的进程和被阻塞的资源,再查阻塞的进程和及其锁定的资源,如果彼此互相锁定且互相请求才构成死锁。再说,这个工作事实上也是没有必要的,因为SQL会自动检测死锁,并牺牲掉一个回滚消耗少的进程。
感谢coleling ,能提供个 可用动态管理视图 的例子吗?
恩最后出现
select * from t with(nolock)的问题
是这样的
当进程A 锁定一个表 T1或T1的某一行,且未做完整提交的时候,其他进程想对T1或T1的特定行进行提交,就会发生阻塞
但注意,这个时候,A进程是依然可以继续向下进行运行的,只是其他的进程会被阻塞,等待A进程的完整提交而已
而使用你的查询语句,,,死锁的进程 将会返回的是A进程的spid
你使用后dbcc inputbuffer(A进程SPID)
返回的是未被提交的A进程正在运行的最后一个SQL语句而已,,,也就是说即使是最后运行的是select 1
dbcc inputbuffer返回的也是它
也就是说你真想查找发生阻塞的语句应该获取的是,,,你使用的查询语句那个 |_牺牲品_> 返回的进程SPID
使用它,dbcc inputbuffer返回的才是其他进程被阻塞的语句