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编辑  收藏  举报