MySQLの独立性レベルについて
5140 ワード
前言
データベースといえば、インデックスと独立性レベルの2つのコンテンツは避けられません.以下の内容は2編のこの内容の比較的に良い文章を紹介します:美団の評価チーム:Innodbの中の事務の隔離のレベルとロックの関係張洋さんの:MySQLインデックスの背後のデータの構造とアルゴリズムの原理
MySQLの隔離レベルについては、紹介する文章がたくさんありますが、多くは紹介に止まっているので、読むたびに概念的な認識があるだけですが、具体的な理解には個人的な理解が必要です.だからこのブログを書いて、点滴と理解を記録します.
説明:以下の内容をよりイメージ的に理解できるように、ぜひ試してみることをお勧めします.例えば2つのMySQLのクライアントを開くのが最も簡単な方法であるべきで、それからBEGIN、COMMINTなどの方式を利用してシミュレーションを行います
4つの独立性レベル
独立性レベル
ダーティリード
繰り返し不可(NonRepeatable Read)
ファントムリード
説明
未読(Read uncommitted/RU)
かもしれない
かもしれない
かもしれない
ダーティ・リードを許可します.つまり、他のセッションでトランザクションの変更がコミットされていないデータが読み込まれる可能性があります.
コミット済みリード(Read committed/RC)
あり得ない
かもしれない
かもしれない
コミットされたデータのみを読み込むことができます.Oracleなど多くのデータベースのデフォルトはこのレベルです(重複しない)
リピート可能(Repeatable read/RR)
あり得ない
あり得ない
かもしれない
繰り返し読むことができます.同じトランザクション内のクエリは、トランザクションの開始時刻に一致します.InnoDBのデフォルトレベルです.SQL規格では、この独立性レベルは重複しない読み取りを排除しますが、幻の読み取りも存在します.
シリアル化可能(Serializable)
あり得ない
あり得ない
あり得ない
完全にシリアル化された読み取りは、読むたびにテーブルレベルの共有ロックを取得する必要があり、読み書きは互いにブロックされます.
上の内容を理解するためには、まず汚い読み、繰り返してはいけない、幻読みの概念を理解する必要があります.汚読:汚読概念の鍵は汚であり、汚は偽、汚データと理解できる.つまり、2つのトランザクションAとトランザクションBです.トランザクションBは、トランザクションでデータを変更し、トランザクションAは、トランザクションBで変更されたデータを読み取ることができるが、トランザクションBで異常が発生してロールバックされた場合、トランザクションAで読み出されたデータは、実際にはダーティデータであり、いわゆるダーティリードである.以上の説明によれば、RU(コミットされていない読み取り)が汚い読み取りを避けることができない理由も理解しにくい. 繰り返し不可:再読み取り不可とは、トランザクションAとトランザクションB、例えばトランザクションAでid=1のデータの読み取りを行い、トランザクションBの後にid=2のデータをupdateしてコミットするトランザクションAとトランザクションBを指す.このときトランザクションAでid=1の読み取りを行っている場合、前回とは異なる内容が読み取られます.これはいわゆる繰り返し不可能読み出しであり,対応するのは独立性レベルのRCである. 幻読幻読の概念はよく理解する必要がある.実際に幻を理解するには、まずMySQLの2つの読み方の違いを理解する必要があります.スナップショット読み取り(snapshot read)と現在の読み取り(current read)です.この2つの読み取りの違いは、主にMySQLが採用したMVCCのバージョン制御によって楽観的なロックメカニズムが行われているためである.Innodbのトランザクション独立性レベルとロックの関係を次に説明します. トランザクションの独立性レベルは、実際にはデータの読み取りの定義であると疑問に思う読者もいるかもしれませんが、ここでは、読み取りと書き込みの2つのモジュールに分解されて説明されています.これは、主にMySQLの読み取りとトランザクション独立性レベルの読み取りが異なるためです.また、RRレベルでは、MVCCメカニズムにより、データを繰り返し読むことができますが、私たちが読んだデータは履歴データであり、タイムリーではないデータであり、データベースの現在のデータではありません.これは、データの時効に特に敏感なビジネスでは、問題が発生する可能性があります.このような履歴データの読み出し方法については、スナップショット読み出し(snapshot read)と呼び、データベースの現在のバージョンデータの読み出し方法については、現在読み出し(current read)と呼ぶ.明らかにMVCCではスナップショット読み:select
現在の読み取り:特殊な読み取り操作、挿入/更新/削除操作、現在の読み取りに属し、処理されているのはすべて現在のデータで、ロックが必要です.
事務A
事務B
BEGIN
BEGIN
SELECT * FROM tb_task;
INSERT INTO tb_task(id, name) VALUES(100, '111');
COMMIT;
UPDATE tb_task SET name = '123';
COMMIT
以上の概念を理解することは幻読を理解する上で重要である.実際の幻読みが発生する原因は,現在の読みによるものであるため,Insert,Update,Deleteなどの操作で幻読みが発生することが多い.例えば、AとBの2つの事務があります.
事務A
事務B
BEGIN
BEGIN
SELECT * FROM tb_task;
INSERT INTO tb_task(id, name) VALUES(100, '111');
COMMIT;
UPDATE tb_task SET name = '123';
COMMIT
つまり、トランザクションAでSELECTに来たときに1つのデータしかないことに気づきましたが、更新を行うと2つのデータが更新されます(トランザクションBが新しいデータを挿入して提出したため).多くの場合、トランザクションAでid=1のデータを更新するなど、幻読みの問題に直面する可能性がありますが、以前はトランザクションBがid=1のデータを削除していました.トランザクションAは、予想される1つのデータではなく、0つのデータを更新します.
ロック
Innodbのデフォルトの独立性レベルはRRであり,MVCCとローロックによってロックされる.MVCCは主に読み取りを対象とし、ロックは主に書くために衝突する.
1つのロックまたは2つのロック
事務A
事務B
begin;
begin;
update class_teacher set class_name='中学3年2組'where teacher_id=1;
update class_teacher set class_name='中学3年生'where teacher_id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;
大量の同時アクセスがあるため、デッドロックを予防するために、一般的なアプリケーションでは、メソッドの開始段階で、どのデータが使用されるかを事前に知っておき、すべてロックし、メソッドが実行された後、すべてロックを解除することを推奨しています.この方法は、トランザクションの開始段階でデータベースがどのデータを使用するか分からないため、ループデッドロックを効果的に回避できますが、データベースでは適用されません.データベースは、トランザクションを2つのフェーズ、ロック・フェーズ、およびロック解除フェーズに分割する2つのロック・プロトコルに従っています(したがって、2つのロックと呼ばれます).以下の例で説明します(ブログInnodbのトランザクション・アイソレーション・レベルとロックの関係から).
事務A
事務B
begin;
begin;
update class_teacher set class_name='中学3年2組'where teacher_id=1;
update class_teacher set class_name='中学3年生'where teacher_id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;
同時実行中の変更の競合を防止するため、トランザクションAのMySQLはteacher_に与えられます.id=1のデータ行がロックされ、commit(ロック解除)されないと、トランザクションBはその行ロックをタイムアウトするまでwaitを取得できません.
この時私たちは注意しなければなりませんteacheridはインデックスがあり、インデックスがないclassであれば名前は?update class_teacher set teacher_id=3 where class_name='初三一班';では、MySQLはテーブル全体のすべてのデータ行にロックをかけます.ここはちょっと不思議に聞こえますが、sqlが実行されている間、MySQLはどのデータ行がclassなのか分かりません.name='中学3年1組'の(インデックスはありませんか)は、1つの条件でインデックスで迅速にフィルタリングできない場合、ストレージエンジンレベルですべてのレコードをロックして戻り、MySQL Serverレベルでフィルタリングします.
しかし、実際の使用過程でMySQLはいくつかの改善を行い、MySQL Serverのフィルタ条件が満たされていないことを発見した後、unlock_を呼び出します.row法は,条件を満たさない記録をロック解除する(二段ロックプロトコルの制約に反する).これにより,最終的には条件を満たすレコード上のロックのみが保持されることが保証されるが,各レコードのロック操作は省略できない.MySQLでも効率のためには仕様に違反していることがわかります.(『高性能MySQL』中国語第三版p 181参照.
実際には,ローロックに加えて,このようなトランザクション間の書き込みによる競合を防止するためにGAPギャップロックも導入されている.詳細については、『高性能MySQL』を参照してください.
データベースといえば、インデックスと独立性レベルの2つのコンテンツは避けられません.以下の内容は2編のこの内容の比較的に良い文章を紹介します:美団の評価チーム:Innodbの中の事務の隔離のレベルとロックの関係張洋さんの:MySQLインデックスの背後のデータの構造とアルゴリズムの原理
MySQLの隔離レベルについては、紹介する文章がたくさんありますが、多くは紹介に止まっているので、読むたびに概念的な認識があるだけですが、具体的な理解には個人的な理解が必要です.だからこのブログを書いて、点滴と理解を記録します.
説明:以下の内容をよりイメージ的に理解できるように、ぜひ試してみることをお勧めします.例えば2つのMySQLのクライアントを開くのが最も簡単な方法であるべきで、それからBEGIN、COMMINTなどの方式を利用してシミュレーションを行います
4つの独立性レベル
独立性レベル
ダーティリード
繰り返し不可(NonRepeatable Read)
ファントムリード
説明
未読(Read uncommitted/RU)
かもしれない
かもしれない
かもしれない
ダーティ・リードを許可します.つまり、他のセッションでトランザクションの変更がコミットされていないデータが読み込まれる可能性があります.
コミット済みリード(Read committed/RC)
あり得ない
かもしれない
かもしれない
コミットされたデータのみを読み込むことができます.Oracleなど多くのデータベースのデフォルトはこのレベルです(重複しない)
リピート可能(Repeatable read/RR)
あり得ない
あり得ない
かもしれない
繰り返し読むことができます.同じトランザクション内のクエリは、トランザクションの開始時刻に一致します.InnoDBのデフォルトレベルです.SQL規格では、この独立性レベルは重複しない読み取りを排除しますが、幻の読み取りも存在します.
シリアル化可能(Serializable)
あり得ない
あり得ない
あり得ない
完全にシリアル化された読み取りは、読むたびにテーブルレベルの共有ロックを取得する必要があり、読み書きは互いにブロックされます.
上の内容を理解するためには、まず汚い読み、繰り返してはいけない、幻読みの概念を理解する必要があります.
select * from table ....;
現在の読み取り:特殊な読み取り操作、挿入/更新/削除操作、現在の読み取りに属し、処理されているのはすべて現在のデータで、ロックが必要です.
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;
事務A
事務B
BEGIN
BEGIN
SELECT * FROM tb_task;
INSERT INTO tb_task(id, name) VALUES(100, '111');
COMMIT;
UPDATE tb_task SET name = '123';
COMMIT
以上の概念を理解することは幻読を理解する上で重要である.実際の幻読みが発生する原因は,現在の読みによるものであるため,Insert,Update,Deleteなどの操作で幻読みが発生することが多い.例えば、AとBの2つの事務があります.
事務A
事務B
BEGIN
BEGIN
SELECT * FROM tb_task;
INSERT INTO tb_task(id, name) VALUES(100, '111');
COMMIT;
UPDATE tb_task SET name = '123';
COMMIT
つまり、トランザクションAでSELECTに来たときに1つのデータしかないことに気づきましたが、更新を行うと2つのデータが更新されます(トランザクションBが新しいデータを挿入して提出したため).多くの場合、トランザクションAでid=1のデータを更新するなど、幻読みの問題に直面する可能性がありますが、以前はトランザクションBがid=1のデータを削除していました.トランザクションAは、予想される1つのデータではなく、0つのデータを更新します.
ロック
Innodbのデフォルトの独立性レベルはRRであり,MVCCとローロックによってロックされる.MVCCは主に読み取りを対象とし、ロックは主に書くために衝突する.
1つのロックまたは2つのロック
事務A
事務B
begin;
begin;
update class_teacher set class_name='中学3年2組'where teacher_id=1;
update class_teacher set class_name='中学3年生'where teacher_id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;
大量の同時アクセスがあるため、デッドロックを予防するために、一般的なアプリケーションでは、メソッドの開始段階で、どのデータが使用されるかを事前に知っておき、すべてロックし、メソッドが実行された後、すべてロックを解除することを推奨しています.この方法は、トランザクションの開始段階でデータベースがどのデータを使用するか分からないため、ループデッドロックを効果的に回避できますが、データベースでは適用されません.データベースは、トランザクションを2つのフェーズ、ロック・フェーズ、およびロック解除フェーズに分割する2つのロック・プロトコルに従っています(したがって、2つのロックと呼ばれます).以下の例で説明します(ブログInnodbのトランザクション・アイソレーション・レベルとロックの関係から).
事務A
事務B
begin;
begin;
update class_teacher set class_name='中学3年2組'where teacher_id=1;
update class_teacher set class_name='中学3年生'where teacher_id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;
同時実行中の変更の競合を防止するため、トランザクションAのMySQLはteacher_に与えられます.id=1のデータ行がロックされ、commit(ロック解除)されないと、トランザクションBはその行ロックをタイムアウトするまでwaitを取得できません.
この時私たちは注意しなければなりませんteacheridはインデックスがあり、インデックスがないclassであれば名前は?update class_teacher set teacher_id=3 where class_name='初三一班';では、MySQLはテーブル全体のすべてのデータ行にロックをかけます.ここはちょっと不思議に聞こえますが、sqlが実行されている間、MySQLはどのデータ行がclassなのか分かりません.name='中学3年1組'の(インデックスはありませんか)は、1つの条件でインデックスで迅速にフィルタリングできない場合、ストレージエンジンレベルですべてのレコードをロックして戻り、MySQL Serverレベルでフィルタリングします.
しかし、実際の使用過程でMySQLはいくつかの改善を行い、MySQL Serverのフィルタ条件が満たされていないことを発見した後、unlock_を呼び出します.row法は,条件を満たさない記録をロック解除する(二段ロックプロトコルの制約に反する).これにより,最終的には条件を満たすレコード上のロックのみが保持されることが保証されるが,各レコードのロック操作は省略できない.MySQLでも効率のためには仕様に違反していることがわかります.(『高性能MySQL』中国語第三版p 181参照.
実際には,ローロックに加えて,このようなトランザクション間の書き込みによる競合を防止するためにGAPギャップロックも導入されている.詳細については、『高性能MySQL』を参照してください.