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避免脏读,但是会导致数据不一致,可重复读保证了数据的一致性,但是不能避免幻读。