From: juejin. Cn/post / 684490…

How is innoDB magic solved at RR level?

Let’s start with a quick review of database isolation levels

  1. RU(Read Uncommitted)

When one transaction A reads uncommitted changes from another transaction B, B rolls back, causing data inconsistencies. (Phenomenon: Dirty reading)

  1. RC(Read Committed)

A transaction A reads A value that is inconsistent with A value that is read A second time during the execution of the transaction because transaction B modified the data and committed the transaction between the two reads. (Symptom: Unrepeatable read)

  1. RR(Repeatable Read)

The first read of A transaction is the same as the second read, but the insert or delete operations of other transactions affect the number of queries.

  1. Serializable

Highest transaction isolation level, serialization.

So why innoDB can solve illusions in RR? Isn’t it contradictory?

Explanation: This is due to inconsistency between InnoDB and standards. Can Innodb RR prevent illusions under github?

Conclusion: Under RR, innoDB illusion is solved by MVCC or GAP lock or next-key lock.

For more information about MVCC, see the article MVCC Principles

Select count(*) from table where col_name = XXX from table where col_name = XXX The readView statement will find the eligible rows and count them. So how to find the rows that meet the conditions, meet the WHERE condition and also meet the visibility of the transaction to these rows. So there is no illusion in the same transaction.

For more information about GAP locks, see the article next-key Lock

Here we need to understand the difference between current reads and snapshot reads

  1. Snapshot read: a simple select operation. Snapshot read is not locked. select * from table where ? ;

  2. Current read: special read operations, such as insert/update/delete operations, belong to current read and need to be locked. select * from table where ? lock in share mode; select * from table where ? for update; Insert into table values (…) ; update table set ? where ? ; delete from table where ? ; All of the above statements belong to the current read, the latest version of the read record. In addition, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. Except for the first statement, which locks the read record with S (shared lock), all other operations are locked with X (exclusive lock).

Here are two pieces of pseudocode:

//code 1
beginTransaction

delete * from table whereid = ? EndTransaction //code 2 beginTransaction SELECT count(*) from tablewhere id = ? forUpdate (add secondary index (not unique index)) endTransaction copy codeCopy the code
  1. Code 1 analysis:

    Due to thedelete * from table where id = ?This is the current read, and it will lock a range using a GAP lock, so that no other transaction can insert data within the range that meets the condition, and thus solve the phantom read.
  2. Code 2 analysis:

    Due to theselect count(*) from table where id = ? for updateIt is also a current read, but its lock is next-key-lock, which is made up of a GAP lock and a record lock, so it can also lock the scope to prevent other transactions from inserting the data, lock the record itself, and prevent other transactions from modifying the data. This also avoids phantom reading