MySQL: transaction isolation levels

Good afternoon, community.

Whoever bought MySQL, it will excite the resources of Our servers with you for a long time - and this is 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? Dont clear. So they came up with such rules.


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

Second READ COMMTITED
In this case, reading data is possible only after calling COMMIT. What does the data inside the transaction also not yet be available.
If you consider the transaction above, the first SELECT will return nothing, because the 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 in that the newly added data will already be available within the transaction, but will not be available until confirmation from the outside.
Here the theoretical problem of “phantom reading” may arise. When data is read inside one transaction, another transaction at this moment inserts new data, and the first transaction reads the same data again.

And the last SERIALIZABLE
At this level, MySQL blocks every row over which some action occurs, this eliminates the appearance of the problem of “phantoms”. In fact, it makes no sense to use this level, because InnoDB and the less popular Falcon solve this problem.

See current isolation level

SHOW VARIABLES LIKE '%tx_isolation%';

Install

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This is just an attempt to freely translate the most common documentation.
Thanks.

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 talk about Falcon and PBXT engines.