寻找合适的表提示组合

我需要使用锁来执行一些SQLServer事务(select+insert)以防止冲突。我有以下场景:我有一个主键是整数但不是自动递增(legacy,don ask)的表,因此它的值确定如下:

• ID递增1
• 使用新的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的场景,编写它的人把它放在一个循环中,并在出现重复键错误时重试该操作。因此,我将其更改为删除循环并在事务级别设置锁,如下所示:

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, ...)

所以我指定了两个 table hints , HOLDLOCK TABLOCKX . 对于一些数据库来说,这看起来不错,但是对于一些在这个表中有上万条记录的数据库来说,这个事务花费了很多时间,大约10分钟。通过查看insqlserver活动监视器,我可以看到事务被挂起,尽管在很长一段时间之后它被成功执行。

然后我把提示改成 (HOLDLOCK, TABLOCK) 和使用提示之前一样快。

问题是,我不确定这是我要找的最好的组合还是其他更合适的组合。我见过 Confused about UPDLOCK, HOLDLOCK 和 https://www.sqlteam.com/articles/introduction-to-locking-in-sql-server 但希望能有专家的意见。