The premise for all of this article is that MySQL’s storage engine is Innodb.

We know that MySql’s default transaction isolation level is RR (repeatable reads), so why do most companies use MySql with transaction isolation level RC(read commit)?

Transaction isolation level

The transaction isolation levels of the database are read uncommitted, read committed, repeatable read, and serialized. The problem of dirty reads exists at the read uncommitted isolation level, and the serialization execution is inefficient. Therefore, two isolation levels, read commit and repeatable read, are generally selected.

Read commit avoids the phenomenon of dirty read, but does not solve the problem of phantom read; Repeatable reading solves the problem of illusory reading.

Second, the phantom reads

What is illusory?

In the same transaction, two queries of the same range (the same query SQL) are returned with inconsistent results. It is important to note that the results of the later query are not consistent with the results of the previous query, that is, new data is inserted into the range. Phantom read is only for the current read, snapshot read does not appear phantom.

When you see the above description of hallucination, you will generally have the following questions:

  1. The data rows in the two query results are the same, but some rows are not modified. For example, the fields in a row are modified. This is also considered as the results of the two queries are not exactly the same.
  2. What is current read and snapshot read, and what is the difference?

The case of Question 1 is not a phantom read, it is a dirty read and only occurs at the isolation level of uncommitted reads.

Current read refers to reading the latest data and locking the read record. The locking depends on the transaction isolation level, including the following situations:

select.forupdate; update... ; insert... ; delete... ;Copy the code

Snapshot reads refer to simple SELECT statements.

How to solve illusory reading?

MySql solves the illusion problem at the repeatable read isolation level.

First, take a look at this example:

  1. Start by creating a T_demo table and inserting a few pieces of data.
CREATE TABLE `t_demo` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB;

insert into t_demo values(1.1.1), (5.5.5), (10.10.10), (15.15.15);

Copy the code
  1. Transaction execution under RC
Session A Session B Session C
t1 begin;

select * from t_demo where b = 10 for update;
t2 update t_demo set b = 10 where id = 1;
t3 Insert into t_demo values,2,10 (2);
t4 select * from t_demo where b = 10 for update;

commit;

If, at the RC isolation level, Session B is blocked on update until Session A commits at t4 and Session C is not blocked on INSERT at T3, As A result, Session A at time T1 and time T4 has two identical query results that are different. This is the case of phantom read at RC isolation level.

This example shows that RC can solve the problem of dirty read, but cannot avoid phantom read. The main reason is that at the RC isolation level, the data that needs to be locked is locked by row. Only existing rows can be locked, but a new row that does not exist is inserted.

  1. To solve the phantom problem, Gap Lock and next-key Lock are introduced. Gap locking, the locking of gaps between index records, represented by an open interval. Such as records have clearance, inserted above (- up, 1), (1, 5), (5, 10), (10, 15), (15, up). Critical key lock, gap lock and row lock constitute critical key lock, front open after closed interval to represent.

Generally speaking, if the current read can hit a specific record, a row lock will be added. Otherwise, a gap lock will be added according to the gap condition to prevent phantom read. The specific locking situation can be divided into two categories: go index and not go index. The specific locking situation will not be discussed here.

Why do many companies choose mysql with transaction isolation level RC?

From the following two aspects,

1. Using RR transaction isolation level can avoid phantom reads, but the scope of locking may be expanded due to the introduction of gap locks, which will affect concurrency and easily cause deadlocks, because gap locks and gap locks are not conflicting;

2. In most business scenarios, the transaction isolation level RC can basically meet business requirements, and the probability of phantom reading is less;

From a sufficient point of view, the RC isolation level is acceptable.