T-SQL:排除阻塞（十六）

yuanzijian-ruiec 2018-08-26 原文

当一个事务持有事务的资源锁，并且另一个事务请求同一资源的不兼容锁时，请求被阻塞并且请求者进入等待状态，直到锁定者释放干扰锁。

长时间运行事务会导致锁被长时间持有，所以只对要开启事务的表操作代码开启事务，不应将业务逻辑也放入事务中。这样做只会增加开启事务的时间会导致其他请求阻塞。

首先我们来模拟一个阻塞情况打开SQL Server 建立一个实例链接再打开建立一个实例链接也就是进程建两个进程连接

新建查询开启进程 sa(进程ID)

在第一个连接中加断点调试卡住断点

在第二个连接中执行查询

select * from Nums where n=123

可以看到请求被阻塞了

环境已经模拟了接下来是排除问题了

获取锁的信息

SELECT -- use * to explore
  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 进程

type 锁类型

dbid数据库ID

name 数据库名

res 资源

mode 锁模式 S:共享锁 X:排他锁 IX:意向排他锁 IS：意向共享锁

status （grant）是否加了锁 (wait) 等待

可以在看到在修改是除了行是排他锁（X）（表）对象和页都是意向排他锁（IX）下面更改锁模式让表锁升级

此时在运行至此查看锁模式

对象锁已经被表锁从意向排他锁升级成排他锁

刚刚只是查找那些资源被加锁进一步的想要查看是加锁信息

SELECT -- use * to explore
  session_id AS spid,
  connect_time,
  last_read,
  last_write,
  most_recent_sql_handle
FROM sys.dm_exec_connections

获取每个进程读写时间和最近运行的批处理语句由于被加锁了可以推断事务还在运行虽然可能不是最准确的但判别发生的场景

在通过 dm_exec_sql_text 把 handle 连接看到调用的最后一个批处理代码

SELECT session_id, text 
FROM sys.dm_exec_connections
  CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST 
WHERE session_id IN(52, 55);

通过甄别看到52最后运行代码可以确定因为阻塞的代码

在通过 sys.dm_exec_sessions 查看主机名确定是那台电脑出现的原因

SELECT -- use * to explore
  session_id AS spid,
  login_time,
  host_name,
  program_name,
  login_name,
  nt_user_name,
  last_request_start_time,
  last_request_end_time
FROM sys.dm_exec_sessions
WHERE session_id =52;

也可用dm_exec_requests 排除阻塞情况因为被阻塞的请求session_id是大于0的

SELECT -- use * to explore
  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;

再开一个连接 3 我们让连接1 卡断点连接2 请求被锁资源连接3 执行以上代码可用看到被阻塞的请求