Analyze the four transaction isolation levels in MySQL using an instance, and analyze mysql using an instance

Analyze the four transaction isolation levels in MySQL using an instance, and analyze mysql using an instance

Preface

In database operations, the transaction isolation level is proposed to effectively ensure the correctness of Concurrent Data Reading. There are four isolation levels for database transactions. Let's take a look at the details below.

There are four isolation levels for database transactions:

• Uncommitted Read (Read Uncommitted): Dirty Read is allowed, that is, data that may be Read from other sessions that have not committed transaction modifications.
• Read Committed: only submitted data can be Read. This level is used by most databases such as Oracle by default.
• Repeated Read. The queries in the same transaction are consistent at the start time of the transaction, and the InnoDB default level. In the SQL standard, this isolation level eliminates non-repeated reads, but there are still Phantom reads.
• Serializable: Fully Serializable read. A table-level shared lock is required for each read, which blocks read/write operations.

The above textbook defines the first contact with the concept of transaction isolation, which may be confusing. Next we will explain the four isolation levels through specific examples.

First, create a user table:

CREATE TABLE user ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE `uniq_name` USING BTREE (name)) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Read uncommitted isolation level

We first set the isolation level of the transaction to read committed:

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

In the following example, we opened two terminals to simulate transaction 1 and transaction 2, p.s: Operation 1 and operation 2 mean to be executed in chronological order.

Transaction 1

Mysql> start transaction; # operation 1 Query OK, 0 rows affected (0.00 sec) mysql> insert into user (name) values ('ziwenxie'); # operation 3 Query OK, 1 row affected( 0.05 sec)

Transaction 2

Mysql> start transaction; # operate 2 Query OK, 0 rows affected (0.00 sec) mysql> select * from user; # operation 4 + ---- + ---------- + | id | name | + ---- + ---------- + | 10 | ziwenxie | + ---- + ---------- + 1 row in set (0.00 sec)

From the above execution results, we can clearly see that at the read uncommited level, we may read data without commit in transaction 1, which is dirty read.

Read submission isolation level

Setting the isolation level to committed can solve the above dirty read problem.

mysql> set session transaction isolation level read committed;

Transaction 1

Mysql> start transaction; # operation 1 Query OK, 0 rows affected (0.00 sec) mysql> select * from user; # operation 3 + ---- + ---------- + | id | name | + ---- + ---------- + | 10 | ziwenxie | + ---- + ---------- + 1 row in set (0.00 sec) mysql> select * from user; # operation 5, the modification in operation 4 does not affect transaction 1 + ---- + ---------- + | id | name | + ---- + ---------- + | 10 | ziwenxie | + ---- + ---------- + 1 row in set (0.00 sec) mysql> select * from user; # operation 7 + ---- + ------ + | id | name | + ---- + ------ + | 10 | lisi | + ---- + ------ + 1 row in set (0.00 sec) mysql> commit; # operate eight Query OK, 0 rows affected (0.00 sec)

Transaction 2

Mysql> start transaction; # operation 2 Query OK, 0 rows affected (0.00 sec) mysql> update user set name = 'lisi' where id = 10; # operation 4 Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; # operation 6 Query OK, 0 rows affected (0.08 sec)

Although the dirty read problem has been solved, note that in transaction 1 operation 7, transaction 2 will cause the data read by transaction 1 twice in the same transaction to be different after six commit operations, which is the non-repeated read problem, the third transaction isolation level repeatable read can solve this problem.

Repeatable read isolation level

The default transaction isolation level of the Innodb Storage engine of MySQL is the Repeatable read isolation level, so we do not need to set redundant settings.

Transaction 1

Mysql> start tansactoin; # operation 1 mysql> select * from user; # operation 5 + ---- + ---------- + | id | name | + ---- + ---------- + | 10 | ziwenxie | + ---- + ---------- + 1 row in set (0.00 sec) mysql> commit; # operation 6 Query OK, 0 rows affected (0.00 sec) mysql> select * from user; # operation 7 + ---- + ------ + | id | name | + ---- + ------ + | 10 | lisi | + ---- + ------ + 1 row in set (0.00 sec)

Transaction 2

Mysql> start tansactoin; # operation 2 mysql> update user set name = 'lisi' where id = 10; # operation 3 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; # operation 4

In operation 5 of transaction 1, we did not read the update in operation 3 of transaction 2. Only after commit can we read the updated data.

Does Innodb solve phantom read?

In fact, the RR level may produce Phantom reads. the InnoDB Engine officially said that the MVCC multi-version concurrency control was used to solve this problem. Let's verify that Innodb has actually solved Phantom reads?

For ease of display, I modified the above user table:

mysql> alter table user add salary int(11);Query OK, 0 rows affected (0.51 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> delete from user;Query OK, 1 rows affected (0.07 sec)mysql> insert into user(name, salary) value('ziwenxie', 88888888);Query OK, 1 row affected (0.07 sec)mysql> select * from user;+----+----------+----------+| id | name  | salary |+----+----------+----------+| 10 | ziwenxie | 88888888 |+----+----------+----------+1 row in set (0.00 sec)

Transaction 1

Mysql> start transaction; # operation 1 Query OK, 0 rows affected (0.00 sec) mysql> update user set salary = '000000'; # operation 6 actually affected the two rows, isn't that about solving phantom read? Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from user; # operation 7, innodb does not completely solve phantom read + ---- + ---------- + -------- + | id | name | salary | + ---- + ---------- + -------- + | 10 | ziwenxie | 4444 | 11 | zhangsan | 4444 | + ---- + ---------- + -------- + 2 rows in set (0.00 sec) mysql> commit; # operate eight Query OK, 0 rows affected (0.04 sec)

Transaction 2

Mysql> start transaction; # operation 2 Query OK, 0 rows affected (0.00 sec) mysql> insert into user (name, salary) value ('zhangsan', '123 '); # operation 4 Query OK, 1 row affected (0.00 sec) mysql> commit; # operation 5 Query OK, 0 rows affected (0.04 sec)

As can be seen from the above example, Innodb does not solve phantom reading as officially said, but it is not common in the above scenarios to avoid excessive worries.

Serializable isolation level

All transactions are executed in a serial mode with the highest isolation level. Phantom read performance will not be poor and is rarely used in actual development.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.