在没有“身份"的情况下获取下一个 ID 号的最佳方法

2022-01-06 00:00:00 sql deadlock transactions locking sql-server

我必须在旧数据库的表中插入一些记录，并且由于它被其他古老系统使用，因此更改表不是解决方案.

I have to insert some records in a table in a legacy database and, since it's used by other ancient systems, changing the table is not a solution.

问题是目标表有一个 int 主键但没有标识说明.所以我必须找到下一个可用的 ID 并使用它:

The problem is that the target table has a int primary key but no identity specification. So I have to find the next available ID and use that:

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

但是，当我这样做时，我想防止其他应用程序插入到表中，这样我们就不会出现任何问题.我试过这个:

However, I want to prevent other applications from inserting into the table when I'm doing this so that we don't have any problems. I tried this:

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 的两个不同窗口中，一个事务总是作为死锁受害者被杀死.

in two different windows in SQL Management Studio and the one transaction is always killed as a deadlock victim.

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

I also tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE first with the same result...

关于如何确保我获得下一个 ID 并使用它而不会冒其他人(或我！)被灌水的风险有什么好的建议吗?

Any good suggestions to how I can ensure that I get the next id and use that without risking that someone else (or me!) is getting hosed?

很抱歉之前没有提到这一点，但这是一个 SQL 2000 服务器，所以我不能使用诸如 FOR UPDATE 和 OUTPUT 之类的东西

Sorry for not mentioning this earlier, but this is a SQL 2000 server so I can't use things like FOR UPDATE and 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 使我可以有多个连接并多次启动查询以引发并发.

The WAITFOR is so that I can have multiple connections and start the query several times to provoke concurrency.

感谢 Quassnoi 的回答以及所有其他做出贡献的人！太棒了！

Thanks to Quassnoi for the answer and to all you other guys that contributed! Awesome!

推荐答案

创建另一个表:

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

对于单行，锁定该行，并在每次需要 IDENTITY 时将 value 加一.

with a single row, lock this row, and increment value by one each time you need an IDENTITY.

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

To lock, increment, and return the new value in a single statement, use:

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

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

If you don't want to update, just lock, then issue:

SELECT  value
FROM    t_identity WITH (UPDLOCK, ROWLOCK)

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

This will lock the table until the end of the transaction.

如果你总是先锁定t_identity，然后再搞乱ancient_table，你永远不会陷入死锁.

If you always first lock t_identity before messing with ancient_table, you will never get a deadlock.

相关文章