Явные блокировки vs триггер

В комментариях к предыдущему посту "Система резервации на 600 заказов в секунду без буферизации и другой дичи" только ленивый не упомянул явные блокировки в Postgres, как способ борьбы с дедлоками.

UPDATE table_name
SET ...
WHERE id in (
  SELECT id 
  FROM table_name
  WHERE ...
  ORDER BY id 
  FOR UPDATE 
)

Это не удивительно, так как select for update - очень популярный прием во всех СУБД, даже на хабре есть статья на эту тему.

Сравнение

Код сохранения данных в базе с явной блокировкой

await db.CreateExecutionStrategy().ExecuteInTransactionAsync(async ct =>
{
    ctx.Orders.Add(order);
    await ctx.SaveChangesAsync(ct);

    await db.ExecuteSqlAsync($"""
    UPDATE stock s
    SET reserved = s.reserved + l.quantity
    FROM (
        SELECT s.item_id,s.warehouse_id,l.quantity
        FROM stock s 
        JOIN order_lines as l 
            ON (s.item_id,s.warehouse_id) = (l.item_id,l.warehouse_id)
        WHERE l.order_id = {order.Id}
        ORDER BY 1,2
        FOR NO KEY UPDATE OF s
    ) l
    WHERE (s.item_id,s.warehouse_id) = (l.item_id,l.warehouse_id)
    """, ct);

}, ct => Task.FromResult(false), ct);

Проверку остатков в этом случае я перенес в check constraints в модели.

Несколько важных деталей:

• В запросе используется предложение FOR NO KEY UPDATE вместо обычного FOR UPDATE. Обычный FOR UPDATE блокирует обновление таблиц, которые своими внешними ключами ссылаются на таблицу указанную в FOR UPDATE. Эта проблема может внезапно выстрелить в более сложных моделях под нагрузкой, что описано в статье на хабре 8-летней давности. Если вы вы не меняете ключевые поля, то достаточно указать FOR NO KEY UPDATE. Несмотря на то, что такая возможность появилась (описана в документации) еще в PostgreSql 9.4 (2014 год), до сих пор в популярных статьях в интернете используется просто FOR UPDATE.

• Явно указана блокируемая таблица. По умолчанию FOR UPDATE\FOR NO KEY UPDATE накладывает блокировки на все таблицы, указанные в SELECT. В этом примере разницы нет, так как строки order_lines были добавлены в этой же транзакции и другие их просто не увидят, но в других случаях блокировка всех таблиц в select неоправданна.

• Для многих может быть непонятно выражение ORDER BY 1,2, оно говорит что отсортировать результат нужно по первой и второй колонке в выражении SELECT. Это называется sort by ordinal и поддерживается всеми СУБД.

Посмотреть можно здесь: https://github.com/gandjustas/habr-post-stock-api/tree/explicit-locking

Для сравнения код с триггерами

ctx.Orders.Add(order);
await ctx.SaveChangesAsync(ct);

и в модели (отличающиеся строки)

  modelBuilder
    .Entity<OrderLine>()
    .AfterInsert(t =>
        t.Action(a =>
            a.Update<Stock>(
                (l, s) => s.ItemId == l.New.ItemId 
                      && s.WarehouseId == l.New.WarehouseId,
                (l, s) => new Stock { Reserved = s.Reserved + l.New.Quantity }
            )
        )
    );

Посмотреть тут: https://github.com/gandjustas/habr-post-stock-api/tree/trigger-and-constraint

Забеги

Каждый вариант прогнал по три раза

Явные блокировки:

HTTP
http_req_duration..............: avg=70.78ms min=2.02ms med=19.59ms max=3.48s p(90)=159.04ms p(95)=305.81ms
  { expected_response:true }...: avg=70.78ms min=2.02ms med=19.59ms max=3.48s p(90)=159.04ms p(95)=305.81ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  703.82643/s

HTTP
http_req_duration..............: avg=69.11ms min=2.61ms med=19.12ms max=3.34s p(90)=152.02ms p(95)=276.18ms
  { expected_response:true }...: avg=69.11ms min=2.61ms med=19.12ms max=3.34s p(90)=152.02ms p(95)=276.18ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  720.489503/s

HTTP
http_req_duration..............: avg=70.33ms min=4.35ms med=19.1ms  max=3.81s p(90)=153.38ms p(95)=296.06ms
  { expected_response:true }...: avg=70.33ms min=4.35ms med=19.1ms  max=3.81s p(90)=153.38ms p(95)=296.06ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  707.956359/s

Триггер в базе:

HTTP
http_req_duration..............: avg=70.37ms min=1.55ms med=17.04ms max=6.27s p(90)=124.51ms p(95)=257.99ms
  { expected_response:true }...: avg=70.37ms min=1.55ms med=17.04ms max=6.27s p(90)=124.51ms p(95)=257.99ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  707.596593/s

HTTP
http_req_duration..............: avg=71.25ms min=615.29µs med=17ms    max=4.59s p(90)=136.17ms p(95)=280.61ms
  { expected_response:true }...: avg=71.25ms min=615.29µs med=17ms    max=4.59s p(90)=136.17ms p(95)=280.61ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  698.989725/s

HTTP
http_req_duration..............: avg=70.13ms min=1.59ms med=16.71ms max=8.17s p(90)=134.78ms p(95)=280.28ms
  { expected_response:true }...: avg=70.13ms min=1.59ms med=16.71ms max=8.17s p(90)=134.78ms p(95)=280.28ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  710.199964/s

Так как нагрузочный тест генерирует случайное количество строк заказов и случайные id, то побъем записи и степень конкурентности непостоянная от теста к тесту, поэтому результаты могут сильно отличаться.

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

