本番運用中のMySQLテーブルのリプレース(RENAME)は安全か？

MAGAZINE

ルーターマガジン

インフラ/運用

本番運用中のMySQLテーブルのリプレース(RENAME)は安全か？

2021.12.10

Pocket

こんにちは、学生アルバイトのkyonoです。

冬に入る熱々の風呂は堪らないものです。入浴のしばらく前に部屋の窓を開けて体を敢えて冷やし、着替えも箪笥から出しておき、ビールやハイボールなど炭酸系の酒が冷蔵庫に眠っていることを確認しておきます。冬ならレンジで燗酒なども好いものです。しょうもない安い酎ハイでごまかしてはいけません。アルバイトで稼いだお金をパッと使う時です。

骨の髄まで凍みたらいよいよ風呂です。ざんぶと体を一気に浸せば、表皮は直ぐに熱を帯び、やがてぱきり、ぱきりと体の内側が溶けゆき熱が染み込んでくるそこがまさにピーク。エクスタシーは桜のように儚いのです。

風呂から上がり、熱を帯びた体を再び冬の冷気に晒します。寒暖が体の中で交わる様にじっと浸ると、まるで目の前の直線がくんにゃりと歪むような感覚。イケナイ脳内物質が出てきているようです。

体が冷めないうちに服を着ます。パンツを履いて、脳内物質に抗えずベッドに倒れ込むとパンツの中からぐしゃりという感覚。恐る恐る中を見るとぐしゃぐしゃに潰れたカメムシがおりました。開けた窓から入ってきたのでしょうか。風呂上がりの白磁の肌にカメムシ拓。私はあまりの仕打ちに堪らなくなって涙を流しました。

やりたいこと

本番稼働中のMySQLデータベース（バージョン14.14）のテーブルの構造を更新したい。

古いテーブルと新しいテーブルを用意して、RENAMEによってそれらを入れ替える方法をとる。

しかし、そのテーブルを利用しているアプリケーションは多数あるので、一々それらをすべて止める訳にはいかない。そこでLOCKを用いてRENAMEを行うセッションのみが排他的に操作できる状況を作ってからRENAMEする。

LOCKを試してみる

先ずはLOCKで遊んでみましょう。

dummiesテーブルを作り、テーブルの構造は以下のようにします。

mysql> DESC dummies;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int unsigned | NO   | PRI | NULL              | auto_increment    |
| text       | varchar(32)  | YES  |     | NULL              |                   |
| created_at | datetime     | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

LOCKにはREADロックとWRITEロックがあり、前者はすべてのクライアントが読み込みしかできないように制限し、後者はロックをとったクライアント以外のクライアントが読み込みも書き込みもできないようにするものです。

READロック

dummiesテーブルにREADロックを掛けて、読み込みと書き込みをしてみましょう。

mysql> LOCK TABLES dummies READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM dummies;
+----+---------+---------------------+
| id | text    | created_at          |
+----+---------+---------------------+
|  1 | dummies | 2021-12-07 11:36:56 |
+----+---------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);
ERROR 1099 (HY000): Table 'dummies' was locked with a READ lock and can't be updated

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

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);
Query OK, 1 row affected (0.01 sec)

このように読み込みしかできなくなっています。UNLOCK後には書き込みができるようになります。

またREADロックを掛けたまま別のウィンドウからmysqlクライアントを起動して、そこから読み込みと書き込みをしてみましょう。別の窓を開いて、下のような操作をしてみます。

mysql> SELECT * FROM dummies;
+----+-------+---------------------+
| id | text  | created_at          |
+----+-------+---------------------+
|  1 | dummy | 2021-12-07 12:17:22 |
|  2 | dummy | 2021-12-07 12:20:17 |
|  3 | dummy | 2021-12-07 12:20:21 |
+----+-------+---------------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);

上の状態で止まってしまいました。どうやら読み込みは問題ないようですが、書き込みが止まっているようです。この状態で元のクライアントでUNLOCKすると、以下のように表示されて書き込みが完了しました。

Query OK, 1 row affected (1 min 10.51 sec)

ロック中に行われた操作は拒否されるのではなく、操作を行ったクライアント側でペンディングされていて、UNLOCKされるとその瞬間に操作が再開されるようです。

WRITEロック

今度はWRITEロックを掛けてみましょう。

mysql> LOCK TABLES dummies WRITE;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM dummies;
+----+-------+---------------------+
| id | text  | created_at          |
+----+-------+---------------------+
|  1 | dummy | 2021-12-07 12:17:22 |
+----+-------+---------------------+
1 row in set (0.00 sec)

WRITEロックを掛けたクライアントからは読み込みも書き込みも行えます。

今度はロックを掛けたまま別のクライアントから操作してみましょう。別の窓を開いて、下のような操作をしてみます。

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);

WRITEロックがかかっている間、操作がペンディングされてこの状態で進みませんでした。元のクライアントでUNLOCKすると、Query OK, 1 row affected (55.80 sec)と表示されて操作が完了しました。読み込みであるSELECT * FROM dummies;に対しても全く同じ動作をしました。

