Shulou(Shulou.com)05/31 Report--

This article mainly introduces that there are several transaction isolation levels in MySQL, which is very detailed and has certain reference value. Friends who are interested must finish it!

In the database operation, in order to effectively ensure the correctness of concurrent reading data, the transaction isolation level is proposed. There are four isolation levels for database transactions, and the SQL standard defines four isolation levels, including specific rules that define which changes inside and outside the transaction are visible and which are not.

There are four isolation levels for database transactions:

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

Commit read (Read Committed): only committed data can be read, which is the default for most databases such as Oracle.

Repeated Read: repeatable. All queries within the same transaction are consistent at the beginning of the transaction, the default level of InnoDB. In the SQL standard, this isolation level eliminates unrepeatable readings, but there are still phantom readings.

Serial read (Serializable): a fully serialized read that requires a table-level shared lock for each read, and both reads and writes block each other.

The above textbook definition of friends who come into contact with the concept of transaction isolation for the first time may look confused. Let's explain the four isolation levels through specific examples.

First, let's 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 the uncommitted isolation level

Let's 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)

Below we open two terminals to simulate transaction one and transaction two, p. S: operation one and operation two mean to be executed in chronological order.

Transaction 1

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

Transaction 2

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

From the above execution results, it is clear that below the read uncommited level, we may read the data without commit in transaction 2 in transaction 1, which is dirty reading.

Read submission isolation level

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

Mysql > set session transaction isolation level read committed

Transaction one

Mysql > start transaction; # Operation one 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 # the modification of operation 5 and 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; # Operation eight query OK, 0 rows affected (0.00 sec)

Transaction two

Mysql > start transaction; # Operation two query OK, 0 rows affected (0.00 sec) mysql > update user set name='lisi' where id=10; # Operation four query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > commit; # Operation six query OK, 0 rows affected (0.08 sec)

Although the problem of dirty reading is solved, note that in operation 7 of transaction 1, transaction 2 will cause transaction 1 to read different data twice in the same transaction after operation 6 commit. This is the problem of non-repeatable reading, which can be solved by using the third transaction isolation level repeatable read.

Repeatable read isolation level

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

Transaction one

# Operation 7 +-+-+ | id | name | +-+-+ | 10 | lisi | +-+-+ 1 row in set (0.00 sec)

Transaction two

Mysql > start tansactoin; # Operation II 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: 0mysql > commit; # Operation 4

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

Did Innodb solve the illusion?

In fact, phantom reading may occur at RR level. The InnoDB engine officially uses MVCC multi-version concurrency control to solve this problem. Let's verify that Innodb has really solved phantom reading.

To facilitate the presentation, 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: 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 one

Mysql > start transaction; # operation 1 Query OK, 0 rows affected (0 sec) mysql > update user set salary='4444'; # operation 6, unexpectedly affected two lines, didn't it solve the phantom reading? Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0mysql > select * from user # Operation 7, Innodb does not completely solve the phantom reading +-+ | id | name | salary | +-+ | 10 | ziwenxie | 4444 | | 11 | zhangsan | 4444 | +-+ 2 rows in set (0.00 sec) mysql > commit # Operation eight query OK, 0 rows affected (0.04 sec)

Transaction two

Mysql > start transaction; # Operation 2 query OK, 0 rows affected (0.00 sec) mysql > insert into user (name, salary) value ('zhangsan',' 666666'); # 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 very common in such scenarios not to worry too much.

Serialization isolation level

All transactions are executed serially, with the highest isolation level, and no phantom reading performance will be very poor, which is rarely used in actual development.

These are all the contents of the article "there are several transaction isolation levels in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.