sqlserver-查阻塞

模拟阻塞:
打开两个窗口:
    窗口一:
1 BEGIN TRANSACTION--开始事务
2 update tblUser set [Password] = '00000' where id = 1
3 --等待1分钟
4 WAITFOR DELAY '00:1'; 

 

 窗口二:

1 select * from tblUser where id = 1

 

查询阻塞(当前被阻塞的进程id,不一定是它阻塞其他进程,有可能是它被其他进程阻塞)

1 select
2     request_session_id spid,
3     OBJECT_NAME(resource_associated_entity_id) tableName
4 from
5     sys.dm_tran_locks
6 where
7     resource_type='OBJECT'

 

查看阻塞的语句:(根据上面查询出来的阻塞进程id)

1 DBCC INPUTBUFFER (54)

 

杀死死锁进程:(根据上面查询出来的阻塞进程id)

1 kill 55

 

知识点:WAITFOR DELAY
    WAITFOR是SQL Server中Transact-SQL提供的一个流程控制语句。它的作用就是等待特定时间,然后继续执行后续的语句。它包含一个参数DELAY,用来指定等待的时间。
    WAITFOR DELAY '0:0:4'--  表示延迟4秒,再继续执行。
 

进化版一:

查阻塞进程,及是谁在阻塞,还有阻塞的表:
 1 select t1.spid as '等待的进程id',
 2     OBJECT_NAME(t2.resource_associated_entity_id) as '等待进程-表' ,
 3     t1.blocked as '阻塞的进程id',
 4     OBJECT_NAME(t3.resource_associated_entity_id) as '阻塞进程-表',
 5     tt1.hostname as '阻塞进程-hostname',
 6     tt1.nt_domain as '阻塞进程-nt_domain',
 7     tt1.nt_username as '阻塞进程-nt_username',
 8     tt1.loginame as '阻塞进程-loginame'
 9 from (
10 select 0 as spid ,blocked
11 from (
12     select * from master..sysprocesses where blocked > 0 
13 ) a   
14 where not exists (
15     select * from (select * from master..sysprocesses where blocked > 0 ) b   
16     where a.blocked=spid
17 )  
18 union 
19 select spid,blocked from master..sysprocesses where blocked > 0
20 ) t1
21 inner join master..sysprocesses tt1 on t1.blocked = tt1.spid
22 left join sys.dm_tran_locks t2 on t1.spid = t2.request_session_id and t2.resource_type = 'OBJECT'
23 left join sys.dm_tran_locks t3 on t1.blocked = t3.request_session_id and t3.resource_type = 'OBJECT'

 

进化版二:(最实用,上面的精简版)

查阻塞数据库的进程id,把这些进程kill掉就可以了;
 1 select t1.blocked as '阻塞的进程id',
 2     OBJECT_NAME(t2.resource_associated_entity_id) as '阻塞的表',
 3     t3.hostname as '阻塞进程-hostname',
 4     t3.nt_domain as '阻塞进程-nt_domain',
 5     t3.nt_username as '阻塞进程-nt_username',
 6     t3.loginame as '阻塞进程-loginame'
 7 from master..sysprocesses t1
 8 inner join sys.dm_tran_locks t2 on t1.blocked = t2.request_session_id and t2.resource_type = 'OBJECT'
 9 inner join master..sysprocesses t3 on t1.blocked = t3.spid
10 where t1.blocked > 0

 

进化版三:(信息最全)

查看等待的进程、阻塞的进程执行的语句,账号,地址等信息;
 1 SELECT
 2    WT.session_id AS '等待的进程',
 3    DB_NAME(TL.resource_database_id) AS '数据库名',
 4    (select OBJECT_NAME(resource_associated_entity_id) --resource_associated_entity_id为Bigint类型,而Object_Name方法参数为int,有可能出现转换失败的情况
 5    from sys.dm_tran_locks 
 6    where request_session_id = WT.blocking_session_id 
 7    and resource_type = 'OBJECT') as '表名',
 8    WT.wait_duration_ms,
 9    TL.request_mode,
