本来あるべきではないのですが、どうしても本番DBで直接クエリをたたいてデータを修正しないといけないことってあります。当然、ミスってしまうと大問題なので慎重の上に慎重に作業をするわけですが、慎重に上乗せする方法としてトランザクションを使うといいことを同僚に教えてもらいました。多分有名な話だと思いますが、私は知らなかったのでメモしておきます。
ざっくり書くとこんな話です。
- 本番クエリはトランザクションの中で行う。
- クエリを実行しても、コミットするまでは実際には本番DBには反映されない。
- でもどうやって反映されるかはトランザクションの中では見ることが出来る。
- クエリをミスってればロールバックすれば本番影響なし。
- クエリが正しいことを確認できればコミットで作業完了。
実際の作業
試した環境は MySQL 8.0.34 です。
以下のようなテーブルがあったとします。
mysql> select * from users;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | taro | male |
| 2 | jiro | female |
| 3 | hanako | female |
+------+--------+--------+
3 rows in set (0.00 sec)
jiro の gender を female から male に修正したいとします。
トランザクションを開始して、クエリを実行します。このとき、誤って WHERE 句をつけ忘れたとします。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- ここからトランザクションの中
mysql> update users set gender="male";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3 Changed: 2 Warnings: 0
mysql> select * from users;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | taro | male |
| 2 | jiro | male |
| 3 | hanako | male |
+------+--------+--------+
3 rows in set (0.00 sec)
jiro だけではなく hanako も male になってしまいました。しかしここはトランザクションの中です。トランザクションの外ではまだ何も起こっていません。
-- トランザクションの外
mysql> select * from users;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | taro | male |
| 2 | jiro | female |
| 3 | hanako | female |
+------+--------+--------+
3 rows in set (0.00 sec)
トランザクションをロールバックします。当然レコードは元のままです。
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from users;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | taro | male |
| 2 | jiro | female |
| 3 | hanako | female |
+------+--------+--------+
3 rows in set (0.00 sec)
改めてトランザクションを開始してクエリを実行します。今度は WHERE 句を付けて、正しいレコードになることを確認してコミットします。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update users set gender="male" where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- これはトランザクションの中
mysql> select * from users;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | taro | male |
| 2 | jiro | male |
| 3 | hanako | female |
+------+--------+--------+
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
-- こちらはトランザクションの外
mysql> select * from users;
+------+--------+--------+
| id | name | gender |
+------+--------+--------+
| 1 | taro | male |
| 2 | jiro | male |
| 3 | hanako | female |
+------+--------+--------+
3 rows in set (0.00 sec)
なお、当然のことながらトランザクションが適切に分離されていることが前提です。ダーティリードが発生する環境ではコミット前にトランザクション外で更新値が読み取れてしまいますので本番影響があります(ロールバックすれば戻りますが)。