SQL поддържа атомарни операции за увеличаване и намаляване на числови колони. „Трикът“ е да използвате заявка за актуализиране, следваща конкретен модел, използвайки относителна стойност от дясната страна.

Наскоро пренаписахме нашата система за управление на инвентара и успяхме да подобрим производителността, като намалихме оперативната сложност, като се възползвахме от собствените SQL операции за увеличаване и намаляване. В тази публикация ще се потопим в подробностите и често срещаните проблеми на тези операции и ще сравним новата ни реализация с предишната, за да подчертаем предимствата.

SQL поддържа атомарни операции за увеличаване и намаляване на числови колони. „Трикът“ е да използвате заявка за актуализиране въз основа на следния модел:

-- This assumes the existence of a table defined as:
-- CREATE TABLE test(id SERIAL PRIMARY KEY, x INTEGER);
UPDATE test set x = x - 1 where id = 1;

Има два важни елемента в тази заявка:

  • Клаузата WHERE трябва да бъде детерминистична (повече за това по-късно).
  • Дясната страна на израза за актуализиране използва относителната стойност вместо предаване на абсолютна, предварително избрана стойност (също повече за това по-късно).

„Документацията на PostgreSQL“ има добър пример.

Риск от безизходица

Важно е да се отбележи, че тъй като заявката UPDATE имплицитно ще използва заключване на ниво ред, блокиране може да възникне, ако се изпълняват множество транзакции с ниво на изолация, зададено като READ COMMITTED, REPEATABLE READ или SERIALIZABLE.

Пример

Нека вмъкнем два реда в таблицата test.

INSERT INTO test VALUES (1, 0);
INSERT INTO test VALUES (2, 0);

Можем да задействаме блокиране с две psql сесии:

$1> psql
psql1> BEGIN;
psql1> UPDATE test SET x = x + 1 WHERE id = 1;
-- A lock is acquired on the row with id 1, no other transactions can update it
$2> psql
psql2> BEGIN;
psql2> UPDATE test SET x = x + 1 WHERE id = 2;
-- A lock is acquired on the row with id 2, no other transactions can update it
psql1> UPDATE test SET x = x + 1 WHERE id = 2;
-- The second session hasn't committed yet, this operation is now waiting
psql2> UPDATE test SET x = x + 1 WHERE id = 1;
-- The first session hasn't committed yet, this operation is now waiting

ЗАДЪЛЖЕНИЕ! Всяка сесия чака другата да се ангажира или върне назад:

ERROR: deadlock detected DETAIL: Process 14803 waits for ShareLock on transaction 43356; blocked by process 14431. Process 14431 waits for ShareLock on transaction 43357; blocked by process 14803. HINT: See server log for query details. CONTEXT: while updating tuple (0,1) in relation "test"

PostgreSQL автоматично открива ситуацията след няколко секунди и автоматично ще върне една от транзакциите, позволявайки на другата да се ангажира успешно.

Забележка: Тази ситуация ще се случи с всички нива на изолация на транзакция

Решение

Един от начините да се предотврати това е да се използва детерминистично подреждане, когато множество редове се актуализират в трансакциите, в този случай, ако и двете транзакции са сортирали редовете по възходящ идентификатор, например, нямаше да има блокирания.

Детерминирано състояние

Както е обяснено в документацията на PostgreSQL, това, което прави заявката за нарастване безопасна с транзакция, използваща нивото на изолация READ COMMITTED, е детерминизмът на условието, използвано в клаузата WHERE на заявката UPDATE.

Нека да разгледаме какво може да се случи с по-малко тривиална заявка:

$1> psql
psql1> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
psql1> UPDATE test SET x = x + 1 WHERE id = 2;
-- A lock is acquired on the row with id 2, no other transaction can update it
$2> psql
psql2> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
psql2> UPDATE test set x = x + 1 WHERE x % 2 = 0;
-- A lock is acquired on all rows with an even x value, since there's a lock on the row with id 2, this query waits for the first transaction to commit or rollback
psql1> UPDATE test set x = x + 1 WHERE x % 2 = 0;
-- The second session hasn't committed yet, this query is now waiting as well

Това създава друга задънена улица.

Изводът е: докато използвате условие за равенство на първичен ключ (или неизменна колона), няма за какво да се тревожите. Ако не го направите... е, трудно е да се каже какво може да се случи.

Забележка: Използването на ограничително ниво на изолация като REPEATABLE READ или SERIALIZABLE всъщност може да направи нещата по-сложни, тъй като кодът на приложението ще трябва да обработва неуспехите на сериализацията с някаква логика за повторен опит. Има примери в секцията с кодове в долната част на статията

Новата стойност, предадена на заявката UPDATE, не знае каква е текущата стойност и това е, което прави тази заявка да работи, тя просто ще увеличи стойността (след придобиване на заключване на реда) до това, което е плюс или минус дадената разлика.

Ако първо трябваше да прочетем стойността и да я използваме за изчисляване на новата стойност, ще трябва да разчитаме на по-сложен заключващ механизъм, за да гарантираме, че стойността няма да се промени, след като я прочетем и преди UPDATE да е готово.

Пример от реалния свят

Обичайно е компаниите за електронна търговия да следят наличностите за всяка SKU, продадена на платформата, проста таблица с инвентара може да се дефинира като:

CREATE TABLE inventories(sku VARCHAR(3) PRIMARY KEY, quantity INTEGER);

Опростена версия на нашата система за инвентаризация работи по следния начин:

  • Вземете всички SKU в количката
  • Сортирайте SKU по лексикографски ред (за предотвратяване на блокирания)
  • Издайте заявка, изглеждаща като UPDATE inventories SET quantity = quantity - x WHERE sku = y RETURNING quantity, където x е заявеното количество, а y е действителната SKU стойност. Ако върнатото количество е твърде малко, се извежда грешка и процесът на закупуване се прекъсва.

Нашето оригинално (прекомерно проектирано) решение

Преди няколко години, когато написахме една от първите версии на нашата система за инвентаризация в Harry’s, не осъзнавахме, че можем да разчитаме на SQL само за издаване на операции за атомно намаляване, в крайна сметка използвахме Redis.

Redis поддържа такива операции извън кутията (INCR / INCRBY & DECR / DECRBY) и тъй като е еднонишков, не излага никакви условия на състезание по подразбиране.

Това определено е валидна реализация (и работи добре дълго време), но добавя значителни оперативни разходи към внедряването, тъй като данните за инвентара „живеят“ в две различни хранилища на данни: Redis и PostgreSQL.

Изпълнението може да се обобщи като:

  • Трябва да намалим инвентара за SKU x
  • Стойността в Redis ли е?
  • Ако не, прочетете го от DB и задайте в Redis
  • Намаляване в Redis
  • Проверете новата стойност, прекъснете, ако е твърде ниска

Примерен код

Написах малък тестов пакет (както за MySQL, така и за PostgreSQL) в Ruby, подчертавайки различните концепции, споменати в тази статия

  • „Сигурността“ на заявка за относителна актуализация, дори при транзакции за четене без ангажиране
  • Проблемът с абсолютните актуализации в транзакциите READ UNCOMMITTED и READ COMMITTED
  • Пример, използващ REPEATABLE READ или SERIALIZABLE транзакции, който изисква приложението изрично да обработва повторни опити за грешки при сериализиране.

Първоначално публикувано в engineering.harrys.com на 28 юни 2017 г.