MySQLストアドプロシージャ内でROLLBACKトランザクションを実行する方法

はじめに

ROLLBACKは、トランザクション開始後に行われたデータベースへの変更をすべて取り消すためのコマンドです。MySQLのストアドプロシージャ内でROLLBACKを実行したい場合は、あらかじめEXITハンドラを宣言しておく必要があります。このハンドラは、sqlexception（SQL例外）またはsqlwarning（SQL警告）のどちらに対しても定義できます。

以下では、実際にROLLBACKを含むストアドプロシージャを作成し、その動作を順を追って確認していきます。

検証用テーブルの準備

まず、次のような構造を持つテーブルggを用意します。

mysql> SHOW CREATE TABLE gg\G
*************************** 1. row ***************************
       Table: gg
Create Table: CREATE TABLE `gg` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

このテーブル定義から分かるように、NameカラムにはNULL値を挿入できません。また、テーブルには次のようなデータが格納されています。

mysql> SELECT * FROM gg;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

ROLLBACKを行うストアドプロシージャの作成例

それでは、EXITハンドラを使ってROLLBACKを実装したストアドプロシージャを作成してみましょう。

mysql> DELIMITER //
mysql> CREATE PROCEDURE st_transaction_Rollback()
    -> BEGIN
    -> DECLARE exit handler FOR sqlexception
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> DECLARE exit handler FOR sqlwarning
    -> BEGIN
    -> ROLLBACK;
    -> END;
    -> START TRANSACTION;
    -> INSERT INTO gg(name) VALUES();
    -> UPDATE gg SET name = 'YashPal' WHERE id = 5;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;
mysql> CALL st_transaction_Rollback();
Query OK, 0 rows affected (0.00 sec)

プロシージャのポイント

• DECLARE exit handler FOR sqlexception：SQL例外が発生した時点で処理を中断し、ROLLBACKを実行します。
• DECLARE exit handler FOR sqlwarning：SQL警告が発生した場合も同様にROLLBACKを実行します。
• START TRANSACTIONでトランザクションを開始し、問題がなければ最後にCOMMITで変更を確定します。
• エラーが発生した場合はCOMMITに到達せず、ハンドラによってトランザクション開始以降のすべての変更がロールバックされます。

実行結果の確認

プロシージャ実行後のテーブルの内容を見てみましょう。

mysql> SELECT * FROM gg;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

上記の結果セットから、UPDATE文による変更（id=5の名前を「YashPal」へ更新）がロールバックされていることが分かります。これは、最初のINSERT文がNULL値を挿入しようとしてエラーをスローしたためです。エラー発生時にEXITハンドラが起動し、トランザクション全体がROLLBACKされたのです。

まとめ

MySQLのストアドプロシージャ内でトランザクションを安全に扱うには、EXITハンドラを宣言し、例外や警告の発生時にROLLBACKを実行するのが基本です。なお、トランザクション機能を利用するには、対象テーブルがInnoDBなどトランザクション対応のストレージエンジンである必要がある点にも注意してください。