Меню

Sql как избавиться от дублирования

Как избавиться от дублей в базе данных (на примере MS SQL)

Всем привет! Меня зовут Евгений, я занимаюсь разработкой и проектированием в Ozon. Больше всего работаю с MS SQL и C#, но попадаются и другие СУБД и языки программирования.

Ozon как продукт быстро растёт: во втором квартале этого года мы доставляли больше миллиона посылок в день. Для обработки такого объёма заказов мы используем разные языки и платформы: .NET (C#), Go, MS SQL Server и PostgreSQL.

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

Взаимодействие информационных систем

Я расскажу об одном таком случае, когда наша команда потратила много времени и сил, но всё-таки нашла оптимальный способ решения проблемы дублирования данных.

Но сначала позвольте погрузить вас немного в предметную область — объясню, на примере чего будет демонстрироваться проблема дублирования данных, и освещу некоторые методы её решения.

расскажу немного о специфике предметной области;

рассмотрим популярные варианты борьбы с дублированием данных;

опишу, в чём заключается наш способ;

приведу пример, как это всё работает.

Примеры будут приведены для MS SQL Server. Однако аналогичное решение можно реализовать на любой другой СУБД с учётом её особенностей.

Предметная область: логистика заказов

У нас в Ozon все заказы делятся по отправлениям:

Состав заказа

В отправлении может быть один или несколько типов товаров.

В базе данных существуют отдельные таблицы для заказов и для отправлений.

Заказы бывают двух типов:

Одноместный заказ, состоящий из одного отправления (Упаковка 1 — одно отправление):

Одноместный заказ из одного отправления (упаковки)

Многоместный заказ, состоящий из двух и более отправлений (Упаковка 1 — одно отправление, Упаковка 2 — второе отправление):

Многоместный заказ из нескольких отправлений (упаковок)

При совершении над отправлением определённых действий происходит тарификация — расчёт суммы за предоставленные услуги. Эти действия записываются в отдельную таблицу операций, а сумма попадает в соответствующее поле этой таблицы.

В контексте статьи нас интересуют два основных действия над отправлениями с точки зрения тарификации: выдача и возврат. Они бывают полными и частичными. Полные — это когда полностью выдали или полностью вернули заказ, а частичные — когда заказ частично выдан или частично возвращён после полной выдачи.

Все отправления записываются в систему LogOzon, в которой заказы существуют только на уровне базы данных. Таким образом, LogOzon имеет дело только с отправлениями и ничего не знает про заказы, с которыми работают другие системы Ozon. Получается, что изменения на уровне всего заказа нельзя сделать в LogOzon; для этого нужно внести корректировки в составные части заказа, а именно в отправления. Так сложилось исторически при создании и масштабировании новых сервисов.

Асинхронная тарификация отправлений

Проблема дублирования данных возникает в отношении многоместных заказов.

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

Читайте также:  Можно ли купать крысу домашнюю обычным шампунем

И тут мы подходим к сути проблемы. Как заставить асинхронные процессы из других систем Ozon обновлять сумму только в рамках одного типа тарификации, то есть на одном отправлении? Если не делать синхронизацию изменений, то можно получить дублирование сумм для разных отправлений заказа по одному и тому же типу тарификации, то есть когда на одном или нескольких отправлениях в рамках одного заказа будут проставлены одни и те же суммы.

Дублирование тарификаций отправлений

Выбираем метод борьбы с дублированием данных

На практике я встречал два основных способа не допустить дублирования данных:

Навесить ограничение на уникальность для нужной комбинации полей в таблице и обрабатывать исключения при нарушении этого ограничения (хорошо работает при вставках и обновлениях данных).

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

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

А что, если сделать гибридный вариант?

И тут мы подходим непосредственно к алгоритму борьбы с дублированием данных в нашей задаче.

На самом деле можно делать пересчёт и постфактум (то есть жить с дублирующими суммами), но в нашем случае это плохое решение, так как разные сервисы будут считывать невалидные данные до момента пересчёта, что приведёт к некорректным счетам;

мы используем уровень изоляции транзакций снимками (SNAPSHOT).

Алгоритм предотвращения дублирования данных

Верхнеуровнево алгоритм для многоместных заказов выглядит следующим образом:

Создаём таблицу, в которой будет содержаться вся необходимая информация для предотвращения дублирования сумм при обновлениях.

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

Создаём хранимую процедуру для записи суммы тарифа. При каждом проставлении суммы сравниваем её с суммой из новой таблицы и сначала обновляем сумму в новой таблице, если там она была 0:

в случае успешного обновления обновляем сумму в операции;

если в новой таблице уже указана сумма, отличная от 0, и отправление в обработке отличается от отправления, на котором проставлена сумма (то есть кто-то уже обновил сумму и нам не нужны дубли), обнуляем её;

в противном случае ничего не делаем.

Схема алгоритма

Примечание: если нужно изменить уже проставленную сумму, необходимо это сделать на протарифицированном отправлении, информация о котором находится в новой таблице.

Создаём таблицу с информацией для предотвращения дублирования сумм при обновлениях

Создадим таблицу dbo.LogisticOrderMultiPostingPrincipalTariff следующим образом:

Определение таблицы dbo.LogisticOrderMultiPostingPrincipalTariff

Таблица dbo.LogisticOrderMultiPostingPrincipalTariff содержит следующие поля:

LogisticOrderID — идентификатор заказа;

Читайте также:  Бледное лицо как избавится

TariffTypeID — идентификатор типа тарификации;

ArticleID — идентификатор отправления;

OperationID — идентификатор операции;

Amount — сумма тарификации;

InsertUTCDate — дата и время создания записи в UTC (служебное поле).

Создаём хранимую процедуру для монопольного добавления записи в таблицу

Создадим хранимую процедуру dbo.AddLogisticOrderMultiPostingPrincipalTariff, которая будет в монопольном режиме добавлять в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff запись с проверкой на существование записи с заданной парой (заказ, тип тарификации) и возвращать статус, показывающий, удалось добавить запись или нет (если нет, то запись уже была добавлена):

Определение хранимой процедуры dbo.AddLogisticOrderMultiPostingPrincipalTariff

Хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:

LogisticOrderID — идентификатор заказа;

TariffTypeID — идентификатор типа тарификации;

ArticleID — идентификатор отправления;

OperationID — идентификатор операции;

IsResult — выходной параметр, возвращающий значение 1 (успех вставки) или 0 (неуспех — когда уже есть запись с таким же заказом и типом тарификации).

Обратите внимание, что при добавлении записи в таблицу dbo.LogisticOrderMultiPostingPrincipalTariff в хранимой процедуре dbo.AddLogisticOrderMultiPostingPrincipalTariff сумма тарификации Amount принимает значение 0, так как проставление этой суммы произойдёт позже через обновление.

Создаём хранимую процедуру для записи суммы тарифа

Создадим ещё одну хранимую процедуру. При каждой записи суммы тарифа сравниваем её с суммой из новой таблицы dbo.LogisticOrderMultiPostingPrincipalTariff и сначала обновляем в ней сумму Amount, если там она 0. В случае успешного обновления меняем сумму в операции. В противном случае (если в операции стоит иная сумма, то есть кто-то её уже обновил) — обнуляем её.

Создадим эту хранимую процедуру dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff:

Определение хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff

Хранимая процедура dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:

LogisticOrderID — идентификатор заказа;

TariffTypeID — идентификатор типа тарификации;

ArticleID — идентификатор отправления;

OperationID — идентификатор операции;

Amount — сумма тарификации;

IsResult — выходной параметр, возвращающий значение:

1 — успех обновления;

0 — неуспех, т е когда уже есть запись с нужной суммой

NULL — когда не нужно ничего менять, например при попытке записать сумму, которая уже указана, в то же отправление, на котором она указана.

Эта хранимая процедура позволяет изменить сумму тарификации только для того отправления, у которого ранее была проставлена ненулевая сумма.

Пример использования созданных хранимых процедур

Давайте посмотрим, как пользоваться хранимыми процедурами, которые мы создали.

У нас все отправления заказа создаются и переходят в статус «Сформировано» до того, как хотя бы одно из них куда-либо поедет. Напомним, что тарификация происходит при выдаче или возврате товара, когда сформированы все отправления заказа.

При тарификации у нас на входе есть следующие параметры:

LogisticOrderID — идентификатор заказа;

TariffTypeID — идентификатор типа тарификации;

ArticleID — идентификатор отправления;

OperationID — идентификатор операции.

На старте тарификации мы определяем, является ли заказ многоместным, то есть состоит ли он более чем из одного отправления. Если да, то вызывается хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff для монопольного добавления записи о том, что мы стали тарифицировать заказ:

По значению переменной @IsResult мы узнаём, получилось ли добавить запись. В нашем случае это нужно просто для информации и не используется для какой-либо логики в коде.

Читайте также:  Как избавиться от вилохвосток

Далее мы вычисляем сумму тарифа по определённому алгоритму и кладём её в переменную @Amount.

На следующем этапе мы записываем ненулевую сумму тарификации @Amount в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff через вызов хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff:

Теперь смотрим на вернувшееся значение в переменной @IsResult:

если @IsResult = 1, то сумму тарифа @Amount не меняем;

если @IsResult = 0, то обнуляем сумму тарифа @Amount и после этого записываем получившуюся сумму тарификации @Amount в нужную операцию отправления заказа;

если @IsResult IS NULL, то ничего не делаем.

Синхронизация проставления суммы тарификации в отправлении в рамках заказа

В случае же с одноместным заказом мы просто записываем сумму тарификации @Amount в соответствующую операцию отправления заказа.

Ключ к уменьшению количества дублей — уровень изоляции транзакций

Смотрите, что у нас получилось. Раньше стоимость доставки и возврата рассчитывалась по составу операций в рамках одного отправления. Теперь мы научились анализировать все отправления заказа для определения конечной операции с ним. Благодаря этому стало возможным автоматически рассчитывать корректные суммы для многоместных заказов.

К этому решению мы пришли не сразу. Изначально мы искали способ, как избежать появления дублей в строках. А нужно было правильно применить уровень изоляции транзакций — так, чтобы блокировка на вставку и обновление данных была минимальной, то есть с выделением нового объекта для блокировки и синхронизации, которым стала новая таблица, описанная выше.

Этот способ — гибридный: мы используем ограничение уникальности по набору (заказ, тип тарификации) и монопольный блок на вставку и обновление на вставку и обновление данных.

Результат правильной тарификации

Приведённое решение можно использовать на разных СУБД в задачах, где происходят асинхронные вставки и обновления данных разными процессами и из-за этого возможно дублирование данных.

Такие задачи распространены во многих сферах:

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

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

Ссылки по теме

Транзакции MS SQL Server: как вызывать транзакции, откатывать и фиксировать их, какие существуют уровни изоляции транзакций и различия между ними.

Предложение OUTPUT: как получить то, что вставили, обновили (заодно смотрим, что было до обновления) или удалили при различных командах модификации данных.

MS SQL Server: общая документация по СУБД, которую я выбрал для реализации описанного метода предотвращения дублирования данных.

Источник