DBA MySQL事务隔离
基础概念
并发问题
当高并发访问时会遇到多个事务的隔离问题，可能会出现以下情况：
- 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
- 不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
- 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
隔离级别
系统默认隔离级别为3级，可能出现幻读的情况：
| 隔离级别 | 中文释义 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|---|
| read uncommitted | 读未提交 | 是 | 是 | 是 | 最低的事务隔离级别，一个事务还没提交时，它做的变更就能被别的事务看到 |
| read committed | 不可重复读 | 否 | 是 | 是 | 保证一个事物提交后才能被另外一个事务读取，另外一个事务不能读取该事物未提交的数据 |
| repeatable read | 可重复读 | 否 | 否 | 是 | 多次读取同一范围的数据会返回第一次查询的快照，即使其他事务对该数据做了更新修改，事务在执行期间看到的数据前后必须是一致的 |
| serializable | 串行化 | 否 | 否 | 否 | 事务 100% 隔离，可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别 |
一般来说系统默认的3级就足以应付大部分应用场景，但是设计金融类数据时一定要慎重，一般金融类数据会选用不可重复读的级别。
查询级别
MySQL8版本查询隔离级别：
SELECT @@GLOBAL.TRANSACTION_ISOLATION,@@TRANSACTION_ISOLATION;
MySQL8以下版本查询隔离级别：
SELECT @@TX_ISOLATION;
设置级别
设置当前会话的隔离级别，关闭终端后失效：
SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
设置全局的隔离级别，mysqld.service服务重启后失效：
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;
操作演示
数据准备
演示事务隔离级别，需要准备的数据如下：
# 成绩表
CREATE TABLE grades(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT "记录编号",
name CHAR(32) NOT NULL COMMENT "学生姓名",
total SMALLINT(3) NOT NULL DEFAULT 0 COMMENT "总成绩",
evaluation CHAR(64) NOT NULL DEFAULT "UNKNOW" COMMENT "评语"
) ENGINE innodb CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
# 插入数据
INSERT INTO
grades(name, total)
VALUES
("Jack", 80),
("Ken", 60),
("Tom", 40);
为了模拟并发场景，下面的演示都需要开启两个终端进行测试。
读未提交（脏读）
开发环境中禁止出现脏读级别！
示例演示如下：
# SESSION 1
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
BEGIN;
UPDATE db1.grades SET total = 81 WHERE name = "Jack";
# SESSION 2
SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted;
BEGIN;
SELECT * FROM db1.grades;
SESSION2查到的结果如下：
# SESSION1并未进行COMMIT操作
# 读取到的数据是内存data buffer pool中的脏页数据
+----+------+-------+------------+
| id | name | total | evaluation |
+----+------+-------+------------+
| 1 | Jack | 81 | UNKNOW |
| 2 | Ken | 60 | UNKNOW |
| 3 | Tom | 40 | UNKNOW |
+----+------+-------+------------+
不可重复读
不可重复读一般在处理金融类数据时比较常见，如我现在想要统计一下所有学生的总成绩，但是一直都在变动就统计不了。
而对于金融类数据而言，比如一年中的最后一天12：00时统计当年总营收，恰好11：59的时候又多了1000万，不可重复读就可以及时的进行记录。
示例如下：
# SESSION 1
SET SESSION TRANSACTION ISOLATION LEVEL read committed;
BEGIN;
UPDATE db1.grades SET total = 81 WHERE name = "Jack";
COMMIT;
# SESSION 2
SET SESSION TRANSACTION ISOLATION LEVEL read committed;
BEGIN;
SELECT SUM(total) FROM db1.grades; -- 结果： SUM(total) = 181
# SESSION 1
BEGIN;
UPDATE db1.grades SET total = 82 WHERE name = "Jack";
COMMIT;
# SESSION 2
SELECT SUM(total) FROM db1.grades; -- 结果： SUM(total) = 182
可重复读
针对不可重复读的现象，将其设置为可重复读即可完成成绩的统计，这也是MySQL默认的级别，但是对金融类的统计就不要用该模式了：
# SESSION 1
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
BEGIN;
UPDATE db1.grades SET total = 81 WHERE name = "Jack";
COMMIT;
# SESSION 2
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
BEGIN;
SELECT SUM(total) FROM db1.grades; -- 结果： SUM(total) = 181
# SESSION 1
BEGIN;
UPDATE db1.grades SET total = 82 WHERE name = "Jack";
COMMIT;
# SESSION 2
SELECT SUM(total) FROM db1.grades; -- 结果： SUM(total) = 181
幻读
默认的级别会有可能有较小几率产生幻读，如下所示，SESSION1的老师开启事务，准备为所有大于60分成绩的同学写评语，恰好此时SESSION2插入了一条分数为100分的同学记录，SESSION1的教师写完评语之后一看表居然还有一个没打评语的，感觉产生了幻觉一样。
# 由于默认级别的幻读很难演示，所以这里使用不可重复读的级别来进行幻读的演示
# 默认级别如果按照下面的方式进行演示将会引发区间行锁
# SESSION 1
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
BEGIN;
SELECT * FROM db1.grades; -- 老师先查看了一下表
# SESSION 2
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
BEGIN;
INSERT INTO
grades(name, total)
VALUES
("Kevin", 100);
# SESSION 1
UPDATE db1.grades
SET evaluation = CONCAT("你考了",db1.grades.total,"分，很不错、下次继续努力" )
WHERE total > 60;
# SESSION 2
COMMIT;
# SESSION 1
COMMIT;
SELECT * FROM db1.grades; -- 老师怀疑人生
结果如下：
# 老师第一次查看表
+----+------+-------+------------+
| id | name | total | evaluation |
+----+------+-------+------------+
| 1 | Jack | 80 | UNKNOW |
| 2 | Ken | 60 | UNKNOW |
| 3 | Tom | 40 | UNKNOW |
+----+------+-------+------------+
# 老师怀疑人生
M > SELECT * FROM db1.grades;
+----+-------+-------+-------------------------------------------------+
| id | name | total | evaluation |
+----+-------+-------+-------------------------------------------------+
| 1 | Jack | 80 | 你考了80分，很不错、下次继续努力 |
| 2 | Ken | 60 | UNKNOW |
| 3 | Tom | 40 | UNKNOW |
| 4 | Kevin | 100 | UNKNOW |
+----+-------+-------+-------------------------------------------------+
串行化
串读可以有效杜绝掉幻读现象，但是并发性支持就不好了，所以一般都不会使用它。
