Всем привет! Меня зовут Евгений, я занимаюсь разработкой и дизайном на Ozon. Больше всего работаю с MS SQL и C#, но встречаю и другие СУБД и языки программирования.
Ozon как продукт быстро растёт: во втором квартале этого года мы доставляли более миллиона посылок в день.
Для обработки такого объема заказов мы используем разные языки и платформы: .
NET (C#), Go, MS SQL Server и PostgreSQL. Заказы пользователей обрабатываются разными системами, которые взаимодействуют друг с другом.
Это создает необходимость рассмотрения множественных интеграций и приводит к проблеме дублирования данных.
Взаимодействие информационных систем Расскажу об одном таком случае, когда наша команда потратила много времени и сил, но все же нашла лучший способ решить проблему дублирования данных.
Но сначала позвольте мне немного погрузить вас в предметную область — я объясню на примере, в чем будет продемонстрирована проблема дублирования данных, и выделю некоторые методы ее решения.
План таков:
- Расскажу немного о специфике предметной области;
- Рассмотрим популярные варианты борьбы с дублированием данных;
- Я опишу, в чем заключается наш метод;
- Я покажу вам реализацию;
- Я приведу вам пример того, как все это работает.
Однако подобное решение можно реализовать и на любой другой СУБД с учетом ее особенностей.
Тематика: логистика заказов
На Ozon все заказы разделены по отгрузкам:
Список заказа Отгрузка может содержать один или несколько видов товаров.
В базе данных есть отдельные таблицы для заказов и для отгрузок.
Существует два типа заказов:
- Разовый заказ, состоящий из одной позиции (Пакет 1 – одна позиция):
Разовый заказ от одной партии (пакета) - Сборный заказ, состоящий из двух и более позиций (Пакет 1 – один товар, Пакет 2 – второй товар):
Многоместный заказ, состоящий из нескольких отправлений (пакетов)
Эти действия записываются в отдельную таблицу транзакций, а сумма попадает в соответствующее поле этой таблицы.
В контексте статьи нас интересуют два основных действия над товарами с точки зрения тарификации: выдача и возврат. Они могут быть полными или частичными.
Полная – это когда заказ полностью оформлен или полностью возвращен, и частичная – когда заказ частично оформлен или частично возвращен после полной выдачи.
Все отправки фиксируются в системе ЛогОзон, в которой заказы существуют только на уровне базы данных.
Таким образом, LogOzon занимается только отправками и ничего не знает о заказах, с которыми работают другие системы Ozon. Получается, что в ЛогОзоне невозможно внести изменения на уровне всего заказа; Для этого необходимо внести корректировки в составляющие заказа, а именно в отгрузки.
Исторически это происходило при создании и масштабировании новых сервисов.
Асинхронная тарификация отправлений Проблема дублирования данных возникает при заказе нескольких позиций.
Сумма тарифа для бизнес-требований должна быть указана полностью только по одной позиции одного вида тарифа внутри заказа (выдача или возврат).
И вот мы подходим к сути проблемы.
Как заставить асинхронные процессы из других систем Ozon обновлять сумму только в пределах одного типа тарифа, то есть за одну отправку? Если не синхронизировать изменения, то можно получить дублирование сумм по разным отправлениям заказа по одному и тому же типу тарифа, то есть когда одинаковые суммы вводятся по одной или нескольким отправлениям в рамках одного заказа.
Повторяющиеся тарифы на доставку
Выбор метода борьбы с дублированием данных
На практике я видел два основных способа предотвращения дублирования данных:- Установите ограничение уникальности для желаемой комбинации полей в таблице и обрабатывайте исключения, когда это ограничение нарушается (хорошо работает для вставки и обновления данных).
- Поместите в транзакцию участок кода, где может произойти дублирование данных, с уровнем изоляции «SERIALIZABLE» (хорошо работает для дополнений и обновлений в монопольном режиме).
.
Что, если мы сделаем гибридную версию? И тут мы подходим непосредственно к алгоритму борьбы с дублированием данных в нашей задаче.
Примечания
- На самом деле, можно делать перерасчет постфактум (то есть жить с повторяющимися суммами), но в нашем случае это плохое решение, так как разные сервисы будут читать неверные данные до момента перерасчета, что приведет к неверным счетам;
- Мы используем уровень изоляции транзакций моментальных снимков (SNAPSHOT).
Алгоритм предотвращения дублирования данных
Алгоритм верхнего уровня для заказов из нескольких позиций выглядит следующим образом:- Создаем таблицу, которая будет содержать всю необходимую информацию, чтобы не допустить дублирования сумм при обновлениях.
- Делаем хранимую процедуру, которая будет добавлять запись с проверкой в новую таблицу в монопольном режиме и возвращать статус, отражающий успешность добавления записи или нет (если нет, значит запись уже добавлена).
- Создаем хранимую процедуру для записи суммы тарифа.
Каждый раз, когда мы вводим сумму, мы сравниваем ее с суммой из новой таблицы и сначала обновляем сумму в новой таблице, если там она равна 0:
- если обновление прошло успешно, обновляем сумму в операции;
- если в новой таблице уже указана сумма, отличная от 0, и обрабатываемый товар отличается от товара, на котором указана сумма (то есть кто-то уже обновил сумму и дубликаты нам не нужны), мы сбрасываем ее на нуль;
- в противном случае мы ничего не делаем.
Схема алгоритма Примечание: если вам необходимо изменить уже проштампованную сумму, то это необходимо сделать на тарифицированном отправлении, информация о котором находится в новой таблице.
Создайте таблицу с информацией, чтобы предотвратить дублирование сумм при обновлениях.
Давайте создадим таблицу dbo.LogisticOrderMultiPostingPrincipalTariff следующим образом: Определение таблицы dbo.LogisticOrderMultiPostingPrincipalTariff
Таблица dbo.LogisticOrderMultiPostingPrincipalTariff содержит следующие поля:SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS, NOCOUNT, XACT_ABORT ON; GO SET DEADLOCK_PRIORITY HIGH; GO IF (OBJECT_ID(N'dbo.LogisticOrderMultiPostingPrincipalTariff', N'U') IS NULL) BEGIN CREATE TABLE [dbo].[LogisticOrderMultiPostingPrincipalTariff] ( [LogisticOrderID] int NOT NULL , [TariffTypeID] int NOT NULL , [ArticleID] int NOT NULL , [OperationID] int NOT NULL , [Amount] money NOT NULL , [InsertUTCDate] datetime NOT NULL , CONSTRAINT [PK_LogisticOrderMultiPostingPrincipalTariff] PRIMARY KEY CLUSTERED ( [LogisticOrderID] ASC , [TariffTypeID] ASC ) ON [PRIMARY] ) ON [PRIMARY]; ALTER TABLE [dbo].
[LogisticOrderMultiPostingPrincipalTariff] ADD CONSTRAINT [DF_LogisticOrderMultiPostingPrincipalTariff_InsertUTCDate] DEFAULT (GETUTCDATE()) FOR [InsertUTCDate]; CREATE NONCLUSTERED INDEX [IX_ArticleID] ON [dbo].
[LogisticOrderMultiPostingPrincipalTariff] ( [ArticleID] ASC ) ON [PRIMARY]; CREATE UNIQUE NONCLUSTERED INDEX [IX_Operation] ON [dbo].
[LogisticOrderMultiPostingPrincipalTariff] ( [OperationID] ASC ) ON [PRIMARY]; CREATE NONCLUSTERED INDEX [IX_InsertUTCDate] ON [dbo].
[LogisticOrderMultiPostingPrincipalTariff] ( [InsertUTCDate] ASC ) ON [PRIMARY]; END; GO
- LogisticOrderID – идентификатор заказа;
- TariffTypeID — идентификатор типа тарифа;
- ArticleID — идентификатор товара;
- OperationID — идентификатор операции;
- Сумма — сумма платежа;
- InsertUTCDate — дата и время создания записи в формате UTC (служебное поле).
Создайте хранимую процедуру для добавления записи исключительно в таблицу.
Создадим хранимую процедуру dbo.AddLogisticOrderMultiPostingPrincipalTariff, которая в монопольном режиме добавит запись в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff с проверкой существования записи с заданной парой (заказ, тип тарифа) и вернет статус, показывающий удалось ли добавить запись или нет (если нет, то запись уже добавлена): Определение хранимой процедуры dbo.AddLogisticOrderMultiPostingPrincipalTariff SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.AddLogisticOrderMultiPostingPrincipalTariff
@LogisticOrderID int
, @TariffTypeID int
, @ArticleID int
, @OperationID int
, @IsResult bit = NULL OUT
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
DECLARE @ID table (
ID int NULL
);
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO dbo.LogisticOrderMultiPostingPrincipalTariff
(
LogisticOrderID
, TariffTypeID
, ArticleID
, OperationID
, Amount
)
OUTPUT
Inserted.LogisticOrderID
INTO @ID
SELECT
@LogisticOrderID
, @TariffTypeID
, @ArticleID
, @OperationID
, 0
WHERE
(NOT EXISTS ( SELECT
1
FROM
dbo.LogisticOrderMultiPostingPrincipalTariff
WHERE
(LogisticOrderID = @LogisticOrderID)
AND (TariffTypeID = @TariffTypeID)));
SET @IsResult = CASE
WHEN (EXISTS ( SELECT
1
FROM
@ID
WHERE
(ID > 0))) THEN 1
ELSE 0
END;
END;
GO
Хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:
- LogisticOrderID – идентификатор заказа;
- TariffTypeID — идентификатор типа тарифа;
- ArticleID — идентификатор товара;
- OperationID — идентификатор операции;
- IsResult — выходной параметр, который возвращает значение 1 (успех вставки) или 0 (неудача — когда уже существует запись с таким же заказом и типом оплаты).
Создайте хранимую процедуру для записи суммы тарифа.
Давайте создадим еще одну хранимую процедуру.
При каждом вводе суммы тарифа мы сравниваем ее с суммой из новой таблицы dbo.LogisticOrderMultiPostingPrincipalTariff и сначала обновляем в ней сумму Amount, если она равна 0. Если обновление прошло успешно, меняем сумму в операции.
В противном случае (если в транзакции другая сумма, то есть ее кто-то уже обновил) — обнуляем.
Давайте создадим эту хранимую процедуру dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff: Определение хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff
@LogisticOrderID int
, @TariffTypeID int
, @ArticleID int
, @OperationID int
, @Amount money
, @IsResult bit = NULL OUT
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SET @IsResult = NULL;
DECLARE @NewAmount table (
Val money
);
DECLARE @ArticleOldID int;
SELECT
@ArticleOldID = LOMPPT.ArticleID
FROM
dbo.LogisticOrderMultiPostingPrincipalTariff LOMPPT
WHERE
(LOMPPT.LogisticOrderID = @LogisticOrderID)
AND (LOMPPT.TariffTypeID = @TariffTypeID);
UPDATE
LOMPPPPT
SET
Amount = @Amount
, OperationID = @OperationID
, ArticleID = @ArticleID
OUTPUT
inserted.Amount
INTO @NewAmount
FROM
dbo.LogisticOrderMultiPostingPrincipalTariff LOMPPT
WHERE
(LOMPPT.LogisticOrderID = @LogisticOrderID)
AND (LOMPPT.TariffTypeID = @TariffTypeID)
AND ( (LOMPPT.Amount = 0)
OR ( (LOMPPT.Amount <> @Amount)
AND (LOMPPT.ArticleID = @ArticleID)));
IF (COALESCE(( SELECT
SUM(Val)
FROM
@NewAmount)
, 0.0) = 0)
BEGIN
IF (@ArticleOldID <> @ArticleID)
BEGIN
SET @IsResult = 0;
END;
ELSE
SET @IsResult = NULL;
END;
ELSE
SET @IsResult = 1;
END;
GO
Хранимая процедура dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:
- LogisticOrderID – идентификатор заказа;
- TariffTypeID — идентификатор типа тарифа;
- ArticleID — идентификатор товара;
- OperationID — идентификатор операции;
- Сумма — сумма платежа;
- IsResult — выходной параметр, который возвращает значение:
- 1 — обновление выполнено успешно;
- 0 - неудача, т.е.
когда уже есть запись с нужной суммой
- NULL – когда нет необходимости что-либо менять, например, при попытке записать уже указанную сумму на том же пункте, на котором она указана.
Пример использования созданных хранимых процедур
Давайте посмотрим, как использовать созданные нами хранимые процедуры.У нас все отправки заказов создаются и переходят в статус «Сформировано» еще до того, как хотя бы одна из них куда-либо отправится.
Напомним, тарификация происходит при выдаче или возврате товара, когда все заказы выполнены.
При зарядке мы имеем на входе следующие параметры:
- LogisticOrderID – идентификатор заказа;
- TariffTypeID — идентификатор типа тарифа;
- ArticleID — идентификатор товара;
- OperationID – идентификатор операции.
Если да, то хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff вызывается исключительно для добавления записи о том, что мы начали взимать плату за заказ: EXEC dbo.AddLogisticOrderMultiPostingPrincipalTariff
@LogisticOrderID = @LogisticOrderID
, @TariffTypeID = @TariffTypeID
, @ArticleID = @ArticleID
, @OperationID = @OperationID
, @IsResult = @IsResult OUT;
По значению переменной @IsResult мы узнаем, удалось ли нам добавить запись.
В нашем случае это просто для информации и не используется для какой-либо логики в коде.
Далее мы рассчитываем сумму тарифа по определенному алгоритму и помещаем ее в переменную @Amount. На следующем этапе мы записываем ненулевую сумму тарифа @Amount в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff через вызов хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff: EXEC dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff
@LogisticOrderID = @LogisticOrderID
, @TariffTypeID = @TariffTypeID
, @ArticleID = @ArticleID
, @OperationID = @OperationID
, @Amount = @Amount
, @IsResult = @IsResult OUT;
Теперь посмотрите на возвращаемое значение в переменной @IsResult:
- если @IsResult = 1, то сумму тарифа @Amount не меняем;
- если @IsResult = 0, то мы сбрасываем сумму тарифа @Amount и затем записываем полученную сумму тарифа @Amount в нужную операцию отправки ордера;
- если @IsResult IS NULL, мы ничего не делаем.
Синхронизация размещения суммы тарифа в отправлении внутри заказа В случае единичного заказа мы просто записываем сумму тарифа @Amount в соответствующую операцию отправки заказа.
Ключом к сокращению дубликатов является уровень изоляции транзакций.
Посмотрите, что у нас получилось.
Раньше стоимость доставки и возврата рассчитывалась исходя из состава операций в пределах одной отправки.
Теперь мы научились анализировать все отгрузки заказа, чтобы определить конечную операцию с ним.
Это позволяет автоматически рассчитывать правильные суммы для заказов, состоящих из нескольких позиций.
К этому решению мы пришли не сразу.
Изначально мы искали способ избежать дублирования строк.
И нужно было правильно применить уровень изоляции транзакций — чтобы блокировка по вставке и обновлению данных была минимальной, то есть с выделением нового объекта для блокировки и синхронизации, которым и стала новая таблица, описанная выше.
Этот метод является гибридным: мы используем ограничение уникальности на основе набора (заказ, тип ценообразования) и эксклюзивный блок для вставки и обновления данных.
Результат правильной тарификации Вышеописанное решение можно использовать на разных СУБД в задачах, где асинхронные вставки и обновления данных происходят разными процессами и из-за этого возможно дублирование данных.
Такие задачи распространены во многих сферах:
- логистика,
- транспортная безопасность,
- управление запасами,
- финансы,
- Бухгалтерский учет,
- строительство,
- аудит.
Но есть у этого подхода и недостаток: на доступ к таблице стоит очередь, пусть и кратковременная.
С другой стороны, если есть возможность, можно разрешить создание дублирующихся данных с последующей автоматизацией разрешения конфликтов дубликатов, либо обработать исключение попытки вставки дублирующих записей на стороне сервиса.
Ссылки по теме
- Транзакции MS SQL-сервера : как вызывать транзакции, откатывать и фиксировать их, какие уровни изоляции транзакций существуют и различия между ними.
- ВЫХОДНОЕ предложение : как получить то, что было вставлено, обновлено (заодно смотрим, что было до обновления) или удалено при различных командах модификации данных.
- MS SQL-сервер : общая документация по СУБД, которую я выбрал для реализации описанного метода предотвращения дублирования данных.
Войти , Пожалуйста.
Какие методы вы используете для синхронизации процессов асинхронной записи при вставке или обновлении данных в разных строках таблицы? 36% Используем SERIALIZABLE — транзакции 9 68% Используем обработку ошибок на основе ограничения уникальности 17 12% Другой вариант (поделюсь в комментариях) 3 Проголосовали 25 пользователей.
15 пользователей воздержались.
Теги: #программирование #базы данных #база данных #Администрирование баз данных #dba #sql #сервер ms sql #логистика #Microsoft SQL Server #транзакции #mssql #mssql #транзакция #tsql
-
Dns-Over-Https – Как Проходит Адаптация?
19 Oct, 24 -
Хабрасходка В Новосибирске.
19 Oct, 24 -
Представляем Гитолит
19 Oct, 24 -
Не Страшен Минусинск
19 Oct, 24
