Example analysis of four transaction isolation levels in MySQL

In database operations, in order to effectively ensure the correctness of concurrently read data, the transaction isolation level is proposed. There are four isolation levels for database transactions. The SQL standard defines four isolation levels, including some specific rules to limit which changes inside and outside the transaction are visible and which are invisible. The following article analyzes the relevant information about the four transaction isolation levels in MySQL in detail through examples. Friends in need can refer to it.

Preface

There is not much to say below, let’s take a look at the detailed introduction.

There are four isolation levels for database transactions:

• Uncommitted read (Read Uncommitted): dirty reads are allowed, that is, uncommitted transactions in other sessions may be read. Modified data.

• Read Committed: Only submitted data can be read. Most databases such as Oracle default to this level.

• Repeated Read: Repeatable Read. Queries within the same transaction are consistent at the start of the transaction, InnoDB default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but phantom reads still exist.

• Serial read (Serializable): Completely serialized read. Each read requires a table-level shared lock, and reading and writing will block each other.

Friends who are exposed to the concept of transaction isolation for the first time may be confused by the textbook definition above. Let’s explain the four isolation levels through specific examples.

First we 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 transaction isolation level 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)

Below we have opened two terminals to simulate transaction one and transaction two respectively. p.s: Operation one and operation two are meant to be executed in chronological order.

Transaction 1

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

Transaction 2

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

It can be clearly seen from the above execution results that under the read uncommited level we may read in transaction one There is no committed data in transaction two, which is a dirty read.

Read committed isolation level

The above dirty read problem can be solved by setting the isolation level to committed.

mysql> set session transaction isolation level read committed;

Transaction One

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

Transaction Two

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

Although the dirty read problem has been solved, please note that in operation seven of transaction one, transaction two is committed after operation six. This will cause transaction 1 to read different data twice in the same transaction. This is a non-repeatable read problem. Using the third transaction isolation level repeatable read can solve this problem.

Repeatable read isolation level

The default transaction isolation level of MySQL's Innodb storage engine is the repeatable read isolation level, so we don't need to make extra settings.

Transaction One

mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name  |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

Transaction Two

mysql> start tansactoin; # 操作二
mysql> update user set name='lisi' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # 操作四

In operation five of transaction one, we did not read the update of transaction two in operation three, only after commit Only then can the updated data be read.

Does Innodb solve phantom reads?

In fact, RR level may produce phantom reads. InnoDB engine officially claims to use MVCC multi-version concurrency control to solve this problem. Let’s verify that Innodb is really Has the phantom reading been solved?

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

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # 操作六，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user; # 操作七， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name  | salary |
+----+----------+--------+
| 10 | ziwenxie | 4444 |
| 11 | zhangsan | 4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.04 sec)

Transaction 2

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作四
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 操作五
Query OK, 0 rows affected (0.04 sec)

As you can see from the above example It turns out that Innodb does not solve phantom reading as officially stated, but the above scenario is not very common and there is no need to worry too much.

Serialized isolation level

All transactions are executed serially. At the highest isolation level, phantom reads will not occur and the performance will be very poor. It is rarely used in actual development.

Related recommendations:

MySQL transaction isolation level example tutorial

MySQL four transaction isolation levels detailed explanation and comparison

A brief analysis of the impact of MySQL transaction isolation level on its performance

The above is the detailed content of Example analysis of four transaction isolation levels in MySQL. For more information, please follow other related articles on the PHP Chinese website!