LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

トラストバンクAdvent Calendar 2024

Day 18
@arinc0in株式会社トラストバンク

「メタデータロックって何？」触って学ぶメタデータロックの世界

Last updated at Posted at 2024-12-17

この記事は トラストバンクAdventCalendar2024 18日目の記事になります。
担当は、ふるさとチョイス バックエンドエンジニアの @arinc0 です。

これまで業務の中で特に意識していなかった「メタデータロック」について、理解を深めることができました。
その経験と知識を共有するために、できるだけわかりやすくまとめてみたいと思います。

はじめに

この記事では、実際にコードを実行しながら「メタデータロック」についての理解を深めていきます。

記事の内容を通して、
「メタデータロックという言葉は聞いたことがあるけどよくわからない」という人が、
「メタデータロックってこういうものなんだな」と理解してもらえたらいいなと思ってます。

実行したコードに対しての出力も記載しているので、手を動かさなくても十分理解できると思います。

※ 登場するコードは、MySQL: 5.7.23で実行しています。

1. 事前知識

1-1. ロックの種類

MySQLには大きく分けて２つのロックがあります
- InnoDBロック1
- メタデータロック2

普段意識することや、使ったことが多いのは、InnoDBロックの方かなと思います。

1-2. メタデータロック

MySQLでは、メタデータロックを使用してデータベースオブジェクトへの同時アクセスを管理し、データの一貫性を確保しています。

メタデータロックはテーブルのみではなく以下のものにも適用されます。

  • スキーマ
  • ストアドプログラム(プロシージャ、ファンクション、トリガーなど)
  • テーブルスペース
  • GET_LOCK() 関数で取得されたユーザーロック
  • ロックサービスで取得されたロック

1-3. メタデータロックの種類

MDL(メタデータロック)には大きく分けると以下の２種類あります。

  • 共有MDL
  • 排他MDL

2種類のロックの関係は以下のようにまとめることができます。

共有MDLと排他MDLの関係
共有MDL 排他MDL
共有MDL :white_check_mark: 競合しない :boom: 競合する
排他MDL :boom: 競合する :boom: 競合する
  1. 共有MDLが取得されている場合、共有MDLは取得できる
  2. 共有MDLが取得されている場合、排他MDLはロック取得待ちになる
  3. 排他MDLが取得されている場合、共有MDLはロック取得待ちになる
  4. 排他MDLが取得されている場合、排他MDLはロック取得待ちになる
  5. 排他MDLがロック取得待ち状態の場合、後続のMDL取得もロック取得待ちの状態になる

1-4は上記の関係から読み取ることができます。
5はロックの仕組み上そうなっているらしいです。

今回はこの5つのうち1, 2, 5を実際に検証したいと思います。
(3, 4は長めのDDL3が実行されるテーブルを用意しないといけないため今回は検証しない。)

2. テーブルの準備

PK として id を持つテーブルを作成します。

CREATE TABLE samples (
    id int unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4;

でテーブルを作って、

mysql> show create table samples \G;
*************************** 1. row ***************************
       Table: samples
Create Table: CREATE TABLE `samples` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR:
No query specified

定義はこんな感じで、

INSERT INTO samples VALUES();

1つだけレコードを入れておきます。

3. 共有MDLをかけてみよう！

まずは共有MDLをかけてみます。
共有MDLをかけるには、SELECT, INSERT, UPDATE, DELETE のいずれかを実行します。

  • SELECTは共有MDL(read)を取得
  • それ以外は共有MDL(write)を取得

MDLがかかる期間はトランザクションの終了までです。
普通に実行すると一瞬でMDLが取得 => 解放されます。
そのため、明示的にトランザクションを開始することでロックの期間を伸ばします。

3-1. 共有MDL(read) をかけてみる

トランザクションを開始して、SELECTします。

-- トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)


-- SELECT実行
mysql> select * from samples;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

ロック状態を見ます。
ロック情報は performance_schema.metadata_locks から取得できます。

mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------+---------------+-------------+
| OBJECT_TYPE | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS |
+-------------+-------------+---------------+-------------+
| TABLE       | SHARED_READ | TRANSACTION   | GRANTED     |
+-------------+-------------+---------------+-------------+
1 row in set (0.01 sec)
整形されたSQL 
select
    -- 不要なカラムを一部省略
    OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
