Apenas inserindo uma linha se ainda não estiver lá

Eu sempre usei algo semelhante ao seguinte para alcançá-lo:

INSERT INTO TheTable SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT NULL FROM TheTable WHERE PrimaryKey = @primaryKey) 

… mas uma vez sob carga, ocorreu uma violação de chave primária. Esta é a única declaração que se insere nessa tabela. Então, isso significa que a afirmação acima não é atômica?

O problema é que isso é quase impossível de recriar à vontade.

Talvez eu possa mudar isso para algo como o seguinte:

 INSERT INTO TheTable WITH (HOLDLOCK, UPDLOCK, ROWLOCK) SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT NULL FROM TheTable WITH (HOLDLOCK, UPDLOCK, ROWLOCK) WHERE PrimaryKey = @primaryKey) 

Embora, talvez eu esteja usando as fechaduras erradas ou usando muito bloqueio ou algo assim.

Eu tenho visto outras perguntas em stackoverflow.com onde as respostas estão sugerindo um “IF (SELECT COUNT (*) … INSERT” etc., mas eu estava sempre sob a suposição (talvez incorreta) de que uma única instrução SQL seria atômica.

Alguém tem alguma idéia?

   

E quanto ao padrão “JFDI” ?

 BEGIN TRY INSERT etc END TRY BEGIN CATCH IF ERROR_NUMBER() <> 2627 RAISERROR etc END CATCH 

Sério, isso é mais rápido e mais concorrido sem bloqueios, especialmente em grandes volumes. E se o UPDLOCK for escalado e toda a tabela estiver bloqueada?

Leia a lição 4 :

Lição 4: Ao desenvolver o procedimento upsert antes de ajustar os índices, primeiro confiei que a linha If Exists(Select…) seria triggersda para qualquer item e proibiria duplicatas. Nada. Em pouco tempo, havia milhares de duplicatas porque o mesmo item atingia o upsert no mesmo milissegundo e ambas as transactions veriam um não e executariam a inserção. Depois de muitos testes, a solução foi usar o índice exclusivo, detectar o erro e tentar novamente a transação para ver a linha e executar uma atualização em vez de uma inserção.

Eu adicionei HOLDLOCK, que não estava presente originalmente. Por favor, desconsidere a versão sem essa dica.

Tanto quanto eu estou preocupado, isso deve ser suficiente:

 INSERT INTO TheTable SELECT @primaryKey, @value1, @value2 WHERE NOT EXISTS (SELECT 0 FROM TheTable WITH (UPDLOCK, HOLDLOCK) WHERE PrimaryKey = @primaryKey) 

Além disso, se você realmente quiser atualizar uma linha, se ela existir e inserir, se você não quiser, poderá achar essa pergunta útil.

Você poderia usar MERGE:

 MERGE INTO Target USING (VALUES (@primaryKey, @value1, @value2)) Source (key, value1, value2) ON Target.key = Source.key WHEN MATCHED THEN UPDATE SET value1 = Source.value1, value2 = Source.value2 WHEN NOT MATCHED BY TARGET THEN INSERT (Name, ReasonType) VALUES (@primaryKey, @value1, @value2) 

Eu não sei se esta é a maneira “oficial”, mas você poderia tentar o INSERT , e voltar para UPDATE se falhar.

Em primeiro lugar, um grande grito para o nosso homem @gbn por suas contribuições para a comunidade. Não posso nem começar a explicar com que frequência me encontro seguindo o conselho dele.

Enfim, bastante fanboying.

Para adicionar um pouco à sua resposta, talvez “melhorar”. Para aqueles que, como eu, se sentiram inseguros com o que fazer no cenário <> 2627 (e nenhum CATCH vazio não é uma opção). Eu encontrei esta pequena pepita do technet .

  BEGIN TRY INSERT etc END TRY BEGIN CATCH IF ERROR_NUMBER() <> 2627 BEGIN DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState ); END END CATCH 

Eu fiz uma operação semelhante no passado usando um método diferente. Primeiro, declaro uma variável para manter a chave primária. Em seguida, preencho essa variável com a saída de uma instrução select que procura um registro com esses valores. Então eu faço e se declaração. Se a chave primária é nula, então insira, senão, retorne algum código de erro.

  DECLARE @existing varchar(10) SET @existing = (SELECT primaryKey FROM TABLE WHERE param1field = @param1 AND param2field = @param2) IF @existing is not null BEGIN INSERT INTO Table(param1Field, param2Field) VALUES(param1, param2) END ELSE Return 0 END