Upserts concorrentes no SQL Server sem disputa de existência

Um upsert parece simples: atualizar quando a chave de negócio existe e inserir caso contrário. Duas sessões, porém, podem observar simultaneamente a mesma ausência e decidir inserir. Com uma restrição única, uma recebe erro. Sem ela, ambas podem concluir e violar o relacionamento esperado.

A primeira decisão é o significado da atualização. Substituir uma preferência de idioma é diferente de incrementar um saldo ou rejeitar uma edição baseada em dados antigos. Upsert representa uma política de gravação. Defina essa política antes de escolher a estratégia de bloqueio.

Proteger a chave de negócio

A tabela de demonstração permite uma preferência por cliente e nome. Crie-a somente em uma base descartável de prática.

-- Create only in a disposable practice database.
CREATE TABLE dbo.PreferenceDemo (
    CustomerId int NOT NULL,
    PreferenceKey nvarchar(50) NOT NULL,
    PreferenceValue nvarchar(200) NOT NULL,
    CONSTRAINT PK_PreferenceDemo PRIMARY KEY (CustomerId, PreferenceKey)
);

A chave única é a última barreira de integridade, mesmo que todas as aplicações devam seguir o procedimento correto. Inclua todas as dimensões necessárias, como TenantId quando identificadores só são únicos dentro de um cliente. Para chaves textuais, combine normalização e sensibilidade a maiúsculas; a collation influencia a igualdade.

Um IF NOT EXISTS separado do INSERT pode sofrer essa disputa no read committed comum. Colocar ambos em uma transação não protege necessariamente a chave ausente. É preciso proteger o intervalo em que ela seria inserida.

Este lote tenta atualizar primeiro e mantém a proteção da busca até o fim da transação.

DECLARE @CustomerId int = 42;
DECLARE @Key nvarchar(50) = N'language';
DECLARE @Value nvarchar(200) = N'en';
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'This batch owns its transaction.', 1;

BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.PreferenceDemo WITH (UPDLOCK, HOLDLOCK)
    SET PreferenceValue = @Value
    WHERE CustomerId = @CustomerId AND PreferenceKey = @Key;

    IF @@ROWCOUNT = 0
        INSERT dbo.PreferenceDemo(CustomerId, PreferenceKey, PreferenceValue)
        VALUES (@CustomerId, @Key, @Value);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

HOLDLOCK aplica comportamento serializable à referência da tabela. UPDLOCK solicita bloqueios voltados à atualização. O índice único permite proteger a chave ou o intervalo pertinente. Isso não garante bloqueio de apenas uma linha física: o caminho de acesso e o restante do trabalho influenciam a abrangência.

Verifique @@ROWCOUNT imediatamente depois do UPDATE, sem inserir uma instrução de log entre eles. Uma linha encontrada conta mesmo quando já contém o valor fornecido; portanto, o código não tenta criar outra nesse caso.

Explicitar conflitos aceitáveis

Duas solicitações atribuindo idiomas diferentes podem ser executadas em sequência. A última atribuição serializada concluída determina o valor. Isso não garante que vença a última requisição recebida pelo servidor web e não identifica alguém sobrescrevendo uma edição de outro usuário.

Se edições antigas devem ser rejeitadas, inclua uma rowversion esperada no predicado UPDATE e investigue a ausência de correspondência como conflito. rowversion é um token de alteração, não uma data. Criação e substituição condicional podem justificar operações distintas na API.

Repetir uma atribuição do mesmo valor costuma ser idempotente no nível do dado. Repetir "somar dez" não é. Gatilhos, auditoria e mensagens externas também podem produzir efeitos adicionais. Utilize um identificador durável de solicitação quando a operação precisa ser aplicada uma vez por pedido.

MERGE não elimina as questões de unicidade, isolamento e concorrência. Avalie a carga e a versão específicas em vez de presumir que uma única instrução torna segura toda a operação de negócio.

Testar com conexões concorrentes

Execute o lote em duas conexões contra a mesma tabela, incluindo uma chave ainda inexistente. Em um teste controlado, pause temporariamente uma sessão após UPDATE, com a transação aberta, e observe a outra aguardando. Retire a pausa do código da aplicação.

Teste também chaves distintas, repetições, violações de restrição e perda de conexão perto de COMMIT. O cliente pode não saber se houve confirmação. Uma repetição cega pode duplicar efeitos; resolva a dúvida pelo identificador da solicitação ou pelo estado autoritativo.

Bloqueios de intervalo podem aumentar a contenção e não eliminam deadlocks. Acesse múltiplas chaves em ordem consistente e mantenha transações curtas. Repita a transação inteira da vítima com espera limitada, e não apenas o INSERT dentro de uma transação comprometida. O upsert correto preserva a chave única e a política declarada de conflitos.

Referências técnicas: Microsoft Learn: Table hints · Microsoft Learn: Transaction locking guide.