Delete语句执行超时锁表，无阻塞SPID问题求助

嘿，我之前处理过好几次类似的SQL Server锁阻塞问题，来帮你捋捋可能的原因和该怎么排查：

首先先明确你的场景：执行这条DELETE语句后，目标表直接锁死，其他操作全卡，只能靠NOLOCK才能查数据，但用SP_WHO2却找不到标有Blk By的SPID。这里的关键是，SP_WHO2的Blk By列显示的是当前会话被哪个SPID阻塞，而你的情况大概率是这条DELETE会话自己拿着锁，把所有其他操作都堵了——你可能只盯着DELETE的那行看，没注意其他被阻塞的会话的Blk By列其实显示的是这个DELETE的SPID。

先从DELETE语句本身说起 #

你的DELETE语句是这样的：

DELETE tbl FROM <TableName> tbl WHERE tbl.Condition1 = <Value> AND tbl.Condition2 = <Value> AND tbl.Condition3 = <Value>

SQL Server里DELETE默认会拿排他锁（X锁），如果这条语句执行时间太长（比如匹配的记录特别多、WHERE条件没走索引全表扫、表上有慢触发器），锁就会一直攥着不放，其他读写操作根本拿不到必要的锁，自然就全部卡住了。

给你几个排查的具体步骤 #

1. 先搞清楚DELETE会话到底拿着什么锁 #

先找到你的DELETE语句对应的SPID（用sp_who2 active看活跃会话就行），然后跑这俩语句：

• 看它持有的锁详情：

  SELECT * FROM sys.dm_tran_locks WHERE request_session_id = <你的DELETE SPID>
  

  重点看resource_type是不是OBJECT（也就是表级锁），如果是，那肯定会堵死所有操作；如果是KEY或PAGE，那可能是锁升级导致的。
• 看这个会话是不是在等什么资源：

  SELECT * FROM sys.dm_exec_requests WHERE session_id = <你的DELETE SPID>
  

  看status列，如果是suspended，再看wait_type——比如如果是LOGMGR，那就是日志空间不够，DELETE卡在那了，锁自然一直不释放。

2. 检查是不是触发了锁升级 #

SQL Server默认持有超过5000个行锁时，会自动升级成表锁。如果你的DELETE要删几千上万行，很可能触发这个机制。可以查下有没有锁升级事件：

SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = N'RING_BUFFER_LOCK_ESCALATION'

要是确认是锁升级，你可以临时把表的锁升级关了（别长期用，只是测试）：

ALTER TABLE <TableName> SET (LOCK_ESCALATION = DISABLE)

3. 排查表上的触发器和依赖 #

如果目标表有DELETE触发器，触发器里的逻辑可能拖慢整个DELETE操作，甚至拿着额外的锁。你可以先禁用触发器，再跑DELETE试试，看是不是还锁表：

DISABLE TRIGGER ALL ON <TableName>

要是禁用后没问题，那就是触发器的锅，得优化触发器里的逻辑。

4. 看看当前会话的隔离级别 #

如果你的会话是SERIALIZABLE隔离级别，DELETE会拿更严格的范围锁，锁的范围更大，更容易堵。查一下当前隔离级别：

SELECT CASE transaction_isolation_level 
       WHEN 0 THEN 'Unspecified' 
       WHEN 1 THEN 'ReadUncommitted' 
       WHEN 2 THEN 'ReadCommitted' 
       WHEN 3 THEN 'RepeatableRead' 
       WHEN 4 THEN 'Serializable' 
       WHEN 5 THEN 'Snapshot' 
       END AS isolation_level 
FROM sys.dm_exec_sessions WHERE session_id = <你的SPID>

临时解决和长期优化建议 #

• 如果要删大量数据，别一次性删，分批次来，比如每次删1000行，这样锁持有时间短，也不容易触发锁升级：

  WHILE 1=1
  BEGIN
      DELETE TOP(1000) tbl FROM <TableName> tbl 
      WHERE tbl.Condition1 = <Value> AND tbl.Condition2 = <Value> AND tbl.Condition3 = <Value>
      IF @@ROWCOUNT = 0 BREAK
  END
  

• 给WHERE条件里的Condition1、Condition2、Condition3建个复合索引，这样DELETE能快速定位目标行，不用全表扫，锁的范围也小，执行时间也短。
• 检查数据库日志空间，要是日志满了，赶紧扩容或者备份日志释放空间，不然DELETE会卡着不动，锁一直不释放。

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