ECサイトや在庫管理システムを構築する上で、最も慎重な設計が求められる処理の一つが在庫引当です。

在庫引当とは、注文が入った際に在庫を確保し、他のユーザーが同じ商品を注文できないように「予約」する状態を指します。

この処理が不適切であると、実在庫以上の注文を受けてしまう「オーバーセリング」や、在庫があるのに売り切れと判定される「機会損失」を招くリスクがあります。

特に同時実行数が多い環境では、複数のトランザクションが同一の在庫データに対して同時に更新を試みるため、データ不整合が発生しやすくなります。

本記事では、SQLを用いた堅牢な在庫引当の実装手法について、トランザクション設計から具体的なクエリの書き方まで詳しく解説します。

目次 [ close ]
  1. 在庫引当の定義とビジネス上の重要性
  2. データベース設計における在庫データの持たせ方
    1. ステート保存型（現在の在庫数カラムを更新する）
    2. イベント保存型（在庫履歴を積み上げる）
  3. 同時実行制御における不整合のリスク
  4. 実装方法1：SELECT FOR UPDATE（悲観的ロック）
    1. 悲観的ロックのクエリ例
  5. 実装方法2：CAS(Check and Set)による楽観的ロック
    1. 楽観的ロックのクエリ例
  6. 実装方法3：原子的なUPDATE文（Atomic Update）
    1. 原子的なUPDATEのクエリ例
  7. デッドロックを未然に防ぐためのベストプラクティス
  8. 大規模システムにおけるパフォーマンス考慮事項
  9. まとめ

在庫引当の定義とビジネス上の重要性

在庫引当は、単に在庫数を減らすだけの処理ではありません。

ビジネスの要件によって、物理的な在庫数と、販売可能な在庫数を明確に区別して管理する必要があります。

一般的に、在庫の状態は以下の3つの概念で整理されることが多いです。

  • 実在庫：倉庫に実際に存在する商品の数
  • 引当数：注文は受けたが、まだ出荷されていない商品の数
  • 有効在庫（販売可能数）：実在庫から引当数を差し引いた、新規注文が可能な数

SQLで設計を行う際は、これらの数値をどのようにテーブルに保持し、どのタイミングで更新するかが極めて重要になります。

もし有効在庫の計算を誤れば、お客様への欠品連絡やキャンセル対応といった、ブランド信頼性を損なう事態に直結します。

データベース設計における在庫データの持たせ方

在庫データを管理するテーブル設計には、大きく分けて2つのアプローチが存在します。

一つは、現在の在庫数を1つのカラムで保持する「ステート保存型」の設計です。

もう一つは、在庫の増減をすべて履歴として記録し、その合計から在庫を算出する「イベント保存型」の設計です。

ステート保存型（現在の在庫数カラムを更新する）

多くのシステムで採用されているのが、商品マスタや在庫テーブルに直接 stock_count カラムを持たせる方法です。

この方法は、現在の在庫数を即座に取得できるため、検索パフォーマンスに優れているというメリットがあります。

一方で、更新時の排他制御を適切に行わないと、複数のトランザクションが同時に UPDATE を実行した際に不整合が発生しやすくなります。

イベント保存型（在庫履歴を積み上げる）

在庫の入庫や出庫を、1レコードずつトランザクション履歴として保存する方法です。

いつ、誰が、何の理由で在庫を変動させたのかという監査証跡が残るため、追跡可能性が高いのが特徴です。

ただし、在庫数を算出するために毎回 SUM() 関数を実行する必要があり、データ量が増えるとクエリの負荷が高くなる傾向にあります。

現代的な高負荷システムでは、基本的にはステート保存型で現在の数値を管理しつつ、履歴テーブルも併用するハイブリッドな設計が一般的です。

同時実行制御における不整合のリスク

在庫引当で最も注意すべきは、複数のユーザーが同時に最後の1個の商品を注文しようとした場合の挙動です。

プログラムが「現在の在庫数を確認する」→「在庫があれば減らす」という2つのステップで動いている場合、重大な問題が起こり得ます。

ユーザーAが在庫1を確認し、まだ UPDATE を実行する前に、ユーザーBも在庫1を確認してしまうケースです。

このまま両方のユーザーが UPDATE を実行すると、在庫が0であるべきところを、両者の注文が成立して在庫がマイナス（または不正確な数値）になってしまいます。

このような現象を「ロストアップデート」や「ダブルブッキング」と呼びます。

これを防ぐためには、SQLのトランザクションと排他制御（ロック）を正しく理解し、実装する必要があります。

実装方法1：SELECT FOR UPDATE（悲観的ロック）

悲観的ロックは、データを読み込む時点でその行を「ロック」し、他のトランザクションによる更新を待機させる手法です。

SQLでは SELECT ... FOR UPDATE 文を使用して実装します。

この手法は、競合が発生する確率が高い（在庫が少ない人気商品の注文など）場合に非常に有効です。

悲観的ロックのクエリ例

以下に、PostgreSQLやMySQLで使用される悲観的ロックを用いた引当処理の流れを示します。

SQL
-- 1. トランザクションを開始する
BEGIN;

-- 2. 対象商品の在庫を行ロックしつつ取得する
-- 他のユーザーがこの行をロックしている場合、解除されるまで待機する
SELECT stock_quantity 
FROM products 
WHERE product_id = 'P001' 
FOR UPDATE;

-- 3. アプリケーション側で在庫が足りるかチェックする
-- (ここでは在庫が1以上あると仮定)

-- 4. 在庫を減らす更新を行う
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 'P001';

