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

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

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

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

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

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

План такой:

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

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

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

4. покажу реализацию;

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

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

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

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

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

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

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

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

   

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

   

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечания

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

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

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

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

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

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

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

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

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

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

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

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

Создадим таблицу 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

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

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

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

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

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

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

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

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

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

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 в хранимой процедуре dbo.AddLogisticOrderMultiPostingPrincipalTariff сумма тарификации Amount принимает значение 0, так как проставление этой суммы произойдёт позже через обновление.

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

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

Создадим эту хранимую процедуру 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 — идентификатор операции;

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

• 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:

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

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

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

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

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

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

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

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

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

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

• логистика,

• транспортная безопасность,

• складской учёт,

• финансы,

• бухгалтерский учёт,

• строительство,

• аудит.

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

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

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

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

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

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