MariaDBでトランザクションを安全に操る!START TRANSACTIONの使い方とサンプルコード

2024-11-07

構文

START TRANSACTION [トランザクション特性] ;

オプション

  • トランザクション特性: トランザクションの特性を指定します。以下の特性が利用可能です。
    • ISOLATION LEVEL: 隔離レベルを指定します。デフォルトは REPEATABLE READ です。
      • READ UNCOMMITTED: 他のトランザクションによる未コミット変更の影響を受けます。
      • READ COMMITTED: 確定済みトランザクションのみの影響を受けます。
      • REPEATABLE READ: 読み取り開始時点のデータスナップショットを使用します。
      • SERIALIZABLE: 他のトランザクションとの競合を回避します。
    • WITH CONSISTENT SNAPSHOT: 一貫性のあるスナップショット読み取りを使用します。
-- デフォルトのトランザクションを開始
START TRANSACTION;

-- 隔離レベルを `READ COMMITTED` に設定してトランザクションを開始
START TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 一貫性のあるスナップショット読み取りを使用してトランザクションを開始
START TRANSACTION WITH CONSISTENT SNAPSHOT;
  • SET AUTOCOMMIT = 0 を実行すると、暗黙的に START TRANSACTION が実行され、すべての操作が手動コミット/ロールバックの対象になります。
  • トランザクションは、ROLLBACK ステートメントを実行することでロールバックできます。
  • トランザクションは、COMMIT ステートメントを実行することでコミットできます。
  • START TRANSACTION を実行すると、自動コミットは無効になり、トランザクションが明示的に終了されるまで、実行された操作はコミットされません。


-- 送金元口座の残高を 조회한다.
SELECT balance FROM accounts WHERE account_id = 1;

-- 送金元口座の残高が 100 円以上であることを確認する
IF balance >= 100 THEN
    -- 開始トランザクション
    START TRANSACTION;

    -- 送金元口座から 100 円を減額する
    UPDATE accounts
    SET balance = balance - 100
    WHERE account_id = 1;

    -- 送金先口座の残高を 100 円増額する
    UPDATE accounts
    SET balance = balance + 100
    WHERE account_id = 2;

    -- コミット
    COMMIT;
    -- 送金処理成功
    PRINT('送金処理が完了しました。');
ELSE
    -- 残高不足の場合、ロールバックする
    ROLLBACK;
    -- 送金処理失敗
    PRINT('残高不足のため、送金処理を中止しました。');
END IF;
  1. このコードは、まず accounts テーブルから account_id が 1 の口座の残高 (balance) を選択します。
  2. その後、残高が 100 円以上であることを確認します。
  3. 残高が十分な場合は、START TRANSACTION でトランザクションを開始します。
  4. トランザクション内で、以下の操作を実行します。
    • account_id が 1 の口座から 100 円を減額します。
    • account_id が 2 の口座に 100 円を加算します。
  5. すべての操作が成功したら、COMMIT でトランザクションをコミットします。
  6. コミットが成功すると、送金処理が完了したことになります。
  7. 残高が不足している場合は、ROLLBACK でトランザクションをロールバックします。
  8. ロールバックされると、すべての操作が取り消され、送金処理は失敗したことになります。
  • ロックの種類やトランザクションの分離レベルなど、より高度なトランザクション機能については、MariaDB のドキュメントを参照してください。
  • この例は、基本的なトランザクション処理のみを示しています。実際には、エラー処理やロック処理などを考慮する必要があります。


暗黙的コミット

SET AUTOCOMMIT = 0 を実行すると、暗黙的コミットが無効になり、すべての操作が手動コミット/ロールバックの対象になります。この状態で最初の SQL ステートメントを実行すると、自動的にトランザクションが開始されます。

-- 暗黙的コミットを無効にする
SET AUTOCOMMIT = 0;

-- 送金元口座の残高を 조회한다.
SELECT balance FROM accounts WHERE account_id = 1;

-- 送金元口座の残高が 100 円以上であることを確認する
IF balance >= 100 THEN
    -- 送金元口座から 100 円を減額する
    UPDATE accounts
    SET balance = balance - 100
    WHERE account_id = 1;

    -- 送金先口座の残高を 100 円増額する
    UPDATE accounts
    SET balance = balance + 100
    WHERE account_id = 2;

    -- コミット
    COMMIT;
    -- 送金処理成功
    PRINT('送金処理が完了しました。');
ELSE
    -- 残高不足の場合、ロールバックする
    ROLLBACK;
    -- 送金処理失敗
    PRINT('残高不足のため、送金処理を中止しました。');
END IF;

SAVEPOINT

長いトランザクションの中で、部分的なコミットポイントを設定したい場合は、SAVEPOINT を使用することができます。SAVEPOINT を指定して ROLLBACK を実行すると、そのポイントまでトランザクションをロールバックすることができます。

-- 開始トランザクション
START TRANSACTION;

-- 送金元口座の残高を 조회한다.
SELECT balance FROM accounts WHERE account_id = 1;

-- 送金元口座の残高が 100 円以上であることを確認する
IF balance >= 100 THEN
    -- 送金処理開始ポイントを設定
    SAVEPOINT transfer_start;

    -- 送金元口座から 100 円を減額する
    UPDATE accounts
    SET balance = balance - 100
    WHERE account_id = 1;

    -- 送金先口座の残高を 100 円増額する
    UPDATE accounts
    SET balance = balance + 100
    WHERE account_id = 2;

    -- 送金処理が成功したことを確認
    -- ...

    -- コミット
    COMMIT;
    -- 送金処理成功
    PRINT('送金処理が完了しました。');
ELSE
    -- 残高不足の場合、ロールバックポイントまでロールバックする
    ROLLBACK TO transfer_start;
    -- 送金処理失敗
    PRINT('残高不足のため、送金処理を中止しました。');
END IF;

プロシージャ/関数を用いる

複雑なトランザクション処理をカプセル化するために、プロシージャや関数を用いる方法もあります。プロシージャや関数内で START TRANSACTION / COMMIT / ROLLBACK を実行することで、トランザクションを制御することができます。

-- 送金処理を実行するプロシージャ
CREATE PROCEDURE transfer(from_account_id INT, to_account_id INT, amount INT)
BEGIN
    -- 開始トランザクション
    START TRANSACTION;

    -- 送金元口座の残高を 조회한다.
    SELECT balance INTO @from_balance
    FROM accounts
    WHERE account_id = from_account_id;

    -- 送金元口座の残高が十分であることを確認する
    IF @from_balance >= amount THEN
        -- 送金元口座から減額する
        UPDATE accounts
        SET balance = balance - amount
        WHERE account_id = from_account_id;

        -- 送金先口座へ加算する
        UPDATE accounts
        SET balance = balance + amount
        WHERE account_id = to_account_id;

        -- コミット
        COMMIT;
        -- 送金処理成功
        PRINT('送金処理が完了しました。');
    ELSE
        -- 残高不足の場合、ロールバックする
        ROLLBACK;
        -- 送金処理失敗
        PRINT('残高不足のため、送金処理を中止しました。');
    END IF;
END;

-- 送金処理を実行
CALL transfer(1, 2, 100);