MySQLラーニング-5|グローバルロックとテーブルロック:テーブルにフィールドを追加するのはどうしてこんなに多くの障害がありますか?

MySQLデータベースラーニング-5|グローバルロックとテーブルロック:テーブルにフィールドを追加するのはどうしてこんなに多くの障害がありますか?

ロックのタイプ

グローバルロック

テーブルレベルロック

まとめ

参考資料

後

に書いてあります
ロックのタイプ
環境:MySQL 5.7.24,for linux-glibc 2.12 (x86_64)
データベース・ロック設計の目的は、同時問題を処理することです.

はマルチユーザ共有リソースとして、同時アクセスが発生した場合、データベースはリソースのアクセスルールを合理的に制御する必要がある.ロックは、これらのアクセスルールを実現するための重要なデータ構造です.

ロックの範囲によって、MySQLのロックは大まかにグローバルロック、テーブルレベルロック、ローロックの3種類に分けることができます.

グローバルロック
グローバル・ロックとは、データベース・インスタンス全体にロックをかけることです.
MySQLのFTWRLはサーバ層1で実現される.
グローバルロックをかけるコマンド(FTWRL)は

-- FTWRL
mysql> flush tables with read lock;

グローバル・ロックは、データベース・インスタンス全体を読取り専用にし、その後、他のスレッドのデータベース更新文(DML増加、削除、変更)、データ定義文(DDLテーブル作成、テーブル構造の変更など)、更新クラストランザクションコミット文がブロックされます.

の一般的な使用シーン:ライブラリ全体の論理バックアップを行います.すなわち、ライブラリ全体の各テーブルをselectしてテキストとして保存します.

注意1:MySQLの公式の論理バックアップツールはmysqldumpで、mysqldumpがパラメータ-single-transactionを使用すると、データを導く前にトランザクションが起動し、一貫性のあるビューが得られることを確認します.MVCC(マルチバージョン同時制御)のサポートにより、このプロセスのデータは正常に更新されます.それなら、なぜFTWRLが必要なのでしょうか.コンシステンシ読み取りは良いですが、エンジンがこの独立性レベルをサポートすることを前提としています.たとえばMyISAMエンジンはトランザクションをサポートしていないので、FTWRLコマンドを使用する必要があります.

注意2:なぜset global readonly=trueを使用しないのですか.確かにreadonly方式は全ライブラリを読み取り専用状態にすることもできますが、FTWRL方式を使用することをお勧めします.主に2つの理由があります.1つは、readonlyの値が他の論理に使用されるシステムがあります.たとえば、1つのライブラリがプライマリ・ライブラリかスタンバイ・ライブラリかを判断します.したがってglobal変数を修正する方法は影響面が大きい.二つ目は、異常処理メカニズムに違いがある.FTWRLコマンドを実行すると、クライアントが異常に切断されたため、MySQLはこのグローバルロックを自動的に解放し、ライブラリ全体が正常に更新できる状態に戻ります.一方、globalを変更してライブラリ全体をreadonlyに設定した後、クライアントに異常が発生した場合、データベースはreadonlyの状態を維持し続け、ライブラリ全体が長時間書き込み不可になり、リスクが高くなります.

テーブルレベルロック
MySQLには、テーブル・ロック、メタデータ・ロック(meta data lock,MDL)の2種類のテーブル・レベルがあります.
テーブルロック

mysql> lock tables tab_name read/write;
-- unlock tables tab_name ;

はFTWRLと同様に、unlock tablesでテーブルロックをアクティブに解除したり、クライアントが切断されたときに自動的に解放したりすることができます.なお、lock tables構文は、他のスレッドの読み書きを制限するほか、本スレッドの次の操作対象も限定される.

は、一般にlock tablesコマンドを使用して同時制御されません.結局、テーブル全体をロックする影響面は大きすぎます.

メタデータロック(meta data lock,MDL)

MDLの役割は読み書きの正確性を保証することであり、MySQL 5.5バージョンが導入されました.MDLは明示的に使用する必要はなく、テーブルにアクセスするときに自動的に追加されます.

一つのテーブルに対して添削改変操作を行う場合、MDLリードロックを加える.

テーブルの構造変更操作が必要な場合は、MDL書き込みロックを追加します.

リードロックは直接反発しない.したがって、複数のスレッドが同時に1つのテーブルを削除して調べることができます.

読み書きロック直接、書き込みロック間は反発し、変更テーブル構造操作の安全性を保証するために使用される.したがって、2つのスレッドが同時に1つのテーブルにフィールドを追加する場合、そのうちの1つは別の実行が完了するまで実行を開始しません.

--  information_schema    innodb_trx
--     60s   
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

注意:小表にフィールドを安全に追加するにはどうすればいいですか?

は、長いトランザクションを解決します.トランザクションはコミットされず、MDLロックが保持されます.

DDLの変更が必要な場合、ちょうど長いトランザクションが実行されているので、DDLを一時停止するか、killを削除することを考慮します.

変更されたテーブルがホットスポットテーブルである場合、データ量は大きくないが、テーブル上の要求は頻繁であり、フィールドを追加せざるを得ない.このときkillは必ずしも役に立つとは限らないかもしれません.新しい要求がすぐに来るからです.理想的なメカニズムは、alter table文に待ち時間を設定することである.この指定された待ち時間でMDL書き込みロックが取れるのがベストなら、取れなくても後のビジネスクエリ文をブロックしないで、先に諦めましょう.

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

まとめ
グローバル・ロックは、主に論理バックアップに使用されます.InnoDBエンジンのライブラリの場合は、-single-transactionパラメータを使用することをお勧めします.アプリケーションに便利です.
テーブルロックは、通常、ローロックがサポートされていない場合に使用されます.アプリケーションにlock tables文がある場合、ライブラリがトランザクションをサポートしないエンジンを使用している場合は、トランザクションをサポートするエンジンにアップグレードすることをお勧めします.エンジンがアップグレードされたか、コードがアップグレードされていないか、lock tablesとunlock tablesをbeginとcommitに変更することをお勧めします.
MDLはトランザクションがコミットされるまで解放されないので、テーブル構造の変更を行うときは、オンラインクエリーと更新がロックされないように注意してください.
参考資料
《高性能MySQL》《MySQL実戦45講》作者:丁奇
あとに書く
以前大神丁奇の《MySQL実戦45講》を学んで、現在《高性能高MySQL》を見ていて、自分でMySQLの知識点を整理したいと思って、力が足りないことを発見して、大神が大神であることを発見して、それは本当の牛のためです.
皆さんはやはり丁奇の《MySQL実戦45講》を勉強することをお勧めします.そして、どの話の後にも質の高いコメントがあり、そこから多くの利益を得ることができます.ありがとう!

間違いがあればご指摘ください.お役に立てばと思います.

MySQLインフラストラクチャ↩︎