Through Baidu search, "MySql transaction isolation level" and "InnoDB transaction isolation level" found many articles "characteristics" as follows:

• The key point is to explain: the difference between non-repeatable reading and hallucination reading;
• Most of the conclusions are: under the isolation level of repeatable read, the problem of non-repeatable reading is solved, but there is hallucination problem.

Correct analysis can be referred to: The relationship between transaction isolation level and lock in Innodb

For dirty reading, hallucination, non-repeatable and lost updates, the following experiments can be done. Emphasis is placed on the MySql environment only. The conclusions of various databases are not suitable for other databases.

The purpose of the experiment is to prove that the Repeatable Read level of MySql InnoDB solves the problem of non-repeatable reading and hallucination, and intuitively perceives dirty reading, hallucination and non-repeatable reading.

Prepare tables and data

CREATE DATABASE Test;
USE Test;

CREATE TABLE test(
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    sex CHAR(1),
    PRIMARY KEY (id),
    KEY (name,sex)
)ENGINE = INNODB;

INSERT test SELECT 1,'saillen','male';
INSERT test SELECT 2,'wenwen','female';
INSERT test SELECT 3,'jocker','male';

Experimental SQL

The experimental SQL is the same at all levels, but the statement setting the transaction level is different.

#Transaction 1
SELECT @@tx_isolation \G
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation \G
START TRANSACTION;
#Update operation: dirty reading, non-repeatable reading
UPDATE test SET name = 'sai' WHERE id = 1;
#Insert operation: phantom reading
INSERT test SELECT 4,'hacker','male';
COMMIT;

#Transaction 2
SELECT @@tx_isolation \G
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation \G
START TRANSACTION;
#Execute once before transaction 1 begins
SELECT * FROM test;
#In transaction 1 update andinsertExecute once after
SELECT * FROM test;
#In Transaction 1 commitAfter execution, there is no repeatability and illusion.
SELECT * FROM test;

Read Uncommitted Level Experiment

Dirty reading occurs at the Read UnCommitted level, indicating that the results of other concurrent uncommitted transactions have been read

experimental result

The result of transaction 1:

mysql> SELECT @@tx_isolation \G
*************************** 1. That's ok ***************************
@@tx_isolation: REPEATABLE-READ
1 Rowing Data Set (0.01 second)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.02 second)

mysql> select @@tx_isolation ;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 Rowing Data Set (0.03 second)

mysql> start transaction;
Query OK, 0 rows affected (0.02 second)

mysql> update test set name = 'sai' where id = 1;
Query OK, 1 rows affected (0.03 second)

mysql> select * from test where id = 1;
+----+------+------+
| id | name | sex  |
+----+------+------+
| 1  | sai  | male  |
+----+------+------+
1 Rowing Data Set (0.02 second)

mysql> rollback;
Query OK, 0 rows affected (0.02 second)

mysql> select * from test where id = 1;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 1  | saillen | male  |
+----+---------+------+
1 Rowing Data Set (0.03 second)

The result of transaction 2:

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 Rowing Data Set (0.03 second)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 second)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 Rowing Data Set (0.02 second)

mysql> start transaction;
Query OK, 0 rows affected (0.02 second)

mysql> select * from test where id = 1;
+----+------+------+
| id | name | sex  |
+----+------+------+
| 1  | sai  | male  |
+----+------+------+
1 Rowing Data Set (0.02 second)

mysql> select * from test where id = 1;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 1  | saillen | male  |
+----+---------+------+
1 Rowing Data Set (0.04 second)

mysql> commit;
Query OK, 0 rows affected (0.01 second)

conclusion

MySql has dirty reads at READ UNCOMMITTED transaction isolation level, because the level is used only in extreme cases without any lock.

Read Committed transaction level

Non-repeatable reading refers to that transaction 2 modifies the rows read by transaction 1 in the course of transaction 1, resulting in inconsistent results read twice by transaction 1. The key difference is that transaction 2 commit can only perceive the modification. Sometimes this situation is allowed and welcomed. For example, when we modify an account, we should be aware of the changes in the balance in real time, but sometimes it should not be perceived. For example, when we do sales statistics for a certain period of time, inventory reduction is perceived in business. First, the program is easy to calculate errors, second, when we calculate, the next moment. Changes are not taken into account.

