摘要：
它的作用就是等待特定时间，然后继续执行后续的语句。它包含一个参数DELAY，用来指定等待的时间。WAITFORDELAY'0:0:4'--表示延迟4秒，再继续执行。
模拟阻塞：
打开两个窗口：
窗口一：
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 asspid ,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 frommaster..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 fromsys.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 ((CASEER.statement_end_offset 12 WHEN -1 THEN DATALENGTH(ST.text) 13 ELSEER.statement_end_offset 14 END - ER.statement_start_offset)/2) + 1) 15 FROM sys.dm_exec_requests ASER 16 CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) ASST 17 WHERE ER.session_id =TL.request_session_id) 18 AS '等待执行的sql', 19 WT.blocking_session_id as '阻塞的进程', 20 WT.resource_description ASblocking_resource_description, 21 CASE WHEN WT.blocking_session_id > 0 THEN 22 (SELECT ST2.text FROM sys.sysprocesses ASSP 23 CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) ASST2 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 ASWT 32 JOIN sys.dm_tran_locks ASTL 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;
一、查看阻塞进程及语句：execsp_who_lock（在master数据库下）
二、解决阻塞：execp_killspid'DBName'（在master数据库下）
二、解决阻塞：execp_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 '现在没有阻塞和死锁信息' asmessage 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