SQL Server计数器表行锁失效与死锁问题排查问询

阿华AIGC实验室

2026-5-6

看起来你在SQL Server里用事务管理计数器时遇到了锁范围过大的问题，导致不同计数器之间互相阻塞甚至死锁——我之前也碰到过类似的情况，核心原因其实是索引设计和查询执行计划的问题，咱们一步步来拆解解决：

问题根源分析

你的核心痛点是查询没有通过索引精准定位到目标行，导致SQL Server执行了索引扫描而非查找（Seek），进而使得UPDLOCK锁定了大量无关行，引发锁等待和死锁。

样本环境的问题

你创建的_COUNTERS_表主键是(ID, CODE, CVALUE)，这个复合索引的首列是自增ID，但你的查询条件是WHERE CODE = 'C1'。由于索引首列不匹配查询条件，SQL Server只能对整个聚集索引进行扫描来找到符合条件的行。即使你指定了ROWLOCK，扫描过程中SQL Server也会对扫描到的每一行加UPDLOCK——包括那些CODE不等于'C1'的行，这就导致窗口3查询CODE='C2'时，被窗口1的事务锁住了无关行，必须等待窗口1提交。

真实环境的问题

你的真实表VISUALSEGCONTADORES主键是(VSC_Alias, VSC_OFICODE)，这个设计本身是合理的，但出现锁等待的原因大概率是：

当查询**不带VSC_OFICODE**时，SQL Server可能因为统计信息过时、索引选择性不佳等原因，选择了扫描而非索引查找；
你创建的ix_VSGCONTADORES索引可能列顺序不合理或包含冗余列，导致SQL Server认为扫描比查找更高效；
事务持有锁的时间过长，进一步增加了死锁和锁等待的概率。

解决方案：确保查询精准定位单行，最小化锁范围

要实现各计数器完全隔离，关键是让每个查询都能通过索引精准定位到唯一行，这样UPDLOCK只会锁定目标行，不会影响其他计数器。

1. 修复样本环境的索引设计

把_COUNTERS_表的主键索引调整为以CODE为首列，因为你的查询是按CODE过滤，这样SQL Server可以直接通过索引查找定位到目标行：

DROP TABLE IF EXISTS _COUNTERS_;
CREATE TABLE _COUNTERS_ ( 
    ID INT IDENTITY NOT NULL, 
    CODE VARCHAR(20) NOT NULL, 
    CVALUE INT NOT NULL DEFAULT 0, 
    CONSTRAINT PK_ID PRIMARY KEY CLUSTERED ( CODE ASC, ID ASC ) 
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) 
);
INSERT INTO _COUNTERS_ (CODE, CVALUE) VALUES ('C1', 0), ('C2', 0);

调整后，执行窗口1和窗口3的事务，两个SELECT语句都会通过索引查找定位到单行，各自锁定自己的目标行，不会互相等待。

2. 优化真实环境的索引与查询

（1）确保主键索引被正确使用

你的主键(VSC_Alias, VSC_OFICODE)已经是聚集索引，对于带这两个条件的查询，应该能直接定位到单行。如果查询没有使用这个索引，建议：

更新表的统计信息：执行UPDATE STATISTICS VISUALSEGCONTADORES;，确保SQL Server能准确判断索引的选择性；
检查是否有隐式类型转换：比如VSC_OFICODE是int类型，查询时是否传入了字符串类型的值，导致索引失效；

临时强制使用主键索引（优先解决根本原因）：在查询中加入WITH (INDEX(PK_VISUALSEGCONTADORES))，比如：

BEGIN TRAN
SELECT * FROM VISUALSEGCONTADORES this_ 
WITH ( UPDLOCK, ROWLOCK, INDEX(PK_VISUALSEGCONTADORES) ) 
WHERE this_.VSC_Alias = 'VSG_UltimoCodEXP' AND VSC_OFICODE = 0

（2）针对仅带`VSC_Alias`的查询优化

如果业务中存在仅用VSC_Alias过滤的查询，且VSC_Alias本身是唯一的，可以单独创建一个非聚集索引：

CREATE NONCLUSTERED INDEX ix_VSC_Alias ON VISUALSEGCONTADORES (VSC_Alias ASC)
INCLUDE (VSC_Id, VSC_Contador) -- 包含查询和更新需要用到的列，避免键查找
WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF);

这样，当查询仅用VSC_Alias时，SQL Server可以通过这个索引快速定位到目标行，避免扫描。

（3）缩短事务持有时间

尽量减少事务中除了查询和更新计数器之外的其他操作，确保事务尽快提交。比如不要在事务中等待用户输入、执行耗时的其他查询等——长时间持有锁会大幅增加死锁和锁等待的概率。

（4）使用`OUTPUT`子句简化逻辑（可选）

你可以将SELECT和UPDATE合并为一个语句，减少事务内的操作步骤，同时确保锁范围最小：

BEGIN TRAN
DECLARE @NewCounter DECIMAL(20,8);
UPDATE VISUALSEGCONTADORES
SET VSC_Contador = VSC_Contador + 1 -- 替换为你需要的具体值
OUTPUT INSERTED.VSC_Contador INTO @NewCounter
WHERE VSC_Alias = 'VSG_UltimoCodEXP' AND VSC_OFICODE = 0;
-- 这里可以使用@NewCounter的值做后续操作
COMMIT TRAN;

这种方式不需要先SELECT加锁再UPDATE，而是直接在UPDATE时锁定目标行，逻辑更简洁，锁持有时间更短。

验证锁范围

你可以通过以下查询来查看当前的锁情况，确认是否只锁定了目标行：

SELECT 
    resource_type,
    resource_description,
    request_mode,
    request_status,
    object_name(resource_associated_entity_id) AS table_name
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

正常情况下，你应该只会看到针对目标行的KEY类型锁（行锁），而不会看到PAGE或OBJECT级别的锁。

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