Только вставка строки, если она еще не существует

Для достижения этой цели я всегда использовал что-то похожее на следующее:

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

... но один раз под нагрузкой произошло нарушение первичного ключа. Это единственное утверждение, которое вообще вставляется в эту таблицу. Значит ли это, что вышеупомянутое утверждение не является атомарным?

Проблема в том, что это невозможно воссоздать по своему усмотрению.

Возможно, я могу изменить его на следующее:

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)

Хотя, возможно, я использую неправильные блокировки или слишком много блокировки или что-то в этом роде.

Я видел другие вопросы о stackoverflow.com, где ответы предполагают "IF (SELECT COUNT (*)... INSERT" и т.д., но я всегда находился под (возможно неправильным) предположением о том, что один оператор SQL быть атомарным.

Есть ли у кого-нибудь идеи?

Ответ 1

Как насчет шаблона "JFDI" ?

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

Серьезно, это самый быстрый и самый одновременный без блокировок, особенно при больших объемах. Что делать, если UPDLOCK эскалации и вся таблица заблокирована?

Прочтите урок 4:

Урок 4: При разработке upsert proc до настройки индексов я сначала доверял, что строка If Exists(Select…) будет запускаться для любого элемента и будет запрещать дубликаты. Нада. За короткое время появилось несколько тысяч дубликатов, потому что тот же самый элемент попал бы вверх в тот же миллисекунда, и обе транзакции увидели бы, что он не существует и выполняет вставку. После долгих испытаний решение заключалось в использовании уникального индекса, уловить ошибку и повторить попытку, чтобы транзакция увидела строку и выполнила обновление вместо вставки.

Ответ 2

Я добавил HOLDLOCK, которого нет первоначально. Пожалуйста, проигнорируйте версию без этого намека.

Насколько мне известно, этого должно быть достаточно:

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

Кроме того, если вы действительно хотите обновить строку, если она существует, и вставить, если это не так, вы можете найти этот вопрос полезным.

Ответ 3

Вы можете использовать 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)

Ответ 4

Я не знаю, является ли это "официальным" способом, но вы можете попробовать INSERT и вернуться к UPDATE, если он не работает.

Ответ 5

Можете ли вы обернуть выражение в транзакции? Это должно блокировать любые другие заявления от попытки вставить до тех пор, пока не будет совершена первая транзакция.

Ответ 6

Во-первых, огромные крики нашему человеку @gbn за его вклад в сообщество. Не могу даже объяснить, как часто я нахожусь после его совета.

Во всяком случае, достаточно фанатов.

Чтобы добавить немного к его ответу, возможно, "улучшите" его. Для тех, кто, как и я, оставил чувство неудовлетворенности тем, что нужно делать в сценарии <> 2627 (и никакой пустой CATCH не вариант). Я нашел этот маленький самородок от 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

Ответ 7

Я сделал аналогичную операцию в прошлом, используя другой метод. Во-первых, я объявляю переменную для хранения первичного ключа. Затем я заполняю эту переменную выводом оператора select, который ищет запись с этими значениями. Тогда я делаю и утверждение IF. Если первичный ключ равен нулю, тогда вставьте, иначе, верните код ошибки.

     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