SQL Server 2008中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
关联的常规非分布式事务和分布式事务。 在第一个查询窗口:
第二个窗口:
第三个窗口:
可以看出是SessionID为52的会话阻塞了SessionID为54的会话。 那么,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 参数以毫秒为单位。超过时会返回锁定错误。示例: 在第一个窗口中执行:
|
|
来自: 唐伯龙 > 《SQLServer》