阻塞和死锁问题整理一
阻塞和死锁问题整理:
--显示数据库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的源码