ロックの紹介#
MySQL のロックは、ロックの粒度に応じて以下の 3 種類に分けられます：
- グローバルロック：データベース内のすべてのテーブルをロック
- テーブルレベルロック：操作ごとにテーブル全体をロック
- 行レベルロック：操作ごとに対応する行データをロック
グローバルロック#
グローバルロックは、データベースインスタンス全体にロックをかけるもので、ロック後はインスタンス全体が読み取り専用の状態になります。その後の DML の書き込み文、DDL 文、更新操作のトランザクションコミット文はすべてブロックされます。
典型的なシナリオは、すべてのテーブルをロックして全体バックアップを行うことです。ロックをかけないと、以下の図のように、注文が生成されたが在庫が減少しないデータの不一致が発生する可能性があります。
ロックをかけると、読み取りのみ可能で書き込みはできず、バックアップの瞬間に全体の整合性が保証されます。
flush tables with read lock; # ロックをかける
mysqldump -uroot -p123456 database > database.sql # バックアップ
unlock tables; # ロックを解除
グローバルロックをかけることは比較的重い操作であり、以下の欠点があります。
- 主データベースでバックアップを行う場合、バックアップ中は更新を実行できず、業務は基本的に停止します。
- 従属データベースでバックアップを行う場合、バックアップ中は従属データベースが主データベースから同期されたバイナリログ binlog を実行できず、主従の遅延が発生します。
InnoDB エンジンでは、パラメータ--single-transactionを追加することでロックなしの整合性データバックアップを実現できます。これは、基盤としてスナップショット読み取りを使用してバックアップ操作を行います。
mysqldump --single-transaction -uroot -p123456 database > database.sql
テーブルレベルロック#
テーブルレベルロックは、操作ごとにテーブル全体をロックし、ロックの粒度が大きく、ロックの競合が最も高く、同時実行性が最も低いです。MyISAM、InnoDB、BDB などのストレージエンジンで使用されます。
テーブルレベルロックは、主に以下の 3 種類に分けられます：
- テーブルロック
- メタデータロック meta data lock MDL
- 意図ロック
テーブルロック#
テーブルロックは、2 種類に分けられます：
- テーブル共有読み取りロック read lock
- テーブル排他書き込みロック write lock
共有読み取りロック read lock は、すべてのトランザクションが読み取り可能ですが、書き込みはできません。
排他書き込みロック write lock は、ロックをかけた側のみが読み書きでき、他の側は操作できません。
メタデータロック#
meta data lock MDL のロックプロセスはプログラムによって自動的に制御され、明示的に使用する必要はありません。テーブルにアクセスする際に自動的に追加されます。MDL ロックの主な目的は、テーブルメタデータのデータ整合性を維持することであり、テーブル上にアクティブなトランザクションがある場合、メタデータに書き込み操作を行うことはできません。DML と DDL の競合を避け、読み書きの正確性を保証します。
MySQL v5.5 では MDL が導入されました：
テーブルに対して DML の追加、削除、変更を行う際に、MDL 読み取りロックを共有として追加します。
テーブル構造の変更操作 DDL を行う際に、MDL 書き込みロックを排他として追加します。
メタデータロックの大意は、トランザクションが進行中の間、テーブル構造を変更できないことです。
トランザクション進行中の DML は MDL 共有読み取りロック SHARE_READ/SHARE_WRITE を申請します。
テーブル構造を変更する DDL は MDL 排他ロック EXCLUSIVE を申請します。
MDL は主にテーブル構造のメタデータに対して、テーブル構造の共有読み取りロック SHARE と変更するための排他書き込みロック EXCLUSIVE を保証し、DML の際にテーブル構造の整合性を制限します。
意図ロック#
DML が実行中に加えられた行ロックとテーブルロックの競合を避けるために、InnoDB では意図ロックが導入され、テーブルロックが各行データがロックされているかどうかを確認する必要がなくなり、意図ロックを使用してテーブルロックの確認を減らします。
テーブル内の他のトランザクションが意図共有ロック IS を保持している場合、この時にテーブルに read テーブルロックを加えることはできますが、write テーブルロックはできません。トランザクションが意図共有ロックを解除するのを待つ必要があります。
テーブル内の他のトランザクションが意図排他ロック IX を保持している場合、この時にテーブルに read テーブルロックと write テーブルロックの両方を加えることはできません。トランザクションが意図排他ロックを解除するのを待つ必要があります。
行レベルロック#
行レベルロックは、操作ごとに対応する行データをロックし、ロックの粒度が最小で、ロックの競合が最も低く、同時実行性が最も高いです。InnoDB ストレージエンジンで使用されます。
行ロック Record Lock：単一の行レコードをロックし、他のトランザクションがこの行に対して update および delete を行うのを防ぎます。ReadCommit、RepeatableRead の隔離レベルでサポートされています。
間隙ロック Gap Lock：インデックスレコードの間隙をロックし、そのレコードを含まず、インデックスレコードの間隙が変わらないことを保証し、他のトランザクションがこの間隙に対して insert を行うのを防ぎ、幻読を防ぎます。RepeatableRead の隔離レベルでサポートされています。
臨鍵ロック Next-Key Lock：行ロックと間隙ロックを組み合わせて、データをロックし、データの前の間隙もロックします。RepeatableRead の隔離レベルでサポートされています。
行ロック Record Lock#
2 種類の行ロック：
- 共有ロック S：1 つのトランザクションが行を読み取ることを許可し、他のトランザクションが同じデータセットの排他ロックを取得するのを防ぎます。
- 排他ロック X：排他ロックを取得したトランザクションがデータを更新でき、他のトランザクションが同じデータセットの共有ロックと排他ロックを取得するのを防ぎます。
read/write に似ています。
関連する SQL 行ロックタイプ
デフォルトでは、InnoDB は RepeatableRead トランザクション隔離レベルで実行され、InnoDB は臨鍵ロック next-key lock を使用して検索とインデックススキャンを行い、幻読を防ぎます。
- ユニークインデックスに対して検索を行う際、既存のレコードに対して等値マッチを行うと、自動的に行ロックに最適化されます。
- InnoDB の行ロックはインデックスに対して加えられるロックであり、インデックス条件を使用してデータを検索しない場合、InnoDB はテーブル内のすべてのレコードをロックします。この時、テーブルロックにアップグレードされます。
間隙ロック gap lock / 臨鍵ロック next key lock#
間隙ロック gap lock の唯一の目的は、他のトランザクションが間隙に挿入するのを防ぐことです。間隙ロックは共存可能であり、1 つのトランザクションが使用する間隙ロックは、別のトランザクションが同じ間隙で間隙ロックを使用するのを妨げません。
少し理解が難しいので、ノートを借りてみましょう。
小結#
- 概要
並行アクセス時にデータアクセスの整合性、有効性の問題を解決します。
グローバルロック、テーブルレベルロック、行レベルロック - グローバルロック
データベースインスタンス全体にロックをかけ、ロック後はインスタンス全体が読み取り専用の状態になります。
性能が悪く、データ論理バックアップ時に使用されます。 - テーブルレベルロック
操作ごとにテーブル全体をロックし、ロックの粒度が大きく、ロックの競合が高いです。
テーブルロック、メタデータロック、意図ロック - 行レベルロック
操作ごとに対応する行データをロックし、ロックの粒度が最小で、ロックの競合が最も低いです。
行ロック、間隙ロック、臨鍵ロック
この記事は Mix Space によって xLog に同期更新されました。
元のリンクは https://blog.0xling.cyou/posts/mysql/mysql-1