MENU
アイデアを形にする( プログラミング / クラウド / フレームワーク / ライブラリ / デザイン / IT豆知識 )
  1. ホーム
  2. MySQL
  3. トランザクションのネスト, 暗黙的コミット, SAVEPOINT

トランザクションのネスト, 暗黙的コミット, SAVEPOINT

MySQL

トランザクションをネストして記述した場合、どういった動作になるか気になったので確認します。結論からいうと、トランザクションはネストできません。暗黙的コミットが行われます。

代わりに、SAVEPOINTステートメントを活用すると、トランザクション内にて一部処理だけロールバックできます。
(動作確認は、MySQL5.7 InnoDBで行っています。)

目次

トランザクションはネストできない

BEGIN( START TRANSACTION ) ~ COMMIT or ROLLBACK の内部に BEGIN( START TRANSACTION ) ~ COMMIT or ROLLBACK を記述してもトランザクションはネストされません。

BEGIN( START TRANSACTION )の時点で暗黙的にコミットされます。暗黙的コミットの動作を確認します。

動作確認1
(ROLLBACK&COMMIT)

下記SQLの動作確認をします。2つ目の BEGIN の時点で暗黙的にコミットされます。

SELECT * FROM `sample`;

---------------------------------------------------------
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");

--------------------------------------------------------------------
-- トランザクション内のトランザクション
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
ROLLBACK;
--------------------------------------------------------------------

INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
COMMIT;
---------------------------------------------------------

SELECT * FROM `sample`;
mysql> SELECT * FROM `sample`;
Empty set (0.01 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");
Query OK, 1 row affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
Query OK, 1 row affected (0.01 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM `sample`;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  3 | CCC  |
+----+------+
2 rows in set (0.00 sec)

最後に COMMIT してますが、トランザクション外の操作になっており、意味のない操作になっています。

  • id=1 のレコードは、2つ目の BEGIN による暗黙的にコミットによって保存されました。
  • id=2 のレコードが存在しないのは、2つ目の BEGIN を ROLLBACK したためです。
  • id=3 のレコードは、トランザクションが開始されていないので、INSERT の時点で保存されています。

動作確認2
(COMMIT&ROLLBACK)

下記SQLの動作確認をします。2つ目の BEGIN の時点で暗黙的にコミットされます。

SELECT * FROM `sample`;

---------------------------------------------------------
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");

--------------------------------------------------------------------
-- トランザクション内のトランザクション
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
COMMIT;
--------------------------------------------------------------------

INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
ROLLBACK;
---------------------------------------------------------

SELECT * FROM `sample`;
mysql> SELECT * FROM `sample`;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");
Query OK, 1 row affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM `sample`;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  2 | BBB  |
|  3 | CCC  |
+----+------+
3 rows in set (0.01 sec)

最後に ROLLBACK してますが、トランザクション外の操作になっており、意味のない操作になっています。

  • id=1 のレコードは、2つ目の BEGIN による暗黙的にコミットによって保存されました。
  • id=2 のレコードは、2つ目の BEGIN を COMMIT したためです。
  • id=3 のレコードは、トランザクションが開始されていないので、INSERT の時点で保存されています。

SAVEPOINT活用

SAVEPOINTステートメント を活用すると、トランザクション内にて一部処理だけロールバックできます。

  • SAVEPOINT identifier
    • SAVEPOINTの設定
  • ROLLBACK TO identifier
    • 指定SAVEPOINTにロールバック
    • トランザクションは終了しない
  • RELEASE SAVEPOINT identifier
    • 指定SAVEPOINTの削除
    • コミット、ロールバックは発生しない

動作確認1
(RELEASE SAVEPOINT&COMMIT)

SAVEPOINTを ROLLBACK しておらず、最後に COMMIT しているので、全て変更が反映されます。

SELECT * FROM `sample`;

---------------------------------------------------------
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");

--------------------------------------------------------------------
-- SAVEPOINT
SAVEPOINT save_id_x;
INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
RELEASE SAVEPOINT save_id_x;
--------------------------------------------------------------------

INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
COMMIT;
---------------------------------------------------------

SELECT * FROM `sample`;
mysql> SELECT * FROM `sample`;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
Query OK, 1 row affected (0.01 sec)

mysql> RELEASE SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `sample`;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  2 | BBB  |
|  3 | CCC  |
+----+------+
3 rows in set (0.00 sec)

動作確認2
(RELEASE SAVEPOINT&ROLLBACK)

最後に ROLLBACK しているので、全て取り消されます。

SELECT * FROM `sample`;

---------------------------------------------------------
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");

--------------------------------------------------------------------
-- SAVEPOINT
SAVEPOINT save_id_x;
INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
RELEASE SAVEPOINT save_id_x;
--------------------------------------------------------------------

INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
ROLLBACK;
---------------------------------------------------------

SELECT * FROM `sample`;
mysql> SELECT * FROM `sample`;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
Query OK, 1 row affected (0.00 sec)

mysql> RELEASE SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `sample`;
Empty set (0.00 sec)

動作確認3
(ROLLBACK TO SAVEPOINT&COMMIT)

最後に COMMIT していますが、ROLLBACK TO SAVEPOINT しているため、id=2のレコード挿入のみ取り消されます。

SELECT * FROM `sample`;

---------------------------------------------------------
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");

--------------------------------------------------------------------
-- SAVEPOINT
SAVEPOINT save_id_x;
INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
ROLLBACK TO SAVEPOINT save_id_x;
--------------------------------------------------------------------

INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
COMMIT;
---------------------------------------------------------

SELECT * FROM `sample`;
mysql> SELECT * FROM `sample`;
Empty set (0.01 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK TO SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM `sample`;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  3 | CCC  |
+----+------+
2 rows in set (0.00 sec)

動作確認4
(ROLLBACK TO SAVEPOINT&ROLLBACK)

最後に ROLLBACK しているので、全て取り消されます。

SELECT * FROM `sample`;

---------------------------------------------------------
BEGIN;
INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");

--------------------------------------------------------------------
-- SAVEPOINT
SAVEPOINT save_id_x;
INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
ROLLBACK TO SAVEPOINT save_id_x;
--------------------------------------------------------------------

INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
ROLLBACK;
---------------------------------------------------------

SELECT * FROM `sample`;
mysql> SELECT * FROM `sample`;
Empty set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (1, "AAA");
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (2, "BBB");
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK TO SAVEPOINT save_id_x;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `sample` (`id`, `name`) VALUES (3, "CCC");
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `sample`;
Empty set (0.01 sec)

参考・関連

MySQL
よかったらシェアしてね！

関連記事

目次