experimental result

Experimental results of transaction 1

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.02 second)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 Rowing Data Set (0.03 second)

mysql> start transaction;
Query OK, 0 rows affected (0.02 second)

mysql> update test set name = 'sai' where id = 1;
Query OK, 1 rows affected (0.02 second)

mysql> insert test select 4,'hacker','male';
Query OK, 1 rows affected (0.01 second)

mysql> commit;
Query OK, 0 rows affected (0.01 second)

Transaction 2 experiment results

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.02 second)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 Rowing Data Set (0.03 second)

mysql> start transaction;
Query OK, 0 rows affected (0.01 second)

mysql> select * from test;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
3 Rowing Data Set (0.02 second)

mysql> select * from test;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
3 Rowing Data Set (0.03 second)

mysql> select * from test;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
3 Rowing Data Set (0.02 second)

mysql> select * from test;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
| 4  | hacker | male  |
| 3  | jocker | male  |
| 1  | sai    | male  |
| 2  | wenwen | female  |
+----+--------+------+
4 Rowing Data Set (0.01 second)

mysql> commit;
Query OK, 0 rows affected (0.01 second)

conclusion

MySql solves the dirty reading problem at the Read Committed transaction isolation level, but it has the problems of non-repeatable reading and hallucination. At Read Committed level, MySql InnoDB engine uses consistent non-lock reading scheme to read data, read without S-lock, but write with X-lock, so reading problems occur. If SELECT* FROM test in SHARE MODE is used to actively add S-lock, non-repeatable reading can be avoided.

If you actively add S or X locks, transaction 1 will be blocked in the place of update, because update will add X locks, row has been added S locks, so to block, transaction concurrency is reduced.

It can also be avoided if SELECT * FROM test FOR UPDATE actively adds X-lock, but it can not avoid hallucination.

Repeatable Read level

Repeatable read is MySql's default transaction level, and it is also the transaction level that some of the online conclusions are problematic. This level is used to solve the problem of non-repeatable reading in the implementation of ISO and some databases, but it can not solve hallucination reading. But in fact, MySql's repeatability level solves two problems: hallucination and non-repeatability.

experimental result

Transaction 1 Results

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.01 second)

mysql> update test set name = 'sai' where id = 1;
Query OK, 1 rows affected (0.02 second)

mysql> insert test select 5,'h','female';
Query OK, 1 rows affected (0.02 second)

mysql> commit;
Query OK, 0 rows affected (0.01 second)

mysql> 

Transaction 2 Results

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 Rowing Data Set (0.02 second)

mysql> start transaction;
Query OK, 0 rows affected (0.01 second)

mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | male  |
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
4 Rowing Data Set (0.02 second)

mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | male  |
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
4 Rowing Data Set (0.05 second)

mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | male  |
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
4 Rowing Data Set (0.03 second)

mysql> 
mysql> select * from test ;
+----+---------+------+
| id | name    | sex  |
+----+---------+------+
| 4  | hacker  | male  |
| 3  | jocker  | male  |
| 1  | saillen | male  |
| 2  | wenwen  | female  |
+----+---------+------+
4 Rowing Data Set (0.02 second)

mysql> commit;
Query OK, 0 rows affected (0.01 second)

mysql> select * from test ;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
| 5  | h      | female  |
| 4  | hacker | male  |
| 3  | jocker | male  |
| 1  | sai    | male  |
| 2  | wenwen | female  |
+----+--------+------+
5 Rowing Data Set (0.03 second)

conclusion

MySql's repeatable transaction level solves the problem of non-repeatability and hallucination, which is different from most databases and ISO standards. The solution is implemented by Next-Key Lock and by locking range.

For the above example, select * from test; there is no where condition, so the range of locking is from negative infinity to positive infinity. If the condition of adding where is id > 10 and id < 20, then the range of locking is that [10,20] will lock id to 10,11,... 20 rows. These rows may not exist, but the data within the lock range will not be update d or insert. There are some minor problems in the solution of hallucination under MySql, but they are not serious.

Serialization

In general flat transactions, MySql's default transaction isolation level is sufficient and does not need serialization. Serialization is prepared by MySql for distributed transactions. Actual use is seldom without experiment here.