SqlServer2008实例20阻塞之找到并解决阻塞进程

资料来源：网络整理时间：2023/2/14 1:04:34 共计：3631 浏览

当一个数据库会话中的事务正在锁定一个或多个其他会话事务想要读取或者修改的资源时，会产生阻塞。通常短时间的阻塞没有问题，且是较忙的应用程序所需要的。然而，设计得比较糟糕的应用程序会导致长时间的阻塞，这样就不必要地锁定了资源，而且阻塞了其他会话读取和更新它们。
在SQL Server中，一个阻塞的进程会无限期地保持阻塞，或者直到它超时(根据SET LOCK TIMEOUT)、服务器关闭、进程被杀死、连接完成了更新或者其他发生在原始事务上的操作导致它释放了资源上的锁。
发生长时间的阻塞的原因：

在一个没有索引的表上的过量的行锁会导致SQL Server得到一个表锁，从而阻塞其他事务。
应用程序打开一个事务，并在事务保持打开的时候要求用户进行反馈或者交互口这通常是让最终用户在GUI上输入数据而保持事务打开的时候发生。保持打开的话，事务引用的任何资源都会被锁占据。
事务BEGIN后查询的数据可能在事务开始之前被引用。
查询不恰当地使用锁定提示。例如，应用程序仅使用很少的行，但是却使用了一个表锁提示。
应用程序使用长时间运行的事务，在一个事务中更新了很多行或很多表（把一个大量更新的事务变成多个更新较少的事务能帮助改善并发性）。

下面说明如何找到阻塞进程，查看由该进程执行的T-SQL，然后强制终止这个活动会话的连接（因此会回滚这个阻塞会话未提交的所有工作）。首先了解一下本例中使用的命令的背景知识。
使用SQL Server动态管理视图sys．dm_os_waiting_tasks能找出阻塞的进程。这个视图用于代替早期版本的SQL Server中使用的系统存储过程sp_who。
在找出阻塞的进程后，使用的sys.dm_exec_sql_text动态管理函数和sys.dm_exec_con_nections DMV找出正在执行的查询的SQL文本——然后来强制结束进程。
要强制终止一个难以控制的活动查询会话，可以使用KILL命令。KILL应该仅用于其他方法都不可用的情况，比如等待进程自己终止或者通过调用应用程序终止或取消操作o KILL的语法如下：

KILL {spid|UOW}[WITH STATUSONLY]

KILL命令参数

参数	描述
spid	与要终止的活动数据库连接关联的会话ID
UOW	分布式事务的工作单元标识符。这是某个分布式事务进程的唯一标识符
WITH STATUSONLY	某些KILL语句回滚事务所花的时间比其他命令更长（这取决于会话中执行的更新范围）。要检查回滚的状态，可以使用WITH STATUSIONLY来获取回滚的估计时间

1.在第一个查询窗口执行下面的代码创建一个阻塞的进程：


		
			
			
				
				
					
					
						
					

				

				
					
					
						use AdventureWorks
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						go
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						BEGIN TRAN
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						UPDATE Production.ProductInventory
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						SET Quantity=400
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						WHERE ProductID=1 AND LocationID=1

2.在第二个查询窗口执行下面的代码：


		
			
			
				
				
					
					
						
					

				

				
					
					
						use AdventureWorks
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						go
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						BEGIN TRAN
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						UPDATE Production.ProductInventory
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						SET Quantity=406
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						WHERE ProductID=1 AND LocationID=1

3.在第三个查询窗口执行：


		
			
			
				
				
					
					
						
					

				

				
					
					
						use AdventureWorks
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						go
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						SELECT blocking_session_id,wait_duration_ms,session_id
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						FROM sys.dm_os_waiting_tasks
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						WHERE blocking_session_id IS NOT NULL

4.查看ID为53的会话在干什么，新建查询窗口中执行下面的查询：


		
			
			
				
				
					
					
						
					

				

				
					
					
						use AdventureWorks
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						go
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						SELECT t.text
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						FROM sys.dm_exec_connections c
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
					

				

			

			
				
				
					
					
						
					

				

				
					
					
						WHERE c.session_id=53

5.强制终止会话：

KILL 53

之后就能继续进行第二个查询的UPDATE了。

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：数据库sqlserver表被锁死解决方法 | ·下一条：SQL Server 2008 存储过程示例