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

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

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

1. mysql> create table test.tbl1(str text) engine=innodb;  
2. Query OK, 0 rows affected (0.02 sec)  
3.   
4. mysql> create table test.tbl2(num int unique) engine=innodb;  
5. Query OK, 0 rows affected (0.01 sec)  

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)

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

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

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

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

1. mysql> DELIMITER //  
2. mysql> CREATE PROCEDURE test.p ()  
3.     ->  BEGIN  
4.     ->    DECLARE EXIT HANDLER FOR SQLEXCEPTION  
5.     ->      BEGIN  
6.     ->        ROLLBACK;  
7.     ->        CALL rollback_occured;  
8.     ->      END;  
9.     ->    START TRANSACTION;  
10.     ->      insert into test.tbl1() values('val1');  
11.     ->      insert into test.tbl2() values(1);  
12.     ->    COMMIT;  
13.     ->  END//  
14. Query OK, 0 rows affected (0.00 sec)  
15. mysql> DELIMITER ;  

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回目)…成功パターン

1. mysql> call test.p;  
2. Query OK, 0 rows affected (0.00 sec)  
3.   
4. mysql> select * from test.tbl1;  
5. +------+  
6. | str  |  
7. +------+  
8. | val1 |  
9. +------+  
10. 1 row in set (0.00 sec)  
11.   
12. mysql> select * from test.tbl2;  
13. +------+  
14. | num  |  
15. +------+  
16. |    1 |  
17. +------+  
18. 1 row in set (0.00 sec)  

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回目)…失敗パターン

1. mysql> call test.p;  
2. ERROR 1305 (42000): PROCEDURE test.rollback_occured does not exist  
3.   
4. mysql> select * from test.tbl1;  
5. +------+  
6. | str  |  
7. +------+  
8. | val1 |  
9. +------+  
10. 1 row in set (0.00 sec)  
11.   
12. mysql> select * from test.tbl2;  
13. +------+  
14. | num  |  
15. +------+  
16. |    1 |  
17. +------+  
18. 1 row in set (0.00 sec)  

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されてますねヾ(*･∀･)ノ"