Пожалуйста, Прекратите Использование Антишаблона Upsert (Sql Server).

Для будущих студентов курса «Разработчик MS SQL-сервера» подготовил перевод статьи.
Также приглашаем вас посмотреть открытый вебинар по теме «Графовые базы данных в SQL Server» .
В ходе урока участники вместе с экспертом рассмотрят, что такое графовые базы данных и какие возможности доступны для работы с графами и иерархиями в SQL Server.

думаю все уже знают мое мнение о MERGE и почему я держусь от него подальше.
Но вот еще один антишаблон, который я вижу все время, когда мне нужно выполнить UPSERT (UPdate inSERT — обновить строку, если она существует, и вставить, если ее нет):
  
  
  
  
   

 IF EXISTS (SELECT 1 FROM dbo.t WHERE [key] = @key)
 BEGIN
   UPDATE dbo.t SET val = @val WHERE [key] = @key;
 END
 ELSE
 BEGIN
   INSERT dbo.t([key], val) VALUES(@key, @val); 
 END

   
Это кажется довольно логичным и соответствует тому, как мы об этом думаем:
Существует ли строка для данного ключа?
ДА : Обновите эту строку.

НЕТ : Тогда добавим.

Но это расточительно
Поиск строки только для проверки ее существования, а затем повторный поиск для ее обновления — пустая трата времени.
И даже если ключ проиндексирован (что, я надеюсь, всегда так).
Эту логику можно изобразить в виде блок-схемы, где для каждого шага показана операция, происходящая в базе данных:

Обратите внимание, что каждая ветвь будет выполнять две операции с индексом.
Более того, если строка не существует, не используются явные транзакции и не учитывается уровень изоляции, то многое может пойти не так (кроме производительности):
Если ключ существует и два сеанса выполняют UPDATE одновременно, то они оба будут успешными (один «выиграет», а «проигравший» получит «потерянное обновление»).
Само по себе это не проблема, системы с параллелизмом работают именно так.
Здесь Пол Уайт более подробно рассказывает о внутренней механике и Здесь Мартин Смит о некоторых других нюансах.

Если ключ не существует и обе сессии проходят этап проверки существования ключа одинаково, то при попытке INSERT может случиться что угодно:
тупик из-за несовместимости замков;
ключевое нарушение , которого не должно быть;
вставка повторяющихся значений ключ, если столбец не имеет допустимых ограничений.

Последний вариант является худшим, поскольку данные может быть поврежден .
Взаимные блокировки и исключения можно легко устранить с помощью обработки ошибок, XACT_ABORT или повторных попыток, в зависимости от того, как часто вы ожидаете коллизий.
Но если вы думаете, что находитесь в безопасности и защищены от дублирования ключей (или ошибок ключей) при проверке ЕСЛИ СУЩЕСТВУЕТ, то вас ждет сюрприз.
Если вы ожидаете, что столбец будет ключевым, сделайте его официальным и добавьте ограничения.

«Многие говорят…»
Дэн Гузман рассказал о состоянии гонки более десяти лет назад в Условное состояние гонки INSERT/UPDATE , а затем в Состояние гонки «UPSERT» с MERGE .
Майкл Сварт также затронул эту тему несколько лет назад в Разрушение мифов: одновременное обновление/вставка решений , включая тот факт, что при сохранении исходной логики и только повышении уровня изоляции нарушения ключевых ограничений переходят в тупики.
Позже он написал о MERGE в статье Будьте осторожны с оператором слияния .
Обязательно прочитайте все комментарии к обоим постам.

Решение
За свою карьеру я исправил множество взаимоблокировок, используя следующий шаблон (удалить лишнюю проверку, обернуть все в транзакцию и защитить доступ к первой таблице соответствующей блокировкой):

BEGIN TRANSACTION; UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key; IF @@ROWCOUNT = 0 BEGIN INSERT dbo.t([key], val) VALUES(@key, @val); END COMMIT TRANSACTION;

Зачем нужны две подсказки? Разве UPDLOCK недостаточно?
UPDLOCK используется для защиты от взаимоблокировок на уровне выражения (пусть еще один сеанс подождет, вместо того, чтобы провоцировать жертву на повторную попытку).

SERIALIZABLE используется для защиты от изменений исходных данных на протяжении всего транзакции (чтобы убедиться, что недостающая строка по-прежнему отсутствует).

