[SQL]死锁检查
方案一:
===================
select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' USE [master] GO SELECT session_id, blocking_session_id, wait_time, wait_type, last_wait_type, wait_resource, transaction_isolation_level, lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 GO
方案二:
===================
--spid smallint SQL Server 进程 ID。 --ecid smallint 用于唯一标识代表单个进程进行操作的子线程的执行上下文 ID。 --status nchar(30) 进程 ID 状态(如运行、休眠等)。 --Objid 对象ID号(要查看这个对象,你可以在主数据库中的sysobjects表格中查询指定的objid) --Indid 索引ID号 --blk char(5) 如果存在阻塞进程,则是该阻塞进程的系统进程 ID。否则该列为零。当与给定的 spid 相关联的事务受到孤立分布式事务的阻塞时,该列将对阻塞孤立事务返回 '-2'。 CREATE Table #Who(spid int, ecid int, status nvarchar(50), loginname nvarchar(50), hostname nvarchar(50), blk int, dbname nvarchar(50), cmd nvarchar(50), request_ID int); CREATE Table #Lock(spid int, dbid int, objid int, indid int, [Type] nvarchar(20), Resource nvarchar(50), Mode nvarchar(10), Status nvarchar(10) ); INSERT INTO #Who EXEC sp_who active --看哪个引起的阻塞,blk INSERT INTO #Lock EXEC sp_lock --看锁住了那个资源id,objid DECLARE @DBName nvarchar(20); SET @DBName='CRM_Other' SELECT #Who.* FROM #Who WHERE dbname=@DBName SELECT #Lock.* FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName; --最后发送到SQL Server的语句 DECLARE crsr Cursor FOR SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0; DECLARE @blk int; open crsr; FETCH NEXT FROM crsr INTO @blk; WHILE (@@FETCH_STATUS = 0) BEGIN; dbcc inputbuffer(@blk); FETCH NEXT FROM crsr INTO @blk; END; close crsr; DEALLOCATE crsr; --锁定的资源 SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock JOIN #Who ON #Who.spid=#Lock.spid AND dbname=@DBName WHERE objid<>0; DROP Table #Who; DROP Table #Lock;
方案三:
===================
select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
方案四:
===================
use master go create procedure sp_who_lock as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int create table #tmp_lock_who ( id int identity(1,1), spid smallint, bl smallint) IF @@ERROR<>0 RETURN @@ERROR insert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from sysprocesses where blocked>0 ) a where not exists(select * from (select * from sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from sysprocesses where blocked>0 IF @@ERROR<>0 RETURN @@ERROR -- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_who IF @@ERROR<>0 RETURN @@ERROR if @intCountProperties=0 select '现在没有阻塞和死锁信息' as message -- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where Id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end -- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_who return 0 end ------执行语句 exec sp_who_lock