Конечно же при подготовке исходной статьи я проверял код с явными блокировками, тогда он выдал результат хуже чем триггеры. Кроме того явные блокировки в подзапросах невозможно на сегодня выразить на C# с использованием EF Core, а я хотел сделать пример, который как можно меньше обращается к голому SQL и Postgres-специфичным возможностям.

Код с триггерами можно перенести на SQL Server, MySQL или SQLite. Достаточно поменять пару пакетов, три строки кода, строку подключения в конфиге и пересоздать миграции. (Посмотреть можно тут: https://github.com/gandjustas/habr-post-stock-api/commit/eb592612dceeab63a5fcfe4ce9035c8e5bdb609f)

Рукопашные транзакции

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

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

Примитивный вариант

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

  ctx.Orders.Add(order);
  await ctx.SaveChangesAsync(ct);
  foreach (var l in order.Lines)
  {
      await ctx.Stock
              .Where(s => s.ItemId == l.ItemId && s.WarehouseId == l.WarehouseId)
              .ExecuteUpdateAsync(setter => 
                  setter.SetProperty(
                    s => s.Reserved, 
                    s => s.Reserved + l.Quantity), 
                  ct);
  }

Средний результат по 5 забегам:

HTTP
http_req_duration..............: avg=46.14ms min=8.55ms med=44.76ms max=360.05ms p(90)=71.87ms p(95)=78.76ms
  { expected_response:true }...: avg=46.14ms min=8.55ms med=44.76ms max=360.05ms p(90)=71.87ms p(95)=78.76ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  1077.372667/s

Работает быстро, но так делать нельзя вообще никогда. Если до окончания цикла возникнет ошибка или пользователь просто отменит запрос, то база останется в несогласованном состоянии и вы не сможете восстановиться.

Синхронный откат

Чтобы не допускать несогласованного состояния в базе данных надо как-то помечать строки заказа, которые уже были обработаны и остатки обновлены, а при исключении проходить по всем обработанным строкам и уменьшать количество резервов. Для этого добавим флаг IsReserved в модель и try\catch в код.

try
{
    ctx.Orders.Add(order);
    await ctx.SaveChangesAsync(ct);
    foreach (var l in order.Lines)
    {
        await using (var t = await ctx.Database.BeginTransactionAsync(ct))
        {
            await ctx.Stock
                    .Where(s => s.ItemId == l.ItemId && s.WarehouseId == l.WarehouseId)
                    .ExecuteUpdateAsync(setter =>
                        setter.SetProperty(
                            s => s.Reserved,
                            s => s.Reserved + l.Quantity),
                        ct);
            l.IsReserved = true;
            await ctx.SaveChangesAsync(ct);
            await t.CommitAsync(ct);
        }
    }
}
catch
{
    // Rollback stock updates
    foreach (var l in order.Lines.Where(l => l.IsReserved))
    {
        await ctx.Stock
                .Where(s => s.ItemId == l.ItemId && s.WarehouseId == l.WarehouseId)
                .ExecuteUpdateAsync(setter =>
                    setter.SetProperty(
                        s => s.Reserved,
                        s => s.Reserved - l.Quantity),
                    CancellationToken.None);
    }
    ctx.Orders.Remove(order);
    await ctx.SaveChangesAsync(CancellationToken.None);

    throw;
}

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

Такой код работает медленно. По 5 забегам лучший результат получился:

HTTP
http_req_duration..............: avg=89.04ms min=12.1ms  med=87.54ms max=298.22ms p(90)=144.41ms p(95)=158.24ms
  { expected_response:true }...: avg=89.04ms min=12.1ms  med=87.54ms max=298.22ms p(90)=144.41ms p(95)=158.24ms
http_req_failed................: 0.00%  0 out of 30000
http_reqs......................: 30000  559.851413/s

Почему так получается:

• Каждое обновление, даже одного байта, создает копию строки таблицы. То есть обновление фага IsReserved фактически удваивает объем записываемых данных на каждый запрос. Это увеличивает объем записи WAL, объем записи страниц на диск, чаще начинает работать вакуум, который блокирует таблицы.

• Много обращений в БД. Примеры в начале этой статьи фактически делают один запрос в базу данных, в данном примере количество обращений равно N*4 +1, где N - количество строк в заказе.

Код смотреть тут: https://github.com/gandjustas/habr-post-stock-api/tree/manual-transactions

Можно ли сделать быстрее?

Не получится. Путем укрупнения транзакций можно немного снизить количество обращений в БД, но это создаст конкуренцию блокировок таблицы остатков. Удвоение объема записи при обновлении флага IsReserved не избежать вообще никак.

Кроме того нужен механизм отмены незавершенных заказов, которые могут возникнуть из-за того, что связь между приложением и базой прервется - или обновить остатки не получится, или откат не выполнится до конца. Необходимо создать фоновый процесс, который находит незавершенные заказы и отменяет их. Для этого придется создать признак завершенности заказа и обновлять его в конце обработки всех строк, а также индекс, чтобы эффективно искать в базе необработанные заказы. Это все дополнительно увеличит объем записи на каждый заказ и объем кода, который нужен чтобы это поддерживать.

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

Заключение

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

Мне кажется именно в этом причина "изобретательства": люди не верят что для скорости работы и устойчивости под высокой нагрузкой достаточно пары строк кода и стандартных возможностей.

Если вы думаете, что сможете написать какой-нибудь буфер, фоновую обработку или другой механизм, что ускорит работу с БД, сохранив тот же уровень надежности и эффективности, то вам стоит просто оптимизировать ваши запросы. Даже если вы тимлид или больше 20 лет этим занимаетесь.