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