Mysql Innodb 事务隔离级别说明

最新推荐文章于 2024-07-14 22:02:42 发布

原创最新推荐文章于 2024-07-14 22:02:42 发布 · 226 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

0 篇文章

订阅专栏

原文链接 https://www.treeroot.tech/archives/mysqlinnodbisolation

事务的四种隔离级别包括以下：

未提交读（Read uncommitted）允许脏读，也就是可以读取到其他事务未提交的数据。
已提交读（Read committed）避免脏读，只能读取已经提交的数据，重复读可能读取到不一样的数据源。
可重复读（Repeatable read）可重复读，同一事务内重复读可以读取到一样的数据，InnoDB默认事务隔离级别，但是还是会有幻读的可能性。
可串行化（Serializable ）完全串行化的读，每次读都需要获取表级共享锁，读写会互斥。

首先创建一个简单的表：

CREATE TABLE `person` (
  `id` int(11) NOT NULL COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

未提交读（Read uncommitted）

修改事务隔离级别为：Read uncommitted

创建两个会话，修改两个会话的事务隔离级别为 Read uncommitted：

mysql> set session transaction isolation level read uncommitted;

Sesstion A:
开启事务，查询数据，返回空

mysql> select * from person;
Empty set (0.00 sec)
mysql>

Session B:
开启事务，插入一条数据，不提交事务

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

mysql> insert into person (id, name, age) values (1, 'zhangsan', 20);
Query OK, 1 row affected (0.00 sec)

mysql>

Session A:
Session B 事务并没有提交而 Session A 却读取到了 Session B 插入的数据，这就是脏读

mysql> select * from person;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  20 |
+----+----------+-----+
1 row in set (0.00 sec)

mysql>

已提交读（Read committed）

修改事务隔离级别为：Read committed

mysql> set session transaction isolation level read committed;

Session B:
插入一条数据，不提交事务

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

mysql> insert into person (id, name, age) values (2, 'lisi', 30);
Query OK, 1 row affected (0.00 sec)

mysql>

Session A:
开启事务查询，因为Session B 事务未提交，所以Session A 读取不到数据

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

mysql> select * from person;
Empty set (0.01 sec)

mysql>

Session B：
提交事务

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

mysql>

Session A:
因为Session B 事务已提交，则Session A 可以读取到数据，但是这里有个问题就是Session A 在同一事务里两次读取到不同的数据，为避免重复读读取不同的数据，就需要使用可重复读（Repeatable read），接下来介绍可重复读。

mysql> select * from person;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | lisi |  30 |
+----+------+-----+
1 row in set (0.00 sec)

mysql>

可重复读（Repeatable read）

修改事务隔离级别为 Repeatable read

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

Session B:
开启事务，插入数据，不提交事务

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

mysql> insert into person (id, name, age) values (3, 'wangwu', 50);
Query OK, 1 row affected (0.00 sec)

mysql>

Session A:
开启事务，因为Session B 事务未提交未读取到数据

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

mysql> select * from person;
Empty set (0.01 sec)

mysql>

Session B:
提交事务

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

mysql>

Session A:
Session B事务已提交, 依然读取不到数据，这就是可重复读（Repeatable read），即使Session B已提交事务，在Session A 事务里依然读取不到数据

mysql> select * from person;
Empty set (0.00 sec)

mysql>

Session A:
提交事务后再读取则能读取到数据

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

mysql> select * from person;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  3 | wangwu |  50 |
+----+--------+-----+
1 row in set (0.00 sec)

mysql>

幻读问题

当前还是可重复读状态
Session B:
开启事务，插入一条数据，不提交事务

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

mysql> insert into person (id, name, age) values (4, 'zhaoliu', 60);
Query OK, 1 row affected (0.01 sec)

mysql>

Session A:
开启事务，读取不到数据

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

mysql> select * from person;
Empty set (0.00 sec)

mysql>

Session B:
提交事务

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

mysql>

Session A:
查询一次，没有读取到数据，则插入一条数据，插入失败错误大意是说主键重复，很奇怪明明读取不到任何数据插入却失败，这就是幻读，仿佛出现了幻觉。

mysql> select * from person;
Empty set (0.00 sec)

mysql> insert into person (id, name, age) values (4, 'zhaoliu', 60);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql>

如何避免幻读

一种方式是在可重复读隔离级别下使用for update 锁, 如果你开启的事务是读判断再插入数据的，那么你需要在select语句后面增加for update
Session A:

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

mysql> select * from person for update;
Empty set (0.00 sec)

mysql>

Session B:
开启事务插入数据，插入数据被阻塞，因为Session A 事务在select 使用了for update，至于for update 锁的细节，我后面会单独写一篇介绍Innodb的锁

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

mysql> insert into person (id, name, age) values (5, 'zhaoqi', 80);
_

Session A:
插入数据，提交事务

mysql> select * from person for update;
Empty set (0.00 sec)

mysql> insert into person (id, name, age) values (6, 'wangqi', 80);
Query OK, 1 row affected (0.00 sec)

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

mysql>

Session B:
因为Session A 已提交事务，所以Session B 这里得insert 从阻塞中返回，插入成功提交事务

mysql> insert into person (id, name, age) values (5, 'zhaoqi', 80);

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

mysql>

这个是在可重复读下使用for update 锁来解决幻读问题。

可串行化（Serializable ）

串行化是读加共享锁，写加排它锁，使用悲观锁的理论，实现简单数据安全但是并发能力太差，如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。串行化也可以解决幻读问题。