Здесь немного больше кода, но это на 1000% безопаснее.
И даже в худшем случае (когда строка еще не существует) она будет работать не хуже рассматриваемого антипаттерна.
И в лучшем случае, если вы обновляете уже существующую строку, это более эффективно, поскольку поиск строки выполняется только один раз.
Давайте еще раз изобразим операции, происходящие в базе данных:

В данном случае имеется ветвь, в которой выполняется только одна операция поиска по индексу.
Оказывается следующее:
Если ключ существует и две сессии пытаются обновить его одновременно, то они оба по очереди успешно обновляют строку , как прежде.

Если ключ не существует, то один из сеансов «выиграет» и вставит строку .
Еще одна сессия придется ждать (даже для проверки существования строки), пока блокировки не будут сняты и не будет выполнено UPDATE.
В обоих случаях сессия, выигравшая гонку, теряет свои данные из-за того, что «проигравший» обновит их впоследствии.
Обратите внимание, что общая пропускная способность в системе с высоким уровнем параллелизма может пострадать.
Но это компромисс, на который вы должны быть готовы пойти.
Некоторые разработчики хотели бы никогда не иметь блокировок, но некоторым они абсолютно необходимы для обеспечения целостности данных.

Но что, если UPDATE менее вероятен?
Очевидно, что приведенное выше решение оптимизировано для UPDATE и предполагает, что наличие ключа в таблице столь же вероятно, как и его отсутствие.
Если, наоборот, вам нужно оптимизировать INSERT, когда INSERT более вероятен, чем UPDATE, тогда вы можете изменить логику и при этом сохранить UPSERT в безопасности:

BEGIN TRANSACTION; INSERT dbo.t([key], val) SELECT @key, @val WHERE NOT EXISTS ( SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE) WHERE [key] = @key ); IF @@ROWCOUNT = 0 BEGIN UPDATE dbo.t SET val = @val WHERE [key] = @key; END COMMIT TRANSACTION;

Здесь также есть подход «просто сделай это», если вы слепо INSERT и позволяете коллизиям вызывать исключения:

BEGIN TRANSACTION; BEGIN TRY INSERT dbo.t([key], val) VALUES(@key, @val); END TRY BEGIN CATCH UPDATE dbo.t SET val = @val WHERE [key] = @key; END CATCH COMMIT TRANSACTION;

Стоимость таких исключений часто превышает стоимость проверки.
Важно знать приблизительный коэффициент попаданий/промахов.
Я написал об этом Здесь И Здесь .

А как насчет обработки нескольких строк?
Все вышесказанное относится к одиночному INSERT/UPDATE, но Джастин Пилинг спросил: что делать с несколькими строками, если вы не знаете, какие из них уже существуют? Если вы передаете список строк через что-то вроде параметра с табличным значением (TVP, табличные параметры), выполните UPDATE с помощью JOIN, а затем INSERT с использованием NOT EXISTS. Общий подход здесь остается таким же, как описано выше:

CREATE PROCEDURE dbo.UpsertTheThings @tvp dbo.TableType READONLY AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; UPDATE t WITH (UPDLOCK, SERIALIZABLE) SET val = tvp.val FROM dbo.t AS t INNER JOIN @tvp AS tvp ON t.[key] = tvp.[key]; INSERT dbo.t([key], val) SELECT [key], val FROM @tvp AS tvp WHERE NOT EXISTS (SELECT 1 FROM dbo.t WHERE [key] = tvp.[key]); COMMIT TRANSACTION; END

Если вы получаете список строк каким-либо другим способом, кроме TVP (XML, список, разделенный запятыми и т. д.), то сначала преобразуйте их в таблицу, а затем выполните JOIN для нужных данных.
Будьте осторожны при оптимизации этого кода для начального INSERT — потенциально вы можете ОБНОВИТЬ некоторые строки дважды.

выводы
Эти шаблоны UPSERT лучше тех, что я часто вижу, и я надеюсь, что вы начнете их использовать.
Я буду ссылаться на этот пост всякий раз, когда увижу шаблон IF EXIST. И еще я хочу передать привет Полу Уайту.
sql.kiwi | @SQK_Kiwi ) - Он так хорошо объясняет сложные понятия простыми словами.
У вас должна быть либо веская причина использовать MERGE (возможно, вам нужна какая-то непонятная функциональность MERGE), либо вы не восприняли приведенные выше ссылки всерьез.

Узнайте больше о курсе «Разработчик MS SQL-сервера» .
Посмотрите открытый вебинар по теме «Графовые базы данных в SQL Server» .