Início / dba / Perguntas / 273765
Accepted
Panzercrisis
Asked: 2020-08-15 08:53:56 +0800 CST

No SQL Server, em termos leigos, como você bloqueia recursos suficientemente bem para executar uma transação INSERT-IF-NOT-EXISTS?

  • 772

Se alguém perguntar como realizar uma INSERT-IF-NOT-EXISTSoperação no SQL Server, normalmente receberá uma resposta como esta:

IF NOT EXISTS(SELECT 1 FROM [TheTable] WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)

O problema que estou vendo com isso é que entre a SELECTdeclaração e a INSERTdeclaração, a situação pode mudar externamente. Outro processo poderia inserir o ColumnXvalor após a SELECTinstrução, mas antes da INSERTinstrução, resultando em um erro sendo gerado.

Trabalho com software há algum tempo, mas não sou especialista em banco de dados, e quando procuro uma resposta para esse problema no SQL Server, os resultados que estou vendo são irrelevantes ou muito difíceis de aplicar (porque são estão respondendo a uma pergunta diferente ou são escritos em termos adaptados para especialistas em BD).

Então, em termos leigos, como você resolve esse problema? Eu fiquei um pouco enferrujado com o SQL na história recente, mas estou pensando que realmente deveria haver um mecanismo de bloqueio pragmático para usar para isso (existe ou não). Como alternativa, talvez o tratamento de erros possa determinar especificamente se um erro gerado corresponde a esse problema exato, ignorando-o nesse caso específico.

De preferência, isso não envolve apenas bloquear toda a mesa todas as vezes.

sql-server locking

1 respostas

  1. Best Answer

    Opção 1: faça um bloqueio que bloqueie pelo menos o intervalo no índice em que a linha existiria.

    SET XACT_ABORT ON;
BEGIN TRAN
IF NOT EXISTS(SELECT 1 FROM [TheTable] WITH (UPDLOCK, HOLDLOCK) WHERE [ColumnX] = @valX)
    INSERT [TheTable] ([ColumnX]) VALUES (@valX)
COMMIT

    HOLDLOCKfornecerá semântica serializável e bloqueará o intervalo entre as chaves existentes no índice onde o valor caberia (se houver um índice adequado, caso contrário, bloqueará toda a tabela). UPDLOCKreduz a probabilidade de deadlocks nesse padrão, pois duas consultas simultâneas não podem remover o mesmo bloqueio de intervalo na fase de leitura.

    Opção 2: você pode simplesmente adicionar uma restrição exclusiva ColumnXe tentar a inserção de qualquer maneira e detectar o erro gerado pela violação de chave duplicada.

    Dado que a Opção 1 precisa de um índice com coluna ColumnXinicial de qualquer maneira para atender à sua preferência de não "bloquear toda a tabela toda vez", você também pode adicionar um e defini-lo como exclusivo. O índice irá acelerar a verificação de existência de qualquer maneira. Com isso no lugar, eu selecionaria entre as opções 1 e 2 com base na frequência com que espero tentativas de inserir duplicatas.

    • 4

relate perguntas