Alleen een rij invoegen als deze er nog niet is

Ik had altijd iets soortgelijks als het volgende gebruikt om het te bereiken:

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

…maar eenmaal onder belasting vond er een schending van de primaire sleutel plaats. Dit is de enige verklaring die überhaupt in deze tabel wordt ingevoegd. Betekent dit dan dat de bovenstaande verklaring niet atomair is?

Het probleem is dat het bijna onmogelijk is om dit naar believen opnieuw te maken.

Misschien kan ik het veranderen in iets als het volgende:

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)

Hoewel, misschien gebruik ik de verkeerde sloten of gebruik ik te veel sloten of zoiets.

Ik heb andere vragen op stackoverflow.com gezien waar de antwoorden een “IF (SELECT COUNT(*) … INSERT” enz. suggereren, maar ik was altijd in de (misschien onjuiste) veronderstelling dat een enkele SQL-instructie atomair zijn.

Heeft iemand een idee?

Antwoord 1, autoriteit 100%

Hoe zit het met het “JFDI”-patroon?

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

Serieus, dit is het snelst en het meest gelijktijdig zonder vergrendelingen, vooral bij hoge volumes.
Wat als de UPDLOCK wordt geëscaleerd en de hele tafel is vergrendeld?

Lees les 4:

Les 4:Bij het ontwikkelen van de upsert-proc voordat ik de indexen afstemde, vertrouwde ik er eerst op dat de regel If Exists(Select…)voor elk item zou worden geactiveerd en doublures verbieden. Nada. In korte tijd waren er duizenden duplicaten omdat hetzelfde item in dezelfde milliseconde de upsert zou raken en beide transacties zouden zien dat een niet bestaat en de invoeging uitvoeren. Na veel testen was de oplossing om de unieke index te gebruiken, de fout op te vangen en opnieuw te proberen de transactie de rij te laten zien en een update uit te voeren in plaats van een invoeging.

Antwoord 2, autoriteit 38%

Ik heb HOLDLOCK toegevoegd dat oorspronkelijk niet aanwezig was. Negeer de versie zonder deze hint.

Wat mij betreft zou dit voldoende moeten zijn:

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

Als je een rij wilt bijwerken als deze bestaat en invoegen als dat niet het geval is, kun je deze vraagnuttig.

Antwoord 3, autoriteit 24%

U zou MERGE kunnen gebruiken:

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)