MySQL InnoDB 事务隔离级别详解（脏读，不可重复读，幻读）

  • 发表于：
  • 分类： MySQL InnoDB
  • 浏览：浏览(761)

本文主要介绍了 MySQL InnoDB 支持的四种事务隔离级别，围绕隔离级别，解释了脏读，不可重复读，以及幻读的概念。

1. MySQL 支持的四种隔离级别

隔离性 isolation (I) 是事务 ACID 四种属性中的一种，它定义了如何将事务与事务之间隔离开来，隔离性是应用程序设计的关键因素之一。根据 SQL:1992 标准，InnoDB 支持四种隔离级别。下面按顺序列出了这些隔离级别的类型，从上到下，隔离性越好，事务的一致性也越高。

  • READ-UNCOMMITTED
  • READ-COMMITTED
  • REPEATABLE-READ（MySQL默认的隔离级别）
  • SERIALIZABLE

可以动态修改会话的隔离级别，在 MySQL 中通过修改参数 transaction_isolation 来动态修改隔离级别，如下：

set global transaction_isolation='read-committed';

2. 读未提交（READ-UNCOMMITTED）

  • 不需要锁
  • 可能会出现脏读，不可重复读，幻读

下面将通过例子演示读未提交，演示脏读现象。创建 2 个会话，设置隔离级别为读未提交。

session1:

mysql> set global transaction_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> \r
Connection id:    16
Current database: percona

mysql> select * from ReadUncommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

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

mysql> update ReadUncommit set name='ram' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2:

mysql> select * from percona.ReadUncommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | ram  |
+----+------+
3 rows in set (0.00 sec)

session2 的会话读到了 session1 未提交的修改，这一现象称之为脏读。

3. 读提交（READ-COMMITTED）

读提交（READ-COMMITTED）隔离级别，简称 RC。

  • 不会出现脏读
  • 可能出现不可重复读和幻读

下面通过示例来演示 RC 隔离级别避免脏读以及产生的不可重复读现象，先看避免脏读，如下：

session1：

mysql> set global transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> \r
Connection id:    18
Current database: percona

mysql> select * from ReadCommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

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

mysql> update ReadCommit set name='ram' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session2：

mysql> select * from percona.ReadCommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

session1 执行了一条更新语句，但是没有提交，session2 不能查到未提交的数据，表明 RC 隔离级别不会出现脏读。

另外一个示例，演示 RC 隔离级别会出现不可重复读，如下：

session1：

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

mysql> select * from ReadCommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

session2：

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

mysql> update percona.ReadCommit set name='ram' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

session1：

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

mysql> select * from ReadCommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from ReadCommit;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | ram  |
+----+------+
3 rows in set (0.00 sec)

session1 开启一个事务，先执行一个 select 查询语句，然后 session2 执行一个 update 更新语句，然后 session1 再次执行同样的 select 语句，session1 在一个事务中，两次执行同样的 select 语句，查询到的结果却不一样，这就是不可重复读现象。

在事务中同一个查询语句产生不同的结果，这可能会导致事务不一致。这种现象可通过“可重复读”隔离级别解决。

4. 重复读（REPEATABLE-READ）

重复读隔离级别，简称 RR。

  • 不会出现脏读和不可重复读
  • 可能出现幻读

下面通过示例来演示可重复读。

session1：

mysql> set global transaction_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

mysql> \r
Connection id:    20
Current database: percona

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

mysql> select * from RepeatRead;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

session2：

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

mysql> update percona.RepeatRead set name='ram' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

session1：

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

mysql> select * from RepeatRead;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from RepeatRead;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

session1 在一个事务里，对同一个查询 SQL 执行了两次，两次中间，session2 对数据进行了更新，但是 session1 两次查询的结果都是一样的。RR 隔离级别避免了 RC 隔离级别无法克服的不可重复读现象。

RR 隔离级别的幻读：

下面通过示例来演示 RR 隔离级别下的幻读。

session1：

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

mysql> select * from RepeatRead;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

session2：

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

mysql> insert into percona.RepeatRead values (4,'ram');
Query OK, 1 row affected (0.00 sec)

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

session1：

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

mysql> select * from RepeatRead;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from RepeatRead;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

mysql> update percona.RepeatRead set name='sriram' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from RepeatRead;
+----+--------+
| id | name   |
+----+--------+
|  1 | jc     |
|  2 | herc   |
|  3 | sri    |
|  4 | sriram |
+----+--------+
4 rows in set (0.00 sec)

session1 执行 select 查询数据，session2 插入了一条新数据，session1 再次执行 select 查询，结果与第一次执行相同。然后 session1 更新 session2 插入的数据，然后再次执行 select 查询，这次执行的结果与前二次不同。这个现象称之为幻读。想要避免幻读，可通过串行化（SERIALIZABLE）隔离级别实现。

5. 串行化（SERIALIZABLE）

  • 没有脏读
  • 没有不可重复读
  • 没有幻读

下面通过示例演示串行化隔离级别。

session1：

mysql> set global transaction_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

mysql> \r
Connection id:    22
Current database: percona

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

mysql> select * from Serialize;
+----+------+
| id | name |
+----+------+
|  1 | jc   |
|  2 | herc |
|  3 | sri  |
+----+------+
3 rows in set (0.00 sec)

session2：

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

mysql> insert into percona.Serialize values (4,'ram');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update percona.Serialize set name='aaa' where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1 执行查询语句，session2 执行更新语句，session2 锁等待超时。串行化是隔离性最好的一种隔离级别，能够确保事务的一致性，但同时也是并发性最差的。

文本译自：

https://www.percona.com/blog/2021/02/11/various-types-of-innodb-transaction-isolation-levels-explained-using-terminal/

文章评论

共0条评论