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

MySQL has what transaction isolation levels, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

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 * times contact with the concept of transaction isolation 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 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; # Operation 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, 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 8 Query OK, 0 rows affected (0.00 sec)

Transaction two

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 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

Mysql > start tansactoin; # Action 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 two

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 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: 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 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: 0 mysql > 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 8 Query OK, 0 rows affected (0.04 sec)

Transaction two

Mysql > start transaction; # Operation 2 Query OK, 0 rows affected (0 sec) mysql > insert into user (name, salary) value ('zhangsan',' 666666'); # Operation 4 Query OK, 1 row affected (0 sec) mysql > commit; # Operation 5 Query OK, 0 rows affected (0.04 sec) will it help you to read the above? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.