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 г.