Mysql(Innodb)幻読を避けるには
18307 ワード
幻読Phantom Rows
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
幻読問題とは、1つのトランザクションの2回の異なる時間の同じクエリが異なる結果セットを返すことです.例えば、1つのselect文は2回実行するが、2回目に1回目に返されなかった行が返されると、これらの行は「phantom」rowである.
read view(またはMVCC)は、コンシステンシ非ロック読み(Consistent Nonlocking Reads)を実現し、幻読みを回避する
実験1:
2つのウィンドウを開く
2つのセッションがトランザクションを開始
SESSION_A現在の時点のスナップショット
SESSION_Bレコードを挿入して発行する
SESSION_A
SESSION_A最初のクエリはSESSIONで開始されるBデータを挿入する前にSELECT操作の時間を基準としたread viewを作成し、幻読みの発生を回避したのでSESSION_Aのトランザクションが終了するまでSESSIONは表示されません.B対表read_viewによる変更(insert,delete,update)
実験2
2つのセッションがトランザクションを開始
SESSION_BはSESSION_A read viewを作成する前にデータを挿入する
SESSION_A
SESSION_A最初のクエリはSESSIONで開始するBテーブルを変更して提出したので、今回のread viewにはSESSION_が含まれていますBによる変更
公式文書にはhttp://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.
コンシステンシリードは、MVCCを介してクエリに時間ベースのポイントを提供するスナップショットです.このクエリは、自分の前にコミットされたデータしか表示されませんが、クエリの開始後にコミットされたデータは表示されません.1つの特例は、このクエリが自分が開始した後の同じトランザクションによって生じた変化を見ることができることです.この特例はいくつかの異常な現象を生じる.
If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.
デフォルトの独立性レベルREPEATABLE READでは、同じトランザクションのすべての一貫性読み取りは、最初のクエリで作成されたスナップショットのみを読み込みます.
実験3
2つのセッションがトランザクションを開始
実験手順を観察すると,最後から2回目のクエリでは存在しない状態が現れた.
the anomaly means that you might see the table in a state that never existed in the database
ここでAの前後2回の読みは,いずれもスナップショット読みであり,同じトランザクションにある.ただし、Bは直接コミットを挿入し、このときAはupdate、updateは現在の読み取りに属するので、新しく挿入した行に作用し、変更行の現在のバージョン番号をAのトランザクション番号に設定することができるので、2回目のスナップショット読み取りは、同じトランザクション番号であるため読み取ることができます.このような状況はMVCCのルールに合致しており、一種の幻読と呼ぶにはやむを得ないが、特殊な状況として扱われるだろう.
With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
read commit独立性レベルでは、同じトランザクションの各コンシステンシはsets and reads its own fresh snapshotを読みます.
実験4
トランザクション独立性レベルの変更
read commitは読み込むたびに新しいスナップショットです
InnoDBはNextkey lockにより現在の読み時の幻読み問題を解決した
Innodb行ロックは次のように分けられます.
を選択します.
説明
Record Lock:
インデックスに単行レコードをロックする.
Gap Lock:
1つの範囲の記録をロックするが、記録自体は含まない.未使用の空き領域には、2つのインデックスレコードの間、または最初のインデックスレコードの前または最後のインデックスの後の空間がロック.
Next-Key Lock:
行ロックとクリアランスロックを組み合わせてNext-Key Lockと言います.範囲をロックし、レコード自体をロックします.ローのクエリーでは、このメソッドが使用され、主な目的は幻読の問題を解決することです.
実験5
テーブルの作成
実験を始める
現在の読み出しモード
できるだけユニークインデックスを使用します.なぜなら、ユニークインデックスはNext-Key LockをRecord Lockに降格させるからです.
実験6
テーブルを作成(mysql@localhost) [fandb]> create table t6(id int primary key); Query OK, 0 rows affected (0.02 sec)
SESSION_A>insert into t6 values(1),(4),(7),(10); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
実験を始める
id列に一意のインデックスがある場合、Next-Key LockはRecords Lockに降格します.
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
幻読問題とは、1つのトランザクションの2回の異なる時間の同じクエリが異なる結果セットを返すことです.例えば、1つのselect文は2回実行するが、2回目に1回目に返されなかった行が返されると、これらの行は「phantom」rowである.
read view(またはMVCC)は、コンシステンシ非ロック読み(Consistent Nonlocking Reads)を実現し、幻読みを回避する
実験1:
2つのウィンドウを開く
set session tx_isolation='REPEATABLE-READ';
select @@session.autocommit;select @@global.tx_isolation,@@session.tx_isolation;
create table read_view(text varchar(50));
insert into read_view values('init');2つのセッションがトランザクションを開始
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)SESSION_A現在の時点のスナップショット
START TRANSACTION WITH CONSISTENT SNAPSHOT;を作成しても同様の効果を得ることができる任意のテーブルにアクセスできるクエリーを実行します.SESSION_A>select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)SESSION_Bレコードを挿入して発行する
SESSION_B>insert into read_view values('after session A select');
Query OK, 1 row affected (0.01 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)SESSION_A
SESSION_A>select * from read_view;
+------+
| text |
+------+
| init |
+------+
1 row in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+------------------------+
| text |
+------------------------+
| init |
| after session A select |
+------------------------+
2 rows in set (0.00 sec)SESSION_A最初のクエリはSESSIONで開始されるBデータを挿入する前にSELECT操作の時間を基準としたread viewを作成し、幻読みの発生を回避したのでSESSION_Aのトランザクションが終了するまでSESSIONは表示されません.B対表read_viewによる変更(insert,delete,update)
実験2
2つのセッションがトランザクションを開始
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)SESSION_BはSESSION_A read viewを作成する前にデータを挿入する
SESSION_B>insert into read_view values('before Session_A select');
Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)SESSION_A
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_A>commit
-> ;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)SESSION_A最初のクエリはSESSIONで開始するBテーブルを変更して提出したので、今回のread viewにはSESSION_が含まれていますBによる変更
公式文書にはhttp://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.
コンシステンシリードは、MVCCを介してクエリに時間ベースのポイントを提供するスナップショットです.このクエリは、自分の前にコミットされたデータしか表示されませんが、クエリの開始後にコミットされたデータは表示されません.1つの特例は、このクエリが自分が開始した後の同じトランザクションによって生じた変化を見ることができることです.この特例はいくつかの異常な現象を生じる.
If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.
デフォルトの独立性レベルREPEATABLE READでは、同じトランザクションのすべての一貫性読み取りは、最初のクエリで作成されたスナップショットのみを読み込みます.
実験3
2つのセッションがトランザクションを開始
SESSION_A
SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_B>insert into read_view values('anomaly'),('anomaly');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
SESSION_B>update read_view set text='INIT' where text='init';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_A " "
SESSION_A>update read_view set text='anomaly!' where text='anomaly';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)実験手順を観察すると,最後から2回目のクエリでは存在しない状態が現れた.
the anomaly means that you might see the table in a state that never existed in the database
ここでAの前後2回の読みは,いずれもスナップショット読みであり,同じトランザクションにある.ただし、Bは直接コミットを挿入し、このときAはupdate、updateは現在の読み取りに属するので、新しく挿入した行に作用し、変更行の現在のバージョン番号をAのトランザクション番号に設定することができるので、2回目のスナップショット読み取りは、同じトランザクション番号であるため読み取ることができます.このような状況はMVCCのルールに合致しており、一種の幻読と呼ぶにはやむを得ないが、特殊な状況として扱われるだろう.
With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
read commit独立性レベルでは、同じトランザクションの各コンシステンシはsets and reads its own fresh snapshotを読みます.
実験4
トランザクション独立性レベルの変更
set session tx_isolation='READ-COMMITTED' 2セッション開始トランザクションSESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
SESSION_B>insert into read_view values('hehe');
Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
| hehe |
+-------------------------+
6 rows in set (0.00 sec)read commitは読み込むたびに新しいスナップショットです
InnoDBはNextkey lockにより現在の読み時の幻読み問題を解決した
Innodb行ロックは次のように分けられます.
を選択します.
説明
Record Lock:
インデックスに単行レコードをロックする.
Gap Lock:
1つの範囲の記録をロックするが、記録自体は含まない.未使用の空き領域には、2つのインデックスレコードの間、または最初のインデックスレコードの前または最後のインデックスの後の空間がロック.
Next-Key Lock:
行ロックとクリアランスロックを組み合わせてNext-Key Lockと言います.範囲をロックし、レコード自体をロックします.ローのクエリーでは、このメソッドが使用され、主な目的は幻読の問題を解決することです.
実験5
テーブルの作成
(mysql@localhost) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)
SESSION_A>insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0実験を始める
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)
SESSION_A>select * from t5 where id=7 for update;
+------+
| id |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t5 values(5); --
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(7); --
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(9); --
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 2 |
| 4 |
| 7 |
| 10 |
| 12 |
+------+
6 rows in set (0.00 sec)現在の読み出しモード
select * from t5 where id=7 for update;でid=7のデータを取得すると、Next-Key Lockが生成され、4-10の範囲とid=7の単一レコードがロックされ、SESSION_がブロックされるBはこの範囲にデータを挿入し、それ以外の範囲にデータを挿入することができる.最後から2番目のクエリでは、read viewの存在により、2と12の2つのデータを見ることが回避され、同時にNext-Key Lockの存在により、他のコールバック挿入データがブロックされるため、現在のモード読み取りでは幻読み取りは発生しない(select for updateは、前読みモードでデータを取得する)できるだけユニークインデックスを使用します.なぜなら、ユニークインデックスはNext-Key LockをRecord Lockに降格させるからです.
実験6
テーブルを作成(mysql@localhost) [fandb]> create table t6(id int primary key); Query OK, 0 rows affected (0.02 sec)
SESSION_A>insert into t6 values(1),(4),(7),(10); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
実験を始める
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)
SESSION_A>select * from t6 where id=7 for update;
+----+
| id |
+----+
| 7 |
+----+
1 row in set (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>insert into t6 values(5); --
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t6 values(8); --
Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
+----+
6 rows in set (0.00 sec)id列に一意のインデックスがある場合、Next-Key LockはRecords Lockに降格します.