Как стать автором
Обновить
828.09
OTUS
Цифровые навыки от ведущих экспертов

Пожалуйста, прекратите использовать антипаттерн UPSERT (SQL Server)

Время на прочтение 5 мин
Количество просмотров 36K
Автор оригинала: Aaron Bertrand

Для будущих учащихся на курсе "MS SQL Server Developer" подготовили перевод статьи.

Также приглашаем посмотреть открытый вебинар на тему «Графовые базы данных в 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

Это выглядит довольно логично и соответствует тому, как мы об этом думаем:

  • Существует ли строка для данного ключа (key)?

    • ДА: Обновляем эту строку.

    • НЕТ: Тогда добавляем ее.

Но это расточительно

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

Обратите внимание, что в каждой ветке будут выполняться две операции с индексами.
Обратите внимание, что в каждой ветке будут выполняться две операции с индексами.

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

  • Если ключ существует и две сессии будут выполнять UPDATE одновременно, то они обе выполнятся успешно (одна "выиграет", а "проигравшая" получит "потерянное обновление"). Само по себе это не проблема, системы с параллелизмом так и работают. Здесь Пол Уайт (Paul White) рассказывает более подробно о внутренней механике, а здесь Мартин Смит (Martin Smith) о некоторых других нюансах.

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

    • взаимная блокировка (deadlock) из-за несовместимых блокировок;

    • нарушение ключа (key violation), которого не должно быть;

    • вставка повторяющихся значений ключа, если для столбца нет корректных ограничений.

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

«Многие люди говорят...»

Ден Гузман (Dan Guzman) говорил о состоянии гонки более десяти лет назад в Conditional INSERT/UPDATE Race Condition, а затем в "UPSERT" Race Condition With MERGE.

Майкл Сварт (Michael Swart) также затронул эту тему несколько лет назад в Mythbusting: Concurrent Update/Insert Solutions, включая тот факт, что сохраняя исходную логику и только повышая уровень изоляции, нарушения ограничения ключа меняются на взаимные блокировки. Позже он написал про MERGE в статье Be Careful with the Merge Statement. Обязательно прочитайте все комментарии к обоим постам.

Решение

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

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, но Джастин Пилинг (Justin Pealing) спросил, как быть с несколькими строками, когда неизвестно, какие из них уже существуют?

Если вы передаете список строк через что-то вроде табличного параметра (TVP, Table-Valued Parameters), то сделайте 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 — потенциально можно выполнить UPDATE для некоторых строк дважды.

Выводы

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

Для использования MERGE у вас должна быть либо веская причина (возможно, вам нужна какая-то маловразумительная MERGE-функциональность), либо вы не восприняли вышеприведенные ссылки всерьез.


Узнать подробнее о курсе "MS SQL Server Developer".

Смотреть открытый вебинар на тему «Графовые базы данных в SQL Server».

Теги:
Хабы:
+14
Комментарии 16
Комментарии Комментарии 16

Публикации

Информация

Сайт
otus.ru
Дата регистрации
Дата основания
Численность
101–200 человек
Местоположение
Россия
Представитель
OTUS