from
    performance_schema.metadata_locks
where
    -- 対象がテーブルの場合テーブル名
    OBJECT_NAME="samples";
  • OBJECT_TYPE=TABLE　→　対象がテーブルであることを表す
  • LOCK_TYPE=SHARED_READ　→　共有MDL(read)であることを表す
  • LOCK_DURATION=TRANSACTION　→　ロックの期間がトランザクション終了までであることを表す
  • LOCK_STATUS=GRANTED　→　ロックが取得されている状態を表す

トランザクションを終了

-- トランザクション終了
COMMIT;

ロック状態の確認をすると、ロックが解放されている。

mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
Empty set (0.00 sec)

3-2. 共有MDL(write) をかけてみる

UPDATEを実行する。

-- トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- UPDATE実行
mysql> UPDATE samples SET id=1 WHERE id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

-- ロック状態確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+--------------+---------------+-------------+
| OBJECT_TYPE | LOCK_TYPE    | LOCK_DURATION | LOCK_STATUS |
+-------------+--------------+---------------+-------------+
| TABLE       | SHARED_WRITE | TRANSACTION   | GRANTED     |
+-------------+--------------+---------------+-------------+
1 row in set (0.00 sec)

LOCK_TYPE=SHARED_WRITE となっていて、共有MDL(write)となっている。

4. パターン1の検証

「共有MDLが取得されている場合、共有MDLは取得できる」を検証します。

ここからはセッションAとセッションBを使用します。
ロックの確認はどっちのセッションで行ってもいいです。

4-1. 検証

セッションAで共有MDL(read)をかけます。

セッションA
-- トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- 共有MDL（read）取得
mysql> select * from samples;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

-- 確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ | TRANSACTION   | GRANTED     |           14660 |
+-------------+-------------+---------------+-------------+-----------------+
1 row in set (0.00 sec)
ロック状態確認SQL(整形済み) 
select
    -- セッションの区別をつけるため OWNER_THREAD_ID を追加した
    OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID
from
    performance_schema.metadata_locks
WHERE
    OBJECT_NAME="samples";

スレッド(ID:14660)で共有MDL(read)が取得されました。

セッションBで共有MDL(write)をかけます。

セッションB
-- トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- 共有MDL（write）取得
mysql> UPDATE samples SET id=1 WHERE id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

-- 確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+--------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE    | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+--------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ  | TRANSACTION   | GRANTED     |           14660 |
| TABLE       | SHARED_WRITE | TRANSACTION   | GRANTED     |           14710 |
+-------------+--------------+---------------+-------------+-----------------+
2 rows in set (0.01 sec)

ロック状態を確認すると、両方ともロックが取れてます。

  • スレッド(ID:14660)で共有MDL(read)
  • スレッド(ID:14710)で共有MDL(write)

4-2. 検証結果

共有MDLが取得されている場合、共有MDLは取得できる。

5. パターン２の検証

「共有MDLが取得されている場合、排他MDLはロック取得待ちになる」を検証します。

5-1. 検証

セッションAで共有MDLを取得。

セッションA
-- トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- 共有MDL取得
mysql> select * from samples;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

-- 確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ | TRANSACTION   | GRANTED     |           14660 |
+-------------+-------------+---------------+-------------+-----------------+
1 row in set (0.00 sec)

セッションBから排他MDLを取得。

排他MDLを取得するには、DDLを実行します。
以下のALTERを実行します。

セッションB
-- nameカラム(文字列)を追加
ALTER TABLE samples ADD COLUMN name varchar(255), ALGORITHM=INPLACE, LOCK=SHARED;

MySQLのバージョン5.7で検証しているため、
ALGORITHMとLOCKで指定できる値がMySQL8.0以降と若干異なります。
必要に応じて公式ドキュメントで確認してください。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html

LOCK=SHAREDのロックは、InnoDBロック2です。

このDDLの実行中にはInnoDBの共有ロックがかかります。
※ SELECTは実行できるが、DML4は実行できないロックです。

