innodb之事务隔离级别示例
- 2019 年 11 月 6 日
- 筆記
下面我们用真实的例子来说明各个级别的情况，首先我们创建一个数据库test，然后再数据库中创建一个表city，在这个city表中来进行测试：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ rows in set (0.03 sec) mysql> create database test; Query OK, row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE `city`(`id` int() NOT NULL AUTO_INCREMENT , -> `name` varchar() not NULL DEFAULT ' ' , -> `state` varchar() not NULL DEFAULT ' ' , -> PRIMARY KEY (`id`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT='城市'; Query OK, rows affected (0.05 sec) mysql> exit Bye
为了更加方便观察执行时间，我们给mysql的命令行前面打上时间戳，也就是在/etc/my.cnf中添加prompt参数
–prompt="\u@\h : \d\r:\m:\s>"，其中u表示user，h表示host、d表示默认数据库，r、m、s是表示当前时间精确到秒，具体参数可以上网查看，这样我们的命令行中就打上了时间戳，更加方便我们理解。
首先来看未提交读(read uncommitted)
会话1：
root@localhost :test11::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'西安','陕西'); Query OK, row affected (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'宝鸡','陕西'); Query OK, row affected (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'陕北','陕西'); Query OK, row affected (. sec) root@localhost :test11::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test11::>begin; Query OK, rows affected (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'陕南','陕西'); Query OK, row affected (. sec) root@localhost :test11::>
我们可以看到，我们插入了三条记录，然后开启事务，插入第四条记录，此时我们打开会话2，如下图所示：
会话2
root@localhost :test11::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test11::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test11::>set session transaction isolation level read uncommitted; Query OK, rows affected (. sec) root@localhost :test11::>select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ row in set, warning (. sec) root@localhost :test11::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test11::>
我们可以发现，innodb默认的隔离级别是可重复读，即repeated-read，当处于这种事务隔离级别的时候，我们现在不能看到第四条记录，因为第四条记录属于第一个会话的事务，还没有提交。当我们使用set session语法将隔离级别改为未提交读，然后重新查看记录的时候，我们发现这条记录已经可以看到了，这就是常说的"脏读"，这种隔离级别下是不能避免脏读的。
再来看看已提交读read committed:
会话1：
root@localhost :test12:09:>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test12::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>insert into city (id,name,state) values (,'壶口','陕西'); Query OK, row affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | +----+--------+--------+ rows in set (. sec)
还跟上次一致，我们在可重复读的情况下开启事务，然后插入一条记录5，然后打开会话2，可以看到如下结果：
会话2：
root@localhost :test12::>set session transaction isolation level read committed; Query OK, rows affected (. sec) root@localhost :test12::>select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ row in set, warning (. sec) root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | +----+--------+--------+ rows in set (. sec)
可以看到，当我们把事务的隔离级别调整至read committed(简称RC)时，我们无法看到第5条记录，因为它没有在会话1当中进行提交，此时我们在会话1中进行提交：
root@localhost :test12::>commit; Query OK, 0 rows affected (0.10 sec)
然后重新查看会话2，我们发现
root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | +----+--------+--------+ rows in set (. sec)
这条记录已经可以查看了，说明这种情况避免了"脏读",数据的真实性得到了保证，但是带来了另外一个问题，那就是同样的数据查询操作，得出了不一样的结果，不能避免重复读导致的数据不一致情况。RC级别下虽然避免的脏读的情况，但是不可避免重复读导致的两次查询不一致的情况。
然后看看可重复读Repeatable Read的情况，我们在记录中插入第6条，然后进行提交，操作如下：
会话1：
root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>insert into city (id,name,state) values (,'富平','陕西'); Query OK, row affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test12::>commit; Query OK, rows affected (. sec)
然后我们打开会话2，开启事务，然后去查询数据，发现第6条数据没有被推过来，即使我们已经在会话1中进行了提交，会话2中依旧没有看到这条信息：
会话2：
root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test12::>commit; Query OK, rows affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | +----+--------+--------+ rows in set (. sec)
可以看到，只有在会话2也进行提交的情况下，才能看到会话1更新的数据，换句话说，在事务没有结束的时候，两个会话里面的操作互不影响。所以说RR级别下可以重复读。
再看一个例子，这个例子说明了RR是如何避免幻读的。在此之前，我们再重复下并发事务带来的问题概念：
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
现在来看会话1：
root@localhost :test08::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test08::>begin -> ; Query OK, rows affected (. sec) root@localhost :test08::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test08::>insert into city (id,name,state) values (,'杨凌','陕西'); Query OK, row affected (. sec) root@localhost :test08::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | | 7 | 杨凌 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test08::>commit; Query OK, rows affected (.08 sec)
再来看看会话2：
root@localhost :test08::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test08::>begin -> ; Query OK, rows affected (. sec) root@localhost :test08::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test08::>insert into city (id,name,state) values (,'杨凌','陕西'); Query OK, row affected (. sec) root@localhost :test08::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test08::>begin; Query OK, rows affected (. sec) root@localhost :test09::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | +----+--------+--------+ rows in set (. sec) root@localhost :test09::08>select * from city for update; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | | 7 | 杨凌 | 陕西 | +----+--------+--------+ rows in set (15.74 sec) root@localhost :test09::>select * from city lock in share mode; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陕西 | | 2 | 宝鸡 | 陕西 | | 3 | 陕北 | 陕西 | | 4 | 陕南 | 陕西 | | 5 | 壶口 | 陕西 | | 6 | 富平 | 陕西 | | 7 | 杨凌 | 陕西 | +----+--------+--------+ rows in set (. sec)
我们可以看到，当我们使用加锁的方法时，可以读取到最新的数据，当使用快照的方法，我们虽然已经插入了记录7，但是还是无法看到这条记录，所以说：RR情况下，不加锁，得到的数据就是旧的。
一点结论：
1.innodb中的已提交读（Read committed） 和可重复读（Repeatable read） 两个级别是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了已提交读，就会导致前后两次读到的结果不一致，违背了可重复读。所以针对这种情况可以使用加锁去查询最新的数据。
2.MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。
3.四个级别逐渐增强，每个级别解决一个问题。事务级别越高,性能越差
4.未提交读RU会导致脏读的情况，已提交读RC避免脏读，但是会导致数据不一致，可重复读保证了数据的一致性，但是不能避免幻读。
