MySQLストアドプロシージャでトランザクションを使いこなす！データの一貫性を守り、エラー処理を簡単にする方法

2024-06-09

MySQLストアドプロシージャにおけるトランザクション

MySQLストアドプロシージャは、データベース操作をカプセル化し、再利用可能なモジュールとして作成できる便利な機能です。トランザクション処理をストアドプロシージャ内に組み込むことで、データの一貫性を保ち、エラー発生時のリカバリを容易にすることができます。

トランザクションは、データベースに対する複数の操作をひとつの処理として扱い、成功した場合はまとめてコミット、失敗した場合はすべてロールバックする機能です。銀行での入出金処理を想像してみてください。入金と出金は密接に関係しており、どちらか一方だけが成功しても意味がありません。トランザクションを使用することで、入出金処理をひとつのトランザクションとして扱い、入金と出金の両方が成功した場合のみ処理を確定することができます。

MySQLストアドプロシージャ内では、START TRANSACTION、COMMIT、ROLLBACKステートメントを使用してトランザクションを制御することができます。

START TRANSACTION: トランザクションを開始します。
COMMIT: トランザクションをコミットし、処理を確定します。
ROLLBACK: トランザクションをロールバックし、処理を元に戻します。

以下に、ストアドプロシージャ内でトランザクションを使用する例を示します。

DELIMITER //

CREATE PROCEDURE transfer_funds(
  FROM_ACCOUNT INT,
  TO_ACCOUNT INT,
  AMOUNT DECIMAL(10,2)
)
BEGIN
  DECLARE TRANSFER_STATUS INT;

  START TRANSACTION;

  UPDATE accounts
  SET BALANCE = BALANCE - AMOUNT
  WHERE ACCOUNT_ID = FROM_ACCOUNT;

  IF @@ERROR THEN
    ROLLBACK;
    SET TRANSFER_STATUS = 1;
    LEAVE;
  END IF;

  UPDATE accounts
  SET BALANCE = BALANCE + AMOUNT
  WHERE ACCOUNT_ID = TO_ACCOUNT;

  IF @@ERROR THEN
    ROLLBACK;
    SET TRANSFER_STATUS = 1;
    LEAVE;
  END IF;

  COMMIT;
  SET TRANSFER_STATUS = 0;

SELECT TRANSFER_STATUS;
END //

DELIMITER ;

このストアドプロシージャは、ある口座から別の口座へ資金を振り替える処理を行います。START TRANSACTIONでトランザクションを開始し、UPDATEステートメントで口座の残高を更新します。どちらかのUPDATEステートメントでエラーが発生した場合、ROLLBACKでトランザクションをロールバックし、処理を元に戻します。すべての処理が成功した場合、COMMITでトランザクションをコミットします。

エラー処理

ストアドプロシージャ内でエラーが発生した場合、@@ERRORシステム変数を使用してエラーを確認することができます。@@ERRORが0以外の場合は、エラーが発生していることを示します。エラーが発生した場合は、ROLLBACKステートメントを使用してトランザクションをロールバックする必要があります。

ストアドプロシージャ全体をトランザクションとして扱うこともできます。この場合は、START TRANSACTIONとCOMMITステートメントをプロシージャの先頭と末尾にそれぞれ配置します。
SAVEPOINTステートメントを使用して、トランザクション内で複数のロールバックポイントを設定することもできます。

MySQLストアドプロシージャ内でトランザクションを使用することで、データの一貫性を保ち、エラー発生時のリカバリを容易にすることができます。ストアドプロシージャを作成する際には、トランザクション処理を適切に組み込むことを検討してください。

DELIMITER //

CREATE PROCEDURE create_order(
  CUSTOMER_ID INT,
  PRODUCT_ID INT,
  QUANTITY INT
)
BEGIN
  DECLARE NEW_ORDER_ID INT;

  START TRANSACTION;

  INSERT INTO orders (CUSTOMER_ID, PRODUCT_ID, QUANTITY)
  VALUES (CUSTOMER_ID, PRODUCT_ID, QUANTITY);

  SET NEW_ORDER_ID = LAST_INSERT_ID();

  UPDATE customers
  SET LAST_ORDER_ID = NEW_ORDER_ID
  WHERE CUSTOMER_ID = CUSTOMER_ID;

  IF @@ERROR THEN
    ROLLBACK;
    LEAVE;
  END IF;

  COMMIT;
END //

DELIMITER ;

このストアドプロシージャは、以下の引数を受け取ります。

CUSTOMER_ID: 注文する顧客のID
PRODUCT_ID: 注文する商品のID
QUANTITY: 注文する商品の個数

ordersテーブルに新しい注文レコードを挿入します。
LAST_INSERT_ID()関数を使用して、挿入されたレコードのIDを取得します。
customersテーブルのLAST_ORDER_ID列を、取得したIDに更新します。
エラーが発生した場合は、ROLLBACKステートメントを使用してトランザクションをロールバックします。

CALL create_order(1, 2, 10);

このコマンドは、顧客IDが1の顧客が、商品IDが2の商品を10個注文することを示します。

このサンプルコードはあくまでも一例であり、実際の用途に合わせて変更する必要があります。

銀行口座間の資金移動
商品在庫の更新
ユーザー登録とプロフィール作成

ストアドプロシージャとトランザクションを組み合わせることで、複雑なデータベース操作を簡単に、安全に実行することができます。

ストアドプロシージャ以外の方法

BEGIN TRANSACTION / COMMIT / ROLLBACK ステートメント

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

この例では、口座1から口座2へ100ドルを振り替える処理を行っています。

SAVEPOINT ステートメントを使用すると、トランザクション内で複数のロールバックポイントを設定することができます。これにより、エラーが発生した場所までのみロールバックすることができ、より柔軟なトランザクション処理が可能になります。

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

SAVEPOINT transfer_money;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

IF @@ERROR THEN
  ROLLBACK TO transfer_money;
  LEAVE;
END IF;

COMMIT;

この例では、口座1から口座2へ100ドルを振り替える処理を行っています。UPDATE ステートメントの2番目の後に SAVEPOINT ステートメントを配置し、ロールバックポイントを設定しています。その後、2番目の UPDATE ステートメントでエラーが発生した場合、ROLLBACK TO transfer_money ステートメントを使用して、最初の UPDATE ステートメントまでのみロールバックすることができます。

ACID特性

MySQLは、ACIDと呼ばれるトランザクションの特性をサポートしています。