MySQL: transaction isolation levels

Good afternoon community.

Anyone who has bought MySQL, it will haunt the resources of Our servers with you for a long time - and that's good.

There is a table

CREATE TABLE test (id INT, value VARCHAR(255)) ENGINE=InnoDB; 

')
What do you think this query will show?

 START TRANSACTION; INSERT INTO test(id, value) VALUES (1, 'test'), (2, 'test 2'); SELECT * FROM test; COMMIT; SELECT * FROM test; 

And what will the simplest SELECT show during the execution of the current transaction? It is not clear. So they came up with such rules.


First READ UNCOMMITTED
Consider the transaction above. After INSERT data immediately become available for reading. Ie, even before calling COMMIT outside the transaction, you can get the newly added data. In English literature, this is called dirty read. This level is rarely used in practice, but in general rarely anyone changes these same levels.

Second READ COMMTITED
In this case, it is possible to read the data only after calling COMMIT. Moreover, inside the transaction, the data will also not be available.
If we consider the transaction above, the first SELECT will not return anything, since our table is still empty and the transaction is not confirmed.

Third REPEATABLE READ
This level is used by default in MySQL. It differs from the second one in that the newly added data will already be available inside the transaction, but will not be available until confirmation from the outside.
There may be a theoretical problem of "phantom reading." When data is read inside one transaction, another transaction inserts new data at that moment, and the first transaction reads the same data again.

And the last SERIALIZABLE
At this level, MySQL blocks every line over which an action occurs, this eliminates the problem of "phantoms". In fact, it makes no sense to use this level, since InnoDB and the less popular Falcon solve this problem.

See current isolation level

 SHOW VARIABLES LIKE '%tx_isolation%'; 

To install

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

This is just an attempt at free translation of the most common documentation.
Thank.

Task:

 CREATE TABLE one(id INT, value VARCHAR(12)) ENGINE = MyISAM; CREATE TABLE two(id INT, value VARCHAR(12)) ENGINE = InnoDB; START TRANSACTION; INSERT INTO one (id, value) VALUES (1, 'test'); INSERT INTO two (id, value) VALUES (1, 'test'); ROLLBACK; SELECT * FROM one; SELECT * FROM two; 

What will be in the first table, and what in the second?

In the next article I will try to tell you about Falcon and PBXT engines.