What is Mysql Innodb transaction isolation level?

This article brings you an introduction to what is Mysql Innodb transaction isolation level? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Mysql has four transaction isolation levels, as follows:

1. Read Uncommitted: It allows reading dirty data that has been changed but not committed by other transactions, and will also be read. Leading to non-repeatable read and phantom read problems.

2. Read Committed: It can avoid reading dirty data, but it will still cause non-repeatable read and phantom read problems.

3. REPEATABLE-READ: Mysql default isolation level will cause phantom reads. However, mysql uses MVCC consistency reading at this level and will not produce phantom reads. .

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

You can use the following method to check the isolation level of the current system

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

Not yet Committed readREAD-UNCOMMITTED Example of dirty read and non-repeatable read:

#session A
mysql> set session transaction isolation level read uncommitted;   #设置隔离级别为未提交读
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 B
mysql> 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 A
mysql> 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 but not submitted by session B. If session B rolls back at this time, Then the data read by A is invalid, which is "dirty data", because the data read by A for the first time is different from the data read for the second time, which is a "non-repeatable read"; In the same way, if new data is inserted into B, new data rows will also be read in this transaction in A. This is a phantom read.

In the same process, if the isolation level of A is changed to read committed, "dirty reading" will not occur, but "non-repeatable reading" and "phantom reading" will also occur

Default Isolation level REPEATABLE-READUnder:

#session A
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 B
mysql> 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: 0
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

#session A
mysql> select * from inno_tbl where id=2;
+----+--------------+
| id | name         |
+----+--------------+
|  2 | John         |
+----+--------------+
1 row in set (0.00 sec)
#注意，此时没有产生“不可重复读”问题，但若是为查询加上共享锁：
mysql> select * from inno_tbl1 where id=2 lock in share mode;
+----+---------+
| id | name    |
+----+---------+
|  2 | Lucy    |
+----+---------+
1 row in set (0.00 sec)

Explanation:

The transaction in session A reads that the name field in the inno_tbl table with id 2 is John, and If the transaction in session B changes the name with id 2 in inno_tbl to Lucy and submits it, if the transaction in A reads this row of data again later, it will be found that if you directly use the select method to query, the read The data is still the old data, and with the shared lock, the real data will be read.

Why? Because in the innodb engine, mysql's add, delete, modify and query statements can be divided into two types: one is snapshot read, and the other is current read. Only ordinary query statements are snapshot reads, while the remaining additions, deletions, modifications and query statements with lock in share mode shared lock or for update exclusive lock are all current reads; what is read at that time is the latest data, while snapshot reading does not necessarily read the latest data.

It can be deduced from this: When updating or deleting with the condition name=John in session A, the update or deletion will definitely not be successful, as shown below:

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

If you isolate If the level is changed to Read Commited, then the query statement in session A can query 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 impossibility. Read repeatedly. As I write this, I have a little question. Are non-repeatable reading and phantom reading contradicting each other? Answer: No, non-repeatable reading is mainly for modification, and phantom reading is mainly for modification. For insertions and deletions.

The above is the detailed content of What is Mysql Innodb transaction isolation level?. For more information, please follow other related articles on the PHP Chinese website!