Four database isolation levels

Isolation level Dirty read Unrepeatable read Phantom read
Read Uncommitted The Read is not committed may may may
Read COMMITTED The Read is committed Can’t be may may
Repeatable read Repeatable read Can’t be Can’t be may
Serializable Read serialization Can’t be Can’t be Can’t be

Create a student table

CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `class_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `idx_class_id` (`class_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ; INSERT INTO 'class_name' VALUES (' class_name ', 'class_id'); INSERT INTO 'score' VALUES (' class_name ', 'class_id'); INSERT INTO 'score' VALUES (' class_name ', 'class_id');Copy the code

RU (read uncommitted) level (test dirty read)

A transaction Transaction B
set session transaction isolation level read uncommitted; set session transaction isolation level read uncommitted;
begin; begin;
select * from student select * from student
Update student set class_name=’ class_name ‘where class_id=1;
select * from student; select * from student;


(A dirty read occurred and uncommitted data was read)
commit; commit;

Conclusion: Consistent with the theory, there was a dirty read, uncommitted data was read

RC (read committed) level (test dirty read, unrepeatable read)

A transaction Transaction B
set session transaction isolation level read committed; set session transaction isolation level read committed;
begin; begin;
select * from student select * from student
Update student set class_name=’ class_name ‘where class_id=1;
select * from student; select * from student;


(The data hasn’t changed)
commit;
select * from student;


(Committed data was read, but the two reads in a transaction were inconsistent, causing unrepeatable reads)
commit;

Conclusion: Dirty reads are avoided, but the same query in transaction B has a different result from the previous one, which is unrepeatable (re-reads produce different results). This is likely to cause some problems, so let’s look at MySQL’s performance at the RR level.

3, RR (repeatable read) level (test unrepeatable read)

A transaction Transaction B
set session transaction isolation level Repeatable Read; set session transaction isolation level Repeatable Read;
begin; begin;
select * from student select * from student
Update student set class_name=’ class_name ‘where class_id=1;
select * from student; select * from student;


(The data hasn’t changed)
commit;
select * from student;


(The data hasn’t changed)
commit;

Conclusion: It is consistent with theory. Avoid dirty reads and unrepeatable reads

4. RR (Repeatable read) level (test phantom read)

A transaction Transaction B
set session transaction isolation level Repeatable Read; set session transaction isolation level Repeatable Read;
begin; begin;
select * from student select * from student
INSERT INTO student(class_name.class_id) VALUES (‘ Grade 1 grade 1 ‘,4);

commit;
select * from student;
select * from student;


(The data hasn’t changed)
commit;

Conclusion: RR level is inconsistent with the theory. It avoided hallucinations

Note: magic and unrepeatable reading are easily confused. But non-repeatable reads focus on UPDATE and DELETE, while phantom reads focus on INSERT.

At the RR level, MySQL uses MVCC (Multi-version Concurrency Control) based on optimistic locking to avoid both of these problems. The MVCC mechanism makes the data repeatable, but the data we read may be historical data, is not timely data, not the current data of the database! This can be problematic in businesses that are particularly sensitive to the timeliness of data.

Database read operations can be classified into snapshot read and current read.

Snapshot read select * from XXX

Select * from XXX for update, lock in share mode, update insert delete, etc

It has been explained that unreal reads of snapshot reads do not appear at the RR level. Test whether unreal reads exist in the current read

5, RR (repeatable read) level (test [current read] unreal read)

Scenario 1: Magic read test for new data whose index ID (class_id=1) is the same as that of the current read index (class_id=1)

A transaction Transaction B
set session transaction isolation level Repeatable Read; set session transaction isolation level Repeatable Read;
begin; begin;
select * from student select * from student
Update student set class_name=’ class_name ‘where class_id=1;
INSERT INTO student(class_name.class_id) VALUES (‘ Grade 1 ‘,1);

waiting….
select * from student;


Modify the success
commit; Insert statement executed successfully
commit;

Scenario 1: No problem, no illusions

Case 2: The magic read test is performed when the new data index ID (class_id=30) is greater than the current read index ID (class_id=20)

A transaction Transaction B
set session transaction isolation level Repeatable Read; set session transaction isolation level Repeatable Read;
begin; begin;
select * from student select * from student
Update student set class_name=’ class_name ‘where class_id=20;
INSERT INTO student(class_name.class_idVALUES (‘ Grade 1, grade 3 ‘,30);

waiting
commit; Insert statement executed successfully
commit;

Scenario 2: No problem, no illusions

Case 3: The magic read test is performed when the new data index ID (class_id=20) is greater than the current read index ID (class_id=15)

A transaction Transaction B
set session transaction isolation level Repeatable Read; set session transaction isolation level Repeatable Read;
begin; begin;
select * from student select * from student
Update student set class_name=’ class_name ‘where class_id=15;
INSERT INTO student(class_name.class_id) VALUES (‘ Grade 1, grade 3 ‘,20);

Insert statement executed successfully
Update student set class_name=’ class_id ‘WHERE class_id=20;

waiting
commit;
Update Succeeded

commit;

Conclusion: CURRENT reads at RR level also achieve query consistency in a transaction and avoid phantom reads

In the RR level, transaction A is locked after update, and transaction B cannot insert new data. In this way, transaction A reads the same data before and after update, avoiding phantom read. This lock is the Gap lock.

The indexed data classid in the table is 1 and 20

Lock class (1,15) and [15,20) when current read change ID is 15

Lock the class interval (1,20) and [20,∞) when the current read change ID is 20

Lock the class interval [20,30] and [30,∞] when the current read change ID is 30.