阻塞和死锁问题整理一

阻塞和死锁问题整理：

--显示数据库TEST的锁信息sp: SP_LOCK

--获取用户信息:    sp_who       or     sp_who2

select *  from sys.sysprocesses as sps with(nolock)

-- or

select sps.spid, sps.kpid , sps.blocked , sps.waittime ,

       sps.cpu, sps.physical_io,sps.loginame ,sps.hostname ,

        sps.program_name ,sps.open_tran , sps. status ,

       sps.sql_handle ,sps.login_time, sps.last_batch

 from sys.sysprocesses as sps with(nolock)

 

 

根据 : hostname + loginame + login time + status + application name选取去找到大致的记录

然后用 ：dbcc inputbuffer(spid) 来进行操作查找出对应的查询或者存储过程

也可以用：SELECT * FROM sysprocesses WITH(NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE spid > 50

 

确定了用户的查询对应的是 sysprocesses 的那一条记录，对于长时间执行的查询，应该看它的状态 status 列 ：

如果是running 一般是OK的，通过 lastwaittype 列可以看到它在操作什么资源

对于 running状态的，多数据是IO

如果状态是 susppend ,那就比较麻烦，通过 waitresource 可以去了解是什么资源没有分配到，导致它这个状态

如果 blocked 列 > 0的话， 这个blocked 列对应

USE MASTER

GO

 

--create procedure sys.sp_lock --- 1996/04/08 00:00 

--@spid1 int = NULL,  /* server process id to check for locks */ 

--@spid2 int = NULL  /* other process id to check for locks */ 

--as 

 

set nocount on 

/* 

**  Show the locks for both parameters. 

*/ 

set transaction isolation level read committed 

if @spid1 is not NULL 

begin 

 select  convert (smallint, req_spid) As spid, 

  rsc_dbid As dbid, 

  rsc_objid As ObjId, 

  rsc_indid As IndId, 

  substring (v.name, 1, 4) As Type, 

  substring (rsc_text, 1, 32) as Resource, 

  substring (u.name, 1, 8) As Mode, 

  substring (x.name, 1, 5) As Status 

 

 from  master.dbo.syslockinfo, 

  master.dbo.spt_values v, 

  master.dbo.spt_values x, 

  master.dbo.spt_values u 

 

 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 req_spid in (@spid1, @spid2) 

end 

 

/* 

**  No parameters, so show all the locks. 

*/ 

else 

begin 

 select  convert (smallint, req_spid) As spid, 

  rsc_dbid As dbid, 

  rsc_objid As ObjId, 

  rsc_indid As IndId, 

  substring (v.name, 1, 4) As Type, 

  substring (rsc_text, 1, 32) as Resource, 

  substring (u.name, 1, 8) As Mode, 

  substring (x.name, 1, 5) As Status 

 

 from  master.dbo.syslockinfo, 

  master.dbo.spt_values v, 

  master.dbo.spt_values x, 

  master.dbo.spt_values u 

 

 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' 

 order by spid 

end 

 

return (0) -- sp_lock 

 

的是阻塞当前查询的进行，你可以去了解这个进程是在做什么

如果 blocked 列 = 0的话，那应该分析一下 waitresource ，看看是在等待什么资源。

 

第一步要做的就是定位他们的查询是那条记录，如果这个检查的过程很慢，应该观赛服务器是否响应有问题。比如：CPU 是否过高，连接数是否过多（COUNT SYSPROCESSES 的结果）

执行语句是什么，sysprocess 的 stmt_start, stmt_end 列标了在 fn_get_sql 中， 当前执行的语句的位置连接数据(通常超过700就会响应异常（不管状态）)，好的服务器可能会更多一些，差的服务器可能会更少一些

--删除锁

KILL

-- 利用: exec sp_helptext  'name'  可以查看函数、存储过程、视图的源码

--eg:

 exec sp_helptext  SP_WHO2

 

--获取数据库相关信息

SELECT * FROM SYSDATABASES

 

--返回对象信息

SELECT OBJECT_NAME(OBJID)

--包含有关所有已授权、正在转换和正在等待的锁请求的信息

sys.dm_tran_locks 能够返回系统中当前活动的锁管理器信息

附一个 sp_lock的源码