アソビュー！ Advent Calendar 2024 12日B面
はじめに
こんにちは。アソビューでエンジニアをやっています。小池です。
最近、MySQLのテーブルにインデックス追加 (ALTER TABLE) をしたのですが、その際にメタデータロックを発生させてしまい、一時的な障害につながりました。
今回の記事はその際の学びと再発防止案として、メタデータロックにタイムアウトを設定して安全にALTER TABLEを実行する話です。
よろしくお願いします！
ALTER TABLEとメタデータロック
まず、メタデータロックについて簡単に説明し、今回起こった事象について整理していきます。
メタデータロック
MySQLのメタデータロックはテーブルの中身ではなく、テーブル構造に対して行われるロックで、トランザクション内でテーブル構造の一貫性を保つために行われます。
SELECTなどのDMLは共有メタデータロックを行います。DML同士、テーブル構造が変更されないので同時に実行可能です。
一方、ALTER TABLEなどのDDLは排他メタデータロックを行います。正確には、実行の開始時と終了時に排他メタデータロックを行い、実行中は共有メタデータロックが行われます。
排他メタデータロックは排他的にロックを取得するため、DMLの共有メタデータロックが存在する場合はその共有ロックが解放されるまで待機します。
事象の整理
上記を踏まえて今回の事象を整理してみます。
- ALTER予定のテーブルに対してロングトランザクション (SELECT文) が動いていました。前述の通り、共有メタデータロックが発生しています。
- 同テーブルに対してALTER TABLE (インデックス追加) を実行しました。これは、排他メタデータロックを取得しようとし、1のトランザクションが終わるまで待機します。
- 同テーブルに対する後続のトランザクションは、2のALTER TABLEの排他メタデータロックが取得されるまで待機し、トランザクションはタイムアウトして失敗していきます。(障害となる事象)
メタデータロックをタイムアウトさせて考慮する
この事象を回避する方法として、ALTER TABLEのメタデータロックをタイムアウトさせる方法があります。
SET SESSION lock_wait_timeoutを利用しALTER TABLEを適切にタイムアウトさせれば、後続のトランザクションが詰まることを予防できます。
※ lock_wait_timeoutのデフォルトの長さは1日で設定されています。
実際にやってみる
ここからは実際に動作を確認していきたいと思います。今回は以下の環境で試しています。
- MySQL 8.0 (Docker)
- DBeaver (DBクライアント)
テーブルなどを準備
まずは利用するテーブルなどを準備します。
-- データベース作成 create database d1; -- テーブル作成 create table d1.t1 ( id int auto_increment, name varchar(255) not null, primary key (id) ); -- データを1件登録しておく insert into d1.t1 (name) values ('koike');
以下の状態で準備完了です。
> select * from d1.t1 id|name | --+-----+ 1|koike| 1 row(s) fetched.
メタデータロックを発生させる
意図的にロングトランザクションを発生させて、メタデータロックを再現させます。
1. SLEEPでt1テーブルへ向けてロングトランザクションを発生させます
> select sleep(300) from d1.t1
2. t1テーブルへのALTER TABLEを実行します
> alter table d1.t1 add index idx_name (name)
ALTER TABLEはすぐ完了せず待機します。
3. t1テーブルへSELECTを実行します
> select * from d1.t1
SELECT結果も返ってこず待機します。
プロセスを確認すると、メタデータロックが発生していることが確認できます。
> show processlist Id|User |Host |db|Command|Time|State |Info | --+---------------+----------------+--+-------+----+-------------------------------+----------------------------------------------------------------------------------------------------+ 5|event_scheduler|localhost | |Daemon | 905|Waiting on empty queue | | 8|root |172.19.0.1:53638| |Sleep | 723| | | 9|root |172.19.0.1:53642| |Sleep | 130| | | 10|root |172.19.0.1:53254| |Sleep | 585| | | 11|root |172.19.0.1:48736| |Query | 72|User sleep |/* ApplicationName=DBeaver 24.2.5 - SQLEditor <Console> */ select sleep(300) from d1.t1¶LIMIT 0, 200| 12|root |172.19.0.1:46858| |Query | 57|Waiting for table metadata lock|/* ApplicationName=DBeaver 24.2.5 - SQLEditor <Console> */ alter table d1.t1 add index idx_name (nam| 13|root |172.19.0.1:53998| |Query | 37|Waiting for table metadata lock|/* ApplicationName=DBeaver 24.2.5 - SQLEditor <Console> */ select * from d1.t1¶LIMIT 0, 200 | 14|root |172.19.0.1:34482| |Query | 0|init |/* ApplicationName=DBeaver 24.2.5 - SQLEditor <Console> */ show processlist | 8 row(s) fetched.
今回のポイントとして、2のALTER TABLEのクエリに加えて、3のSELECTもメタデータロックによって待機していることが確認できます。
また、metadata_locksテーブルでもメタデータロックに関する情報を確認することができます。
> SELECT
OBJECT_TYPE
, OBJECT_SCHEMA
, OBJECT_NAME
, LOCK_TYPE
, LOCK_DURATION
, LOCK_STATUS
, OWNER_THREAD_ID
FROM
performance_schema.metadata_locks
WHERE
OBJECT_NAME='t1'
AND LOCK_STATUS='GRANTED'
OBJECT_TYPE|OBJECT_SCHEMA|OBJECT_NAME|LOCK_TYPE |LOCK_DURATION|LOCK_STATUS|OWNER_THREAD_ID|
-----------+-------------+-----------+-----------------+-------------+-----------+---------------+
TABLE |d1 |t1 |SHARED_READ |TRANSACTION |GRANTED | 48|
TABLE |d1 |t1 |SHARED_UPGRADABLE|TRANSACTION |GRANTED | 49|
2 row(s) fetched.
THREAD_ID: 48がSLEEPのロック情報で、共有ロック (SHARED_READ) が行われていることが確認できます。
また、THREAD_ID: 49がALTER TABLEのロック情報で、排他ロックへアップグレード可能な共有ロック (SHARED_UPGRADABLE) が行われています。排他ロックを取得しようと待ちになっている状態です。
ALTER TABLEをタイムアウトさせる
では、lock_wait_timeoutを1秒に変更し、ALTER TABLEがタイムアウトするようして確認してみたいと思います。
1. 先ほどと同様にSLEEPでt1テーブルへ向けてロングトランザクションを発生させます
> select sleep(300) from d1.t1
2. lock_wait_timeoutを変更してからALTER TABLEを実行します
> set session lock_wait_timeout=1 0 row(s) modified. > alter table d1.t1 add index idx_name (name) SQLエラー [1205] [40001]: Lock wait timeout exceeded; try restarting transaction
タイムアウトによってALTER TABLEが失敗することが確認できました。
ALTER TABLEが適切にタイムアウトしたことによって、後続のトランザクションはメタデータロックすることなく実行できます。
> select * from d1.t1 id|name | --+-----+ 1|koike| 1 row(s) fetched.
注意事項
上記でメタデータロックによってクエリが詰まることの防止はできますが、set session lock_wait_timeout=1のようにタイムアウトを短くした場合、長時間のメタデータロックが頻発している場合はそもそもALTER TABLEが成功しなくなってしまう可能性も高いです。
そのため、長時間のメタデータロックが発生しないような設計や改善をしていくことも大切です。
おわりに
今回はMySQLのメタデータロックとALTER TABLEについて書きました。この手の話は他にも記事がたくさんありますが、実際に動かして確認することでより理解を深めることができました。
システムの開発が進む以上、ALTER TABLEは避けては通れない道ですが、クエリが詰まったりすると場合によっては大きな障害につながる場合もあります。少しでも安全に実行していければと思います。
アソビューでは、一緒に働くメンバーを大募集しています！カジュアル面談も実施しておりますので、少しでもご興味をお持ちいただけましたら、ぜひお気軽にご応募ください！ www.asoview.com
