Upserts concurrents dans SQL Server: protéger la clé absente

Un upsert semble simple: modifier la ligne si sa clé métier existe, sinon l'insérer. Deux sessions peuvent pourtant constater simultanément l'absence de la même clé et décider toutes deux d'insérer. Avec une contrainte unique, l'une reçoit une erreur. Sans contrainte, les deux peuvent réussir et créer une incohérence.

La première décision concerne la signification du remplacement. Affecter une préférence linguistique n'est pas incrémenter un solde ni refuser une modification fondée sur des données anciennes. Un upsert représente une politique d'écriture métier. Définissez-la avant de choisir les verrous.

Protéger la clé métier

Cette table de démonstration impose une préférence par client et par nom. Créez-la seulement dans une base de pratique jetable.

-- 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)
);

La clé unique reste la dernière protection, même si toutes les applications doivent utiliser la même procédure. Incluez toutes les dimensions nécessaires, notamment TenantId si les identifiants sont propres à un locataire. Pour les clés textuelles, collation et normalisation déterminent quelles chaînes sont égales.

Un IF NOT EXISTS séparé d'un INSERT est vulnérable en lecture validée ordinaire. Une transaction autour des deux instructions ne protège pas nécessairement la clé absente. Il faut protéger la plage où cette clé pourrait être insérée.

Ce batch tente d'abord la mise à jour et conserve la protection jusqu'à la fin de la transaction.

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 applique un comportement sérialisable à cette référence de table. UPDLOCK demande des verrous adaptés à une future modification. L'index unique permet de protéger la clé ou sa plage. Cela ne garantit pas qu'une seule ligne physique sera verrouillée: le chemin d'accès et les autres opérations influencent la portée.

La vérification de @@ROWCOUNT doit suivre immédiatement UPDATE. N'intercalez pas une instruction de journalisation. Une ligne trouvée compte même si sa valeur est déjà égale à la valeur fournie; le code ne tente donc pas d'insérer une seconde ligne dans ce cas.

Définir les conflits acceptables

Deux requêtes concurrentes affectant des langues différentes peuvent se succéder. La dernière affectation sérialisée terminée détermine la valeur. Ce n'est pas une garantie que la dernière requête reçue par le serveur web gagne. Le mécanisme ne détecte pas non plus l'écrasement d'une modification faite par un autre utilisateur.

Pour refuser les éditions périmées, utilisez une rowversion attendue dans le prédicat UPDATE et examinez l'absence de correspondance comme un conflit. rowversion est un jeton de changement, pas une date. Création et remplacement conditionnel peuvent alors mériter des opérations d'API distinctes.

Répéter la même affectation est généralement idempotent au niveau de la valeur. Répéter une addition de dix ne l'est pas. Déclencheurs, audit et messages externes peuvent également produire des effets supplémentaires. Une opération devant être appliquée une seule fois par demande nécessite un identifiant durable de cette demande.

MERGE n'élimine pas les questions d'unicité, d'isolation et de concurrence. Évaluez le comportement sur la charge et la version précises, sans déduire la sûreté métier du simple fait qu'il s'agit d'une instruction unique.

Tester deux sessions concurrentes

Exécutez le batch depuis deux connexions vers la même table, notamment pour une clé encore absente. Dans un test contrôlé, suspendez temporairement une session après UPDATE, transaction ouverte, et observez l'attente de l'autre. Retirez cette pause du code réel.

Testez également des clés différentes, les répétitions, les violations de contraintes et une coupure autour de COMMIT. Après une perte de connexion, le client peut ignorer si la validation a eu lieu. Une reprise aveugle risque de répéter des effets; consultez l'identifiant de demande ou l'état faisant autorité.

Les verrous de plage peuvent augmenter la contention et n'excluent pas les interblocages. Accédez à plusieurs clés dans un ordre cohérent et gardez les transactions courtes. Rejouez toute la transaction victime avec un délai borné, pas seulement INSERT dans une transaction devenue invalide.

Références techniques: Microsoft Learn: Table hints · Microsoft Learn: Transaction locking guide.