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

Я всегда использовал что-то похожее на следующее, чтобы добиться этого:

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)

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

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

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

Источник

7 ответов

Решение

Что насчет паттерна "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…) линия будет срабатывать для любого элемента и будет запрещать дублирование. Нада. За короткое время появились тысячи дубликатов, потому что один и тот же элемент попадал бы в верхний предел в одну и ту же миллисекунду, и обе транзакции увидели бы, что существует, и выполнили вставку. После тщательного тестирования решение состояло в том, чтобы использовать уникальный индекс, перехватить ошибку и повторить попытку, чтобы транзакция увидела строку и выполнила обновление вместо вставки.

Источник

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

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

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

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

Источник

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

Во-первых, огромное спасибо нашему человеку @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
Источник

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

Источник

В дополнение к принятому шаблону ответа JFDI вы, вероятно, захотите игнорировать 2601ошибки (в дополнение к 2627), что является «Нарушением уникального индекса».

      ...
IF ERROR_NUMBER() NOT IN (2601, 2627) THROW
...

PS И если вы уже используете C# и .NET, вот как вы можете аккуратно справиться с этим без сложного кода SQL, используя простой C# 6.0 whenутверждение:

      try
{
    connection.Execute("INSERT INTO etc");
}
catch (SqlException ex) when (ex.Number == 2601 || ex.Number == 2627)
{
    //ignore "dup key" errors
}

Кстати, вот хорошее чтение на эту тему: https://michaeljswart.com/2017/07/sql-server-upsert-patterns-and-antipatterns/

Источник

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

     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
Источник
Другие вопросы по тегам