データベース・SQL

メタデータロックとDDL

ただのメモ。全ては MySQLのメタデータロックについて にわかりやすく書いてある。

メタデータロック

MySQLはデータベースオブジェクトへの同時アクセスを管理し、一貫性を確保するために、オブジェクトのメタデータに対して取得するロック(メタデータロック)があります。

(略)

たとえば、トランザクション内で参照や更新を行ったテーブルに対して、DDLでの変更が行われると一貫性が失われてしまいます。そのため、DDLの操作を防止するようそのトランザクション内でアクセスしたテーブルに対してメタデータロックを取得し、ロックを取得している間に実行されたDDLは待機します。そのトランザクションが終了するとメタデータロックを解放します。

引用: MySQLのメタデータロックについて

DDL実行時の注意点

MySQL 8.0 Reference Manual :: 15.12.1 Online DDL Operations をみてオンラインで実行可能だと安心して本番で実行すると痛い目に合う可能性があるので注意。

DDL を実行するテーブルがトランザクション内で参照されていると、そのトランザクションが終了するまでメタデータロック待ちとなる。さらに、DDL待機中は後続の他のセッションからのクエリもロック待ちになる。

session 1session 2session 3
BEGIN;
SELECT * FROM table_a;
ALTER TABLE ~;
waiting
SELECT * FROM table_a;
waiting

トランザクションが長びくと、DDLだけでなく、そのテーブルに対するクエリがロック待ちでどんどん溜まっていく。あと、「あらゆるAPIから参照される」みたいな参照頻度が恐ろしく高いテーブルだったりすると、どんどんクエリが溜まってしまう。

下記のQiitaの著者は4行しかないテーブルにALTER TABLE 実行したらWaiting for table metadata lockが溢れてMySQLが応答しなくなりサービスダウンしたそう。(めっちゃ参照されるテーブルだったらしい)

実際に実行したら結果が帰ってこない。あれ?って思ってたらWaiting for table metadata lockが溢れてMySQLが応答しなくなりサービスがダウンしちゃいました。

サービス稼働中にMySQLでALTER TABLEしたら Waiting for table metadata lock が溢れて死んだ

ローカルで 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 も動き出す。

参考

イチオシ記事

1

フリーランスエンジニアに興味があるけど、「なんとなく不安」、「自分だと単価いくら?」、「どんな案件がある?」といった不安や疑問をお持ちの方は多いのではないでしょうか!? このような方はフリーランスエー ...

2

はじめまして、フリーランスエンジニアのヨノと申します。 Twitterやブログ記事などで と言っている人を見かけますが、むむむ.....本当でしょうか? こういった発言に対して思うことがあるので「フリ ...

3

ネット上で色々言われているフリーランスエンジニア....。「本当はどうなの?」と思っている人は多いでしょう。 そこで本記事ではフリーランスエンジニア5年生の私が、ネット上の意見も引用しながら実態を解説 ...

-データベース・SQL