[SQL, Microsoft SQL Server] Пожалуйста, прекратите использовать антипаттерн UPSERT (SQL Server) (перевод)
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Для будущих учащихся на курсе "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».
===========
Источник:
habr.com
===========
===========
Автор оригинала: Aaron Bertrand
===========Похожие новости:
- [Тестирование IT-систем, Java] Автоматизированное тестирование баз данных в Java с помощью JdbcTemplate (перевод)
- [JavaScript, Программирование] 7 вопросов про замыкания в JavaScript (перевод)
- [Oracle, PostgreSQL, Конференции, DevOps] 18 марта: DataBase Meetup Online
- [Разработка веб-сайтов, NoSQL, Node.JS] ArangoDB в реальном проекте
- [SQL, Графический дизайн, IT-компании] Открытки в стиле SQL
- [ReactJS] 6 лучших практик React в 2021 году (перевод)
- [Программирование, Java] Итак, вы хотите оптимизировать gRPC. Часть 2 (перевод)
- [Agile, Контент-маркетинг] 10 советов для написания хороших пользовательских историй (перевод)
- [Python, PostgreSQL] Обрезаем большую таблицу PostgreSQL в production
- [Программирование, Scala] Основы Cat Concurrency с Ref и Deferred (перевод)
Теги для поиска: #_sql, #_microsoft_sql_server, #_sql_server, #_sql, #_upsert, #_tsql, #_sql_server_2016, #_grafovye_bazy_dannyh (графовые базы данных), #_blog_kompanii_otus (
Блог компании OTUS
), #_sql, #_microsoft_sql_server
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 17:57
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
|
Для будущих учащихся на курсе "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
 Обратите внимание, что в каждой ветке будут выполняться две операции с индексами.Более того, если строка не существует, не используются явные транзакции и не учитывается уровень изоляции, то многое может пойти не так (помимо производительности):
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;
 В этом случае есть ветка, в которой выполняется только одна операция поиска по индексу.Получается следующее:
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; 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; 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 Узнать подробнее о курсе "MS SQL Server Developer".Смотреть открытый вебинар на тему «Графовые базы данных в SQL Server».
=========== Источник: habr.com =========== =========== Автор оригинала: Aaron Bertrand ===========Похожие новости:
Блог компании OTUS ), #_sql, #_microsoft_sql_server |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 17:57
Часовой пояс: UTC + 5