Как сделать уменьшение склада в СУБД безопасными правильным?

Question

[Александр Рублев]

Здравствуйте. Не знаю как задать вопрос правильно, попробую описать на примере.
ПРИМЕР:
Есть простая таблица склада товара, 2 колонки [ID товара, количество товара].
Предположим на складе работники сканируют каждую единицу товара, и при этом счетчик склада товара уменьшается на 1 штуку.
Получается что при каждом скане, программа запрашивает у таблицы склада строку с ID товара. После чего, вычитает от остатка единицу, и обновляет строку в базе данных.

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

balance = SELECT balance FROM warehouse WHERE product_id= 5
new_balance = balance - 1
UPDATE warehouse SET balance = new_balance  WHERE product_id= 5

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

Пример очень упрощен, для того, что бы я смог объяснить в чем проблема. Сильно не придирайтесь. Я понимаю, что в данном примере можно сделать простой UPDATE без запроса остатка, но по факту все сложнее, и каждое сканирование товара, делает много запросов к базе, и сразу несколько изменений, и все в одном комите.

PS
Всем спасибо за ответы. Самое простое решение найдено, это посчитать на сколько нужно изменить склад, и сделать это простым запросом UPDATE warehouse SET balance = balance - change_delta WHERE product_id= 5. Не знаю, почему это банально простое решение не пришло мне в голову. Таким образом пропадает вся конкуренция, и никаких проблем.
Я понимаю всех, кто говорит, что так делать не правильно, нужно в отдельной таблице записывать транзакции изменения. Но повторю еще раз, я хотел понять как сделать простой счетчик, и привел простой пример. Для ответственных вещей, так делать не правильно.

Comments

[Everything_is_bad]

ну погугли уже, проблеме 100 лет, все возможные решения давно уже описаны, это конечно нонсен писать складской учет, но не знать про кучу видов блокировок, тот же select for update, но повторюсь, всё это уже кучу раз рассказано со всеми нюансами.

а за отдельное вычисление new_balance надо бить ногами, что тебе помешало в самом запросе сделать set balance = balance - 1 (ну или отдельно вычисленное кол-во товара на списание, но не сам баланс) и получил бы атомарную операцию

[Akina]

Схема БД должна быть СЛЕДСТВИЕМ бизнес-процесса.

Если "работники сканируют каждую единицу товара", то сама схема БД, которая хранит не отдельные (сканируемые) единицы, а агрегированное брутто-количество, является принципиальной ошибкой.

[tukreb]

new_balance = balance - 1
Вот что бывает, когда люди начинают писать БД, не прочитав учебник.
Изучите что такое нормализованные формы хотя бы.

[Александр Рублев]

Everything_is_bad, Честно говоря, не знаю как правильно загуглить этот вопрос.
про set balance = balance - 1 я понял. Достаточно логично. Что то в голову не пришло сразу!

[Александр Рублев]

Akina, Я понимаю о чем вы говорите. Но этот вопрос касается не обязательно склада. Это может быть любой счетчик. Скорее этот вопрос задан с целю, понять как правильно делать подобные простые счетчики.

[Александр Рублев]

tukreb, Знаю я что такое нормальная форма. Не первый день с базами имею дело, хоть и на базовом уровне.
Вопрос задан с целью понять, как сделать в базе простой счетчик. Без лишних таблиц.

[tukreb]

Александр Рублев, без лишний таблиц у вас будет не СУБД, а эксель, где данные никогда не будут согласованы. И зачем тогда оно нужно? Простой счётчик используют тогда, когда его в любой момент могут пересчитать, за исключением вариантов, когда он для красоты (например счётчик посещений)

[Vitsliputsli]

Александр Рублев, простой счетчик, как уже писали: balance = balance - 1.
Но, когда реальный остаток и остаток в БД не сойдутся, нужно будет искать ошибку, а без "лишних" таблиц искать негде, у вас только одно число и все. Если есть логи в приложении, то можно по ним, если в них удобно.

[Александр Рублев]

Vitsliputsli, Я вас прекрасно понимаю. Но я теперь понял как делать простой счетчик! Спасибо!