ALTER実行したまま結果が返ってこないので、他のセッションからロック状態を確認します。

セッションA
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ       | TRANSACTION   | GRANTED     |           14660 |
| TABLE       | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     |           14710 |
| TABLE       | EXCLUSIVE         | TRANSACTION   | PENDING     |           14710 |
+-------------+-------------------+---------------+-------------+-----------------+
3 rows in set (0.00 sec)

スレッド(ID:14710)は以下の状態です。

  • 共有MDL(アップグレード可能)5が取得済み
  • 排他MDLが保留中(PENDING)

ロック取得待ちの状態は information_schema.PROCESSLIST6 から確認できます。

セッションA
mysql> select ID, COMMAND, STATE, INFO from information_schema.PROCESSLIST;
+-------+---------+---------------------------------+----------------------------------------------------------------------------------+
| ID    | COMMAND | STATE                           | INFO                                                                             |
+-------+---------+---------------------------------+----------------------------------------------------------------------------------+
| 14685 | Query   | Waiting for table metadata lock | ALTER TABLE samples ADD COLUMN name varchar(255), ALGORITHM=INPLACE, LOCK=SHARED |
| 14635 | Query   | executing                       | select ID, COMMAND, STATE, INFO from information_schema.PROCESSLIST              |
+-------+---------+---------------------------------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

ALTER TABLE が Waiting for table metadata lock となっていてメタデータロック取得待ちだということがわかります。

セッションAでトランザクションをコミットしましょう。

セッションA
COMMIT;

セッションBのALTER TABLEが実行されました。

セッションB
-- 結果待ちだった状態から、実行され結果が返ってくる。
mysql> ALTER TABLE samples ADD COLUMN name varchar(255), ALGORITHM=INPLACE, LOCK=SHARED;
Query OK, 0 rows affected (9 min 59.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- テーブル定義を確認
mysql> show create table samples \G;
*************************** 1. row ***************************
       Table: samples
Create Table: CREATE TABLE `samples` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL, -- name が追加されている！
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR:
No query specified

nameが追加されてますね。

5-2. 検証結果

共有MDLが取得されている場合、排他MDLはロック取得待ちになる。

6. パターン５の検証

「排他MDLがロック取得待ち状態の場合、後続のMDL取得もロック取得待ちの状態になる」を検証します。

6-1. 検証

セッションA
-- トランザクション開始
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

-- 共有MDL(read)
mysql> select * from samples;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.01 sec)

-- 確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ | TRANSACTION   | GRANTED     |           14660 |
+-------------+-------------+---------------+-------------+-----------------+
1 row in set (0.00 sec)

以下のALTERを実行します。

セッションB
-- name カラムを削除
mysql> ALTER TABLE samples DROP COLUMN name, ALGORITHM=INPLACE, LOCK=NONE;

結果が返ってこないので別セッションでロック状態の確認をします。

セッションA
-- ロック状態確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ       | TRANSACTION   | GRANTED     |           14660 |
| TABLE       | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     |           14710 |
| TABLE       | EXCLUSIVE         | TRANSACTION   | PENDING     |           14710 |
+-------------+-------------------+---------------+-------------+-----------------+
3 rows in set (0.00 sec)

-- ロック取得待ちを確認
mysql> select ID, COMMAND, STATE, INFO from information_schema.PROCESSLIST;
+-------+---------+---------------------------------+---------------------------------------------------------------------+
| ID    | COMMAND | STATE                           | INFO                                                                |
+-------+---------+---------------------------------+---------------------------------------------------------------------+
| 14685 | Query   | Waiting for table metadata lock | ALTER TABLE samples DROP COLUMN name, ALGORITHM=INPLACE, LOCK=NONE  |
| 14635 | Query   | executing                       | select ID, COMMAND, STATE, INFO from information_schema.PROCESSLIST |
+-------+---------+---------------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

下記の状態であることがわかります。

  • 排他MDLが保留中(PENDING)
  • DDLがロック取得待ち(Waiting for table metadata lock)
セッションC
-- 止まる
mysql> select * from samples;

結果が返ってこないので別セッションでロック状態の確認をします。

