[SQL必知必会学习] 11 事务隔离

 

事务隔离的级别

SQL-92 标准中已经对 3 种异常情况进行了定义， 这些异常情况级别分别为脏读（Dirty Read） 、 不可重复读（Nnrepeatable Read） 和幻读（Phantom Read） 。

SQL-92 标准定义了 4 种隔离级别来解决事务隔离出现的异常情况。这四种隔离级别从低到高分别是：读未提交（READ UNCOMMITTED ） 、 读已提交（READ COMMITTED） 、 可重复读（REPEATABLE READ） 和可串行化（SERIALIZABLE） 。 这些隔离级别能解决的异常情况如下表所⽰：

图片来自极客时间SQL必知必会专栏.png

读未提交， 也就是允许读到未提交的数据， 这种情况下查询是不会使用锁的；读已提交，就是只能读到已经提交的内容， 可以避免脏读的产生；可重复读， 保证一个事务在相同查询条件下两次查询得到的数据结果是一致的， 可以避免不可重复读和脏读， 但无法避免幻读。 MySQL 默认的隔离级别就是可重复读。可串行化， 将事务进行串行化， 也就是在一个队列中按照顺序执行， 可串行化是最高级别的隔离等级， 可以解决事务读取中所有可能出现的异常情况， 但是它牺牲了系统的并发性

事务并发处理可能存在的异常

模拟3种异常，通过以下命令建立英雄数据表 heros_temp。

DROP TABLE IF EXISTS `heros_temp`;
CREATE TABLE `heros_temp`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `heros_temp` VALUES (1, '张飞');
INSERT INTO `heros_temp` VALUES (2, '关羽');
INSERT INTO `heros_temp` VALUES (3, '刘备');

模拟3种异常.png

通过命令查询当前Mysql隔离级别：

SHOW VARIABLES LIKE 'tx_isolation';

transaction_isolation在MySQL 5.7.20中添加了作为别名 tx_isolation，现已弃用，并在MySQL 8.0中删除。

 

当前Mysql隔离级别.png

设置为READ UNCOMMITTED（读未提交）降低隔离级别到最低：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

设置为READ UNCOMMITTED（读未提交）.png

更改MySQL 默认的事务自动提交：

SET autocommit =0;
SHOW VARIABLES LIKE 'autocommit';

关闭事务自动提交.png

启动终端 2，并按上述操作设置隔离级别为 READ UNCOMMITTED（读未提交） ， autocommit 设置为 0。

脏读（Dirty Read）

终端 2 中开启一个事务， 在 heros_temp 表中写入一个新的英雄“吕布”， 但是这个时候不要提交。

BEGIN;
INSERT INTO `heros_temp` VALUES (4, '吕布');

终端 1中查看当前的英雄表：

脏读（Dirty Read）.png

终端 1 中读取了终端 2 未提交的新英雄“吕布”， 实际上终端 2 可能马上回滚， 从而造成了“脏读”。

终端 2 未提交事务， 但是终端 1 却读到了一张还没有提交的数据， 这种现象我们称之为“脏读”。

不可重复读（Nnrepeatable Read）

终端 1 来查看 id=1 的英雄，然后用终端 2 对 id=1 的英雄姓名进行修改：

UPDATE heros_temp SET name = '张翼德' WHERE id = 1;

image.png

对于终端 1 来说， 同一条查询语句出现了“不可重复读”

同一条记录， 终端 1连续两次读取的结果不同，称之为“不可重复读”

幻读（Phantom Read）

终端 1 查询数据表中的所有英雄，然后终端 2， 开始插入新的英雄“吕布”，再用终端 1 重新进行查看：

幻读（Phantom Read）.png

终端 1连续两次查询，发现多了一个英雄， 原来只有 3 个， 现在变成了 4 个。 这种异常情况我们称之为“幻读”。

三种异常情况的特点：

1. 脏读：读到了其他事务还没有提交的数据。

2. 不可重复读：对某数据进行读取， 发现两次读取的结果不同， 也就是说没有读到相同的内容。 这是因为有其他事务对这个数据同时进行了修改或删除。

3. 幻读：事务 A 根据条件查询得到了 N 条数据， 但此时事务 B 更改或者增加了 M 条符合事务A 查询条件的数据， 这样当事务 A 再次进行查询的时候发现会有 N+M 条数据， 产生了幻读。

不可重复读VS幻读

不可重复读是对于同一条记录内容的“不可重复读”
幻读是对于某一范围的数据集，发现查询数据集的行数多了或者少了，从而出现的不一致。
不可重复读的原因是 对于要查询的那条数据进行了UPDATE或DELETE
幻读是对于要查询的 那个范围的数据集，进行了INSERT。

事务处理与事务的隔离级别.png

参考资料：

练习数据库使用 SQL必知必会专栏（极客时间）中的作者提供的 王者荣耀数据库以及NBA数据库
练习系统 MySQL Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

SQL必知必会专栏（极客时间）链接：
http://gk.link/a/103Sm

《MySQL必知必会》学习笔记（24-29）:

https://www.jianshu.com/p/ee15e71ab1b8

此篇内容:使用游标、使用触发器、管理事务处理、全球化和本地化、安全管理、数据库维护

---

GitHub链接：
https://github.com/lichangke/LeetCode

知乎个人首页：
https://www.zhihu.com/people/lichangke/

简书个人首页：
https://www.jianshu.com/u/3e95c7555dc7

个人Blog:
https://lichangke.github.io/

欢迎大家来一起交流学习