재고를 실시간으로만 맞추려니 어긋난 걸 못 잡았다

주문이 들어오면 재고를 바로 뺀다. 취소되면 되돌린다. 논리는 단순한데 실제 수량이 자꾸 안 맞았다. 창고에서 세어 온 수와 화면 수가 며칠에 한 번씩 어긋났다.

Open Table of contents

재고를 바꾸는 자리가 여럿이었다
현재 수량만으로는 판단할 수 없었다
기록으로 남긴 변동
검증 — 대조가 실제로 잡는지 봤다
정리

재고를 바꾸는 자리가 여럿이었다

차감은 주문 저장과 같은 트랜잭션 안에 있었다.

$this->db->trans_start();

$this->db->insert('order_item', [...]);
$this->db->query(
    "UPDATE stock SET qty = qty - ? WHERE product_no = ?",
    [$qty, $no]
);

$this->db->trans_complete();

trans_start 와 trans_complete 사이라 주문이 들어가면 stock 도 같이 준다. 문제는 재고를 바꾸는 자리가 여기만이 아니라는 것이었다.

$ grep -rn "UPDATE stock" --include=*.php application/

일곱 군데였다. 주문 저장과 주문 취소와 반품 입고와 관리자 수동 조정과 입고 등록과 교환 처리와 일괄 등록 화면이다.

일곱 곳이 각자 qty 를 더하고 뺀다. 한 군데라도 트랜잭션 밖이면 거기서 어긋난다.

일괄 등록 화면이 그랬다.

foreach ($rows as $r) {
    $this->db->query("UPDATE stock SET qty = qty + ? WHERE product_no = ?", [...]);
}

트랜잭션이 없어서 autocommit 이 켜진 채로 한 줄씩 커밋된다. 200행을 처리하다 중간에 오류가 나면 앞의 것만 반영된 채로 끝나는데 화면에는 실패했다고 나오니 다시 올리고 앞의 것이 두 번 더해진다.

현재 수량만으로는 판단할 수 없었다

일곱 군데를 전부 트랜잭션에 넣어도 남는 것이 있었다. 어긋났다는 것을 알 방법이 없다는 점이다.

stock.qty 라는 숫자 하나만 들고 있으면 그것이 맞는지 판단할 근거가 없다. 값이 틀려도 틀렸다는 것을 알 수 없는 구조였다.

어긋난 것을 창고에서 세어 올 때만 발견했고 그 사이에 며칠이 지나 있었다.

기록으로 남긴 변동

그래서 두 가지를 같이 뒀다. 하나는 변동을 기록으로 남기는 것이다.

CREATE TABLE stock_history (
  history_no INT NOT NULL AUTO_INCREMENT,
  product_no INT NOT NULL,
  diff       INT NOT NULL,
  reason     VARCHAR(20) NOT NULL,
  ref_no     INT NULL,
  reg_date   DATETIME NOT NULL,
  PRIMARY KEY (history_no),
  KEY idx_product_date (product_no, reg_date)
);

qty 를 바꿀 때 stock_history 에 diff 와 reason 을 같이 남긴다. 같은 트랜잭션 안이다.

다른 하나는 주기적으로 대조하는 것이다.

SELECT s.product_no, s.qty, IFNULL(SUM(h.diff), 0) AS calc
FROM stock s
LEFT JOIN stock_history h ON h.product_no = s.product_no
GROUP BY s.product_no
HAVING s.qty != calc;

현재 수량과 SUM(diff) 가 다르면 어딘가에서 기록 없이 바뀐 것이다.

돌려 보니 12건이 나왔다. 일괄 등록에서 중간에 끊긴 것이 5건이고 관리자가 직접 고친 것이 4건이며 반품 처리에서 기록을 안 남기는 경로가 3건이었다.

세 번째가 코드 문제라 stock_history 를 남기게 고쳤다. 두 번째는 그런 일이 있었다는 것 자체가 확인된 것이고 앞으로 관리자 화면으로 하게 안내했다. 대조가 없었으면 이 세 부류를 구분조차 못 했다.

검증 — 대조가 실제로 잡는지 봤다

새벽에 하루 한 번 돌게 하고 결과가 있으면 목록을 남겼다.

[재고대조 2014-11-06] 불일치 3건
  product 1204: qty=15 calc=18 diff=-3
  product 2871: qty=0  calc=2  diff=-2
  product 3390: qty=44 calc=41 diff=+3

자동으로 고치지는 않게 했는데 qty 와 calc 중 어느 쪽이 맞는지 프로그램이 판단할 수 없어서 사람이 보고 정한다.

자동으로 맞추면 원인을 못 찾는다. 매일 조용히 맞춰지니 새는 곳이 안 드러난다.

돌려서 0건이 나왔을 때 그것이 정말 안 어긋난 것인지 대조가 아무것도 안 보는 것인지 구분이 안 됐다.

UPDATE stock SET qty = qty + 5 WHERE product_no = 9999;

stock_history 를 건너뛰고 qty 만 바꿔 한 건을 어긋나게 만들었더니 다음 대조에서 그 건이 나왔다. 되돌리고 나서 다시 0건이 됐다.

이걸 해 보기 전까지는 0건이 통과인지 아닌지 알 수 없었다.

정리

재고를 바꾸는 자리가 여러 곳이면 한 군데만 트랜잭션 밖이어도 어긋난다
grep -rn 으로 그 컬럼을 건드리는 자리를 전부 찾는다
autocommit 이 켜져 있으면 foreach 안의 UPDATE 가 한 줄씩 커밋된다
반복문 안의 갱신에 트랜잭션이 없으면 중간 실패가 부분 반영으로 남는다
현재 수량만 들고 있으면 그 값이 맞는지 판단할 근거가 없다
변동을 stock_history 에 남기고 합계와 현재 수량을 주기적으로 대조한다
HAVING s.qty != calc 로 어긋난 것만 뽑는다
대조 결과를 자동으로 맞추지 않는다. 조용히 맞춰지면 새는 곳이 안 드러난다
0건이 나오면 일부러 어긋나게 해서 대조가 잡는지 확인한다