获得没有“身份"的下一个ID号的最佳方法是: [英] Best way to get the next id number without "identity"
问题描述
我必须在旧数据库的表中插入一些记录,并且由于其他古代系统使用了该记录,因此更改表不是解决方案.
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
加1.
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.
这篇关于获得没有“身份"的下一个ID号的最佳方法是:的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!