LOCKを取得していないテーブルの操作

LOCKの際にはテーブル名を指定しますが、それ以外のテーブルの動作はどうなるのでしょうか。ここではdummiesテーブルと全く同じ構造を持つmummiesテーブルを作り、検証しました。

dummiesにREADロックを掛けた状態でmummiesからSELECTしてみました。

mysql> LOCK TABLES dummies READ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mummies;
ERROR 1100 (HY000): Table 'mummies' was not locked with LOCK TABLES

このようにエラーが発生しました。これはINSERTをする際にも、またWRITEロックの際にも同じエラーが出ました。つまりロックを掛けたクライアントは、ロックを取得したテーブル以外に対してあらゆる操作が行えません。

次にdummiesにREADロックを掛けたまま、別のクライアントからmummiesの読み込みをしてみます。

mysql> SELECT * FROM mummies;
+----+-------+---------------------+
| id | text  | created_at          |
+----+-------+---------------------+
|  1 | dummy | 2021-12-07 12:45:15 |
|  2 | dummy | 2021-12-07 13:04:13 |
|  3 | dummy | 2021-12-07 13:05:13 |
+----+-------+---------------------+
3 rows in set (0.00 sec)

問題なく読み込めました。これはINSERTをする際にも、またWRITEロックの際にも問題ありませんでした。つまりロックの掛かっていないテーブルは、ロックを取得したクライアント以外からは通常どおりの操作が行えます。

LOCKの動作のまとめ

以上の実験の結果を、下の表に纏めました。

		READロック		WRITEロック
		読み込み	書き込み	読み込み	書き込み
ロックを掛けたクライアント	ロックを取得したテーブル	◯	エラー	◯	◯
ロックを掛けたクライアント	それ以外のテーブル	エラー	エラー	エラー	エラー
それ以外のクライアント	ロックを取得したテーブル	◯	保留	保留	保留
それ以外のクライアント	それ以外のテーブル	◯	◯	◯	◯

本番環境で利用中のテーブルをLOCKしてRENAMEする

さていよいよ本題です。

実験条件

テーブル構造は前章で用いたdummies、mummiesと同じです。

本番環境を再現するため、active_recordで1秒間に1回dummiesテーブルにレコードが挿入されます。そのスクリプトは下の通りです。

require 'active_record'

host = 'localhost'
user = 'root'
pass = ''
dbname = 'test_lock'
ActiveRecord::Base.logger = Logger.new(STDOUT) unless ARGV.include?('test')

ActiveRecord::Base.establish_connection(
        :adapter  => 'mysql2',
        :database => dbname,
        :host     => host,
        :username => user,
        :charset => 'utf8mb4',
        :encoding => 'utf8mb4',
        :collation => 'utf8mb4_general_ci',
        :password => pass
)

Time.zone_default =  Time.find_zone! 'Tokyo'
ActiveRecord::Base.default_timezone = :local

class Dummy < ActiveRecord::Base; end

i = 0
loop do
  Dummy.create(text: "dummy#{i}")
  i += 1
  sleep 1
end

その状態でRENAMEを行うとき、LOCKの有無によって書き込みの動作がどのようになるかを観察します。 RENAMEを行うためのクエリは以下の通りです。

RENAME TABLES dummies TO rummies, mummies TO dummies, rummies TO mummies;

LOCKを掛ける場合

Rubyスクリプトを回したまま、以下のようにクエリを投げました。

mysql> LOCK TABLE dummies WRITE, mummies WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLES dummies TO rummies, mummies TO dummies, rummies TO mummies;
Query OK, 0 rows affected (0.04 sec)

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

結果は以下のようになりました。

mysql> SELECT * FROM mummies;
+----+--------+---------------------+
| id | text   | created_at          |
+----+--------+---------------------+
|  1 | dummy0 | 2021-12-07 14:27:56 |
|  2 | dummy1 | 2021-12-07 14:27:58 |
|  3 | dummy2 | 2021-12-07 14:27:59 |
|  4 | dummy3 | 2021-12-07 14:28:00 |
|  5 | dummy4 | 2021-12-07 14:28:01 |
|  6 | dummy5 | 2021-12-07 14:28:02 |
+----+--------+---------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM dummies;
+----+---------+---------------------+
| id | text    | created_at          |
+----+---------+---------------------+
|  1 | dummy6  | 2021-12-07 14:28:03 |
|  2 | dummy7  | 2021-12-07 14:28:21 |
|  3 | dummy8  | 2021-12-07 14:28:22 |
|  4 | dummy9  | 2021-12-07 14:28:23 |
|  5 | dummy10 | 2021-12-07 14:28:24 |
|  6 | dummy11 | 2021-12-07 14:28:25 |
+----+---------+---------------------+
6 rows in set (0.00 sec)

テーブルを入れ替えた際に、INSERTの対象も切り替わっていることがわかります。

LOCKを掛けない場合

次はLOCKを掛けずにRENAMEしてみます。

