Melhor maneira de obter o próximo número de identificação sem "identidade"
Tenho que inserir alguns registros em uma tabela em um banco de dados herdado e, como é usado por outros sistemas antigos, alterar a tabela não é uma soluçã
O problema é que a tabela de destino possui uma chave primária int, mas nenhuma especificação de identidade. Então, tenho que encontrar o próximo ID disponível e usá-lo:
select @id=ISNULL(max(recid)+1,1) from subscriber
No entanto, quero impedir que outros aplicativos sejam inseridos na tabela ao fazer isso, para que não tenhamos problemas. Eu tentei o seguinte:
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
em duas janelas diferentes no SQL Management Studio e a transação única é sempre eliminada como vítima de impass
Eu também tenteiSET TRANSACTION ISOLATION LEVEL SERIALIZABLE
primeiro com o mesmo resultado ...
Alguma boa sugestão de como posso garantir que recebo o próximo ID e o use sem arriscar que outra pessoa (ou eu!) Esteja sendo processada?
Desculpe por não mencionar isso anteriormente, mas este é um servidor SQL 2000, então não posso usar coisas como FOR UPDATE e OUTPUT
ATUALIZA: Esta é a solução que funcionou para mim: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 é para que eu possa ter várias conexões e iniciar a consulta várias vezes para provocar simultaneidad
Obrigado a Quassnoi pela resposta e a todos vocês que contribuíram! Impressionante