Answer 1

[Василий Банников]

Можно использовать оптимистичную блокировку вида

UPDATE items SET amount = :new_amount WHERE amount = :expected_amount AND id = :item_id

И проверять, что в результате обновилась одна строка и повторять в случае если не обновилась.
Либо делать SELECT * from items where id = :item_id FOR UPDATE в рамках транзакции, чтобы заблокировать конкретную строку.

Либо можно попробовать что-то типа

UPDATE items SET amount = amount - 1 WHERE id = :item_id

чтобы вычисление нового количества произошло также атомарно.

Либо использовать схему с event sourcing, как предложил constantinesx и хранить только события "такой-то пользователь просканировал такой-то товар" и потом уже отдельно считать агрегированное значение (есть разные способы)

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

Чтобы выбрать наилучший вариант - надо смотреть на систему полностью, а не на один маленький кусочек в рамках вопроса.

Comments

[Александр Рублев]

Можно использовать оптимистичную блокировку вида

Решение понял, но получается каждый раз нужно делать лишний селект.

Либо делать SELECT * from items where id = :item_id FOR UPDATE в рамках транзакции, чтобы заблокировать конкретную строку.

Посмотрю про эту блокировку, спасибо.

чтобы вычисление нового количества произошло также атомарно.

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

потом уже отдельно считать агрегированное значение

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

[Василий Банников]

Александр Рублев,

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

нет, не нужно, если вы заранее агрегаты посчитаете.

Как это сделать - тоже есть разные варианты, но нужно будет выбирать компромисс между согласованностью и пропускной способностью.

Answer 2

[Akina]

Скорее этот вопрос задан с целю, понять как правильно делать подобные простые счетчики.

Правильно - это выполнение операции в одном запросе. В вашем случае это будет

UPDATE warehouse 
SET balance = balance - 1 
WHERE product_id = 5
  AND balance > 0;

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

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

Можно (хоть и нежелательно) это делать и "на ручной тяге". Например, если вы хотите всё же использовать трёхзапросную форму, как в вопросе, то (схематично) будет что-то вроде

SET @balance = (SELECT balance FROM warehouse WHERE product_id = 5);
SET @new_balance = @balance - 1;
UPDATE warehouse 
    SET balance = @new_balance  
    WHERE product_id = 5
      AND balance = @balance;

Обратите внимание на дополнительное условие в последнем запросе. Оно проверяет, что с момента запроса старого значения и до попытки записать новое никто не внёс изменений в данные. Если же кто-то подсуетился и успел, то последний запрос обновит ноль записей, что и будет маркером, что операция корректировки баланса неудачна и надо повторить попытку (впрочем, ноль получится и в случае, если кто-то удалит запись для этого продукта - тогда никакие повторения не помогут).

Answer 3

[constantinesx]

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

Comments

[Александр Рублев]

Вариант понятный, пользуюсь этим методом. Но в день может сканироваться более 5к товаров. Таблица быстро разрастется до больших размеров. Ну и для получения склада, нужно каждый раз применять агрегатные функции, не могу понять на сколько это станет тяжело, когда будет большое кол-во записей. Ведь для получения склада, БД каждый раз придётся прочитать все строки.

[tukreb]

Александр Рублев, никогда не занимайтесь оптимизацией будущего. 5к товаров? Это шутка для БД, которая спокойно работает с миллионами строк.
На 5к вы всегда можете на лету всё вычислять без необходимости вообще хранить мета информацию.

[Akina]

Таблица быстро разрастется до больших размеров.

С точки зрения СУБД "большие размеры" - это где-то от десятков миллионов записей как минимум.

Answer 4

[d-stream]

Нормальный вариант - "таблица выдачи" - туда помещаются те самые позиции, которые кладовщик складывает в корзину для выдачи. По фиксации факта выдачи - уже данные из оной таблицы и используются для одиночной транзакции уменьшения остатка. Заодно эта же таблица сможет выступать блокировкой резервирования позиций (в наличии 100, доступно 95 шт).

p.s. ну и да более практичный подход - от движений: остаток по складу - есть сумма приходов - минус сумма расходов.