寻找合适的表提示组合

Question

我需要使用锁来执行一些SQL Server事务(select+insert(以防止冲突。我有以下场景：我有一个表，它的主键是一个整数，但不是自动递增的(遗留的，不要问(，因此它的值确定如下：

• 选择从表中检索最大ID值
• ID增加一
• 使用新ID在表中插入新记录

所有这些都是在事务中完成的，SQL命令如下：

SELECT @maxvalue = max(MyId) FROM MyTable
IF @maxvalue > 0 
SET @maxvalue = @maxvalue + 1 
ELSE 
SET @maxvalue = 1
INSERT INTO MyTable(MyValue, ...) VALUES(@maxvalue, ...)

在某些情况下，很容易出现重复的ID，编写该ID的人会将其放入循环中，并在出现重复密钥错误时重试该操作。因此，我改变了这一点，删除循环并在事务级别设置锁，如下所示：

SELECT @maxvalue = max(MyId) FROM MyTable WITH (HOLDLOCK, TABLOCKX)
IF @maxvalue > 0 
SET @maxvalue = @maxvalue + 1 
ELSE 
SET @maxvalue = 1
INSERT INTO MyTable(MyValue, ...) VALUES(@maxvalue, ...)

所以我指定了两个表提示，HOLDLOCK和TABLOCKX。对于一些数据库来说，这看起来不错，但对于一些在该表中有数万条记录的数据库来说，此事务花费了大量时间，大约10分钟。在SQL Server活动监视器中查看，我可以看到事务被挂起，尽管它在很长一段时间后被成功执行。

然后，我将提示更改为(HOLDLOCK, TABLOCK)，工作速度与使用提示之前一样快。

问题是，我不确定这是我想要的最佳组合，还是其他更合适的组合。我看过关于UPDLLOCK、HOLDLOCK和https://www.sqlteam.com/articles/introduction-to-locking-in-sql-server但我们将感谢专家的意见。

Answer 1

这应该可以防止重复。添加了一个额外的列来说明如何使用更多的列：

DECLARE @val INT
INSERT MyTable(MyValue, val2)
SELECT coalesce(max(MyValue),0) + 1, @val
FROM mytable

为了确保这一点，还要创建一个独特的约束：

ALTER TABLE MyTable   
ADD CONSTRAINT UC_MyValue UNIQUE (MyValue);