Detailed explanation and comparison of MySQL's four transaction isolation levels-Mysql Tutorial-php.cn

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

This article mainly introduces the detailed explanation and comparison of the four MySQL transaction isolation levels. Here is a detailed introduction to the basic knowledge of MySQL and its transaction isolation. For comparison, friends in need can refer to

MySQL’s four transaction isolation levels detailed explanation and comparison

According to SQL:1992 transaction isolation level, InnoDB defaults to repeatable read (REPEATABLE READ). MySQL/InnoDB provides all four transaction isolation levels described by the SQL standard. You can set the default isolation level for all connections using the --transaction-isolation option on the command line or in an options file.
For example, you can set this option in the [mysqld] section of the my.inf file like the following:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

Copy after login

Users can use the SET TRANSACTION statement to change the isolation level of a single session or all incoming connections. Its syntax is as follows:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

Copy after login

Note: The default behavior (without session and global) is to set the isolation level for the next (unstarted) transaction. If you use the GLOBAL keyword, the statement sets the default transaction level globally for all new connections created from that point on (except connections that do not exist). You need SUPER privileges to do this. Use the SESSION keyword to set the default transaction level for future transactions performed on the current connection. Any client is free to change the session isolation level (even in the middle of a transaction), or to set the isolation level for the next transaction.

You can use the following statement to query the global and session transaction isolation levels:

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

Copy after login

----Theoretical knowledge in the above manual;
========== ================================================== ===============================
Isolation level Dirty Read NonRepeatable Read Phantom Phantom Read
========================================== =================================================

Read uncommitted Possible Possible

Read committed Impossible Possible Possible

Repeatable read Impossible Impossible Possible

Serializable Not possible ================================================== ================

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

·Read Committed: Only submitted data can be read. Most databases such as Oracle default to this level (no repeated reads)

·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 , reading and writing will block each other

Use examples to illustrate the situation at each level:

① 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

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

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

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttd values(1);
Query OK, 1 row affected (0.05 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
+------+
1 row in set (0.00 sec)

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

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ  |    --------该隔离级别下(除了 read uncommitted)
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from ttd;
Empty set (0.00 sec)       --------不会出现脏读

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)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |                    --------REPEATABLE-READ级别出现脏读

+------+
1 row in set (0.00 sec)

Copy after login

结论:session 2 在READ-UNCOMMITTED 下读取到session 1 中未提交事务修改的数据.

② 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

session 1:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED     |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
+------+
1 row in set (0.00 sec)

session 2 :

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

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
+------+
1 row in set (0.00 sec)

mysql> insert into ttd values(2); /也可以更新数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
|  2 |
+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

session 2 提交后,查看session 1 的结果;

session 1:

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |               --------和第一次的结果不一样,READ-COMMITTED 级别出现了不重复读
|  2 |
+------+
2 rows in set (0.00 sec)
③ 可重复读:
session 1:
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ    |
+------------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |
|  2 |
+------+
2 rows in set (0.00 sec)

session 2 :

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

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttd values(3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

session 2 提交后,查看session 1 的结果;

session 1:

mysql> select * from ttd;
+------+
| id  |
+------+
|  1 |                   --------和第一次的结果一样,REPEATABLE-READ级别出现了重复读
|  2 |
+------+
2 rows in set (0.00 sec)

(commit session 1 之后 再select * from ttd 可以看到session 2 插入的数据3)

Copy after login

④ 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

mysql>CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default &#39;0&#39;,
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ    | REPEATABLE-READ |
+-----------------------+-----------------+

实验一：

t Session A          Session B
|
| START TRANSACTION;     START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|               INSERT INTO t_bitfly
|               VALUES (1, &#39;a&#39;);
|
| SELECT * FROM t_bitfly;
| empty set
|               COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, &#39;a&#39;);
| ERROR 1062 (23000):
| Duplicate entry &#39;1&#39; for key 1
v (shit, 刚刚明明告诉我没有这条记录的)

如此就出现了幻读，以为表里没有数据，其实数据已经存在了，傻乎乎的提交后，才发现数据冲突了。

实验二：

t Session A         Session B
|
| START TRANSACTION;     START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              INSERT INTO t_bitfly
|              VALUES (2, &#39;b&#39;);
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|
| UPDATE t_bitfly SET value=&#39;z&#39;;
| Rows matched: 2 Changed: 2 Warnings: 0
| (怎么多出来一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | z   |
| |  2 | z   |
| +------+-------+

Copy after login

本事务中第一次读取出一行，做了一次更新后，另一个事务里提交的数据就出现了。也可以看做是一种幻读。

当隔离级别是可重复读，且禁用innodb_locks_unsafe_for_binlog的情况下，在搜索和扫描index的时候使用的next-key locks可以避免幻读。

再看一个实验，要注意，表t_bitfly里的id为主键字段。

实验三：
t Session A         Session B
|
| START TRANSACTION;    START TRANSACTION;
|
| SELECT * FROM t_bitfly
| WHERE id<=1
| FOR UPDATE;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              INSERT INTO t_bitfly
|              VALUES (2, &#39;b&#39;);
|              Query OK, 1 row affected
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              INSERT INTO t_bitfly
|              VALUES (0, &#39;0&#39;);
|              (waiting for lock ...then timeout)
|              ERROR 1205 (HY000):
|              Lock wait timeout exceeded;
|              try restarting transaction
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+
|              COMMIT;
|
| SELECT * FROM t_bitfly;

| +------+-------+
| | id  | value |
| +------+-------+
| |  1 | a   |
| +------+-------+

Copy after login

可以看到，用id<=1加的锁，只锁住了id<=1的范围，可以成功添加id为2的记录，添加id为0的记录时就会等待锁的释放。

实验四：一致性读和提交读
t Session A           Session B
|
| START TRANSACTION;       START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| +----+-------+
|                INSERT INTO t_bitfly
|                VALUES (2, &#39;b&#39;);
|                COMMIT;
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| +----+-------+
|
| SELECT * FROM t_bitfly LOCK IN SHARE MODE;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| | 2 | b   |
| +----+-------+
|
| SELECT * FROM t_bitfly FOR UPDATE;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| | 2 | b   |
| +----+-------+
|
| SELECT * FROM t_bitfly;
| +----+-------+
| | id | value |
| +----+-------+
| | 1 | a   |
| +----+-------+

Copy after login

如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。

本身，可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

可以这么讲，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据（提交读）。
MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。

以上就是MySQL 四种事务隔离级别详解及对比的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！