This article will learn the transaction isolation level of MySQL through practice.

0. Prepare the test table

First create a table tb_test for testing:

CREATE TABLE `tb_test` (
  `id` int(11) NOT NULL,
  `text` varchar(200) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Then insert three test records:

INSERT INTO `tb_test`(`text`) VALUES ('first row');
INSERT INTO `tb_test`(`text`) VALUES ('second row');
INSERT INTO `tb_test`(`text`) VALUES ('third row');
Copy the code

1. READ the UNCOMMITTED level

Start terminal A, set session isolation level to READ UNCOMMITTED, start A transaction with BEGIN statement, and then READ the record whose ID =1:

- end A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
select * from tb_test where id = 1;
Copy the code

Query result:

Then start terminal B, start a transaction with BEGIN and update the text field of the record with id=1:

- terminal B
BEGIN;
UPDATE TB_TEST SET TEXT = '1 row' WHERE ID = 1;
Copy the code

The command is successfully executed:

Switch to terminal A and query the record whose ID =1 again:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- BEGIN;
-- select * from tb_test where id = 1;
select * from tb_test where id = 1;
Copy the code

The query results are as follows:

As you can see, even though the transaction in terminal B has not yet committed, the new field value is also queried, which is a dirty read. This is a very dangerous pattern, because the transaction in terminal B might roll and there would be data inconsistencies.

To clean up, both terminal A and terminal B’s transactions are formally committed:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- BEGIN;
-- select * from tb_test where id = 1;
-- select * from tb_test where id = 1;
COMMIT;

- terminal B
-- BEGIN;
-- UPDATE TB_TEST SET TEXT = '1 row' WHERE ID = 1;
COMMIT;
Copy the code

Conclusion:

To solve the dirty READ problem, we can increase the isolation level to the READ comisolation level.

2. READ COMMITTED level

Before we do this isolation level test, let’s take a look at the status of the data in the current table:

Let’s start testing and verifying.

Open terminal A, set the isolation level of this session to READ COMMITTED, and run the query:

- end A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM tb_test;
Copy the code

The query results are as follows:

Then open terminal B and update the value of the field text of the record whose id=1:

- terminal B
BEGIN;
UPDATE TB_TEST SET TEXT = 'first row' WHERE ID = 1;
Copy the code

The command is executed successfully:

Switch to terminal A and query again:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT * FROM tb_test;
SELECT * FROM tb_test;
Copy the code

The query results are as follows:

You can see that the update in terminal B is not queried. Now we commit the transaction for terminal B:

- terminal B
-- BEGIN;
-- UPDATE TB_TEST SET TEXT = 'first row' WHERE ID = 1;
COMMIT;
Copy the code

Then switch back to terminal A to query again:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT * FROM tb_test;
-- SELECT * FROM tb_test;
SELECT * FROM tb_test;
Copy the code

You can see that the update submitted by terminal B was detected:

We can see that the READ COMMITTED isolation level prevents dirty reads, but there is another problem. The same query performed in the same transaction at Terminal A can produce different results. This non-repeatable read is commonly referred to as a phantom read, and it is unacceptable in some business scenarios.

To clean up, both terminal A and terminal B’s transactions are formally committed:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SELECT * FROM tb_test;
-- SELECT * FROM tb_test;
-- SELECT * FROM tb_test;
COMMIT;

- terminal B
-- BEGIN;
-- UPDATE TB_TEST SET TEXT = 'first row' WHERE ID = 1;
COMMIT;
Copy the code

Conclusion:

  1. How to solve the dirty read problem in MySQL? Why can’t you avoid hallucinations?

    SELECT * FROM tb_test; Statement is actually a common no-lock read statement, which is called Consistent Nonlocking Reads in the official document of MySQL. The principle of such statement to prevent dirty Reads is based on MVCC, namely multi-version concurrency control. Simply speaking, each transaction submitted corresponds to one version. At the READ COMMITTED isolation level, the most recently COMMITTED transaction snapshot is READ each time to avoid dirty reads. But because every time you execute this SELECT * FROM tb_test; Is the latest committed transaction snapshot read, so there is no way to avoid phantom reads.

  2. At the READ COMMITTED isolation level, statements executed within a transaction must also have locking reads. Locking reads are referred to as locking reads in MySQL official documentation, such as SELECT FOR UPDATE statements, UPDATE statements, and DELETE statements. Does this lock read pair block inserts from other transactions?

    The answer is no, because this isolation level does not enable gap locking, that is, does not block other transactions between records in the query (FOR example, SELECT * FROM TB_test where id >= 10 and ID <=20 FOR UPDATE; , where the gap is 10~20) insert a new record. The reason why this is mentioned here is to compare with REPEATABLE READ, this isolation level is turned on gap lock, that is to prevent record insertion in gap, this benefit can avoid phantom READ problem.

  3. To solve the phantom problem, we can continue to upgrade the transaction isolation level to REPEATABLE READ.

3. REPEATABLE READ level

REPEATABLE READ in SQL standard is not required to prevent phantom READ, but MySQL implementation is more strict to prevent phantom READ.

Phantom definition: Transaction A reads data according to certain conditions, during which transaction B inserts new data with the same search conditions, when transaction A reads data according to the original conditions again, the new data inserted by transaction B is called phantom. If transaction A searches according to certain conditions, transaction B deletes A data that meets the conditions during the search period, so that the data is missing when transaction A reads again, this situation is classified as unrepeatable read and not unreal read.

Before starting the test, let’s prepare the test data:

Let’s start testing. Open terminal A, adjust the transaction to REPEATABLE READ level and execute the following query:

- end A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM tb_test where id > = 10 and id < =20;
Copy the code

Id >= 10 and id <=20; The query results are as follows:

Then switch to destination B and insert a record with id=15:

- terminal B
BEGIN;
INSERT INTO `tb_test`(`id`, `text`) VALUES (15.'inserted row');
COMMIT;
Copy the code

The command is executed successfully:

Then switch back to terminal A and re-execute the previous query:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- BEGIN;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
SELECT * FROM tb_test where id > = 10 and id < =20;
Copy the code

The query result is as follows:

Visibility avoids illusion. If we delete the record with ID =20 in terminal B, can terminal A find it? You can keep trying. Switch to terminal B to execute:

- terminal B
BEGIN;
DELETE FROM tb_test where id = 20;
COMMIT;
Copy the code

Switch to terminal A to execute:

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- BEGIN;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
SELECT * FROM tb_test where id > = 10 and id < =20;
Copy the code

The query result remains unchanged:

It also avoids the problem of unrepeatable reads in delete scenarios.

Cleaning up: the transaction of terminal A is committed.

- end A
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- BEGIN;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
-- SELECT * FROM tb_test where id >= 10 and id <=20;
COMMIT;
Copy the code

SELECT * FROM tb_test where id >= 10 and id <=20; You can see the update record of terminal B:

Conclusion:

  1. How does the MySQL REPEATABLE READ isolation level prevent dirty and phantom reads?

    For common no-lock READ statements, the principle of REPEATABLE READ isolation level is the same as that of REPEATABLE READ, which is based on MVCC, except that the SELECT statement in REPEATABLE READ transaction does not READ the latest committed snapshot every time. Instead, it reads the first committed snapshot, thus preventing phantom reads as well.

  2. FOR REPEATABLE READ isolation level, the statements executed inside the transaction must also have locked reads, such as SELECT FOR UPDATE statement, UPDATE statement, DELETE statement. Does this lock READ pair block INSERT of other transactions?

    The answer is blocking. This isolation level enables gap locking, which blocks updating, deleting records selected by the SELECT FOR UPDATE statement, and inserting selected range gaps.

References:

  1. Dev.mysql.com/doc/refman/…
  2. medium.com/@huynhquang…
  3. Blog.pythian.com/understandi…
  4. Dev.mysql.com/doc/refman/…