SQLServer2008中SQL应用之三“阻塞(Blocking)”

当一个数据库会话中的事务正锁定一个或多个其他会话事务想要读取或修改的资源时，会产生阻塞(Blocking)。通常短时间的阻塞没有问题，且是较忙的应用程序所需要的。然而，设计糟糕的应用程序会导致长时间的阻塞，这就不必要地锁定了资源，而且阻塞了其他会话读取和更新它们。

在SQL Server中，一个阻塞的进程会无限期地保持阻塞，或者直到它超时（根据set lock_timeout）、服务器关闭、进程被杀死、连接完成了更新或者其他发生在原始事务上的操作导致它释放了资源上的锁。

发生长时间阻塞的原因如下：

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

一、找到并解决阻塞进程

下面我们演示使用SQL Server动态管理视图sys.dm_os_waiting_tasks找出阻塞进程，该视图用于代替早期SQL Server版本中的系统存储过程sp_who

找出阻塞的进程后，我们使用sys.dm_exec_sql_text动态管理函数和sys.dm_exec_Connections（DMV）找出正在执行的查询的SQL文本，然后强制结束进程。

强制结束进程，我们使用kill命令。kill的用法，请参看MSDN:http://msdn.microsoft.com/zh-cn/library/ms173730.aspx

该命令有三个参数：

  • session ID    要终止的进程的会话 ID。session ID 是在建立连接时为每个用户连接分配的***整数 (int)。在连接期间，会话 ID 值与该连接捆绑在一起。连接结束时，则释放该整数值，并且可以将它重新分配给新的连接。使用 KILL session ID 可终止与指定的会话 ID 关联的常规非分布式事务和分布式事务。
  • UOW    标识分布式事务的工作单元 (UOW) ID。UOW 是可从 sys.dm_tran_locks 动态管理视图的 request_owner_guid 列中获取的 GUID。也可从错误日志中或通过 MS DTC 监视器获取 UOW。有关监视分布式事务的详细信息，请参阅 MS DTC 文档。使用 KILL UOW 可终止孤立的分布式事务。这些事务不与任何真实的会话 ID 相关联，与虚拟的会话 ID = '-2' 相关联。可使标识孤立事务变得更为简单，其方法是查询 sys.dm_tran_locks、sys.dm_exec_sessions 或 sys.dm_exec_requests 动态管理视图中的会话 ID 列。
  • WITH STATUSONLY    生成由于更早的 KILL 语句而正在回滚的指定 session ID 或 UOW 的进度报告。KILL WITH STATUSONLY 不终止或回滚 session ID 或 UOW，该命令只显示当前的回滚进度。

在***个查询窗口：

 
 
 
    
  
  
  
  1. BEGIN TRAN  
    2.   
  
  
  2. UPDATE Production.ProductInventory  
    3.   
  
  
  3. SET Quantity = 400  
    4.   
  
  
  4. WHERE ProductID = 1 AND 
    5.   
  
  
  5. LocationID = 1 
    6.

第二个窗口：

 
 
 
    
  
  
  
  1. UPDATE Production.ProductInventory  
    2.   
  
  
  2. SET Quantity = 406  
    3.   
  
  
  3. WHERE ProductID = 1 AND 
    4.   
  
  
  4. LocationID = 1 
    5.

第三个窗口：

 
 
 
    
  
  
  
  1. SELECT blocking_session_id, wait_duration_ms, session_id  
    2.   
  
  
  2. FROM sys.dm_os_waiting_tasks  
    3.   
  
  
  3. WHERE blocking_session_id IS NOT NULL 
    4.   
  
  
  4.  
    5.   
  
  
  5. /*blocking_session_id    wait_duration_ms    session_id  
    6.   
  
  
  6.            52                  23876             54  
    7.   
  
  
  7. */ 
    8.

可以看出是SessionID为52的会话阻塞了SessionID为54的会话。

