MySQL InnoDB 事务隔离级别详解(脏读,不可重复读,幻读)

本文主要介绍了 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条评论