MySQLストアドプロシージャ内でCOMMITによるトランザクション制御を行う方法

MySQLでは、START TRANSACTIONステートメントでトランザクションを開始し、開始後に行われた変更内容を確定（永続化）するためにCOMMITを使用します。この2つはストアドプロシージャの中でも問題なく組み合わせて利用できます。

本記事では、ストアドプロシージャ内でSTART TRANSACTIONとCOMMITを使い、テーブルへの新規レコード挿入と更新をまとめてコミットする具体例を紹介します。

準備：対象となるテーブルのデータ

以下の例では、「employee.tbl」というテーブルに対して操作を行います。事前のデータは次の通りです。

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

例：COMMITを含むストアドプロシージャの作成

次のように、START TRANSACTIONでトランザクションを開始し、INSERT文とUPDATE文を実行した後にCOMMITで変更を確定するストアドプロシージャを作成します。

mysql> Delimiter //
mysql> Create Procedure st_transaction_commit()
    -> BEGIN
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl(name) values ('Yash');
    -> UPDATE employee.tbl set name = 'Sohan' WHERE id = 3;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.03 sec)

ポイント解説

• Delimiter //：ストアドプロシージャ作成時は、デフォルトの区切り文字を一時的に変更して、BEGIN〜END内のセミコロンがそのまま処理されるようにしています。
• START TRANSACTION：ここから後続のSQL文の変更がトランザクション管理下に入ります。
• COMMIT：トランザクション開始以降のすべての変更を確定し、他のセッションからも参照できる状態にします。

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

区切り文字をもとに戻し、CALL文でこのプロシージャを呼び出すと、INSERTとUPDATEが実行され、その変更が自動的にコミットされます。

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

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

実行結果の読み方

• ID 5として「Yash」という新しいレコードが挿入されています。
• ID 3の名前が「Rahul」から「Sohan」へ更新されています。

このように、ストアドプロシージャ内でSTART TRANSACTIONとCOMMITを組み合わせることで、複数のDML文（INSERT・UPDATE・DELETEなど）をひとつの単位としてまとめて確定させることができ、データ整合性を保ちながら安全なデータベース操作が実現できます。