Transactions must meet the requirements of ACID:

1) Atomicity (atomicity). Either all or none of the operations must be performed.
2) Consistency (consistency, coherence). Each transaction should record only permissible results (e.g., in the bank account example, there should be a debit of an amount from one account and a credit to another).
3) Isolation (isolation). Parallel transactions must be isolated from each other (in practice, there are isolation modes that do not completely isolate a transaction).
4) Durability (strength, durability). If a transaction has completed successfully, the changes made to it should not be undone due to a failure.

Transaction isolation levels:

1) Read uncommited. The worst isolation, the highest speed. Possible dirty read.

Open 2 terminals and set the desired isolation level

mysql> set session transaction isolation level read uncommitted;

In terminal 1 we make sure that there is no data available

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

mysql> select * from test;
Empty set (0,01 sec)

In terminal 2 we start the transaction and add data, but do not commit it

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

mysql> insert into test (value) values (100);
Query OK, 1 row affected (0,00 sec)

In terminal 1 we already see a new line, although the transaction is not completed. This is a manifestation of the “dirty read” problem

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0,01 sec)

If you roll back the transaction in terminal 2, the query will produce an empty result in terminal 1.

2) Read committed. Concurrently running transactions see only committed changes (solving the “dirty read” problem). The problems of “unrepeatable read” and “lost updates” are possible..

Open 2 terminals and set the desired isolation level

mysql> set session transaction isolation level read committed;

In terminal 1 we make sure that there is no data available

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

mysql> select * from test;
Empty set (0,01 sec)

In terminal 2 we start the transaction and add data, but do not commit it

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

mysql> insert into test (value) values (100);
Query OK, 1 row affected (0,00 sec)

In terminal 1 we will make sure again that no data is present

mysql> select * from test;
Empty set (0,01 sec)

In terminal 2 we record the transaction

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

In terminal 1 we see a new line

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |   100 |
+----+-------+
1 row in set (0,01 sec)

Now let's look at the “non-repeatable read” problem. Within 2 independent transactions, let's update the data, and one of the transactions will get different results when reading.

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

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

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

In terminal 1 we see the new value, although the transaction has not been committed in this terminal

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 |   200 |
+----+-------+
1 row in set (0,01 sec)

3) Repeatable read. A transaction does not see any changes to the data that was previously read to it. At the same time, another transaction cannot change the data read by the current transaction until it commits. This solves the problems of “unrepeatable reads” (unrepeatable reads) and “lost updates” (lost updates).

At this level of isolation the problem of “phantom read” (phantom read) is possible - the first transaction selects data by condition (for example, balance amount) several times, in the intervals between requests the second transaction adds rows (or changes data of some rows).

Open 2 terminals and set the desired isolation level

mysql> set session transaction isolation level repeatable read;

In 1 terminal within the transaction we will find the sum

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

mysql> select sum(value) from test;
+------------+
| sum(value) |
+------------+
|        100 |
+------------+
1 row in set (0,00 sec)

In terminal 2 add data

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

mysql> insert into test (value) values (200);
Query OK, 1 row affected (0,00 sec)

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

In terminal 1 we make a repeated request

mysql> select sum(value) from test;
+------------+
| sum(value) |
+------------+
|        300 |
+------------+
1 row in set (0,00 sec)

4) Serializable. The highest level of isolation. Transactions are completely isolated from each other. No “phantom reading” effect.

Sources:
https://en.wikipedia.org/wiki/Isolation_(database_systems)