无需``身份''即可获取下一个ID号的最佳方法。

Question

我必须在遗留数据库的表中插入一些记录，并且由于其他古代系统使用了该记录，因此更改表不是解决方案。

问题在于目标表具有int主键，但没有标识规范。因此，我必须找到下一个可用的ID并使用该ID：

select @id=ISNULL(max(recid)+1,1) from subscriber

但是，在执行此操作时，我想防止其他应用程序插入表中，以免出现任何问题。我尝试了这个：

begin transaction
    declare @id as int
    select @id=ISNULL(max(recid)+1,1) from subscriber WITH (HOLDLOCK, TABLOCK)
    select @id
    WAITFOR DELAY '00:00:01'
    insert into subscriber (recid) values (@id)
commit transaction
select * from subscriber

在SQL Management Studio中的两个不同的窗口中，一个事务始终作为死锁受害者被杀死。

我也SET TRANSACTION ISOLATION LEVEL SERIALIZABLE首先尝试了相同的结果…

关于如何确保我获得下一个ID并使用它而又不冒其他人（或我！）的危险的任何好的建议？

抱歉，您之前没有提到此内容，但这是一台SQL 2000服务器，因此我无法使用FOR UPDATE和OUTPUT之类的功能

更新 ：这是为我工作的解决方案：

BEGIN TRANSACTION
    DECLARE @id int

    SELECT  @id=recid
    FROM    identities WITH (UPDLOCK, ROWLOCK)
    WHERE table_name = 'subscriber'

    waitfor delay '00:00:06'

    INSERT INTO subscriber (recid) values (@id)

    UPDATE identities SET recid=recid+1 
    WHERE table_name = 'subscriber'

COMMIT transaction

select * from subscriber

WAITFOR使我可以有多个连接，并多次启动查询以激发并发性。

感谢Quassnoi的回答以及所有其他贡献的人！惊人的！

Answer 1

创建另一个表：

t_identity (id INT NOT NULL PRIMARY KEY CHECK (id = 1), value INT NOT NULL)

如果只有一行，请锁定此行，并value在每次需要时将其递增一IDENTITY。

要在单个语句中锁定，递增和返回新值，请使用：

UPDATE  t_identity
SET     value = value + 1
OUTPUT  INSERTED.value

如果您不想更新，只需锁定，然后发出：

SELECT  value
FROM    t_identity WITH (UPDLOCK, ROWLOCK)

这将锁定表直到事务结束。

如果您总是t_identity在弄乱之前先锁ancient_table，那么您将永远不会陷入僵局。