-- 5. 引当履歴や注文詳細を保存する
INSERT INTO order_items (order_id, product_id, quantity) 
VALUES ('ORD-123', 'P001', 1);

-- 6. トランザクションを確定させる
COMMIT;

このクエリの実行により、SELECT FOR UPDATE が実行された瞬間から COMMIT または ROLLBACK されるまで、他のトランザクションは同じ product_id の行を更新・ロックできなくなります。

これにより、完全に直列的な処理が保証され、不整合が防がれます。

ただし、ロックの保持時間が長すぎると、システム全体のパフォーマンスが低下し、ユーザーへのレスポンスが遅くなる点に注意してください。

実装方法2：CAS(Check and Set)による楽観的ロック

楽観的ロックは、データを読み込むときにはロックをかけず、更新する瞬間に「データが読み込み時と変わっていないか」を確認する手法です。

通常、テーブルに version（バージョン番号）や updated_at（更新日時）などのカラムを追加して管理します。

この手法は、競合が発生する頻度が低い場合に適しており、データベースのロック時間を最小限に抑えられます。

楽観的ロックのクエリ例

バージョン番号を用いた楽観的ロックの実装例です。

SQL
-- 1. 現在の在庫数とバージョン番号を取得する (ロックなし)
SELECT stock_quantity, version 
FROM products 
WHERE product_id = 'P001';

-- 2. アプリケーション側で在庫チェックを行う
-- (在庫があり、versionが5だったとする)

-- 3. 更新を行う際、条件に読み取ったversionを含める
UPDATE products 
SET stock_quantity = stock_quantity - 1, 
    version = version + 1 
WHERE product_id = 'P001' 
  AND version = 5;

この UPDATE 文の実行結果（影響を受けた行数）を確認します。

実行結果
-- 成功した場合の出力
Query OK, 1 row affected

-- 他のトランザクションが先に更新していた場合の出力
Query OK, 0 rows affected

もし影響を受けた行数が0であれば、データが読み取り時と異なっている（誰かが先に更新した）ことを意味します。

この場合、アプリケーション側で「もう一度在庫確認からやり直す（リトライ）」か、「エラーを返す」という処理を記述します。

実装方法3：原子的なUPDATE文（Atomic Update）

最もシンプルかつ高パフォーマンスな方法が、1つの UPDATE 文の中に在庫チェックのロジックを含める方法です。

「現在の在庫が注文数以上であれば、在庫を減らす」という条件を WHERE 句に記述します。

この方法は、SQLの標準的な原子性を利用しており、明示的な SELECT FOR UPDATE やバージョン管理を必要としません。

原子的なUPDATEのクエリ例

以下のクエリは、在庫が1個以上ある場合のみ減算を実行します。

SQL
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 'P001' 
  AND stock_quantity >= 1;

この手法のメリットは、データベースとの通信回数を減らせる点と、ロック保持時間を極小化できる点にあります。

実行後、アプリケーション側で更新された行数（Affected Rows）が1であれば引当成功、0であれば在庫不足として処理します。

多くの大規模ECサイトでは、この原子的なUPDATEがパフォーマンスと整合性のバランスが最も良いとして採用されています。

デッドロックを未然に防ぐためのベストプラクティス

在庫引当の処理が複雑になり、複数の商品を一度に引き当てるようになると、デッドロックのリスクが高まります。

デッドロックとは、2つのトランザクションが互いに相手がロックしているデータの解放を待ち続け、処理が停止してしまう状態です。

例えば、ユーザーAが商品1をロックした後に商品2をロックしようとし、同時にユーザーBが商品2をロックした後に商品1をロックしようとすると発生します。

これを防ぐための最も効果的なルールは、「常に同じ順序でデータをロックする」ことです。

注文に含まれる商品IDをプログラム側で昇順にソートしてから、1件ずつ処理（または FOR UPDATE）を実行するように徹底してください。

また、トランザクション内の処理は必要最小限に留め、重い外部API連携や複雑な計算はトランザクションの外で行うように設計することが推奨されます。

大規模システムにおけるパフォーマンス考慮事項

秒間数千リクエストが集中するような超高負荷環境では、データベースへの直接的な UPDATE だけでは限界が来ることがあります。

そのような場合、Redisなどのインメモリデータベースを在庫管理のフロントとして活用し、高速に在庫数を減算する手法が取られます。

Redisでの引当に成功したリクエストのみをバックエンドのSQLデータベースに流し、非同期で永続化を行う構成です。

ただし、この構成はシステムが複雑になり、RedisとRDBの間の数値の整合性をどう保つかという新たな課題も生じます。

まずはSQLのインデックス最適化を徹底し、不要なフルテーブルスキャンが発生していないかを確認することが先決です。

product_id にプライマリキーまたはユニークインデックスが適切に貼られていれば、行ロックの範囲は最小限に抑えられ、十分なスループットを確保できるケースが大半です。

まとめ

SQLによる在庫引当の実装は、単なる数値の計算ではなく、同時実行制御をいかに設計するかが鍵となります。

確実性を優先し、複雑なビジネスロジックを含む場合は悲観的ロック（FOR UPDATE）が適しています。

一方で、参照が多く更新競合が少ない場合や、スループットを重視する場合は楽観的ロックが有力な選択肢となります。

そして、最も効率的でシンプルな解決策は、WHERE 句に在庫チェックを含めた原子的な UPDATE 文の活用です。

システムの規模や、想定されるトラフィック特性、許容される不整合の度合いを考慮し、最適な手法を選択してください。

本記事で紹介したテクニックを組み合わせることで、データの整合性と高い信頼性を兼ね備えた在庫管理システムを構築できるはずです。