数据库死锁语句脚本

一 :  查看死锁以及数据表阻塞信息

CREATE procedure sp_who_lock 
as 
begin 
declare @spid int 
declare @blk int 
declare @count int 
declare @index int 
declare @lock tinyint 
set @lock=0 
create table #temp_who_lock 
( 
id int identity(1,1), 
spid int, 
blk int 
) 
if @@error<>0 return @@error 
insert into #temp_who_lock(spid,blk) 
select 0 ,blocked 
from (select * from master..sysprocesses where blocked>0)a 
where not exists(select * from master..sysprocesses where a.blocked =spid and blocked>0) 
union select spid,blocked from master..sysprocesses where blocked>0 
if @@error<>0 return @@error 
select @count=count(*),@index=1 from #temp_who_lock 
if @@error<>0 return @@error 
if @count=0 
begin 
select '没有阻塞和死锁信息' 
return 0 
end 
while @index<=@count 
begin 
if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid)) 
begin 
set @lock=1 
select @spid=spid,@blk=blk from #temp_who_lock where id=@index 
select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' 
select @spid, @blk 
dbcc inputbuffer(@spid) 
dbcc inputbuffer(@blk) 
end 
set @index=@index+1 
end 
if @lock=0 
begin 
set @index=1 
while @index<=@count 
begin 
select @spid=spid,@blk=blk from #temp_who_lock where id=@index 
if @spid=0 
select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下' 
else 
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' 
dbcc inputbuffer(@spid) 
dbcc inputbuffer(@blk) 
set @index=@index+1 
end 
end 
drop table #temp_who_lock 
return 0 
end

二 : 查看库内所有在WAIT状态的锁

SELECT  L1.resource_type ,
        DB_NAME(L1.resource_database_id) AS DatabaseName ,
        CASE L1.resource_type
          WHEN 'OBJECT'
          THEN OBJECT_NAME(L1.resource_associated_entity_id,
                           L1.resource_database_id)
          WHEN 'DATABASE' THEN 'DATABASE'
          ELSE CASE WHEN L1.resource_database_id = DB_ID()
                    THEN ( SELECT   OBJECT_NAME(object_id,
                                                L1.resource_database_id)
                           FROM     sys.partitions
                           WHERE    hobt_id = L1.resource_associated_entity_id
                         )
                    ELSE NULL
               END
        END AS ObjectName ,
        L1.resource_description ,
        L1.request_session_id ,
        L1.request_mode ,
        L1.request_status
FROM    sys.dm_tran_locks AS L1
        JOIN sys.dm_tran_locks AS L2 ON L1.resource_associated_entity_id = L2.resource_associated_entity_id
WHERE   L1.request_status <> L2.request_status
        AND ( L1.resource_description = L2.resource_description
              OR ( L1.resource_description IS NULL
                   AND L2.resource_description IS NULL
                 )
            )
ORDER BY L1.resource_database_id ,
        L1.resource_associated_entity_id ,
        L1.request_status ASC;

三 : 查看阻塞持续时间超过5000ms的会话

SELECT  W.session_id AS waiting_session_id , 
        W.waiting_task_address , 
        W.wait_duration_ms , 
        W.wait_type , 
        W.blocking_session_id , 
        W.resource_description 
FROM    sys.dm_os_waiting_tasks AS W 
WHERE   W.wait_duration_ms > 5000 
        AND blocking_session_id IS NOT NULL;

四 : 查看Lock类型为X,S,U的锁

SELECT  request_session_id , 
        resource_type , 
        DB_NAME(resource_database_id) AS DatabaseName , 
        OBJECT_NAME(resource_associated_entity_id) AS TableName , 
        request_mode , 
        request_type , 
        request_status 
FROM    sys.dm_tran_locks AS L 
        JOIN sys.all_objects AS A ON L.resource_associated_entity_id = A.object_id 
WHERE   request_type = 'LOCK' 
        AND request_status = 'GRANT' 
        AND request_mode IN ( 'X', 'S' ) 
        AND A.type = 'U' 
        AND resource_type = 'OBJECT' 
        AND L.resource_database_id = DB_ID();

五 : 监听数据库异常信息 当数据异常时,会抛出异常信息,在消息中.

DBCC TRACEON(1222,-1)