【Oracle】トランザクション完全ガイド｜COMMIT・ROLLBACK・SAVEPOINT・分離レベル・SELECT FOR UPDATE・読み取り一貫性

-- トランザクション開始: 最初の DML 実行時に自動開始（明示的な BEGIN は不要）
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;
INSERT INTO audit_log VALUES (SYSDATE, 'salary_update', USER);

-- COMMIT: すべての変更を確定（他のセッションから見えるようになる）
COMMIT;

-- ROLLBACK: すべての変更を取り消し（最後の COMMIT 時点に戻す）
UPDATE employees SET salary = 0;  -- 誤操作
ROLLBACK;                          -- すべての変更を取り消し
SELECT salary FROM employees WHERE department_id = 50;  -- 元の値に戻っている

DECLARE
    v_count NUMBER;
BEGIN
    -- Step 1: 古いデータを削除
    DELETE FROM order_items WHERE order_id = 1001;
    SAVEPOINT sp_after_delete;   -- ここにセーブポイントを設定

    -- Step 2: 新しいデータを INSERT
    INSERT INTO order_items VALUES (1001, 'ItemA', 5, 1000);
    INSERT INTO order_items VALUES (1001, 'ItemB', 2, 3000);
    SAVEPOINT sp_after_insert;

    -- Step 3: 合計金額を更新
    UPDATE orders
    SET total_amount = (SELECT SUM(qty * unit_price) FROM order_items WHERE order_id = 1001)
    WHERE order_id = 1001;

    -- もし Step 3 だけ失敗した場合、Step 3 だけをロールバック
    -- ROLLBACK TO sp_after_insert;  -- sp_after_insert 以降の変更だけを取り消す

    COMMIT;  -- すべて成功したら確定
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- 何かエラーが起きたらトランザクション全体をロールバック
        RAISE;
END;
/

-- 注意: ROLLBACK TO savepoint は SAVEPOINT 後の変更だけを取り消す
-- SAVEPOINT 自体は COMMIT するまで有効なまま

-- フラッシュバック・クエリ: AS OF TIMESTAMP で過去時点のデータを参照
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE department_id = 50;

-- AS OF SCN でも参照可能（SCN はシステムの変更番号）
SELECT * FROM employees
AS OF SCN 12345678
WHERE department_id = 50;

-- 現在の SCN を確認
SELECT CURRENT_SCN FROM V$DATABASE;

-- 誤ってデータを削除した場合の復元（フラッシュバック・クエリで取得してINSERT）
INSERT INTO employees
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE)
WHERE employee_id = 999;  -- 削除してしまった行を復元

-- SERIALIZABLE: トランザクション開始から終了まで同じスナップショットで読む
-- → 途中で他セッションがコミットしても見えない（完全なスナップショット隔離）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN
    SELECT COUNT(*) INTO :v_before FROM orders WHERE status = 'PENDING';

    -- 別セッションが PENDING → COMPLETED に変更してコミットしても...
    -- このトランザクション内では引き続き古い数を見る

    SELECT COUNT(*) INTO :v_after FROM orders WHERE status = 'PENDING';
    -- :v_before = :v_after（SERIALIZABLE なので同じ値が返る）
END;
/
COMMIT;

-- READ COMMITTED に戻す（デフォルト。明示的に設定する場合）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 注意: SET TRANSACTION はトランザクションの最初（最初の DML の前）に実行する必要がある
-- DML の後に SET TRANSACTION を実行すると ORA-01453 エラー

-- 基本: SELECT FOR UPDATE で行をロック（他セッションの UPDATE は待機させる）
SELECT employee_id, salary
FROM employees
WHERE department_id = 50
FOR UPDATE;   -- このセッションが COMMIT/ROLLBACK するまで他セッションは UPDATE できない

-- NOWAIT: ロックが取れなければ即座に ORA-00054 エラーを返す（待機しない）
SELECT employee_id, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE NOWAIT;   -- ロック中なら ORA-00054: resource busy

-- WAIT n: n秒待機してロックが取れなければエラーを返す
SELECT employee_id, salary
FROM employees
WHERE employee_id = 100
FOR UPDATE WAIT 5;   -- 5秒待機して取れなければエラー

-- SKIP LOCKED: ロック中の行をスキップして残りを取得（キュー処理に有用）
SELECT job_id, payload
FROM job_queue
WHERE status = 'PENDING'
FOR UPDATE SKIP LOCKED
FETCH FIRST 10 ROWS ONLY;   -- ロックされていない先頭10件を取得

-- OF 句: 結合クエリで特定テーブルの行だけロック
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
FOR UPDATE OF e.salary;    -- employees の行だけロック（departments はロックしない）

-- EXCLUSIVE: 他セッションの読み取りも書き込みもブロック（最強のロック）
-- SHARE: 他セッションは読み取りOKだが書き込みをブロック
-- ROW EXCLUSIVE: 通常の DML で自動取得されるロック
LOCK TABLE employees IN EXCLUSIVE MODE;
-- → このセッションが COMMIT/ROLLBACK するまで、他セッションは employees を参照・更新できない

-- NOWAIT でロックできない場合は即エラー
LOCK TABLE employees IN SHARE MODE NOWAIT;

-- 使用例: テーブル全体を一括更新する前に競合を防ぐ
LOCK TABLE orders IN EXCLUSIVE MODE;
UPDATE orders SET status = 'ARCHIVED' WHERE created_at < DATE '2020-01-01';
COMMIT;   -- ロックは自動解放