Identidade SQL (autonumber) é incrementada mesmo com um rollback de transação

Eu tenho uma transação de .net com uma inserção de SQL para um database do SQL Server 2005. A tabela tem uma chave primária de identidade.

Quando ocorre um erro dentro da transação, Rollback() é chamado. As inserções de linha são recuperadas corretamente, no entanto, na próxima vez que eu inserir dados na tabela, a identidade será incrementada como se a reversão nunca tivesse ocorrido. Então, essencialmente, existem lacunas na sequência de identidade. Existe alguma maneira de ter o método Rollback() recuperar a identidade ausente?

Eu não estou me aproximando disso da maneira certa?

Se você pensar sobre isso, o número de incremento automático não deve ser transacional. Se outras transactions tivessem que esperar para ver se o número automático seria usado ou “revertido”, elas seriam bloqueadas pela transação existente usando o número automático. Por exemplo, considere meu código de psuedo abaixo com a tabela A usando um campo de numeração automática para a coluna ID:

 User 1 ------------ begin transaction insert into A ... insert into B ... update C ... insert into D ... commit User 2 ----------- begin transaction insert into A ... insert into B ... commit 

Se a transação do usuário 2 iniciar um milissegundo após o usuário 1, a inserção na tabela A teria que aguardar a conclusão da transação inteira do usuário 1 para ver se o número automático da primeira inserção em A foi usado.

Este é um recurso, não um bug. Eu recomendaria usar outro esquema para gerar números automáticos se você precisar que eles sejam bem sequenciais.

Se você depende de seus valores de identidade estarem vazios, então sim – você está fazendo errado. O ponto principal de uma chave substituta é não ter significado comercial .

E, não, não há como mudar esse comportamento (além de rolar seu próprio incremento automático e sofrer as conseqüências de desempenho de bloquear outras inserções).

Você obtém lacunas na sua sequência se DELETE uma linha também.

Sequências são necessárias para serem únicas, mas elas não precisam ser seqüenciais. O fato de que eles estão aumentando monotonicamente é apenas um acaso de implementação.

Tanto quanto eu sei as linhas de inserção afirmam o número de autonome e na reversão esse número é perdido para sempre. Se você estiver dependendo do número de autonúmeros em sequenciamento, talvez queira considerar a abordagem que está usando.

Eu não acho que exista qualquer exigência de que as teclas autonumeradas sejam sequenciais. Na verdade, não acho que eles precisem ser:

  • a transação começa e insere
  • transação b inicia e insere
  • transação aborta

    você consegue um buraco. nada a ver com isso.

Todos os outros cartazes que dizem não se preocupar com isso, e que você deve ter lacunas, estão certos. Se houver um significado comercial para o número, e esse significado não funcionar com lacunas, não use uma coluna de identidade.

FYI, se por qualquer motivo você quiser remover as lacunas, a maioria dos bancos de dados tem uma maneira de propagar novamente a numeração automática para o número de sua escolha. É uma dor na bunda, e se você precisar fazer isso regularmente, você definitivamente não deveria estar usando um campo de autonumber / identity, como mencionado acima. Mas aqui está o código para fazer isso no servidor SQL:

DBCC CHECKIDENT (‘Produto’, RESEED, 0)

Isso define a tabela de produtos para iniciar em 1 (embora, se você tiver registros na tabela, obviamente ignore os valores de ID que já foram usados.) Outros fornecedores de RDBMS possuem sua própria syntax, mas o efeito é aproximadamente o mesmo, Portanto, procure “redigite a identidade” ou “repassa o número de autonumeração” nos arquivos de ajuda do sistema ou nas internets.

Mais uma vez: isto é para ocasiões especiais, não uso regular. Não coloque em um procedimento armazenado e faça com que todos venham até lá.

Muhan tenta pensar nisso no contexto de muitas conexões simultâneas executando esta transação e não uma de cada vez. Alguns falharão e alguns terão sucesso. Você deseja que o SQL Server se concentre em executar as novas solicitações conforme elas entram e não em manter uma coluna de identidade sem espaços. IMO-lo (lacunas nos valores) é definitivamente algo que não vale a pena gastar tempo.

Não. As implementações de sequência usam uma transação autônoma. No Oracle, a transação autônoma já foi interna ao dbms, mas agora está exposta para seu próprio uso (e é frequentemente usada incorretamente)

 PRAGMA AUTONOMOUS_TRANSACTION;'