3

I'm developing warehouse system, that can manage product with different BBDs (best before dates). So table is quite simple (i'm using MySQL + PHP)

ID - PK, Autoincrement
ProductID - FK products
Quantity - positive, if put products to warehouse and negative if take from warehouse
BBD - best before date
OperationDate - DATETIME when operation was created

I some goods arrive warehouse, I simply add rows to this table, like

INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, 500, '2022-02-02', UTC_TIMESTAMP());
INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, 100, '2022-05-15', UTC_TIMESTAMP());
INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, 50, '2022-10-27', UTC_TIMESTAMP());

But the problem is, when I want to withdraw from warehouse certain amount of exact good. For example, I have 2 orders (to take FROM warehouse) First one for 550 pieces, and second one for 70 pieces.

What is the best way and algorythm to calculate, what Quantity and what BBD will go for each order? In my example for first order will go:

500 pieces with bbd: 2022-02-02
50 pieces with bbd: 2022-05-15
in sql:
INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, -500, '2022-02-02', UTC_TIMESTAMP());
INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, -50, '2022-05-15', UTC_TIMESTAMP());

and for second order:

50 pieces with bbd: 2022-05-15
20 pieces with bbd: 2022-10-27
in sql:
INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, -50, '2022-05-15', UTC_TIMESTAMP());
INSERT INTO warehouse (ProductID, Quantity, BBD, OperationDate) VALUES (1, -20, '2022-10-27', UTC_TIMESTAMP());

Main thing - the worst BBD will left warehouse first.

How should I implement locks/transactions/etc. if there will be many incoming and outgoing orders in parallel?

My first though was, if I can somehow block for exact ProductID even for READ operations when I calculate what BBD to reduce. I tried to use LOCK TABLES but it needs to lock every table in query, not so good solution. I tried to SELECT ... FOR UPDATE, but I'm not sure will it prevent for reading same rows from another sessions.

So, what is the right way to get this?

miken32
  • 42,008
  • 16
  • 111
  • 154
Lari13
  • 1,850
  • 10
  • 28
  • 55
  • pos dupes: https://stackoverflow.com/questions/8170611/how-to-implement-pessimistic-locking-in-a-php-mysql-web-application https://stackoverflow.com/questions/2133125/php-mysql-simple-code-to-implement-transaction-commit-rollback https://stackoverflow.com/questions/2133125/php-mysql-simple-code-to-implement-transaction-commit-rollback – treyBake Dec 11 '19 at 12:28
  • Where do 550 and 70 come from? – Strawberry Dec 11 '19 at 13:22
  • @Strawberry 550 and 70 - are just quantities for orders FROM warehouse. Just for example. – Lari13 Dec 11 '19 at 15:34

1 Answers1

0

InnoDB (which I assume you are using) supports row locking and you will be able to do that with SELECT... FOR UPDATE.

You can easily test by running 2 different scripts, one trying to update and the other trying to select, the select query will wait until the update is completed.

I am not sure why you are inserting records here, I presume you meant UPDATE.

ikyuchukov
  • 565
  • 3
  • 12