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

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

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

首先我们来模拟一个阻塞情况 打开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 执行以上代码  可用看到被阻塞的请求

也可以通过设置请求过期时间 

SET LOCK_TIMEOUT 5000;

当然如果加锁的电脑一直占用资源 或者不知道什么情况可能 直接终止 进程

kill 52

释放资源 解除锁  而52进程 连接的电脑会包以下错

自动回滚