MySQLのストアドプロシージャ内でSTART TRANSACTIONを実行する方法

MySQLでは、START TRANSACTIONを実行すると新しいトランザクションが開始され、自動コミット（auto-commit）モードがオフに設定されます。これにより、明示的にCOMMITまたはROLLBACKを実行するまで、データの変更内容は確定されません。

以下の例では、START TRANSACTIONを含むストアドプロシージャを作成し、employee.tblテーブルに新しいレコードを挿入します。まず、対象となるテーブルには現在、次のデータが格納されています。

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

ストアドプロシージャの作成例

次のように、デリミタを変更してプロシージャを作成します。BEGINブロック内でSTART TRANSACTIONを実行し、その後INSERT文で新しいレコードを追加しています。

mysql> Delimiter //
mysql> Create Procedure st_transaction()
    -> BEGIN
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl(name) values ('Saurabh');
    -> END //
Query OK, 0 rows affected (0.00 sec)

プロシージャの実行と結果の確認

作成したプロシージャを呼び出すと、employee.tblテーブルに新しい値が挿入されます。

mysql> Delimiter ;
mysql> Call st_transaction();
Query OK, 0 rows affected (0.17 sec)

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

実行結果を見ると、「Saurabh」という名前のレコードがId 4として正常に追加されていることが確認できます。

補足：COMMITとROLLBACKの活用

実際の開発では、トランザクション内の処理が成功した場合にCOMMITで変更を確定し、エラーが発生した場合にはROLLBACKで変更を取り消すのが一般的です。例えば、DECLARE EXIT HANDLER FOR SQLEXCEPTIONを組み合わせれば、エラー発生時に自動的にロールバックする堅牢なストアドプロシージャを作成できます。複数のINSERTやUPDATEをまとめて扱う際に、トランザクション制御はデータ整合性を保つために非常に有効です。