MySQLストアドプロシージャでCOMMITを使用した場合、START TRANSACTION内の一部のトランザクションが失敗するとどうなるか？

MySQLのストアドプロシージャ内で START TRANSACTION を発行し、複数のクエリを実行したうえで COMMIT を呼び出す場合、そのうちの1つのクエリが失敗したりエラーを生成したりしても、正常に実行された残りのクエリの変更は、MySQLによってそのままコミットされます。つまり、単なるエラーの発生だけでは自動的なロールバックは行われません。

この挙動は、以下のようなデータを持つテーブル「employee.tbl」を使った例で確認できます。

例

mysql> Select * from employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
+----+---------+
5 rows in set (0.00 sec)

mysql> Delimiter //

mysql> Create Procedure st_transaction_commit_save()
    -> BEGIN
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl (name) values ('Rahul');
    -> UPDATE employee.tbl set name = 'Gurdas' WHERE id = 10;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.00 sec)

このプロシージャでは、テーブルに id = 10 の行が存在しないため、UPDATE文は結果的に何も更新しません。一方、先頭のINSERT文は正常に完了するため、最後の COMMIT の時点で、成功したクエリによる変更だけがテーブルへ確定・保存されます。仮に途中のステートメントで実際にエラーが発生した場合でも、明示的に ROLLBACK を呼び出さない限り、それまでに完了した変更はコミットされてしまう点に注意してください。

mysql> Delimiter ;
mysql> Call st_transaction_commit_save()//
Query OK, 0 rows affected (0.07 sec)

mysql> Select * from employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
|  1 | Mohan   |
|  2 | Gaurav  |
|  3 | Sohan   |
|  4 | Saurabh |
|  5 | Yash    |
|  6 | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

実行結果を見ると、「Rahul」を挿入するINSERT文の変更だけが反映され、6行目としてテーブルに追加されていることがわかります。UPDATE文は該当する行が存在しなかったため、何も変更していません。

補足：エラー時にロールバックさせたい場合

一部のクエリが失敗した際にトランザクション全体を取り消したい場合は、DECLARE ... HANDLER FOR SQLEXCEPTION で例外を捕捉し、ROLLBACK を実行するのが一般的です。

mysql> Delimiter //
mysql> Create Procedure st_transaction_safe()
    -> BEGIN
    -> DECLARE EXIT HANDLER FOR SQLEXCEPTION
    -> BEGIN
    -> ROLLBACK;
    -> RESIGNAL;
    -> END;
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl (name) values ('Rahul');
    -> UPDATE employee.tbl set name = 'Gurdas' WHERE id = 10;
    -> COMMIT;
    -> END //
mysql> Delimiter ;

このようにハンドラを定義しておけば、プロシージャ内でSQL例外が発生した時点で自動的にロールバックが実行され、一部だけが確定された不整合な状態を防ぐことができます。