gihyo.jpサイトのロゴ

MySQL道普請便り

第180回MySQLのメタデータロックについて

2022-09-20

MySQLでは、データベース操作の信頼性および一貫性を確保するために行ロック、テーブルロックやインサートインテンションロックなどさまざまなタイプのロックがあります。今回はその中からメタデータロックについて紹介したいと思います。

メタデータロックとは

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

メタデータロックは、以下のようなオブジェクトに対して適用されます。

  • テーブル
  • スキーマ
  • テーブルスペース
  • ストアドプロシージャ（プロシージャ、ファンクション、トリガー、イベント）
  • GET_LOCK()関数によるユーザロック

どういったときにメタデータロックが取得されるのか説明します。

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

メタデータロックで待機しているステートメントはSHOW PROCESSLISTのStateカラムにWaiting for table metadata lockが表示されます。

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+--------------------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                     |
+----+------+-----------+------+---------+------+---------------------------------+--------------------------+
| 43 | root | localhost | db1  | Query   |    6 | Waiting for table metadata lock | ALTER TABLE t0 ADD t int |
<snip>

ちなみに、更新や参照同士はそれぞれ共有メタデータロックを取得するため競合しません。DDLは排他メタデータロックを取得するため、共有メタデータロックと競合します。

運用中によくある問題

運用中のサービスでDDLを実行すると、このメタデータロックにより問題になることがあります。

トランザクション中に触ったテーブルに対してDDLを実行すると、そのトランザクションが終了するまでメタデータロックで待機するとは先ほど説明しました。このDDLだけが待機するのであれば問題ないのですが、DDLの待機中はそのテーブルに対しての他セッションからの後続のクエリがすべて待機されてしまうのです。

以下の例では、session1がトランザクションを開始してt0テーブルを参照しました。session1のトランザクションは継続中のため、session2のt0テーブルに対するDDLは待機します。そして、後続のsession3とsession4がt0テーブルに対して参照しようとするとそれぞれ待機します。

session1 session2 session3 session4
BEGIN;
SELECT t0
ALTER TABLE t0
waiting
SELECT t0 SELECT t0
waiting waiting

頻繁にアクセスされるテーブルでこの問題が起こると、サービスからのすべてのセッションが待機され、サービス障害につながる可能性があります。そのため、MySQLではトランザクションは極力小さく保つことと、commitやrollback漏れによる終了することのないトランザクションを防がなければいけません。

もし、この問題が起こってしまった際に解消するには以下の方法になります。

  1. session1のトランザクションが正常終了するまで待つ
  2. session1をkillステートメントで強制終了させる
  3. session2のDDLをキャンセルする、またはタイムアウトされるまで待つ（lock_wait_timeoutパラメータ）

1.については、たまたまロングトランザクションが実行されているのであれば、このトランザクションが正常終了するまで待ってから、DDLを実行します。

2.については、正常終了する見込みのない予期しないトランザクションであれば、killステートメントで強制終了させる必要があります。その方法は後述する「metadata_locksテーブルについて」にて説明します。

3.については、DDLの実行をキャンセルするとsession3とsession4のクエリは流れるようになります。また、メタデータロックで待機しているDDLはlock_wait_timeoutの値まで待機して、その後タイムアウトします。この値はデフォルトは31536000（1年）なので、デフォルトのままではタイムアウトされることは期待できません。安全にDDLを実施するために、DDL実行前にはセッション単位でlock_wait_timeoutを小さな値するのがいいでしょう。

mysql> SET SESSION lock_wait_timeout=1;
mysql> ALTER TABLE t0 ADD t int;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

metadata_locksテーブルについて

メタデータロックを取得中のトランザクションを強制終了させたいが、どのトランザクションまたはセッションがそのロックを取得しているのか、調べなくてはいけません。

メタデータロックを取得しているセッションを探すには、performance_schema.metadata_locksテーブルを利用します。

見方

では、先ほどのsession1をmetadata_locksテーブルから探して強制終了させてみましょう。メタデータロック情報を見てみます。

mysql> SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_NAME='t0' AND LOCK_STATUS='GRANTED'\G
*************************** 1. row ***************************
    OBJECT_TYPE: TABLE
  OBJECT_SCHEMA: i
    OBJECT_NAME: t0
      LOCK_TYPE: SHARED_READ
  LOCK_DURATION: TRANSACTION
    LOCK_STATUS: GRANTED
OWNER_THREAD_ID: 67

クエリのWHERE条件として、OBJECT_NAMEカラムに対象のオブジェクト名を入れます。今回の場合はt0になります。また、ロックが取得済であるとLOCK_STATUSカラムがGRANTEDになるので、これもWHERE条件に追加します。

強制終了させるセッションはOWNER_THREAD_IDカラムを確認します。OWNER_THREAD_IDはメタデータロックをリクエストしたスレッドを表示します。

そして、threadsテーブルからPROCESSLIST_IDを取得して、その値をkillステートメントに指定し実行することでセッションを強制終了させることができます。

注意として、performance_schema内で使用されるthread_idはkillステートメントなどで指定するprocesslist_idとは異なるものなので、threadsテーブルから取得する必要があります。

mysql> SELECT PROCESSLIST_ID FROM threads WHERE THREAD_ID=67;
+----------------+
| PROCESSLIST_ID |
+----------------+
|             44 |
+----------------+

mysql> kill 44;
Query OK, 0 rows affected (0.00 sec)

このようにして、メタデータロックを取得しているセッションを特定して強制終了することができます。

以下のように、threadsテーブルとJOINしてクエリしても問題ありません。

SELECT ml.OBJECT_TYPE,ml.OBJECT_SCHEMA,ml.OBJECT_NAME,ml.LOCK_TYPE,ml.LOCK_DURATION,ml.LOCK_STATUS,ml.OWNER_THREAD_ID,t.PROCESSLIST_ID 
FROM performance_schema.metadata_locks ml 
JOIN performance_schema.threads t ON ml.OWNER_THREAD_ID=t.THREAD_ID
WHERE ml.OBJECT_NAME='t0' AND ml.LOCK_STATUS='GRANTED';

有効化

metadata_locksテーブルはMySQL 8.0ではデフォルト有効になっていますが、MySQL 5.7ではデフォルト無効になっています。有効にするには以下SQLを実行します。

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';

まとめ

今回はメタデータロックについて紹介しました。

8.11.4 メタデータのロックと27.12.13.3 metadata_locks テーブルを基に紹介したので、こちらを読んでもらうと、より理解が深まると思います。

おすすめ記事

記事・ニュース一覧