检测阻塞

USE TSQLFundamentals2008

Connection 1:

BEGIN TRAN;

UPDATE Production.Products

SET unitprice=unitprice+1.0

WHERE productid=2;

为了更新这一行，会话必须先获得一个排它锁，更新成功后将释放这个排它锁

 

Connection 2:

SELECT productid,unitprice

FROM Production.Products

WHERE productid=2

为了读取数据，会话需要一个共享锁，但是这一行已经被其他会话持有的排它锁锁定，因而获取不到共享锁，所以这个会话会被阻塞

 

Connection 3:

select

request_session_id as spid,

resource_type as restype,

resource_database_id as dbid,

DB_NAME(resource_database_id) as dbname,

resource_description as res,

resource_associated_entity_id as resid,

request_mode as mode,

request_status as status

from sys.dm_tran_locks

spid: 每个会话的唯一标识，服务进程标识符

restype: 被锁定的资源的类型

dbid: 被锁定资源所位于的数据库的ID，可以通过DB_Name转化成数据库名称

res: 资源说明

resid: 资源相关联的实体ID

mode: 锁模式

status: 已经授予锁还是正在请求锁

sys.dm_tran_locks 视图只提供当前阻塞链中设计到的集成的ID信息，除此之外没有其他关于进程的信息。为了获取与阻塞链中涉及到的进程相关联的联接的信息，可以查询一个名为sys.dm_exec_connections的动态管理视图

 

SELECT

session_id as spid,

connect_time,

last_read,

last_write,

most_recent_sql_handle 

FROM

sys.dm_exec_connections

根据spid找到阻塞的进程：

most_recent_sql_handle: 代表进程执行的sql语句，可以通过sys.dm_exec_sql_text将二进制数据转化成文本

select * from sys.dm_exec_sql_text(0x0100050030696736D0C2C980000000000000000000000000)

 

对于阻塞状态中涉及到的会话，用动态管理视图sys.dm_exec_sessions也能够找到很多有用的信息。

SELECT

session_id as spid,

login_time,

program_name,

login_name,

nt_user_name,

last_request_start_time,

last_request_end_time 

FROM sys.dm_exec_sessions

where session_id in (52,53)

对于排除阻塞状态，另一个可能有用的动态管理视图是sys.dm_exec_requests。下面的查询只筛选被阻塞的进程:

SELECT

session_id as spid,

blocking_session_id,

command,

sql_handle,

database_id,

wait_type,

wait_time,

wait_resource 

FROM sys.dm_exec_requests WHERE blocking_session_id>0

 

如果发现一直阻塞的进程，可以使用KILL(spid)来结束阻塞的进程。

此外，在默认情况下会话不会设置锁定的超时起先，如果想限制会话等待锁释放的时间，可以设置LOCK_TIMEOUT选项,-1代表无限等待。

SET LOCK_TIMEOUT 5000;

SELECT productid,unitprice

FROM Production.Products

WHERE productid=2

这样5秒之后就会抛出异常:

Lock request time out period exceeded.

转载于:https://www.cnblogs.com/fast-michael/archive/2012/07/04/2576631.html