Wstawianie wiersza tylko wtedy, gdy go jeszcze nie ma

Zawsze używałem czegoś podobnego do następującego, aby to osiągnąć: 

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

...ale po załadowaniu doszło do naruszenia klucza głównego. Jest to jedyne polecenie, które w ogóle wstawia się do tej tabeli. Czy to oznacza, że powyższe stwierdzenie nie jest atomowe?

Problem w tym, że jest to prawie niemożliwe do odtworzenia do woli.

Być może mógłbym to zmienić na coś takiego: 

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)

Chociaż, może używam złe zamki albo za dużo zamków.

Widziałem inne pytania na stackoverflow.com gdzie odpowiedzi sugerują " IF (SELECT COUNT (*)... INSERT " itd., ale zawsze byłem pod (być może niepoprawnym) założeniem, że pojedyncze polecenie SQL będzie atomowe.

Czy ktoś ma jakieś pomysły?
Author: Zameer Ansari, 2010-08-04
Source

6 answers

A co ze wzorem"JFDI"?

BEGIN TRY
   INSERT etc
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() <> 2627
      RAISERROR etc
END CATCH
Poważnie, jest to najszybsze i najbardziej równoczesne bez zamków, szczególnie przy dużych ilościach. Co jeśli UPDLOCK jest eskalowany i cały stół jest zablokowany?

Przeczytaj lekcję 4:

Lekcja 4: podczas opracowywania proc upsert przed strojeniem indeksów, najpierw zaufałem, że linia If Exists(Select…) odpali dla dowolnego elementu i zabroni duplikatów. Nic. W krótkim czasie były tysiące duplikatów, ponieważ ten sam element trafiłby w upsert w tej samej milisekundzie i obie transakcje zobaczyłyby nie istnieje i wykonałyby insert. Po wielu testach rozwiązaniem było użycie unikalnego indeksu, wychwycenie błędu i ponowna próba umożliwienia transakcji zobaczenia wiersza i wykonania aktualizacji zamiast wstawiania.

 65
Author: gbn,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-12-13 20:20:14

Dodałem HOLDLOCK, który pierwotnie nie był obecny. Proszę pominąć wersję bez tej podpowiedzi.

Jeśli O mnie chodzi, to powinno wystarczyć: 
INSERT INTO TheTable 
SELECT 
    @primaryKey, 
    @value1, 
    @value2 
WHERE 
    NOT EXISTS 
    (SELECT 0
     FROM TheTable WITH (UPDLOCK, HOLDLOCK)
     WHERE PrimaryKey = @primaryKey)

Ponadto, jeśli chcesz zaktualizować wiersz, jeśli istnieje, a wstawić, jeśli nie, możesz znaleźć to pytanie przydatne.

 24
Author: GSerg,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:09:59

Możesz użyć 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)
 17
Author: Chris Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-04 17:45:14

Nie wiem, czy to jest "oficjalny" sposób, ale możesz spróbować INSERT, i wycofać się do UPDATE, jeśli się nie powiedzie.

 1
Author: Marcelo Cantos,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-04 16:59:46

Po pierwsze, ogromny okrzyk dla naszego człowieka @ gbn za jego wkład w społeczność. Nie potrafię nawet wyjaśnić, jak często podążam za jego radą.

W każdym razie, dość fanboyów. Aby dodać nieco do jego odpowiedzi, być może "poprawić" ją. Dla tych, tak jak ja, którzy czuli się zaniepokojeni tym, co robić w scenariuszu <> 2627 (i nie ma pustego {[2] } nie wchodzi w grę). Znalazłem ten mały samorodek z 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
 1
Author: pim,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-03 20:28:31

Wykonywałem podobną operację w przeszłości, używając innej metody. Najpierw deklaruję zmienną, która będzie trzymać klucz główny. Następnie wypełniam tę zmienną wyjściem instrukcji select, która szuka rekordu z tymi wartościami. Wtedy i jeśli oświadczenie. Jeśli klucz podstawowy jest null, to Wstaw, w przeciwnym razie zwróć jakiś kod błędu.

     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
 -5
Author: Marc,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-16 00:39:11