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漏れによる終了することのないトランザクションを防がなければいけません。
もし、この問題が起こってしまった際に解消するには以下の方法になります。
- session1のトランザクションが正常終了するまで待つ
- session1をkillステートメントで強制終了させる
- session2のDDLをキャンセルする、またはタイムアウトされるまで待つ
( lock_
パラメータ)wait_ timeout
1.については、たまたまロングトランザクションが実行されているのであれば、このトランザクションが正常終了するまで待ってから、DDLを実行します。
2.については、正常終了する見込みのない予期しないトランザクションであれば、killステートメントで強制終了させる必要があります。その方法は後述するmetadata_
テーブルについて」
3.については、DDLの実行をキャンセルするとsession3とsession4のクエリは流れるようになります。また、メタデータロックで待機しているDDLはlock_
の値まで待機して、その後タイムアウトします。この値はデフォルトは31536000lock_
を小さな値するのがいいでしょう。
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_
.metadata_
テーブルを利用します。
見方
では、先ほどのsession1をmetadata_
テーブルから探して強制終了させてみましょう。メタデータロック情報を見てみます。
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_
カラムに対象のオブジェクト名を入れます。今回の場合はt0
になります。また、ロックが取得済であるとLOCK_
カラムがGRANTED
になるので、これもWHERE条件に追加します。
強制終了させるセッションはOWNER_
カラムを確認します。OWNER_
はメタデータロックをリクエストしたスレッドを表示します。
そして、threads
テーブルからPROCESSLIST_
を取得して、その値をkillステートメントに指定し実行することでセッションを強制終了させることができます。
注意として、performance_
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_
テーブルはMySQL 8.
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
まとめ
今回はメタデータロックについて紹介しました。
8.