检测阻塞

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.