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

トランザクションの基本

注文を登録し、在庫を減らす処理のように、複数の変更をすべて成功させるか、すべて取り消したい場合に使います。

注文と在庫を確定するSQL

BEGIN;

UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;

INSERT INTO orders (product_id, quantity, ordered_at)
VALUES (1, 1, DATE '2026-10-17');

COMMIT;

実行結果の例OUTPUT

UPDATE 1
INSERT 0 1
COMMIT

在庫更新の件数を確認し、在庫がなければCOMMITせずにROLLBACKします。

ROLLBACKで取り消す

次の例は、節01を実行していない状態で、開始時の在庫が5の場合です。節01を確定したあとなら、開始時の値は4になります。

在庫を変えて戻すSQL

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT stock FROM products WHERE id = 1;
ROLLBACK;
SELECT stock FROM products WHERE id = 1;

実行結果の考え方OUTPUT

トランザクション中: 4
ROLLBACK後:        5

ロックとMVCC

PostgreSQLはMVCCにより、読み取りと書き込みが不要に互いを待たせにくい仕組みを持ちます。ただし、同じ在庫を同時に更新するような処理では、対象行をロックしてから判断する設計が必要です。

行をロックするSQL

BEGIN;
SELECT id, stock
FROM products
WHERE id = 1
FOR UPDATE;

UPDATE products SET stock = stock - 1
WHERE id = 1 AND stock >= 1;
COMMIT;

確認結果の例OUTPUT

id  stock
1   4
UPDATE 1
COMMIT

節01を実行して在庫5から1減らした後の値を表示しています。節01を実行していない場合は、開始時の在庫5が表示されます。ロックを長時間保持すると待ち時間が増えます。短いトランザクション、適切なインデックス、タイムアウトやデッドロックのエラー処理を考えます。

SAVEPOINTで一部だけ戻す

保存点まで戻るSQL

BEGIN;
INSERT INTO products (name, price, stock)
VALUES ('テスト商品', 100.00, 1);
SAVEPOINT after_product;
UPDATE products SET price = 0 WHERE name = 'テスト商品';
ROLLBACK TO SAVEPOINT after_product;
COMMIT;

確定後に残る値OUTPUT

商品名: テスト商品
価格: 100.00

pg_dumpでバックアップする

PostgreSQLには論理バックアップを取得するpg_dumpがあります。バックアップファイルを作っただけで安心せず、復元できることを確認します。

バックアップと復元COMMAND

pg_dump -h 127.0.0.1 -U backup_user -d study_postgres -Fc -f study_postgres.dump
createdb -h 127.0.0.1 -U restore_user restored_postgres
pg_restore -h 127.0.0.1 -U restore_user -d restored_postgres study_postgres.dump