那么，52正在干啥坏事呢？在第三个窗口中执行：

 
 
 
    
  
  
  
  1. SELECT t.text  
    2.   
  
  
  2. FROM sys.dm_exec_connections c  
    3.   
  
  
  3. CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t  
    4.   
  
  
  4. WHERE c.session_id = 54  
    5.   
  
  
  5.  
    6.   
  
  
  6. /*text(@1 int,@2 tinyint,@3 tinyint)  
    7.   
  
  
  7. UPDATE [Production].[ProductInventory] set [Quantity] = @1    
    8.   
  
  
  8. WHERE [ProductID]=@2 AND [LocationID]=@3  
    9.   
  
  
  9. */ 
    10.

注意：这并不是***个查询窗口中的原SQL语句，SQL Server进行了自动参数化计划缓存（预编译）。

我们强制终止会话。在第三个窗口中执行：

kill 52 注意：窗口一的语句和窗口二的语句均终止。

提示：第三个语句中，使用sys.dm_exec_connections(DMV)返回了Session ID为53的most_recent_sql_handle列。这是SQL文本在内存中的指针。作为sys.dm_exec_sql_text动态管理函数的输入参数使用。从sys.dm_exec_sql_text返回了text列，该列显示了阻塞进程的SQL文本。如果阻塞成串，必须通过blocking_session_id和session_ID列仔细查看每一个阻塞进程，直到发现原始的阻塞进程。

二、配置语句等待锁释放的时长

如果有一个事务或语句被阻塞，意味着它在等待资源上的锁被释放。我们可以事先通过set lock_Timeout来设定需要等待的时间。

语法如下：SET LOCK_TIMEOUT time_period

参数以毫秒为单位。超过时会返回锁定错误。示例：

在***个窗口中执行：

 
 
 
    
  
  
  
  1. USE AdventureWorks  
    2.   
  
  
  2. BEGIN TRAN  
    3.   
  
  
  3. UPDATE Production.ProductInventory  
    4.   
  
  
  4. SET Quantity = 400  
    5.   
  
  
  5. WHERE ProductID = 1 AND 
    6.   
  
  
  6. LocationID = 1 
    7.

在第二个窗口中执行：

 
 
 
    
  
  
  
  1. USE AdventureWorks  
    2.   
  
  
  2. SET LOCK_TIMEOUT 1000  
    3.   
  
  
  3. UPDATE Production.ProductInventory  
    4.   
  
  
  4. SET Quantity = 406  
    5.   
  
  
  5. WHERE ProductID = 1 AND 
    6.   
  
  
  6. LocationID = 1  
    7.   
  
  
  7.  
    8.   
  
  
  8. /*１秒后的执行结果Msg 1222, Level 16, State 51,Line 3  
    9.   
  
  
  9. Lock request time out period exceeded.  
    10.   
  
  
  10. The statement has been terminated.  
    11.   
  
  
  11. */ 
    12.

解析:在这个示例中，我们设置了锁超时时间为1000毫秒，即１秒。这个设置不会影响资源被进程占有的时间，只会影响等待另一个进程释放资源访问的时间。

原文链接：http://www.cnblogs.com/downmoon/archive/2011/02/18/1957913.html

 【编辑推荐】

  1. SQL Server 2008中SQL应用之一“死锁(Deadlocking)”
  2. SQL Server 2008中SQL应用之二“锁定(locking)”
  3. 浅析SQL Server 2008中的代码安全之二：DDL触发器与登录触发器
  4. 浅析SQL Server 2008中的代码安全之三：通过PassPhrase加密
  5. 浅析SQL Server 2008中的代码安全之四：主密钥 

 

名称栏目：SQLServer2008中SQL应用之三“阻塞(Blocking)”
当前路径：http://www.csdahua.cn/qtweb/news22/457472.html

网站建设、网络推广公司-快上网，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 快上网

成都快上网为您推荐相关内容

App开发知识

各行业网站

高端网站设计　　 　泸州网站建设　　 　彭州做网站　　 　定制网站　　 　明安消防器材　　 　成都酒店服定制　　 　绵竹网站建设　　 　格蓝特设备　　 　成都网站制作　　 　营山月子会所　　 　瑞杰管理　　 　成都移动服务器托管　　 　微信开发　　 　犀浦防纹窗　　 　成都网站设计　　 　注册域名　　 　乐山电信机房　　 　香港空间腾讯云　　 　网站设计制作　　 　网站改版维护