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                                          |
+----+-------+-------+-------------------------------------------------+

串行化

串读可以有效杜绝掉幻读现象，但是并发性支持就不好了，所以一般都不会使用它。

posted @ 2021-02-23 01:11 云崖君阅读(59) 评论(0) 编辑收藏举报

刷新页面返回顶部

摘星小筑