MySQL Resolving Phantom Reads - MVCC and Gap Locks

Currently reading withsnapshottingphrase marked by pause

Current reading:

select...lock in share mode (shared reading lock)
select...for update
update , delete , insert

• 1
• 2
• 3

The current read, read the latest version, and read the record lock, blocking other transactions at the same time to change the same record, to avoid security problems.

For example, suppose you want to update a record, but another transaction has already deleted the data and committed, if you don't lock it, it will create a conflict. So the update must be the current read, get the latest information and lock the corresponding record.

Snapshot reading:
simpleselectmanipulate(exclusive of select … lock in share mode, select … for update)。

Read Committed isolation level: each select generates a snapshot read.

Read Repeatable isolation level: the first select statement after the transaction is opened is where the snapshot is read, rather than as soon as the transaction is opened.

At the RR level, snapshot reads are achieved by MVVC (multiple version control) and undo log, and current reads are achieved by adding record lock and gap lock.

How to solvefantasy reading

Phantom reads for snapshot reads are solved with MVCC, and phantom reads for current reads are solved with gap locks.

The default transaction isolation level for innodb is rr (repeatable read).Its implementation technology is mvcc。This technique not only ensures repeatable reads of innodb, but also prevents phantom reads. (This is also is previously practiced with rr isolation level, not only can prevent repeatable reads, but also prevented phantom reads) But it prevents snapshot reads, that is, read data although consistent, but the data is historical data.

There is an example of this within this post:Phantom Read Problems with MySQL's InnoDB
Some articles have written that InnoDB's repeatable reads avoid "phantom reads", which is not accurate.

So what's up with InnoDB pointing out that phantom reads can be avoided?

The following is translated fromMySQLOfficial document (/pub6/mysql/doc/refman/5.5/en/), translation level is average, please forgive me.

When the isolation level is repeatable reads and innodb_locks_unsafe_for_binlog is disabled, phantom reads can be avoided by next-key locks that are used during searches and scans of the index. That is to say gap locks.

InnoDB provides next-key locks, but requires the application to add the locks itself

An example:

SELECT * FROM child WHERE id> 100 FOR UPDATE;

• 1

The query scans the index from the first row with an id greater than 100 . If the table contains rows with id values between 90 and 102. If the locks set on the indexed rows in the scan range do not lock what is inserted in the gap (in this case, between 90 and 102. another transaction can insert a new row into the table with a row id of 101. if you were to perform the same SELECT in the same transaction, you would see a new row with an id of 101 in the result set returned by the query (a "phantom"), which creates a phantom read. This is the opposite of the isolation principle of transactions: a transaction should be able to run so that the data it has read does not change during the course of the transaction. If we consider a set of rows as data items, the new "phantom" child record may violate this isolation principle.

In order to prevent "phantoms", InnoDB uses an application callednext-key lockingInnoDB performs row-level locking by combining index row locking with gap locking.The way InnoDB performs row-level locking is that when it searches or scans a table index, it sets shared or exclusive locks on the index records that it encounters. Thus, 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, the next-key lock is an index-record lock plus a gap lock on the gap before the index record. If a session has a shared or exclusive lock on a record R in the index, another session cannot insert a new index record in the gap before R.

When InnoDB scans an index, it also locks the gap after the last of all records. This just happened in the previous example: the locking set by InnoDB prevents any inserts into tables where the id could be greater than 100. All inserts with an id of 101 are not executable!

You can use next-key locks to implement uniqueness checking in your application: if you read data with a shared lock (select ... from lock in share mode; generates a table-level shared lock that allows other threads to read the data but not modify it.) If you read the data with a shared lock and do not see the row you want to copy inserted, then you can safely insert the row and understand that the next-key lock set on subsequent rows of your row during the read will prevent anyone from inserting a copy of your row at the same time. Thus, next-key locks allow you to "lock" content that does not exist in the table.

Suppose we have a table: city with the following structure:

Start a transaction in the first query window:

Ask, what are the results of each of the three SELECTs for transaction A:

First: Nanjing, Guangzhou, Hangzhou
Second: Nanjing, Guangzhou, Hangzhou
Third: Nanjing, Guangzhou, Hangzhou, Chengdu

We can see that if you use normal reads, you will get consistent results, and if you use locked reads, you will read the results of the "latest" "commit" reads. If you need to display the data in real time, you still need to manually lock to achieve. This time will use next-key technology to achieve.

itself.Repeatable reads and commit reads are contradictory. In the same transaction, if repeatable reads are guaranteed, you will not see the commits of other transactions, violating commit reads; if commit reads are guaranteed, it will lead to inconsistent results between the previous and previous reads, violating repeatable reads.InnoDB provides such a mechanism, and in the default isolation level of repeatable reads, you can use locking reads to query the latest data: IN SHARE MODE;

Let's open another transaction and execute it:

BEGIN;
INSERT INTO city VALUES (7, 'Jinan');
COMMIT;

• 1
• 2
• 3

What's going to happen?
The result is blocking. Because of the added gap lock

Let's open another transaction and execute it:

BEGIN;
UPDATE city SET name = 'Jinan' WHERE ID = 1
UPDATE city SET name = 'Jinan' WHERE ID = 2
COMMIT;

• 1
• 2
• 3
• 4

What's going to happen?
Modification successful, no problem, gap lock not added here.

If I write

BEGIN;
UPDATE city SET name = 'Jinan' WHERE ID = 1

• 1
• 2

Definitely blocking, because of the gap lock.

Conclusion: Repeatable reads in MySQL InnoDB are not guaranteed to avoid phantom reads and need to be guaranteed by the application using locking reads. The mechanism used for this locking degree is next-key locks.

In mysql, two transaction isolation techniques are provided, the first is mvcc and the second is next-key technique. This can be chosen dynamically when using different statements. Snapshot reads without lock inshare mode or the like use mvcc. Otherwise, current reads use next-key.Advantage of mvcc is no locking and high concurrency. Disadvantage is not real-time data. next-key has the advantage of getting real-time data, but requires locking.

Also, important:

At the rr level.mvcc completely solves duplicate reads, but doesn't really avoid phantom reads completely, only in some scenarios using historical data to circumvent phantom readings

For snapshot reads, mysql partially avoids phantom reads by using mvcc to utilize historical data (in some scenarios it looks like phantom reads are circumvented)

To avoid it completely, you need to manually add locks to adjust snapshot reads to current reads (mysql does not automatically add locks), then mysql uses next-key to avoid phantom reads completely, for example, under rr, lock 1 (0, 2, 3, 4), another thread's INSERT 3 that is, it is blocked, and under rrc, the other thread can still waltz in and insert it, such as the current thread querying again such as count, it will be inconsistent

It is recommended to go to the official documentation.

Reference Article:
innodb current read vs. snapshot read
Phantom Read Problems with MySQL's InnoDB
Related official documentation
Locking Mechanisms in MySQL - Record Locks, Gap Locks, Pro-Key Locks
[MySQL] Current Read, Snapshot Read, MVCC