第134回　DDLと暗黙的なコミットについて

皆さんはMySQLを開発に利用している時に、カラム追加や変更を同時に行いたい場面によく出くわすと思います。特に、Webアプリケーションフレームワークなどで用意されているデータベーススキーマのマイグレーションツール等を利用している時に、マイグレーション途中で失敗して中途半端に適用されてしまう、なんてことがあるかもしれません。

マイグレーションが中途半端に適用されてしまった場合、マイグレーションツールでは簡単に元に戻せず、スキーマの復旧のためにmysqlでログインして手作業で復旧するはめになってしまって困った経験がある方もいるかも知れません。そういうアトミック性が欲しい時は、トランザクションを利用して…と、考えると思いますが、これは実は上手くいきません。

今回はその理由である「暗黙的なコミット」について解説していきたいと思います。

検証環境

今回は、第125回　phpMyAdminでDockerで建てたMySQLにアクセスするで紹介したdocker-composeを利用して作成します。手元で簡単に試せるように、GitHubの私のレポジトリにサンプルコードとして置いてあるので気軽に試したい方は、git cloneして試してみてください。試すにはdockerとdocker-composeが必要です。

トランザクションを発生させてからDDLを実行してみる

ここでは第2回　MySQLにはじめてのデータを入れてみるで紹介されているzipcodeテーブルを例に進めていきます。

検証環境として用意したdocker-composeの環境をビルドして中に入ってmysqlコマンドラインクライアントでログインしてみましょう。

$ docker-compose build
$ docker-compose up -d
$ docker-compose exec mysql /bin/bash
# mysql -uroot -ppassword zipcode
～省略～
mysql>

以上のように、mysqlコマンドラインクライアントが利用できる状態になれば準備完了です。まずは、現在のデータベース内のテーブルの内容について確認していきます。

mysql> show tables;
+-------------------+
| Tables_in_zipcode |
+-------------------+
| zipcode           |
+-------------------+
1 row in set (0.00 sec)

この状態の時に、START TRANSACTIONでトランザクション処理を開始してからzipcodeテーブルをDROP TABLEして、最後にROLLBACKを行ってみようと思います。結果はどうなるでしょうか？

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

mysql> DROP TABLE zipcode;
Query OK, 0 rows affected (0.02 sec)

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

mysql> show tables;
Empty set (0.00 sec)

ということで、上記からトランザクションROLLBACKを行ったにもかかわらず、テーブルが消えてしまったことがわかります。なぜトランザクション処理の中で実行したはずのDROP TABLEはROLLBACKできなかったのでしょうか？

ちなみに今回は、docker-composeで起動の際に自動でテーブルを用意しているので、元の状態に戻したい場合は以下のように一度コンテナの外に出て、docker-compose downとdocker-compose up -dを実行するだけで元通りの状態に戻せます。

mysql> exit
Bye
# exit
exit
$ docker-compose down
$ docker-compose up -d

暗黙的なコミットとは？

MySQLの暗黙的なコミットは、特定のクエリを実行した際に現在のセッションで実行されているトランザクションを全てコミットしてから実行されるクエリで、クエリ自身の実行後もコミットされます。公式のドキュメントとしては、こちらに記載されています。

文章で説明するとややこしいのですが、先ほどのDROP TABLEを行ったクエリの順番で考えていきます。

mysql> START TRANSACTION;
mysql> DROP TABLE zipcode;
mysql> ROLLBACK;

上記のクエリに、先ほど説明した内容の暗黙的なコミットを明示的に入れ込んでみると、以下のようになります。

mysql> START TRANSACTION;
mysql> COMMIT; -- 処理の前に自動的にコミットされる
mysql> DROP TABLE zipcode;
mysql> COMMIT; -- 処理の後に自動的にコミットされる
mysql> ROLLBACK;

ということで、ROLLBACKを打ったとしても結果が全てコミットされてしまっているため、元に戻せないことがわかります。

よくある悲劇的な話としては、テーブル内のデータの削除の高速化のためにDELETE文で削除していたものを、TRUNCATE TABLEに変更した時などに起こります。トランザクション処理の途中で単純に置き換えをしてしまった場合に、暗黙のコミットが挟まってしまって予期せぬ挙動になってしまうことがあります。

特に削除後の処理の何らかの理由で、DELETE文を含む箇所でROLLBACKが必要な事態になった時に、DELETE文で処理していた時は問題なくロールバックできていたところが、TRUNCATEに変更したことによって知らない間にコミットされてしまい、ROLLBACKができないということがあったりします。

DELETEの代わりにTRUNCATEを利用する際には、違いによく注意をしましょう。

DDL以外で暗黙的なコミットが発生するもの

DDL以外で暗黙的なコミットが発生するものとして注意がいるものとしては、トランザクションの制御およびロックステートメントです。MySQLのトランザクション処理はネストができないため、トランザクション処理の中でトランザクション処理を利用したい場合には、SAVEPOINTを活用して管理していく必要があります。

その他では、mysqlデータベースの操作で使用されるクエリ（ユーザの追加や削除、権限操作やパスワード設定など）や、管理ステートメント（ANALYZE TABLE、第35回で解説したOPTIMIZE TABLEなど）やレプリケーション制御ステートメント（START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO）などが当たります。この辺りはどちらかというと管理目的の操作になるので、アプリケーション開発時にはあまり影響がありませんが、覚えておいても損はないと思います。

まとめ

今回は、DDLでトランザクションが利用できない理由の暗黙的なコミットについて、確認と解説を行いました。

普段WebアプリケーションフレームワークのマイグレーションツールからMySQLのスキーマ操作を行う場合には、暗黙のコミットはとくに意識をしていないかもしれません。

しかし、スキーマのマイグレーション操作に中途半端に失敗した場合に、どういうデータ構造になってしまうのか等を意識して設計ができるようになると、マイグレーションツールをより便利に扱えるのではないかと思います。