10    (SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,
11       ((CASE ER.statement_end_offset
12          WHEN -1 THEN DATALENGTH(ST.text)
13          ELSE ER.statement_end_offset
14         END - ER.statement_start_offset)/2) + 1)
15    FROM sys.dm_exec_requests AS ER
16       CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
17    WHERE ER.session_id = TL.request_session_id)
18       AS '等待执行的sql',
19    WT.blocking_session_id as '阻塞的进程',
20    WT.resource_description AS blocking_resource_description,
21    CASE WHEN WT.blocking_session_id > 0 THEN
22       (SELECT ST2.text FROM sys.sysprocesses AS SP
23              CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
24       WHERE SP.spid = WT.blocking_session_id)
25       ELSE NULL
26    END AS '阻塞进程执行的sql',
27     SPP.hostname as '阻塞进程-hostname',
28     SPP.nt_domain as '阻塞进程-nt_domain',
29     SPP.nt_username as '阻塞进程-nt_username',
30     SPP.loginame as '阻塞进程-loginame'
31 FROM sys.dm_os_waiting_tasks AS WT
32    JOIN sys.dm_tran_locks AS TL
33    ON WT.resource_address = TL.lock_owner_address
34    inner join sys.sysprocesses as SPP on WT.blocking_session_id = SPP.spid
35 WHERE WT.wait_duration_ms > 5000--等待5s以上的
36    AND WT.session_id > 50;

 

进化版四:

    一、查看阻塞进程及语句:exec sp_who_lock(在master数据库下)
    二、解决阻塞:exec p_killspid 'DBName'(在master数据库下)
 
创建存储过程,查当前阻塞:
 1 USE [master]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[sp_who_lock]    Script Date: 2018/4/14 22:26:36 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 create procedure [dbo].[sp_who_lock]  
 9 as  
10 begin  
11 declare @spid int,@bl int,  
12 @intTransactionCountOnEntry int,  
13         @intRowcount    int,  
14         @intCountProperties   int,  
15         @intCounter    int  
16   
17 create table #tmp_lock_who (  
18 id int identity(1,1),  
19 spid smallint,  
20 bl smallint)  
21   
22 IF @@ERROR<>0 RETURN @@ERROR  
23   
24 insert into #tmp_lock_who(spid,bl) select 0 ,blocked  
25    from (select * from sysprocesses where blocked>0 ) a   
26    where not exists(select * from (select * from sysprocesses where blocked>0 ) b   
27    where a.blocked=spid)  
28    union select spid,blocked from sysprocesses where blocked>0  
29   
30 IF @@ERROR<>0 RETURN @@ERROR   
31   
32 -- 找到临时表的记录数  
33 select @intCountProperties = Count(*),@intCounter = 1  
34 from #tmp_lock_who  
35   
36 IF @@ERROR<>0 RETURN @@ERROR   
37   
38 if @intCountProperties=0  
39 select '现在没有阻塞和死锁信息' as message  
40   
41 -- 循环开始  
42 while @intCounter <= @intCountProperties  
43 begin  
44 -- 取第一条记录  
45 select @spid = spid,@bl = bl  
46 from #tmp_lock_who where Id = @intCounter   
47 begin  
48 if @spid =0   
49             select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  
50 else  
51             select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'  
52 DBCC INPUTBUFFER (@bl )  
53 end   
54   
55 -- 循环指针下移  
56 set @intCounter = @intCounter + 1  
57 end  
58   
59 drop table #tmp_lock_who  
60   
61 return 0  
62 end 

 

创建存储过程,解除阻塞:

 1 USE [master]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[p_killspid]    Script Date: 2018/4/14 22:18:31 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8   
 9 create proc [dbo].[p_killspid]  
10 @dbname varchar(200)    --要关闭进程的数据库名  
11 as   
12     declare @sql nvarchar(500)
13     declare @spid nvarchar(20)
14   
15     declare #tb cursor for  
16         --select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) --杀所有当前阻塞在数据库中的进程
17         select spid=cast(blocked as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) and blocked > 0 --只杀阻塞其他进程的进程
18     open #tb
19     fetch next from #tb into @spid
20     while @@fetch_status=0
21     begin
22         exec('kill '+@spid)
23         fetch next from #tb into @spid
24     end
25     close #tb
26     deallocate #tb

 

参考:

https://blog.csdn.net/snowfoxmonitor/article/details/7182948

https://blog.csdn.net/wulex/article/details/61916478

https://blog.csdn.net/zouxistudent/article/details/7029759

https://blog.csdn.net/a237428367/article/details/9004802

https://www.cnblogs.com/fuyuanming/p/5783421.html

 

posted @ 2018-04-15 19:27  张泽果  阅读(1585)  评论(0编辑  收藏  举报