mysql> RENAME TABLES dummies TO rummies, mummies TO dummies, rummies TO mummies;
Query OK, 0 rows affected (0.04 sec)

結果は以下の通りです。

mysql> SELECT * FROM mummies;
+----+--------+---------------------+
| id | text   | created_at          |
+----+--------+---------------------+
|  1 | dummy0 | 2021-12-07 14:43:08 |
|  2 | dummy1 | 2021-12-07 14:43:09 |
|  3 | dummy2 | 2021-12-07 14:43:10 |
|  4 | dummy3 | 2021-12-07 14:43:11 |
|  5 | dummy4 | 2021-12-07 14:43:12 |
|  6 | dummy5 | 2021-12-07 14:43:13 |
|  7 | dummy6 | 2021-12-07 14:43:14 |
+----+--------+---------------------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM dummies;
+----+---------+---------------------+
| id | text    | created_at          |
+----+---------+---------------------+
|  1 | dummy7  | 2021-12-07 14:43:15 |
|  2 | dummy8  | 2021-12-07 14:43:16 |
|  3 | dummy9  | 2021-12-07 14:43:17 |
|  4 | dummy10 | 2021-12-07 14:43:18 |
|  5 | dummy11 | 2021-12-07 14:43:19 |
+----+---------+---------------------+
5 rows in set (0.00 sec)

あれ、わざわざLOCKしなくても問題ない？

トランザクションの途中でRENAMEする場合

こうなったら意地でも不安定なリプレースの方法を見つけたい。そこでトランザクションの途中にリプレースの操作が起きた場合を検証してみましょう。

トランザクションとは

データベースの信頼性を保つために必要な要素として、原子性、一貫性、分離性、持続性があります（参照）。トランザクションはこのうち原子性を保つためのものです。原子性とは、それ以上分割できない処理の一連（トランザクション）に含まれる処理がすべて正常に行われるか、ロールバックによって無効になるかのどちらかである性質のことです（参照）。つまりトランザクションが処理A → 処理B → 処理C というように構成されていた場合、処理Cまで正常に行われるか、あるいは全ての処理が全く行われていない状態に戻るかのどちらかになることが保証されます。

普通はSQL側でうまいことやってくれるので何も気にする必要はありませんが、ユーザ側が複数のSQL文に対して原子性を保証したい場合があります。 MySQLにはこの為の仕組みが用意されています。例えばINSERTしてからSELECTする動作を一つのトランザクションとする場合は以下のようになります。 BEGINでトランザクションの記述を開始し、COMMITした時点でようやく操作が確定します。

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

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dummies;
+----+-------+---------------------+
| id | text  | created_at          |
+----+-------+---------------------+
|  1 | dummy | 2021-12-07 15:56:45 |
+----+-------+---------------------+
1 row in set (0.00 sec)

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

上の操作ではSELECT時に結果が表示されていますが、実際にはINSERTは完了していません。 COMMITする前に他のMySQLクライアントを立ち上げてそこからSELECTしてみると、以下のように処理が完了していないことがわかります。

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

処理を取り消すにはROLLBACKします。下のようにROLLBACKによってトランザクションの処理が取り消され、INSERTがされていない状態に戻ります。

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

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM dummies;
+----+-------+---------------------+
| id | text  | created_at          |
+----+-------+---------------------+
|  1 | dummy | 2021-12-07 16:04:05 |
+----+-------+---------------------+
1 row in set (0.00 sec)

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

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

トランザクションの途中で別のクライアントからRENAMEする

さて、トランザクションの実行途中に突然他のユーザがテーブルをRENAMEしてしまったらどうなるでしょう。以下のように実験してみました。

先ずは書き込みをするクライアントでトランザクションの操作を書きます。

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

mysql> INSERT INTO dummies VALUES(DEFAULT, "dummy", DEFAULT);
Query OK, 1 row affected (0.00 sec)

次に別のクライアントを立ち上げ、テーブル名を変更する操作を行います。この操作に対しては応答がなく、処理がペンディングされていることがわかります。

mysql> RENAME TABLES dummies TO rummies, mummies TO dummies, rummies TO mummies;

最後に元のクライアントでCOMMITします。

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

するとテーブルのRENAMEに対しても応答がありました。

mysql> RENAME TABLES dummies TO rummies, mummies TO dummies, rummies TO mummies;
Query OK, 0 rows affected (1 min 6.85 sec)

実行結果は以下のようになりました。

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

mysql> SELECT * FROM mummies;
+----+-------+---------------------+
| id | text  | created_at          |
+----+-------+---------------------+
|  2 | dummy | 2021-12-07 16:10:44 |
+----+-------+---------------------+
1 row in set (0.00 sec)

明示したトランザクションにあるdummiesテーブルへの書き込みが行われ、その後にRENAMEによってそのテーブルがmummiesになったことがわかります。

結論

リプレースは基本的に安全。

冬でも網戸はちゃんと確認。

Pocket

CONTACT

お問い合わせ・ご依頼はこちらから