レッスン
開始
勉強を始める
CodeGym /コース /SQL SELF /ネストされたトランザクションでよくあるミスの分析

ネストされたトランザクションでよくあるミスの分析

SQL SELF
レベル 54 , レッスン 4
使用可能

Postgresでのプログラミングって、まじで冒険だよね。時々「自分のミスを探せ」っていうクエストになるし。このセクションでは、ネストされたトランザクションを扱うときに出会いがちな典型的なミスや落とし穴について話すよ。いくぞ！

関数やプロシージャ内でのトランザクションコマンドの誤用

ミス例: FUNCTIONの中でCOMMIT、ROLLBACK、SAVEPOINTを使おうとする。

なぜダメか: PostgreSQLの関数（CREATE FUNCTION ... LANGUAGE plpgsql）は一つの外部トランザクションの中で実行されるから、関数内でトランザクションコマンドを使うのは禁止されてる。やろうとするとシンタックスエラーになるよ。

ミス例:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- ミス: トランザクションコマンドは禁止
END;
$$ LANGUAGE plpgsql;

正しいやり方:

「全部やるか、全部やらないか」みたいなアトミックな操作には、明示的なトランザクションコマンドなしで関数を使おう。もし段階的に変更を確定したいなら、プロシージャを使ってね。

ミス例: PL/pgSQLのプロシージャでROLLBACK TO SAVEPOINTを使おうとする。

なぜダメか: PostgreSQL 17では、プロシージャ（CREATE PROCEDURE ... LANGUAGE plpgsql）内でCOMMIT、ROLLBACK、SAVEPOINT、RELEASE SAVEPOINTだけが許可されてる。でもROLLBACK TO SAVEPOINTはPL/pgSQLで使えない！やろうとするとシンタックスエラーになるよ。

ミス例:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- ミス! 使えない
END;
$$;

正しいやり方:

「部分的なロールバック」にはBEGIN ... EXCEPTION ... ENDブロックを使おう。これで自動的にセーブポイントが作られて、ブロック内でエラーが起きたら最初までロールバックされるよ。

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- エラーが起きるかもしれない操作
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'BEGIN ... EXCEPTION ... ENDブロック内でロールバックしたよ';
    END;
END;
$$;

ネストされたプロシージャ呼び出し: 制限と典型的なミス

ミス例: すでにクライアント側でトランザクションが開いている状態で、COMMIT/ROLLBACKを含むプロシージャを呼び出す。

なぜダメか: トランザクション制御付きプロシージャはautocommitモード（1プロシージャ＝1トランザクション）でしか正しく動かない。クライアント側でトランザクションが開いてると、プロシージャ内でCOMMITやROLLBACKを使おうとした時にエラーになる。

例:

# Pythonのpsycopg2ではデフォルトでautocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- my_proc内でCOMMITしようとしてエラー

正しいやり方:

  • プロシージャを呼ぶ前にコネクションをautocommitモードにしてね。
  • プロシージャは関数やSELECT経由で呼ばないこと。

ミス例: トランザクション制御付きプロシージャ（COMMIT, ROLLBACK）はCALLコマンド以外（例えばSELECT経由）で呼ぶと動かない。

なぜダメか: CALL（または匿名DOブロック）経由でしかトランザクション制御できない。関数から呼ぶのはNG。

ロックとデッドロック（Deadlock）の問題

ロックって、まるで招かれざる客みたい。最初は邪魔なだけだけど、やがてカオスを呼ぶ。デッドロックは、トランザクション同士がお互いを永遠に待ち続けるときに発生するよ。典型的な例はこれ：

  1. トランザクションAがordersテーブルの行をロックして、productsテーブルの行を更新しようとする。
  2. トランザクションBがproductsテーブルの行をロックして、ordersテーブルの行を更新しようとする。

結果、どっちのトランザクションも進めなくなる。まるで2台の車が同時に狭いカーブに突っ込もうとして、渋滞になる感じ。

例:

-- トランザクションA
BEGIN;
UPDATE orders SET status = '処理中' WHERE id = 1;

-- トランザクションB
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- ここでトランザクションAが`products`の同じ行を更新しようとし、
-- トランザクションBが`orders`の行を変更しようとする。
-- デッドロック！

どうやって回避する？

  1. データの更新順序を常に同じにする。例えば、まずorders、次にproducts。
  2. トランザクションを長くしすぎない。
  3. LOCKは必要最小限のロックレベルで賢く使おう。

動的SQL（EXECUTE）の誤用

動的SQLは、うっかり使うと本当に頭痛のタネになる。最もありがちなミスはSQLインジェクション。例えば：

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

user_inputに1; DROP TABLE orders;みたいなのが入ってたら、ordersテーブルとサヨナラすることになる。

どうやって防ぐ？プリペアドクエリを使おう：

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

こうすればアプリをSQLインジェクションから守れるよ。

エラー処理が不十分な場合のトランザクションロールバック

エラーをちゃんと処理しないと、トランザクションが無効な状態のまま残っちゃう。例えば：

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, '保留中');

BEGIN;
-- 何かの操作でエラー発生
INSERT INTO non_existing_table VALUES (1);
-- エラー発生、でもトランザクションは終わってない

COMMIT; -- エラー: 現在のトランザクションは中断された

エラーのせいで、全部のコードが詰まっちゃう。

どうやって防ぐ？ EXCEPTIONブロックでちゃんとロールバックしよう：

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, '保留中');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'エラーが発生したのでトランザクションをロールバックするよ。';
END;

ミスを避けるためのコツとアドバイス

  • 複雑なプロシージャを書くときは、まず擬似コードから始めよう。全てのステップとエラー発生ポイントを書き出してみて。
  • SAVEPOINTを使って、トランザクションの一部だけロールバックできるようにしよう。ただし使い終わったら必ず解放してね。
  • 長いトランザクションは避けよう。長くなるほどロックのリスクが高くなる。
  • ネストしたプロシージャ呼び出しでは、外側と内側のトランザクションコンテキストがちゃんと同期してるか確認しよう。
  • 自分のプロシージャのパフォーマンスはEXPLAIN ANALYZEで必ずチェック！
  • エラーはテーブルやテキストファイルにログしておくと、デバッグが楽になるよ。

ミス例とその修正

例1: ネストしたプロシージャ呼び出しでのミス

ミスのあるコード:

BEGIN;

CALL process_order(5);

-- process_order内でROLLBACKが発生
-- トランザクション全体が無効になる
COMMIT; -- エラー

修正版コード:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- エラー時だけロールバック
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

例2: デッドロックの問題

ミスのあるコード:

-- トランザクションA
BEGIN;
UPDATE orders SET status = '処理中' WHERE id = 1;
-- `products`を待ってる

-- トランザクションB
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- `orders`を待ってる

修正:

-- 両方のクエリを同じ順序で実行する:
-- まず`products`、次に`orders`
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = '処理中' WHERE id = 1;
COMMIT;

こういうミスを見ると、トランザクションの扱いには注意と経験が必要だって分かるよね。でも、練習すればするほど、リアルな現場（とキャリア）でROLLBACKを食らう確率は減るから！

1
アンケート/クイズ
ネストされたプロシージャ、レベル 54、レッスン 4
使用不可
ネストされたプロシージャ
ネストされたプロシージャ
オープン
コメント
TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION