MySQL幻読およびGAP、Next-Key Locking

幻読phantomとは

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.

以上はMySQLの公式定義で、私たちは簡単な翻訳の下で:幻読湖は今1つの事務の中で、同じクエリー文は異なる時間に異なる結果を返しました.たとえば、1つのselect文は2回実行されますが、2回目に返されるrowsは1回目に返されるものではありません.この行は幻読みと呼ばれます.データベース定義の4つの独立性レベルのうち、最も高い独立性レベルSERIALIZABLE_READは幻読みの問題が発生しないことを保証できる.一方、RR(Repeatable Read)レベルでは、現在の読み取りに対して、RR分離レベルは、読み取った記録にロック(記録ロック)をかけることを保証するとともに、読み取った範囲にロックをかけることを保証し、新たなクエリー条件を満たす記録は挿入(スロットロック)できず、幻読現象は存在しない.しかし、厳密な意味では、ギャップロックは幻読を完全に解決していません.例えば、以下のようにします.

set session transaction isolation level repeatable read;

まず、トランザクション独立性レベルをRRに設定します.
Session A
Session B
start transaction;
start transaction;
select * From test where id < 10;
insert into test values(5, 2, 2);
insert into test values(5, 2, 2);
commit;
commit;
以上のような挿入シーンでは,Aトランザクションの読み出し行数はidが5のレコードはないが,我々がレコードを挿入するとロック待ちが発生し,一定の意味で幻読みも発生する.
ファンタジーの解決方法
RC(Read Committed)レベルで生成されたローロックを知っています.一方,RRレベルでは,新しいロックGAP(ギャップロック)とNext−Keylockingが導入された.

To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

上記の5.7バージョンのソリューションでは、幻読みを阻止するために、InnoDBはnext-key lockingと呼ばれるアルゴリズムを提供し、next-key lockingはインデックス行ロックとgap lockingを結合しています.nex-key lockは、現在のインデックス行だけでなく、インデックス行の前後のレコードもロックします.
GAPロック

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

GAPロック、すなわちインデックスの前後のレコードをロックします.
GAPロックは1行の記録、複数行の記録、さらには空行をロックすることができる.

A gap might span a single index value, multiple index values, or even be empty.

次のテストでは、まず独立性レベルをRRに設定します(ギャップロックのため、RRレベルが有効になるだけです).同時に、テーブル全体のデータを空にします.次のトランザクションは空のテーブルに対して行われます.
Session A
Session B
start transaction;
start transaction;
SELECT * FROM test WHERE id BETWEEN 4 and 10 FOR UPDATE;
insert into test values(5, 2, 2);
commit;
commit;
以上のように、セッションBがinsert文を実行すると、ロック待ちが発生し、テーブル全体が空のローになります.同じ文では、RC独立性レベルではロック待ちは発生しません(RCにはGAPロックがないため)

show engine innodb status;

対応するロック情報の表示

---TRANSACTION 4116897, ACTIVE 723 sec
1 lock struct(s), heap size 1184, 0 row lock(s)
MySQL thread id 66, OS thread handle 0x70000b16a000, query id 1712 localhost root cleaning up
Trx read view will not see trx with id >= 4116898, sees < 4116896
---TRANSACTION 4116896, ACTIVE 729 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 65, OS thread handle 0x70000b126000, query id 1625 localhost root cleaning up

GAPロックの場合、現在のインデックス行が一意である場合、現在のインデックスのみがロックされ、インデックスの前後行はロックされません.現在のインデックスが一意でない場合、またはデータが存在しない場合、前後行データはロックされます.

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

ここでは、トランザクションが実行される前に、トランザクションが実行されるときにinsertではなく、ローがロックされ、insertローがロックされると、他のトランザクションではデータが表示されないため、ギャップが発生する前提にも注意してください.以下のRRレベルで、行idが4のレコードを挿入します.

insert into test values(4, 2, 2);

Session A
Session B
start transaction;
start transaction;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
insert into test values(5, 2, 2);
commit;
commit;
このようなトランザクションは正常に実行され、ギャップロックは発生しません.
GAPロックは主に挿入文のため、更新文はGAPを生成しません

Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function

GAPロックの目的は主に挿入問題を解決するためであり,異なるトランザクションが同時にGAPロックを持つことができる.
GAPは主にRR独立性レベルに基づく

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows 

参照先:https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html