带NOLOCK的SELECT是否会引发跨表阻塞？求排查思路

• 抓取完整阻塞链与锁资源详情
  用系统视图获取精准的锁冲突信息，不要只看表面的阻塞关系：

  • 重点确认插入语句（阻塞头）持有的锁类型、对应的资源ID，以及被阻塞SELECT请求的LCK_M_S锁资源是否与之重叠
  • 执行以下查询获取锁与会话的关联信息：

    SELECT 
        tl.request_session_id AS blocked_session,
        tl.resource_type,
        tl.resource_description,
        tl.request_mode AS blocked_request_mode,
        wt.blocking_session_id,
        st.text AS blocking_sql,
        st2.text AS blocked_sql
    FROM sys.dm_tran_locks tl
    JOIN sys.dm_os_waiting_tasks wt ON tl.lock_owner_address = wt.resource_address
    CROSS APPLY sys.dm_exec_sql_text(wt.blocking_session_id) st
    CROSS APPLY sys.dm_exec_sql_text(tl.request_session_id) st2
    WHERE wt.blocking_session_id <> 0;
    

• 排查wmfrLog表的隐式依赖
  插入操作可能通过触发器、约束间接关联到SELECT涉及的表：

  • 检查wmfrLog上的INSERT触发器，看是否在插入时操作了其他表：

    SELECT name, definition FROM sys.triggers WHERE parent_id = OBJECT_ID('wmfrLog');
    

  • 查看表上的外键约束，确认是否引用了SELECT查询的关联表：

    SELECT 
        fk.name AS foreign_key,
        OBJECT_NAME(fk.referenced_object_id) AS referenced_table
    FROM sys.foreign_keys fk WHERE parent_object_id = OBJECT_ID('wmfrLog');
    

• 验证NOLOCK的实际锁行为
  WITH (NOLOCK)并非完全无锁，它会获取Sch-S（架构稳定锁），如果插入操作触发了架构变更（比如自动统计信息更新、索引维护），会持有Sch-M锁，二者会直接冲突。另外：

  • 检查SQL Server日志，确认插入wmfrLog时是否触发了自动统计信息更新
  • 查看SELECT的执行计划，确认是否间接访问了wmfrLog（比如通过视图、隐式JOIN）

• 分析执行计划的资源依赖
  获取被阻塞SELECT的实际执行计划，重点排查：

  • 是否涉及分区表，且wmfrLog与SELECT的表使用了相同分区键，导致分区锁冲突
  • 是否存在键范围锁（比如SELECT用了范围查询，插入操作刚好命中该范围）
  • 是否通过索引关联间接访问了wmfrLog的资源

• 排查系统级资源冲突
  插入操作会修改系统元数据表（如sysallocunits、sysindexes），如果SELECT的NOLOCK语句在获取元数据时刚好遇到系统表被锁，也会出现跨表阻塞：

  • 用sys.dm_tran_locks过滤resource_type = 'METADATA'的锁记录，查看是否存在系统表锁冲突
  • 检查磁盘IO、CPU负载，确认是否因资源瓶颈导致锁持有时间过长，放大阻塞影响

• 在测试环境复现阻塞
  模拟生产环境的插入与查询操作，逐步移除触发器、修改执行计划、禁用约束，观察阻塞是否消失，以此定位根因。

内容的提问来源于stack exchange，提问作者Peter Proost