Links to the original text: https://blog.csdn.net/qq_39455116/article/details/96480845

3. MySQL 8.0 looks at the isolation level of default transactions and tests them

MySQL defaults to: repeatable

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

3.1 Change the isolation level to: Uncommitted Read Tests

The two command-line clients are A and B respectively; they constantly change the isolation level of A and modify data at B.
But do not change the isolation level of B, only change the isolation level of A, B or the default isolation level: repeatable reading
A:

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
## See
mysql>  select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

1. Quasi-one table, test transaction

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
## Insert three pieces of data
INSERT INTO `trans`.`test` (`id`, `num`) VALUES ('1', '1');
INSERT INTO `trans`.`test` (`id`, `num`) VALUES ('2', '2');
INSERT INTO `trans`.`test` (`id`, `num`) VALUES ('3', '3');

Open MySQL with two windows

mysql -uroot -p123456

1. Window 1: Start transaction A. Three records in the database are in the initial state

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window 2. Start transaction B, modify data, but do not commit transaction B

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set num =33 where id =3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
## Changed in Transaction B
mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |  33 |
+----+-----+
3 rows in set (0.00 sec)

1. Window 1 Transaction A to see if the data has been modified
   It was found that it had been modified, which is called "dirty reading".

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |  33 |
+----+-----+
3 rows in set (0.00 sec)

1. Window 2 rollback transaction B

mysql> rollback ;
Query OK, 0 rows affected (0.04 sec)

1. Window 1 Transaction A to see if the data is rolled back
   Found to have rolled back

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. After the above experiments, we can conclude that transaction B updates a record but does not commit, at this time transaction A can query the uncommitted record. Cause dirty reading. Uncommitted reads are the lowest isolation level

3.2 Test isolation level: submit read

Replace the isolation level of window A with: Submit read, window B without change or default

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

mysql>  select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | READ-COMMITTED          |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

1. Window A opens transactions and queries

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window 2 opens transactions, modifies data, and views

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set num =22 where id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window A checks again to see if the data has been updated
   No, the isolation level is in effect.

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window B commits transactions

mysql> commit ;
Query OK, 0 rows affected (0.08 sec)

1. Window A queries the data again
   It was found that the data had been modified to meet expectations.

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec

Look at the normal code, but what's wrong at this time?
There are: In transaction A, the data read twice is inconsistent, that is, it can not be read repeatedly.

1. Through the above experiments, it can be concluded that the submitted read isolation level solves the problem of dirty reading, but there is a problem of non-repeatable reading, that is, the data of transaction A is inconsistent between the two queries, because transaction B updates one data between the two queries. Submitted reads allow only the submitted records to be read, but do not require repeatable reads.

3.3 Test repeatable

1. Transaction A settings are readable and transaction view is turned on

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

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

## View data
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Transaction B can view transactions by default.

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

## Transaction B opens the transaction and modifies the data with id=1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

mysql> update test set num =11 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window A View found that no changes were made, which was in line with expectations.

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window B submits the transaction and looks at it and finds that the data has been updated.

mysql> commit ;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

1. Window A Views Data

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec)

It was found that the data was still unchanged, in line with the expectation of repeatable readability, that data reading a value within a transaction should always be consistent
But!!!
What's the problem?!! ——————>> Data is not the latest data

1. What if I open the transaction again and commit the transaction to insert a record in window B?
   The following is in window B, but does not open transactions

mysql> start transaction;

mysql> insert into test (num) value(4);
Query OK, 1 row affected (0.07 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
+----+-----+
4 rows in set (0.00 sec)

1. Window A looks again and finds that the latest data is still not available.

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |  22 |
|  3 |   3 |
+----+-----+
3 rows in set (0.00 sec

1. Submit transaction A, get data, and find that it is up to date.

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test ;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
+----+-----+
4 rows in set (0.00 sec

From the above experiments, it can be concluded that the repeatable read isolation level allows only the submitted records to be read, and transaction A cannot be seen if other transaction B updates the records during one transaction A reading one record twice. However, this transaction does not require serialization with other transactions. For example, when a transaction can find a record that has been updated by a submitted transaction, hallucination can occur (note that it is possible because the database implements a different isolation level). Like the above experiments, there is no problem of data hallucination.

3.3.1 Simulated Illusion Reading

There is no hallucination in the above experiment. Now let's simulate what hallucination is.

1. Guarantee the isolation level of two windows AB: both are repeatable

select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
## Assuming that there are now six records, they are
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
|  5 |  55 |
|  6 |  55 |
+----+-----+
6 rows in set (0.00 sec)

1. Open transaction and query in window A

mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
|  5 |  55 |
|  6 |  55 |
+----+-----+
6 rows in set (0.00 sec)

1. Insert a record directly without opening the transaction in window B, num=7

mysql> insert into test (num) values (7);
Query OK, 1 row affected (0.10 sec)

1. Query in window A if there is a num=7 record

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
|  5 |  55 |
|  6 |  55 |
+----+-----+
6 rows in set (0.00 sec)

It is found that there is no record of Num=7, which verifies the repeatability and meets the expectation, but how do we simulate hallucination?

1. Add a record in window A, num equals 7, and query

mysql> insert into test (num) values (7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
|  5 |  55 |
|  6 |  55 |
|  8 |   7 |
+----+-----+
7 rows in set (0.00 sec)

We found that we skipped the data of id=7 directly, which also showed that there must be other threads operating. How many records would we update if we update num=7? If you update one item, it's not hallucination. If you update two items, it's hallucination. Let's verify this.

1. Update the num=7 record in window A and query again

mysql> update test set num =777 where num =7;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
|  1 |  11 |
|  2 |  22 |
|  3 |   3 |
|  4 |   4 |
|  5 |  55 |
|  6 |  55 |
|  7 | 777 |
|  8 | 777 |
+----+-----+
8 rows in set (0.00 sec)

Wow!!! The result matches two, and queries out one more record, is this a phantom reading???? YES, this is hallucination

Simulated successfully, similar deletion also has similar effect, we will no longer simulate.
The general idea is that when a transaction is opened in window A, two 777 records are queried, a new record num =777 is added to window B, and three records are added to the original two 777 records. At this time, deletion statements are executed in window A.

mysql> delete from test where num =777;
Query OK, 3 rows affected (0.00 sec)

It was found that three items were deleted, but two were expected, so there was a hallucination.

3.4 Serialization does not test, that is, the question of order, who opens the business first, who carries out the other things have to wait.