2024-12-15 19:22:38
MySQL で ALTER TABLE によるメタデータロックによって後続のプロセスがロック解放待ちになるのを再現してみる
はじめに
こちらの記事の出来事を再現してみます。恐らくこういうことだろうという再現です。
（大変勉強になりました。共有ありがとうございます！）
小さなテーブルへのALTER TABLE一撃でサービス停止。そこから学んだ惨事を繰り返さないためのルール作り
私は以前に、「カラム追加している最中って、INSERTしたりUPDATEってできるんか・・・？」と思い調べた結果、「オンラインDDLとやらでうまくやってくれるのか」という結論のもと今日まで生きていきました。
どうやら私の認識していたオンラインDDLの解像度は低かったようでした。
そもそも「メタデータロックって何？」状態です。そして今も分かってないです。
テーブルとかデータを準備する
MySQL やデータベースは準備済みということでお願いします。
テーブル作成
CREATE TABLE `target` (
`target_id` INT NOT NULL DEFAULT '0',
`description` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`target_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;データを入れておく
INSERT INTO `target` VALUES (1, "example 1"), (2, "example 2");再現用のSQLを準備する
記載した順番で実行してきます。
ロックなしの処理時間が長い SELECT
SLEEP(10) を使って、頻繁に参照される状況を再現しているつもりです。
select SLEEP(10), target.* FROM target WHERE target_id = 1;カラム追加をする ALTER TABLE
件の記事とほぼ同じ ALTER TABLE です。
この SQL によってメタデータロックされます。
ALTER TABLE `target` ADD COLUMN `added_column` bit(1) DEFAULT b'0' COMMENT 'コメント';ロックなし SELECT
メタデータロックされたあとに続く参照を再現するための SELECT です。
select target.* FROM target;ロック状態を確認する SELECT
この SELECT でロック状態を確認します。
SELECT COMMAND, STATE, INFO FROM information_schema.PROCESSLIST WHERE DB = "exampledb";実行した結果のロック状態を見る
再現している最中のGifです。左上からZ字の順番で操作しています。思ったよりも小さいので新しいタブで開くといいかもしれないです。
以下のSQLでロック状況を確認しています。
ALTER TABLE の実行開始後に実行開始した select target.* FROM target; の STATE が確かに Waiting for table metadata lock になっています。
mysql> SELECT COMMAND, STATE, INFO FROM information_schema.PROCESSLIST WHERE DB = "exampledb";
+---------+---------------------------------+-------------------------------------------------------------------------------------------+
| COMMAND | STATE | INFO |
+---------+---------------------------------+-------------------------------------------------------------------------------------------+
| Query | Waiting for table metadata lock | ALTER TABLE `target` ADD COLUMN `added_column` bit(1) DEFAULT b'0' COMMENT 'コメント' |
| Query | Waiting for table metadata lock | select target.* FROM target |
| Query | executing | SELECT COMMAND, STATE, INFO FROM information_schema.PROCESSLIST WHERE DB = "exampledb" |
| Query | User sleep | select SLEEP(10), target.* FROM target WHERE target_id = 1 |
+---------+---------------------------------+-------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)件の記事内での出来事ではキャッシュ機構の不具合によって、対象テーブルが頻繁に参照されている状態になっていたそうです。この頻繁を SLEEP(10) で再現しているつもりです。
頻繁に参照されている状態で ALTER TABLE による排他メタデータロック(※1)がかかり 、後続のユーザアクセスで Waiting for table metadata lock が大量に起きてしまったということだと認識してます。
※1: 「排他メタデータロック」という表記が正しいか分かりません。公式サイトでは見つけられませんでしたが、Waiting for table metadata lock は LOCK TABLES target lock_type READ; を使っても表記されるので「共有メタデータロック」もあると思っています。
おわり
「メタデータロックって何？」状態ですが、再現することで（したつもりによって）こういう事が起きるという知識が身につきました。
この記事を書いているときに占有ロックと排他ロックが別物だと知りました。終わらないです。
参考リンク
↓↓ メタデータロックって何を調べたときのサイト
https://gihyo.jp/article/2022/09/mysql-rcn0180
↓↓ インテンションロックって何を調べたときの公式サイト
InnoDB では、行ロックとテーブルロックの共存を許可する複数粒度ロックがサポートされています。 たとえば、LOCK TABLES ... WRITE などのステートメントは、指定されたテーブルに対して排他ロック (X ロック) を取得します。 複数の粒度レベルでロックするには、InnoDB で intention locks を使用します。 インテントロックは、トランザクションが後でテーブルの行に必要とするロックのタイプ (共有または排他) を示すテーブルレベルのロックです。 インテントロックには、次の 2 種類があります:
↓↓ テーブルを明示的にロックするにはどうすればいいか調べたときの公式サイト
https://dev.mysql.com/doc/refman/8.0/ja/lock-tables.html
↓↓ show processlist って絞り込めないのか調べたときのサイト
https://blog.bassbone.tokyo/archives/882
完全に自分用のメモ
調べている途中で使っていたSQLのメモです。
占有ロックをする SELECT
select target.* FROM target WHERE target_id = 1 FOR UPDATE;共有ロックをする SELECT
select target.* FROM target WHERE target_id = 1 LOCK IN SHARE MODE;明示的にロックする
LOCK TABLES `target` lock_type READ;
UNLOCK TABLES;LOCK TABLES `target` lock_type WRITE;
UNLOCK TABLES;