前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >innodb之事务隔离级别示例

innodb之事务隔离级别示例

作者头像
AsiaYe
发布于 2019-11-06 16:16:29
4050
发布于 2019-11-06 16:16:29
举报
文章被收录于专栏：DBA随笔
innodb之事务隔离级别示例
Innodb事务的隔离级别示例
上一篇文章中，我们已经知道事务的隔离级别分为未提交读(Read Uncommitted)、提交读(Read Committed)、可重复读(Repeated Read)以及串行读(serialzable),这里我们再次给出

下面我们用真实的例子来说明各个级别的情况，首先我们创建一个数据库test，然后再数据库中创建一个表city，在这个city表中来进行测试：

代码语言：javascript
复制
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：

代码语言：javascript
复制
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

代码语言：javascript
复制
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：

代码语言：javascript
复制
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：

代码语言：javascript
复制
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中进行提交：

代码语言：javascript
复制
root@localhost :test12::>commit;
Query OK, 0 rows affected (0.10 sec)

然后重新查看会话2，我们发现

代码语言：javascript
复制
root@localhost :test12::>select * from city;
+----+--------+--------+
| id | name   | state  |
+----+--------+--------+
|  1 | 西安   | 陕西   |
|  2 | 宝鸡   | 陕西   |
|  3 | 陕北   | 陕西   |
|  4 | 陕南   | 陕西   |
|  5 | 壶口   | 陕西   |
+----+--------+--------+
 rows in set (. sec)

这条记录已经可以查看了，说明这种情况避免了"脏读",数据的真实性得到了保证，但是带来了另外一个问题，那就是同样的数据查询操作，得出了不一样的结果，不能避免重复读导致的数据不一致情况。RC级别下虽然避免的脏读的情况，但是不可避免重复读导致的两次查询不一致的情况。

然后看看可重复读Repeatable Read的情况，我们在记录中插入第6条，然后进行提交，操作如下：

会话1：

代码语言：javascript
复制
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：

代码语言：javascript
复制
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：

代码语言：javascript
复制
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：

代码语言：javascript
复制
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避免脏读，但是会导致数据不一致，可重复读保证了数据的一致性，但是不能避免幻读。

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2018-12-28，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
sql

本文分享自 DBA随笔 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

数据库
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论