SQLの更新とトランザクション | SQL入門

在庫テーブルを用意する

このページでは結果を独立して再現できるよう、専用テーブルを作ります。何度も練習するときは練習用データベースを作り直してください。

初期データSQL

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    stock INTEGER NOT NULL CHECK (stock >= 0)
);

INSERT INTO inventory VALUES
    (1, 'SQL入門書', 5),
    (2, 'ノート', 12);

INSERTで列を明示して追加する

列名を書くと、テーブルの列順に依存せず、何を登録するか読み取れます。複数行は一つのVALUESへ並べられます。

商品を追加SQL

INSERT INTO inventory (product_id, name, stock) VALUES
    (3, 'ボールペン', 0),
    (4, 'マグカップ', 3);

SELECT COUNT(*) AS product_count FROM inventory;

実行結果OUTPUT

product_count
-------------
4

主キーが重複したり、在庫へ負数を入れたりすると制約違反になります。エラーを無視して次の処理へ進めないよう、アプリ側でも失敗を扱います。

UPDATEの前後を確認する

まずSELECTで対象を確認し、同じ条件で更新します。在庫を減らす場合は、在庫が足りることも条件に含めます。

2冊分の在庫を減らすSQL

SELECT product_id, name, stock
FROM inventory
WHERE product_id = 1 AND stock >= 2;

UPDATE inventory
SET stock = stock - 2
WHERE product_id = 1 AND stock >= 2;

SELECT product_id, name, stock
FROM inventory
WHERE product_id = 1;

実行結果OUTPUT

product_id  name       stock
----------  ---------  -----
1           SQL入門書  5

product_id  name       stock
----------  ---------  -----
1           SQL入門書  3

アプリでは更新された行数を確認します。0行なら、商品がないか在庫が不足しています。先にSELECTしても、別処理が直後に更新する可能性があるため、UPDATE自体にも条件が必要です。

DELETEは復元方法を考えてから使う

削除も先に対象件数を確認します。関連する行がある場合は外部キー制約で失敗することがあります。

在庫0の商品だけ削除SQL

SELECT product_id, name
FROM inventory
WHERE stock = 0;

DELETE FROM inventory
WHERE stock = 0;

SELECT COUNT(*) AS remaining FROM inventory;

実行結果OUTPUT

product_id  name
----------  ----------
3           ボールペン

remaining
---------
3

WHEREなしは全行 UPDATE inventory SET stock = 0やDELETE FROM inventoryは全商品を変更します。バックアップと復元手順も含めて準備します。

複数の更新をまとめる

店舗在庫から倉庫在庫へ2個移す例を考えます。片方だけ更新されると総数が変わるため、二つを一つのトランザクションにします。

保管場所別の在庫を用意SQL

CREATE TABLE location_stock (
    product_id INTEGER NOT NULL,
    location TEXT NOT NULL,
    stock INTEGER NOT NULL CHECK (stock >= 0),
    PRIMARY KEY (product_id, location)
);

INSERT INTO location_stock VALUES
    (1, '店舗', 5),
    (1, '倉庫', 10);

店舗から倉庫へ2個移動SQL

BEGIN IMMEDIATE;

UPDATE location_stock
SET stock = stock - 2
WHERE product_id = 1 AND location = '店舗' AND stock >= 2;

UPDATE location_stock
SET stock = stock + 2
WHERE product_id = 1 AND location = '倉庫';

SELECT location, stock
FROM location_stock
WHERE product_id = 1
ORDER BY location;

COMMIT;

確定後の実行結果OUTPUT

location  stock
--------  -----
店舗       3
倉庫       12

途中の更新件数が想定と違う、またはエラーが起きた場合はCOMMITの代わりにROLLBACKします。アプリでは例外処理と組み合わせ、成功時だけコミットします。

ROLLBACKを試す

更新を取り消すSQL

BEGIN;
UPDATE inventory SET stock = 99 WHERE product_id = 2;
ROLLBACK;

SELECT name, stock FROM inventory WHERE product_id = 2;

実行結果OUTPUT

name    stock
------  -----
ノート  12

同時実行を想定する

複数の利用者が同時に在庫を購入すると、「読み取ってから計算して更新」の間に値が変わることがあります。SET stock = stock - 1 WHERE stock >= 1のように条件付きの一文で更新し、更新行数を確認すると競合を扱いやすくなります。

ACIDの基本

原子性：一連の処理をすべて行うか、すべて取り消す
一貫性：制約などのルールを満たした状態へ移る
分離性：同時の処理が互いに不正な影響を与えないようにする
永続性：確定した結果が障害後も保持される

ロック方法や分離レベルはDBMSで異なります。SQLite以外を利用するときは、その製品のトランザクション仕様を確認します。

PRACTICE

ミニ課題：在庫を安全に戻す

商品1の店舗在庫を1増やし、倉庫在庫を1減らしてください。倉庫在庫が1以上のときだけ成功させ、両方の更新をトランザクションにまとめます。

解答例SQL

BEGIN IMMEDIATE;

UPDATE location_stock
SET stock = stock - 1
WHERE product_id = 1 AND location = '倉庫' AND stock >= 1;

-- 上の更新が1行だったことをアプリ側で確認してから実行する
UPDATE location_stock
SET stock = stock + 1
WHERE product_id = 1 AND location = '店舗';

COMMIT;

SQLだけで終わらせない 一つ目のUPDATEが0行でも二つ目は実行できます。実際のアプリでは各更新の件数を検査し、想定外ならROLLBACKしてください。DBMSによってはストアドプロシージャなどで判定をデータベース側へまとめます。

← テーブル設計 SQL入門へ戻る →