Shulou(Shulou.com)06/01 Report--

Editor to share with you what the Mysql Innodb transaction isolation level refers to, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

There are four transaction isolation levels for Mysql, as follows:

1. Read Uncommitted: it allows you to read dirty data changed by other transactions but not committed, which also leads to unrepeatable and phantom reading problems.

2. Read Committed: it can avoid reading dirty data, which will still lead to unrepeatable reading and phantom reading.

3. REPEATABLE-READ:Mysql defaults to the isolation level, which will lead to phantom reading. However, mysql uses MVCC consistent reading at this level and does not produce phantom readings.

4. Serializable: the highest isolation level, which will avoid the above problems.

You can view the isolation level of the current system in the following ways

Mysql > select @ @ global.tx_isolation,@@tx_isolation +-+-+ | @ @ global.tx_isolation | @ @ tx_isolation | +-+-+ | REPEATABLE-READ | REPEATABLE-READ | +- -+-+ 1 row in set (0.00 sec)

Examples of dirty and non-repeatable READ-UNCOMMITTED readings not submitted:

# session Amysql > set session transaction isolation level read uncommitted; # set isolation level to unsubmitted read Query OK, 0 rows affected (0.00 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > select * from inno_tbl where id=2;+----+-+ | id | name | +-- +-+ | 2 | John | +-+-+ 1 row in set (0.00 sec) # session Bmysql > select @ @ tx_isolation +-+ | @ @ tx_isolation | +-+ | REPEATABLE-READ | +-+ 1 row in set (0.00 sec) mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > update inno_tbl set name='Jack Ma' where id=2 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0#session Amysql > select * from inno_tbl where id=2;+----+-+ | id | name | +-- +-+ | 2 | Jack Ma | +-+ 1 row in set (0.00 sec)

At this time, session A reads the data modified by session B but not submitted. If session B rolls back at this time, the data read by An is invalid. This is "dirty data". Because the data read by A for the first time is different from the data read for the second time, it is "unrepeatable". Similarly, if new data is inserted in B, the new data row will also be read in this transaction in A, which is called illusory reading.

In the same process, changing the isolation level of A to read committed will not produce "dirty reading", but it will also produce "unrepeatable reading" and "phantom reading".

Under the default isolation level REPEATABLE-READ:

# session Amysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > select * from inno_tbl where id=2;+----+-+ | id | name | +-- +-+ | 2 | John | +-+-+ 1 row in set (0.00 sec) # session Bmysql > begin Query OK, 0 rows affected (0.00 sec) mysql > update inno_tbl set name='Lucy' where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > commit;Query OK, 0 rows affected (0.03 sec) # session Amysql > select * from inno_tbl where id=2 +-+-+ | id | name | +-+-+ | 2 | John | +-+-+ 1 row in set (0.00 sec) # Note: there is no "non-repeatable read" problem at this time, but if you add a shared lock to the query: mysql > select * from inno_tbl1 where id=2 lock in share mode +-+-+ | id | name | +-+-+ | 2 | Lucy | +-+-+ 1 row in set (0.00 sec)

Description:

The transaction in session A reads the name field in the inno_ TFL table with id 2 as John, and if the transaction in session B changes the name with id 2 in inno_tbl to Lucy and commits, and when the transaction in A reads this row of data again, you will find that if you directly use the select query, the read data is still the old data, and with the shared lock, the real data will be read.

Why? Because in the innodb engine, the addition, deletion, modification and query statements of mysql can be divided into two types: one is snapshot reading, the other is current reading. Only ordinary query statements are snapshot reads, while the remaining additions, deletions and modifications and queries with lock in share mode shared lock or for update exclusive lock belong to the current read. At that time, the read read is the latest data, while the snapshot read is not necessarily the latest data.

It can be inferred that when updating or deleting in session A with conditional name=John, it will definitely not be updated or deleted successfully, as shown below:

Mysql > update inno_tbl set name=' Zhang San 'where name='John';Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0mysql > delete from inno_tbl where name='John';Query OK, 0 rows affected (0.00 sec)

If the isolation level is changed to Read Commited, the query statement in session A can query out the latest content that has been changed and submitted in session B without adding lock in share mode or for update. This situation is called unrepeatable. At this point, I have a little question: is there a contradiction between unrepeatable reading and phantom reading? Answer: no, unrepeatable reading is mainly aimed at modification, while phantom reading is mainly aimed at inserting and deleting.

The above is all the content of the article "what is the Mysql Innodb transaction isolation level?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.