🚧
mysqlコマンドを使うときはautocommitの設定に注意
結論
MySQLはautocommit（自動コミットモード）がデフォルトなので気をつけよう。
背景
もともと業務でOracleをよく利用していたのですが、最近MySQLも使うようになりました。「OracleもMySQLも同じSQLだし気にすることないかー」と思いながらmysqlコマンドで作業していたところ、、
mysql> SELECT * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+------+-----------+
# suzukiを消すつもりが、間違えてtakahashiを消してしまった。
mysql> DELETE FROM user WHERE id = '3';
# ロールバックしよーっと。
mysql> ROLLBACK;
# あれっ、rollbackされないぞ。。
mysql> SELECT * FROM user;
+------+--------+
| id | name |
+------+--------+
| 1 | sato |
| 2 | suzuki |
+------+--------+
MySQLは自動コミットモードがデフォルト
調べてみると、MySQLはautocommit（自動コミットモード）がデフォルトのようです。さきほどの例だと、DELETE文実行後に自動でCOMMITされたため、ROLLBACKが効かなかったのです。
autocommitが有効化されているかどうかは、以下で確認できます。
# 1: 有効化, 0: 無効化
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
自動コミットモードを無効化する
方法は2つあります。
- 自動コミットモード自体を無効化する
-
BEGINを使用する
自動コミットモード自体を無効化する
セッション内で自動コミットモード自体を無効化したい場合、以下で設定できます。
SET autocommit=0;
BEGINを使用する
トランザクション開始時に使用することで、トランザクションを制御できます。
# トランザクションを開始する。
mysql> BEGIN;
mysql> SELECT * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+------+-----------+
mysql> DELETE FROM user WHERE id = '3';
mysql> ROLLBACK;
# ロールバックされている。
mysql> SELECT * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+------+-----------+
BEGINの注意点として、COMMITやROLLBACKでトランザクションを終了すると、自動コミットモードはもとに戻ります。
以下のように、1回目のROLLBACKで自動コミットモードはもとに戻るので、2回目のROLLBACKは無効になります。ですので、トランザクションごとにBEGINを利用する必要があります。
mysql> BEGIN;
mysql> SELECT * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+------+-----------+
mysql> DELETE FROM user WHERE id = '3';
mysql> ROLLBACK;
# ロールバックされている。
mysql> SELECT * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+------+-----------+
mysql> INSERT INTO user VALUES ('4','tanaka');
mysql> ROLLBACK;
# ロールバックされない。。
mysql> SELECT * FROM user;
+------+-----------+
| id | name |
+------+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
| 4 | tanaka |
+------+-----------+
Discussion