SQL Server按GroupID控制事务锁：仅阻塞指定值的增删改

我在SQL Server中遇到一个经典多线程并发问题，需要按数据值控制锁行为，但找不到合适的关键词搜索解决方案。

假设在Read-Committed隔离级别下有表A，主键为GroupID和ItemID：

CREATE TABLE A (
    GroupID INT
    , ItemID INT
    , Attribute1 VARCHAR(50) 
    PRIMARY KEY (GroupID, ItemID)
)

需求是：高效地仅阻塞特定GroupID值的插入/更新/删除操作，且该GroupID初始可能不存在于表中。

举例：所有操作GroupID=1的事务必须串行执行（顺序无关），但操作GroupID=2的新事务可以并行处理。

我知道当GroupID已存在时，UPDLOCK能完美工作，但无法处理同时插入新GroupID的场景。已尝试的其他方案都有副作用：

• 将GroupID拆分到上层表：修改现有代码成本极高，且新表插入场景仍需用TABLOCK处理
• 使用TABLOCK锁定整张表：性能损失巨大

TRUNCATE TABLE A
DECLARE @GroupID INT = 1 --其他线程改为2
DECLARE @RowCount INT
BEGIN TRAN
SELECT @RowCount = COUNT(*) FROM A WITH(TABLOCKX) WHERE A.GroupID = @GroupID

WAITFOR DELAY '00:00:10' --其他线程注释此行

INSERT INTO A SELECT @GroupID,@RowCount+1,'Value1'

COMMIT TRAN
--线程2必须等待线程1完成

• 使用HOLDLOCK或SERIALIZABLE强制所有事务串行执行：仍存在巨大性能损失

TRUNCATE TABLE A
DECLARE @GroupID INT = 1 --其他线程改为2
DECLARE @RowCount INT
BEGIN TRAN
SELECT @RowCount = COUNT(*) FROM A WITH(UPDLOCK, HOLDLOCK) WHERE A.GroupID = @GroupID

WAITFOR DELAY '00:00:10' --其他线程注释此行

INSERT INTO A SELECT @GroupID,@RowCount+1,'Value1'

COMMIT TRAN
--线程2必须等待线程1完成

请问是否有其他解决方案，或可用于搜索的关键词？谢谢！

更新：临时解决方案（无删除操作场景） #

经过团队头脑风暴，我们得到以下临时方案，满足两个要求：

1. 可阻塞相同GroupID的事务
2. 允许其他GroupID的事务并行执行

--如果存在删除操作，需使用UPDLOCK, HOLDLOCK
IF NOT EXISTS(SELECT 1 FROM A WITH(NOLOCK) WHERE A.GroupID = @GroupID) 
AND NOT EXISTS(SELECT 1 FROM A WITH(UPDLOCK, READPAST) WHERE A.GroupID = @GroupID)
    SELECT @RowCount = COUNT(*) FROM A WITH(UPDLOCK, HOLDLOCK) WHERE A.GroupID = @GroupID
ELSE
    SELECT @RowCount = COUNT(*) FROM A WITH(UPDLOCK) WHERE A.GroupID = @GroupID

但我强烈怀疑NOLOCK和UPDLOCK之间存在时间间隙的问题。目前仍在使用该方案，因为经过3次400万行4线程测试，我们可以接受每月仅出现一次失败的风险。

更新#2：sp_getapplock方案测试 #

我测试了@siggemannen建议的sp_getapplock，发现这是适合我场景的最佳方案，替代了上述存在潜在失败风险的临时方案。

但以下测试结果表明，该方法是否适用取决于业务操作模式，并非适合所有场景：

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