ただのメモ。全ては MySQLのメタデータロックについて にわかりやすく書いてある。
メタデータロック
MySQLはデータベースオブジェクトへの同時アクセスを管理し、一貫性を確保するために、オブジェクトのメタデータに対して取得するロック（メタデータロック）があります。
（略）
たとえば、トランザクション内で参照や更新を行ったテーブルに対して、DDLでの変更が行われると一貫性が失われてしまいます。そのため、DDLの操作を防止するようそのトランザクション内でアクセスしたテーブルに対してメタデータロックを取得し、ロックを取得している間に実行されたDDLは待機します。そのトランザクションが終了するとメタデータロックを解放します。
引用: MySQLのメタデータロックについて
DDL実行時の注意点
MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations をみてオンラインで実行可能だと安心して本番で実行すると痛い目に合う可能性があるので注意。
DDL を実行するテーブルがトランザクション内で参照されていると、そのトランザクションが終了するまでメタデータロック待ちとなる。さらに、DDL待機中は後続の他のセッションからのクエリもロック待ちになる。
| session 1 | session 2 | session 3 |
|---|---|---|
| BEGIN; | ||
| SELECT * FROM table_a; | ||
| ALTER TABLE ~; | ||
| waiting | ||
| SELECT * FROM table_a; | ||
| waiting |
トランザクションが長びくと、DDLだけでなく、そのテーブルに対するクエリがロック待ちでどんどん溜まっていく。あと、「あらゆるAPIから参照される」みたいな参照頻度が恐ろしく高いテーブルだったりすると、どんどんクエリが溜まってしまう。
下記のQiitaの著者は４行しかないテーブルにALTER TABLE 実行したらWaiting for table metadata lockが溢れてMySQLが応答しなくなりサービスダウンしたそう。（めっちゃ参照されるテーブルだったらしい）
実際に実行したら結果が帰ってこない。あれ？って思ってたら
サービス稼働中にMySQLでALTER TABLEしたら Waiting for table metadata lock が溢れて死んだWaiting for table metadata lockが溢れてMySQLが応答しなくなりサービスがダウンしちゃいました。
ローカルで Waiting for table metadata lock を体験
検証はMySQL公式サンプルデータ sakila で行いました。
シナリオ
- session1：トランザクション内で
countryテーブルを参照 - session2：ALTER TABLE 実行
- session3：
countryテーブルを参照
session1 トランザクション内で country テーブル参照
mysql> BEGIN;
Query OK, 0 rows affected (0.07 sec)
mysql> SELECT * FROM country LIMIT 5;
+------------+----------------+---------------------+
| country_id | country | last_update |
+------------+----------------+---------------------+
| 1 | Afghanistan | 2006-02-15 04:44:00 |
| 2 | Algeria | 2006-02-15 04:44:00 |
| 3 | American Samoa | 2006-02-15 04:44:00 |
| 4 | Angola | 2006-02-15 04:44:00 |
| 5 | Anguilla | 2006-02-15 04:44:00 |
+------------+----------------+---------------------+
5 rows in set (0.00 sec)session2 ALTER TABLE 実行
country テーブルに memo カラムを追加する
mysql> ALTER TABLE country ADD COLUMN memo varchar(255);全然進まない....
session3 country テーブル参照
別のセッションから country テーブルを参照。
mysql> SELECT * FROM country WHERE country_id = 10;こちらも全然進まない...
metadata lock 確認
country テーブルに関するメタデータロックの状況を performance_schema.metadata_locks から確認。
mysql> select * from performance_schema.metadata_locks WHERE OBJECT_NAME = 'country'\G
*************************** 1. row *************************** // <- session1 のトランザクション
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sakila
OBJECT_NAME: country
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 105553138323520
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5981
OWNER_THREAD_ID: 58
OWNER_EVENT_ID: 39
*************************** 2. row *************************** // <- session2 の ALTER TABLE
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sakila
OBJECT_NAME: country
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 105553138337264
LOCK_TYPE: SHARED_UPGRADABLE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5981
OWNER_THREAD_ID: 50
OWNER_EVENT_ID: 49
*************************** 3. row *************************** // <- session2 の ALTER TABLE
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sakila
OBJECT_NAME: country
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 105553139108672
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: mdl.cc:3753
OWNER_THREAD_ID: 50
OWNER_EVENT_ID: 50
*************************** 4. row *************************** // <- session3 の SELECT
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sakila
OBJECT_NAME: country
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 105553139149680
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:5981
OWNER_THREAD_ID: 51
OWNER_EVENT_ID: 36
4 rows in set (0.00 sec)session1 のトランザクションを終了させる
session1 で COMMIT してトランザクションを終了させると、session2, 3 も動き出す。
