MySQLのロック

mysql

ロックとは

データベース上の同一データに対して書込み要求が同時に発生した場合、トランザクションが逐次化されていないと結果に矛盾が生じてしまう。

以下図を見ればロックがなぜ必要なのか直感的にわかる

引用: https://www.teradata-jp.com/post/col05i

トランザクションとは

「複数の処理を一つにまとめたもの」

https://qiita.com/zd6ir7/items/6568b6c3efc5d6a13865

上の記事がトランザクションの役割を表している。ここではトランザクションの特性の一つであるACID特性についてのみ記載しておく。

ACID特性

トランザクションはACID特性を満たしていなければならない

特性 説明
原子性(atomicity) トランザクションは完全に実行完了するか、全く実行されないかのどちらかでなくてはならない
一貫性(consistency) トランザクションの終了状態に関わらず、データベースの整合性が保たれていなければならない
独立性(isolation) トランザクションを複数同時実行しても、単独実行の場合と同じ処理結果にならなければならない
耐久性(durability) トランザクションの結果は障害が発生しても失われてはならない

各トランザクションがこの特性を保持した上で、データの整合性を失わずに読み書きを行う必要がある。そのためロックという仕組みが重要となってくる。

共有(shared)ロック

読み取りのために使われるロック。INSET/UPDATEが行えなくなる。読み込みはできる。

他の共有ロックと競合しないので、同じレコードに対していくつも共有ロックを取ることができる

排他(exclusive)ロック

更新のために使われるロック。他のトランザクションからは更新はできない。読み込みは悲観的ロックか楽観的ロックかで可否が分かれる。

排他ロックの制御方法にはいくつか種類がある

楽観的ロック

めったなことでは他者との同時更新は起きないであろう、という楽観的な前提の排他制御。

データそのものに対してロックは行わずに、更新対象のデータがデータ取得時と同じ状態であることを確認してから更新することで、データの整合性を保証する方式。

悲観的ロック

他者が同じデータに頻繁に変更を加えるであろう、という悲観的な前提の排他制御。

更新対象のデータを取得する際にロックをかけることで、他のトランザクションから更新されないようにする方式。

データのロックはRDBMSの「SELECT 〜 FOR UPDATE」を利用して実現されるのが一般的。悲観ロックでは、ロックの解放漏れがあると、いつまで経っても他者が操作できないということに繋がるため、データ更新後はロックの解放を必ず行うこと。

共有ロックと排他ロックの関係性

- 共有ロック 排他ロック
共有ロック ○ ×
排他ロック × ×

「共有ロックがかかっているレコードに墓のトランザクションが行えるのは共有ロックとだけ」ということになる。それ以外の場合はwait(待ち)が発生する・

トランザクション分離レベル

MySQL特有のロックの種類を見るために、ここではトランザクションレベルについて言及する。特にファントムリードについて、後述するMySQL特有のロックが発生するので留意しておく。

「トランザクション分離レベル」とは、トランザクションの変更が他のトランザクションでどのように見えるのか、トランザクション同士の影響レベルのこと。他のトランザクションとの影響についてはいくつか種類がある

分離レベルの種類

分離レベル ダーティリード ノンリピータブルリード ファントムリード
READ UNCOMMITED ○ ○ ○
READ COMMITED × ○ ○
REPEATABLE READ × × ○
SERIALIZE × × ×

MySQLではREPEATABLE READがデフォルト。

トランザクションをサポートしているRDBMSではトランザクションの並列性を上げることが性能アップの一つ。競合を防ぐためにSERIALIZEにしていては性能が落ちてしまう。

ダーティリード

あるトランザクションが更新されている最中に、他のトランザクションからデータを読み出すことができてしまう現象のこと。

排他ロックを悲観的ロックにすれば発生しない。

ノンリピータブルリード(ファジーリード)

同じトランザクションで再度同じ行を読み込んだときに、その行が更新または削除されていること。

こちらも排他ロックを悲観的ロックにすることで発生しない

ファントムリード

ある探索条件を満たす行の集合を読み込んだ後、ほかのトランザクションによる行の挿入または更新により、その探索条件を満たす行が生成され、再度同じ探索条件を読み込んだときに、異なる行の挿入が得られる。

トランザクション1がテーブルに対して共有ロックを取ることで防げる

MySQL(InnoDB)でファントムリードを防ぐ仕組み

MySQL(InnoDB)ではトランザクション分離レベルをREPEATABLE READに設定してもファントムリードは発生しない。

これはMySQLのロック機構であるギャップロックとネクストキーロックのおかげである。

MySQLのロック機構は公式リファレンスでも丁寧に解説されている

レコードロック

  • いわゆる行ロック。
  • インデックスレコードに対するロックである。
  • テーブルにインデクスが貼ってなくても常にインデックスレコードをロックする
    • 主キーがクラスターインデックスで有ることに起因する

ギャプロック

  • レコードとレコードの間に対するロック
  • インデックスの先頭レコードの前に対するロック
  • インデックスの最後のレコードの後ろに対するロック
  • 分離レベルをREAD COMMITED以下にすれば発生しない

ネクストキーロック

  • レコードとその前に対するロック
  • レコードロック + ギャップロック
  • これによりファントムリードを防ぐ

インテンションロック

上3つが行レベルロックであるのに対し、インテンションロックはテーブルレベルロックである

INSERT 操作では行の挿入前に、挿入インテンションギャップロックと呼ばれる一種のギャップロックが設定される。 このロックは、同じインデックスギャップに挿入する複数のトランザクションは、そのギャップ内の同じ場所に挿入しなければ相互に待機する必要がないように、意図的に挿入することを示している。

で、結局仕組みは？

ギャップロックとネクトキーロックの詳細な仕組みは、以下の記事が非常にわかりやすいので委譲する。記事を読めばなぜファントムリードを防ぐことができるのか理解できる。

MySQL(InnoDB)のネクストキーロックの仕組みと範囲を図解する

参考サイト