MySQLで例外発生時にロールバック処理をさせる

前提条件：テーブルのエンジンがinnodb等、トランザクションに対応したエンジンであること

■サンプルテーブルの作成

mysql> create table test.tbl1(str text) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> create table test.tbl2(num int unique) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

■既存ストアドプロシージャがあったら削除

mysql> DROP PROCEDURE IF EXISTS test.p;
Query OK, 0 rows affected (0.00 sec)

■デリミタを変更しストアド作成→デリミタを元に戻す

mysql> DELIMITER //
mysql> CREATE PROCEDURE test.p ()
    ->  BEGIN
    ->    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->      BEGIN
    ->        ROLLBACK;
    ->        CALL rollback_occured;
    ->      END;
    ->    START TRANSACTION;
    ->      insert into test.tbl1() values('val1');
    ->      insert into test.tbl2() values(1);
    ->    COMMIT;
    ->  END//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

■ストアド呼び出し(1回目)…成功パターン

mysql> call test.p;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test.tbl1;
+------+
| str  |
+------+
| val1 |
+------+
1 row in set (0.00 sec)

mysql> select * from test.tbl2;
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

■ストアド呼び出し(2回目)…失敗パターン

mysql> call test.p;
ERROR 1305 (42000): PROCEDURE test.rollback_occured does not exist

mysql> select * from test.tbl1;
+------+
| str  |
+------+
| val1 |
+------+
1 row in set (0.00 sec)

mysql> select * from test.tbl2;
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

2回目の呼び出しでtbl1にinsertした後、tbl2へのinsertで失敗し見事rollbackされてますねヾ(*･∀･)ノ"