我必须在遗留数据库的表中插入一些记录,并且由于它被其他古代系统使用,因此更改表不是解决方案.
问题是目标表有一个int主键但没有标识规范.所以我必须找到下一个可用的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的答案以及所有其他贡献的人！真棒！
解决方法
创建另一个表：
t_identity (id INT NOT NULL PRIMARY KEY CHECK (id = 1),value INT NOT NULL)
使用单行,锁定此行,并在每次需要IDENTITY时将值增加1.
要在单个语句中锁定,递增和返回新值,请使用：
UPDATE t_identity SET value = value + 1 OUTPUT INSERTED.value
如果您不想更新,只需锁定,然后发出：
SELECT value FROM t_identity WITH (UPDLOCK,ROWLOCK)
这将锁定表直到事务结束.
如果你在使用ancient_table之前总是首先锁定t_identity,你将永远不会陷入僵局.
