技術共有|外部キーとメタデータロックの変更

原文:https://www.percona.com/blog/2019/07/02/alters-foreign-keys-and-metadata-locks-oh-my/
著者:Mike Benshoof
原文:管長龍
あなたがALTERを実行し始めると、あなたは恐ろしい「メタデータロック待ち」に出会って、私はあなたがきっと出会ったことがあると確信しています.最近、変更されたテーブルが小さな範囲の更新(<100行)を実行するケースに遭遇しました.ALTERは負荷テスト中に何時間も待っていた.負荷テストを停止した後、ALTERは予想通り1秒足らずで完了した.ここで何があったの?
 
外部キーの確認
奇数回のロックタイミングがあるたびに、私の最初の直感は外部キーをチェックすることです.もちろん、このテーブルには、より忙しいテーブルを参照する外部キーがあります.しかし、このような行為は依然としておかしいようだ.テーブルに対してALTERを実行すると、サブテーブルに対してSHARED_が要求されます.UPGRADEABLEメタデータロック.そして親に対するSHARED_READ_ONLYメタデータロック.
ドキュメントに基づいてメタデータロックを取得する方法を見てみましょう[1]:
指定されたロックに複数のサーバがある場合、最初に最高優先度のロック要求を満たし、max_write_lock_countシステム変数に関係します.書き込みロック要求の優先度は、読み込みロック要求よりも高い.
[1]:https://dev.mysql.com/doc/refman/en/metadata-locking.html
ロック順序はシーケンス化されていることに注意してください.文は同時に取得するのではなく、メタデータロックを1つずつ取得し、このプロセスでデッドロック検出を実行します.
通常、キューを考慮する際には、先進的な先発を考慮します.次の3つの文を発行すると(この順序で)、次の順序で完了します.

INSERT INTO parent 2. ALTER TABLE child 3. INSERT INTO parent

ただし、子ALTER文が親の読み込みロックを要求すると、ソートされますが、2つの挿入はALTERの前に完了します.次の例は、この例を示すシーンの例です.
データの初期化:

CREATE TABLE `parent` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`val` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_parent` (`parent_id`),
CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB;

INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");

Session 1:

start transaction;
update parent set val = "four-new" where id = 4;

Session 2:

alter table child add index `idx_new` (val);

Session 3:

start transaction;
update parent set val = "three-new" where id = 3;

このとき、セッション1にはオープントランザクションがあり、スリープ状態にあり、親に書き込みメタデータロックが付与されます.セッション2には、子レベルで許可されたアップグレード可能(書き込み可能)ロックがあり、親レベルの読み込みロックを待っています.最後のセッション3には、親に対する許可された書き込みロックがあります.

mysql> select * from performance_schema.metadata_locks;
+-------------+-------------+-------------------+---------------+-------------+
| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |
+-------------+-------------+-------------------+---------------+-------------+
| TABLE | child | SHARED_UPGRADABLE | TRANSACTION | GRANTED | 

保留中のロック状態を持つ唯一のセッションはセッション2(ALTER)であることに注意してください.セッション1とセッション3(それぞれALTERの前後で公開)には書き込みロックが付与されている.ソートに失敗したのは、セッション1でコミットが発生したときです.シーケンスキューを考慮すると、セッション2がロックされ、作業が継続されることが期待されます.しかし、メタデータロックシステムの優先度の性質のため、セッション3はロックされ、セッション2はまだ待機している.
別の書き込みセッションが新しいトランザクションにアクセスして開始し、親テーブルに対する書き込みロックを取得すると、セッション3が完了してもALTERはブロックされます.
親テーブルに対してメタデータロックを開くアクティブなトランザクションを保持している限り、サブテーブルのALTERは永遠に完了しません.さらに悪いことに、サブテーブルの書き込みロックは成功しました(ただし、完全な文は親の読み込みロックの取得を待っています).したがって、サブテーブルに対するすべての読み込み要求はブロックされます.
また、通常、完了できない文をトラブルシューティングする方法を考えてください.長い間開いていたトランザクション(プロセス・リストとInnoDBステータス)を表示します.ただし、ブロックスレッドはALTERスレッドよりも若くなっているため、最も古いトランザクション/スレッドはALTERです.
これはまさにこのような状況で発生した状況である.リリースの準備中、クライアントはALTER文を実行し、負荷テストを組み合わせています(非常に良い方法です!)スムーズなリリースを確保します.問題は、負荷テストが親テーブルに対してアクティブな書き込みトランザクションを開いたままであることです.これは、ずっと書いているだけではなく、複数のスレッドがあり、1つは常に活発です.これは、ALTERの完了を阻止し、相対的に静的なサブテーブルに対する後続の読み取り要求を阻止する.
幸いなことに、この問題には解決策があります(設計モードから外部キーを駆逐する以外).変数max_write_lock_count[2]は、書き込みロック後に読み取りロックの前に読み取りロック連続書き込みロックを付与することを可能にするために使用することができる.デフォルトでは、この変数は18446744073709551615に設定されています.テーブルに10000回の書き込み/秒を発行すると、5800万年ロックされます.
[2]:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_write_lock_count
このような海が枯れて石が腐るのを防ぐために、簡単にmax_write_lock_countは小さな数字(例えば10?)に減少し、さらに、10個毎の書き込みロックを取得すると、メタデータロックシステムのサブシステムは、保留中の読み出しロックを検索して1個付与し、書き込みに戻る.問題解決!
動的変数として、実行時にALTERの完了を待つように調整できます.一般的に、これは、書き込みテーブル間で通常、読み込みロックを取得するのに時間がかかるため、エッジの状況に似ています.ただし、インスタンスがコンカレント・セッションを実行状態に保つ場合、外部キーとして参照されるテーブルがトランザクションされることがよくあります.幸いなことに、修復は簡単で、ダイナミックに完成できます!
注:パフォーマンスモードとmetadataの有効化locksテーブルでは、このトラブルシューティングを実行できます.
以下に説明します.
https://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html