事务隔离的级别
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 个。 这种异常情况我们称之为“幻读”。
三种异常情况的特点:
-
脏读:读到了其他事务还没有提交的数据。
-
不可重复读:对某数据进行读取, 发现两次读取的结果不同, 也就是说没有读到相同的内容。 这是因为有其他事务对这个数据同时进行了修改或删除。
-
幻读:事务 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
此篇内容:使用游标、使用触发器、管理事务处理、全球化和本地化、安全管理、数据库维护
个人Blog:
https://lichangke.github.io/
欢迎大家来一起交流学习