セッションA
-- ロック状態確認
mysql> select OBJECT_TYPE, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_ID from performance_schema.metadata_locks WHERE OBJECT_NAME="samples";
+-------------+-------------------+---------------+-------------+-----------------+
| OBJECT_TYPE | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS | OWNER_THREAD_ID |
+-------------+-------------------+---------------+-------------+-----------------+
| TABLE       | SHARED_READ       | TRANSACTION   | GRANTED     |           14660 |
| TABLE       | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     |           14710 |
| TABLE       | EXCLUSIVE         | TRANSACTION   | PENDING     |           14710 |
| TABLE       | SHARED_READ       | TRANSACTION   | PENDING     |           14965 |
+-------------+-------------------+---------------+-------------+-----------------+
4 rows in set (0.00 sec)

-- ロック取得待ちを確認
mysql> select ID, COMMAND, STATE, INFO from information_schema.PROCESSLIST;
+-------+---------+---------------------------------+---------------------------------------------------------------------+
| ID    | COMMAND | STATE                           | INFO                                                                |
+-------+---------+---------------------------------+---------------------------------------------------------------------+
| 14685 | Query   | Waiting for table metadata lock | ALTER TABLE samples DROP COLUMN name, ALGORITHM=INPLACE, LOCK=NONE  |
| 14940 | Query   | Waiting for table metadata lock | select * from samples                                               |
| 14635 | Query   | executing                       | select ID, COMMAND, STATE, INFO from information_schema.PROCESSLIST |
+-------+---------+---------------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

以下状態が確認できます。

  • スレッド(ID:14965)が共有MDL(read)取得待ち(PENDING)
  • select * from samples が、MDL取得待ち

セッションAでCOMMITすると後続のロックが取得できるようになるので、セッションB、セッションCで実行結果が返ってきます。

セッションB
-- 実行される
mysql> ALTER TABLE samples DROP COLUMN name, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (6 min 35.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- テーブル定義更新されている
-- name カラムが削除された
mysql> show create table samples \G;
*************************** 1. row ***************************
       Table: samples
Create Table: CREATE TABLE `samples` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR:
No query specified
セッションC
-- 実行される
mysql> select * from samples;
+----+------+
| id | name |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (3 min 46.80 sec)

6-2. 検証結果

排他MDLがロック取得待ち状態の場合、後続のMDL取得もロック取得待ちの状態になる。

まとめ

  1. 共有MDLが取得されている場合、共有MDLは取得できる
  2. 共有MDLが取得されている場合、排他MDLはロック取得待ちになる
  3. 排他MDLが取得されている場合、共有MDLはロック取得待ちになる
  4. 排他MDLが取得されている場合、排他MDLはロック取得待ちになる
  5. 排他MDLがロック取得待ち状態の場合、後続のMDL取得もロック取得待ちの状態になる

今回は上記のうち、1, 2, 5について検証しました。

MySQLでDDLを実行する上で、これらの挙動の理解はとても大切です。
実際に下記のような問題が発生することもあります。

まさに5のパターンですが、この場合セッションAのトランザクションが永遠に終わらなければ障害が発生します。
具体的には、セッションBがDDLを実行した瞬間からテーブルへの後続のクエリー7, DML, DDLが全て止まり続けます8。

できるだけサービスを止めずにテーブル定義を変更していくために、学ぶべきことはたくさんあるなと思いました。

終わりに

弊社では絶賛エンジニア募集中です！
気になった方、是非お気軽にWantedlyからご連絡ください！

  1. https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html ↩

  2. https://dev.mysql.com/doc/refman/8.0/ja/metadata-locking.html ↩ ↩2

  3. データ定義言語 Data Definition Language ↩

  4. データ操作言語 Data Manipulation Language ↩

  5. オンラインDDLはフェーズの中で、最初にアップグレード可能な共有MDLが取得され、最後に排他MDLへアップグレードされます。
    公式ドキュメントの、オンライン DDL のパフォーマンスと同時実行性に詳しく記載されています。 ↩

  6. https://dev.mysql.com/doc/refman/8.0/ja/performance-schema-processlist-table.html ↩

  7. SELECTのこと ↩

  8. 実際には lock_wait_timeoutで設定されている時間ロック取得を待ち続けます。 ↩

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?