The difference between MySQL RC and RR

1. Database Transaction ACID Properties

4 Features of database transactions:
Atomicity (Atomic): operations in a transaction either succeed or fail;
Consistency (consistency): After a transactional operation, the state of the database and the business rules are consistent; For example, the total amount of the account is not changed after mutual transfer;
Isolation (Isolation): multiple transactions, like serial execution, do not affect each other;
Persistence (Durability): After a transaction commits, it is persisted to persistent storage.

2. Isolation

There are four types of isolation :

READ UNCOMMITTED: Can read UNCOMMITTED data, uncommitted data is called dirty data, so also known as dirty read. At this time: Phantom reading, non-repeatable reading and dirty reading are allowed;
Read COMMITTED: Can only read the data that has been submitted, at this time: Allow Phantom Read and non-repeatable read, but do not allow dirty read, so the RC isolation level requires to resolve dirty read;
REPEATABLE READ: Executing the same select multiple times in the same transaction, the data read does not change; At this time: Phantom reads are allowed, but non-repeatable reads and dirty reads are not allowed, so the RR isolation level requires resolution of non-repeatable reads;
SERIALIZABLE: Phantom Reading, non-repeatable reading and dirty reading are not allowed, so SERIALIZABLE request to solve the Phantom read;

3. Several concepts

Dirty reads : UNCOMMITTED data can be read. RC required to resolve dirty read;

non-repeatable reads : The same select is executed multiple times in the same transaction, and the data read is changed (update and commit by other transaction);

Repeatable reads : The same select is executed multiple times in the same transaction, the data read is not changed (generally using the MVCC implementation); RR levels are required to meet repeatable reading standards;

Phantom reads : The same select is executed multiple times in the same transaction, and the data rows read are changed. That is, the number of rows decreased or increased (Delete/insert and submitted by other transactions). Serializable request to solve the Phantom reading problem;

Be sure to differentiate between non-repeatable and Phantom reads:

The key to non-repeatable reading is to modify :
The same condition of SELECT, the data you read, read it again and find the value is different.

The focus of Phantom reading is to Add or delete :
The same conditions for select, the 1th and 2nd read out the number of records are different

In terms of results, both are inconsistent with the results of multiple reads. But if you look at it from the point of view of implementation, they are quite different:
For the former, under RC only need to lock the record that satisfies the condition, can avoid being modified by other transaction, namely select for Update, select in Share mode; Use MVCC to realize repeatable reading under RR isolation;
For the latter, to lock the records that satisfy the condition and the gap between all these records, that is, Gap lock is required.

While the ANSI SQL standard does not define isolation, it defines the level of isolation for transactions, and defines the three major concurrency problems that are addressed by different transaction isolation levels:

Isolation level	Dirty Read	unrepeatable Read	Phantom Read
Read UNCOMMITTED	YES	YES	YES
READ COMMITTED	NO	YES	YES
READ Repeatable	NO	NO	YES
SERIALIZABLE	NO	NO	NO

4. Default isolation level for databases

In addition to the default RR isolation level for MySQL, several large databases use the RC isolation level.

But their implementation is also very different. Oracle only implements the RC and serializable isolation levels. The RC isolation level is used by default to resolve dirty reads. However, non-repeatable reads and Phantom reads are allowed. The serializable is the solution of dirty reading, non-repeatable reading, and Phantom reading.

MySQL implementation: MySQL default to the RR isolation level, the SQL standard is to require RR to solve the problem of non-repeatable read, but because MySQL uses the gap lock, so the actual MySQL RR isolation level also solves the problem of Phantom reading. So what's the serializable of MySQL? In fact, the MySQL serializable adopted a classic implementation, both read and write lock.

So how does MySQL's RR isolation level resolve non-repeatable read and Phantom reads?

5. Differences between RC and RR isolation levels in MySQL

Place

The difference between MySQL RC and RR