記事：「MySQLでWebサービスを止めずに、その裏のバッチ処理で大量にデータを洗い替える」
で、イメージアップのためにWeb在庫販売システムを例にしました。今回はこの記事の補足です。
Webオンラインの裏でバッチ処理でデータを洗い替えたら、引き当てた在庫が消えてしまう？！
Web在庫販売システムですので、Webオンラインで商品を受注するサービスがあり、このサービスは受注内容を記録し、「受注した個数分、在庫を引き当てます(減らします)」。
そして、それをまた未引当在庫量としてWebオンラインで開示するサービスがあります。
とすると、Webオンラインサービスを止めずに、その裏のバッチ処理で大量にデータを洗い替えてしまったら、
Webオンラインサービスで更新した引当数量が消えてなくなってしまうので、この設計では成り立っていない？！と思うかもしれないですが、
そこは「未引当在庫－受注＝次の未引当在庫」のように差分を求めれば大丈夫です。
Webオンラインの裏でバッチ処理でデータを洗い替えてもオンラインデータを残すには？
受注テーブルには、受注内容が登録されているので、これと在庫テーブルの差分を求めれば、現在の在庫状況は求められます。
例えば、次の図では、
①初期状態として、在庫テーブルには、倉庫１商品Aは100個の在庫があった。
②受注サービスは、倉庫１商品Aは10個を受注し、受注テーブルに登録。在庫引当の減算更新はしない
③未引当在庫量開示サービスは、「在庫テーブル(倉庫１商品A100個)」－「受注テーブル(倉庫１商品A10個)」＝「現在の在庫(倉庫１商品A90個)」
と見せることができます。
もし、受注後に在庫テーブルが洗い替えられたとしても(②と③の間でバッチの大量洗い替えがされたとしても)、
③’未引当在庫量開示サービスは、「在庫テーブル(倉庫１商品A110個)」－「受注テーブル(倉庫１商品A10個)」＝「現在の在庫(倉庫１商品A100個)」
と見せることができます。
もし、洗い替えた結果、受注を十分満たすだけの在庫情報じゃなかったらどうする？
ここでまた気になるのは、この図では、倉庫１WKテーブルに110個ということで、受注を十分満たすだけの在庫情報で洗い替えしているのでよいのですが、
もし、受注を十分満たすだけの在庫情報じゃなかったら、どうすればよいのか？となります。
ここからは、納期と在庫が無かった時の契約と交渉次第と思います。
納期が十分に先であれば、それに間に合う様にメーカに発注して倉庫に入庫させればよいです。
また、在庫販売であれば、安全在庫を構えられないような、一品モノは契約で相当ガードしていると思いますが、通常であれば安全在庫率を考慮した在庫管理をしているはずなので、
在庫がマイナスになるのは、何らかの事故(トラックに積み込む時に破損した、事故が起きた等)や災害(商品が火災で燃えた、水没した等)が発生したと考えられます。
いずれにせよ、倉庫からやってくる在庫情報は事実として受け入れるしかないので、バッチで洗い替えた後に、在庫状況を確認するバッチ処理を設けて、
マイナスが発生している商品について、発注担当者や受注担当者、在庫管理担当者へ警告メールを出すなどの仕組みを設ければよいと思います。
実装例
次のような受注テーブルがあったとします。
DROP TABLE T_ORDER;
CREATE TABLE T_ORDER (
ORDER_YM INT,
ORDER_ID CHAR(10),
STOCKHOUSE_ID INT,
ITEM_ID CHAR(10),
AMOUNT INT,
VER_ID CHAR(1),
UPD_DTTM DATETIME
);
CREATE INDEX IDX_NO1 ON T_ORDER (ORDER_YM, ORDER_ID);
ALTER TABLE T_ORDER ADD CONSTRAINT PK_NO1 PRIMARY KEY(ORDER_YM, ORDER_ID);
倉庫ID=1、商品ID=1234567-01、数量=100の注文を受け、受注テーブルに登録します。(在庫テーブルは変えない)
INSERT INTO T_ORDER (ORDER_YM, ORDER_ID, STOCKHOUSE_ID, ITEM_ID, AMOUNT, VER_ID, UPD_DTTM)
VALUES (201509, 'ABCDEFG-01', 1, '1234567-01', 100, 1, NOW());
ちなみに、受注テーブルと、在庫テーブルは次のような状態です。
mysql> SELECT
-> ORDER_YM,
-> ORDER_ID,
-> STOCKHOUSE_ID,
-> ITEM_ID,
-> AMOUNT
-> FROM
-> T_ORDER;
+----------+------------+---------------+------------+--------+
| ORDER_YM | ORDER_ID | STOCKHOUSE_ID | ITEM_ID | AMOUNT |
+----------+------------+---------------+------------+--------+
| 201509 | ABCDEFG-01 | 1 | 1234567-01 | 100 |
+----------+------------+---------------+------------+--------+
1 row in set (0.00 sec)
mysql> SELECT
-> S.*
-> FROM
-> T_STOCK S
-> WHERE
-> S.STOCKHOUSE_ID= 1;
+---------------+------------+--------+--------+---------------------+
| STOCKHOUSE_ID | ITEM_ID | AMOUNT | VER_ID | UPD_DTTM |
+---------------+------------+--------+--------+---------------------+
| 1 | 1234567-01 | 313 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-02 | 600 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-03 | 702 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-04 | 395 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-05 | 903 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-06 | 679 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-07 | 2 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-08 | 158 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-09 | 518 | 1 | 2015-09-23 16:33:52 |
| 1 | 1234567-10 | 75 | 1 | 2015-09-23 16:33:52 |
+---------------+------------+--------+--------+---------------------+
10 rows in set (0.15 sec)
現在の未引当在庫数量を問い合わせます。
商品ID=1234567-01の数量が313から213に代わっているのがわかります。
mysql> SELECT
-> S.STOCKHOUSE_ID,
-> S.ITEM_ID,
-> (MIN(S.AMOUNT) - IFNULL(SUM(O.AMOUNT), 0)) AS AMOUNT2
-> FROM
-> T_STOCK S LEFT JOIN T_ORDER O
-> ON(
-> S.STOCKHOUSE_ID = O.STOCKHOUSE_ID
-> AND S.ITEM_ID = O.ITEM_ID)
-> WHERE
-> S.STOCKHOUSE_ID= 1
-> GROUP BY
-> S.STOCKHOUSE_ID,
-> S.ITEM_ID
-> ;
+---------------+------------+---------+
| STOCKHOUSE_ID | ITEM_ID | AMOUNT2 |
+---------------+------------+---------+
| 1 | 1234567-01 | 213 |
| 1 | 1234567-02 | 600 |
| 1 | 1234567-03 | 702 |
| 1 | 1234567-04 | 395 |
| 1 | 1234567-05 | 903 |
| 1 | 1234567-06 | 679 |
| 1 | 1234567-07 | 2 |
| 1 | 1234567-08 | 158 |
| 1 | 1234567-09 | 518 |
| 1 | 1234567-10 | 75 |
+---------------+------------+---------+
10 rows in set (0.00 sec)
この裏で、在庫情報の洗い替えをしています。
商品ID=1234567-01の数量が400になっているのに注目してください。
mysql> SELECT
-> *
-> FROM
-> T_STOCK_S01;
+---------------+------------+--------+--------+---------------------+
| STOCKHOUSE_ID | ITEM_ID | AMOUNT | VER_ID | UPD_DTTM |
+---------------+------------+--------+--------+---------------------+
| 1 | 1234567-01 | 400 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-02 | 600 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-03 | 400 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-04 | 400 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-05 | 600 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-06 | 400 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-07 | 400 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-08 | 600 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-09 | 400 | 1 | 2015-09-23 16:45:12 |
| 1 | 1234567-10 | 400 | 1 | 2015-09-23 16:45:12 |
+---------------+------------+--------+--------+---------------------+
10 rows in set (0.09 sec)
オンスクリーンとオフスクリーンを切り替えます。
ALTER TABLE T_STOCK
EXCHANGE PARTITION P_S01
WITH TABLE T_STOCK_S01;
現在の未引当在庫数量を問い合わせます。
商品ID=1234567-01の数量が300になっているのに注目してください(他の商品IDも洗い替えた数量になっています)。
これは、期待通りに、商品ID=1234567-01の
未引当在庫(400)－受注(100)＝次の未引当在庫(300)
で計算されたためです。
mysql> SELECT
-> S.STOCKHOUSE_ID,
-> S.ITEM_ID,
-> (MIN(S.AMOUNT) - IFNULL(SUM(O.AMOUNT), 0)) AS AMOUNT2
-> FROM
-> T_STOCK S LEFT JOIN T_ORDER O
-> ON(
-> S.STOCKHOUSE_ID = O.STOCKHOUSE_ID
-> AND S.ITEM_ID = O.ITEM_ID)
-> WHERE
-> S.STOCKHOUSE_ID= 1
-> GROUP BY
-> S.STOCKHOUSE_ID,
-> S.ITEM_ID
-> ;
+---------------+------------+---------+
| STOCKHOUSE_ID | ITEM_ID | AMOUNT2 |
+---------------+------------+---------+
| 1 | 1234567-01 | 300 |
| 1 | 1234567-02 | 600 |
| 1 | 1234567-03 | 400 |
| 1 | 1234567-04 | 400 |
| 1 | 1234567-05 | 600 |
| 1 | 1234567-06 | 400 |
| 1 | 1234567-07 | 400 |
| 1 | 1234567-08 | 600 |
| 1 | 1234567-09 | 400 |
| 1 | 1234567-10 | 400 |
+---------------+------------+---------+
10 rows in set (0.00 sec)