阅读 84

MySQL | MySQL 事务隔离级别和多版本并发控制MVCC

MySQL 提供了两种事务型的存储引擎:InnoDB 和 NDB Cluster 。另外还有一些第三方存储引擎也支持事务


  • 1. 数据库事务

  • 2. 事务并发可能出现的问题

    • 2.1 脏读

    • 2.2 不可重复读

    • 2.3 幻读

  • 3. 事务隔离级别

    • 3.1 读已提交 (READ COMMITTED)是如何工作的

    • 3.2 可重复读 (REPEATABLE READ)

    • 3.3 串行化 (SERIALIZABLE)

  • 4. 多版本并发控制(MVCC)

    • 4.1 InnoDB 的MVCC

  • 5. 混合存储引擎下的事务问题

  • 6. 查看和设置隔离级别

    • 6.1 查看隔离级别

    • 6.2 修改隔离级别


1. 数据库事务

数据库事务指的是一组数据操作,事务内的操作要么就是全部成功,要么就是全部失败。

例如在转账的流程下,张三给李四转账 2000,第一步在账单账户下扣除 2000,第二步在李四账户下增加 2000,这两步可以视为一个事务。如果两步都成功则转账成功,如果其中任意一步失败,则撤回转账操作

事务具有原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)四个特性,简称 ACID。

这里主要说的是 隔离性

2. 事务并发可能出现的问题

为什么要有隔离级别,主要为了防止在事务并发中出现的一系列问题

假设我们的表结构和数据如下:

create table movie (
   id int unsigned not null auto_increment primary key,
   name varchar(30) not null comment '电影名称',
   price int not null comment '票价')ENGINE=InnoDB comment='电影';INSERT INTO movie (id, name, price) VALUES (1, '唐探3', 70);INSERT INTO movie (id, name, price) VALUES (2, '你好,李焕英', 60);

2.1 脏读

脏读指的是读到了其他事务未提交的数据。在事务中未提交的数据有可能是临时数据或者可能需要回滚的数据,本身可能并不会存到数据库中,这就是脏读。

在事务开始前,唐探 3 的价格为 70 元。

事务一先开启事务,然后事务二后开启事务,因为事务二当时处理的快点,先把价格改成了 55 元,此时事务还未提交或者回滚,这时事务一要读这条数据,如果读到事务二已经改动但未提交的数据,即 55 元,这时就发生了脏读

时间、事务事务一事务二
1begin
2
begin
3
update movie set price = 55 where id = 1;
4select price from movie where id = 1;如果这里读到了 55 则出现了脏读
5commit;
6
rollback;

2.2 不可重复读

不可重复读指的是在同一事务内,不同的时刻读到的同一批数据可能是不一样的,可能会受到其他事务的影响,比如在第一次读数据和第二次读数据时,中间其他事务也对这个数据进行了修改,导致同一事务对同一条数据读取发生前后不一致。这种情况通常发生在数据更新(UPDATE)操作。

时间、事务事务一事务二
1begin
2
begin
4select price from movie where id = 1; 此时为 70
3
update movie set price = 55 where id = 1;
4select price from movie where id = 1;如果这里读到了 55 则出现不可重复读
5commit;
6
rollback;

2.3 幻读

幻读是指一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。

时间、事务事务一事务二
1begin
2
begin
4select name from movie where id <= 70; 此时应该查询到 2 条数据为 1. 唐探3, 2. 你好,李焕英
3
INSERT INTO movie (id, name, price) VALUES (3, '刺杀小说家', 50);
4select name from movie where id <= 70; 此时查询到 3 条数据为 1. 唐探3, 2. 你好,李焕英 3. 刺杀小说家
5commit;
6
rollback;

3. 事务隔离级别

SQL 标准定义了四种隔离级别,MySQL 全都支持。这四种隔离级别分别是:

  1. 读未提交(READ UNCOMMITTED)

  2. 读提交 (READ COMMITTED)

  3. 可重复读 (REPEATABLE READ)

  4. 串行化 (SERIALIZABLE)

其中每个隔离级别来解决对应的问题

隔离级别 | 问题脏读不可重复读幻读
读未提交(READ UNCOMMITTED)未解决未解决未解决
读提交 (READ COMMITTED)解决未解决未解决
可重复读 (REPEATABLE READ)解决解决未解决
串行化 (SERIALIZABLE)解决解决解决

从这个表中可以看到隔离级别越高则解决的问题越多,同时级别越高对并发性能的影响也就越大

其中 读未提交(READ UNCOMMITTED) 则什么都没有解决,这里不在讨论这种隔离级别

3.1 读已提交 (READ COMMITTED)是如何工作的

读已提交解决了脏读问题

在读已提交中,主要是针对 update 语句进行处理,首先再看一下,出现脏读的场景,其中前 6 步是刚才模拟发生脏读的情况,在读已提交的隔离级别下:在第 4 步中需要执行发现需要执行查询操作,但是因为发现在事务二中该数据已经被修改,则需要等到事务二完成(提交或者回滚)才能进行查询,因此事务一的第 4,5 步需要等到事务二中 6 执行完成后才能执行

时间、事务事务一事务二
1begin
2
begin
3
update movie set price = 55 where id = 1;
4select price from movie where id = 1;
5commit;
6
rollback;
5第 4 步中的 sql 要等到这里才能执行
6第 5 步中的 sql 要等到这里才能执行

3.2 可重复读 (REPEATABLE READ)

可重复读解决了脏读和不可重复读的问题

时间、事务事务一事务二
1begin
2
begin
4select price from movie where id = 1; 此时为 70
3
update movie set price = 55 where id = 1;
4select price from movie where id = 1; 此时读到还是 70
5commit;
6
commit;
7select price from movie where id = 1; 此时读到是 55

Q: 为什么对事务二中对 id = 1 的数据进行了写(update)而事务一步需要阻塞还能读到呢?

A: 因为InnoDB有MVCC机制(多版本并发控制),可以使用快照读,而不会被阻塞。

3.3 串行化 (SERIALIZABLE)

这个模式就相对简单了,它要求事务序列化执行,事务只能一个接着一个地执行,不能并发执行,解决了脏读、不可重复读和幻读,当然性能也是最差的。

4. 多版本并发控制(MVCC)

MySQL 大部分事务型存储引擎并不是简单的行级锁。基于提升并发行的考虑,它们一般都同时实现了多版本并发控制 MVCC.

4.1 InnoDB 的MVCC

InnoDB 的 MVCC ,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间,一个保存行的过期时间(或删除时间),当然存储的并不是真正的时间,而是系统版本号。每开始一个事务,系统版本号就会自动递增,事务开始时刻的版本号作为当前事务的版本号,用来和查询到的每行记录的版本号就行比较。

以下是 REPEATABLE READ 的隔离级别下具体操作:

  • SELECT

    InnoDB 会根据以下两个条件检查每行记录:

    a. InnoDB 只查询版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版号),这样可以确保事务读取的行,要么是在事务开始前的已经存在的,要么是事务自身插入或者修改过的。
    b. 行的删除版本要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。

    只有符合上述两个条件的记录,才能返回作为查询结果

  • INSERT

    InnoDB 为新插入的每一行保存当前系统版本号作为行版本号

  • DELETE

    InnoDB 为删除的每一行保存当前系统版本号作为行删除标识

  • UPDATE
    InnoDB 为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识

保存着两个额外的系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行

5. 混合存储引擎下的事务问题

MySQL 在服务层不管理事务,事务由下层的存储引擎实现,所以在不同的存储引擎中处理同一个事务是不可靠的。
例如: table_a 使用 innodb 引擎, table_b  使用 MyISAM 引擎(不支持事务)
假设以下操作在一个事务中:

insert table_a ...    # 1
insert table_b ...    # 2
update table_a ...    # 3

假设在 执行 #3 时出现了异常,这时事务要回滚,因为 table_b 并不支持事务,这就导致 table_b 的修改无法回滚,导致违反事务的 一致性 和 原子性

6. 查看和设置隔离级别

6.1 查看隔离级别

查看当前的隔离级别

-- 方式一show variables like '%tx_isolation%';-- 方式二select @@tx_isolation;

查看会话的隔离级别

SELECT @@session.tx_isolation;

查看全局的隔离级别

SELECT @@global.tx_isolation;

以上的方式适用 MySQL 8.0 以前版本,在 MySQL 8.0.3 中将 tx_isolation 替换成了 transaction_isolation 变量替换了,  将上面的 tx_isolation

6.2 修改隔离级别

MySQL 提供了 SET TRANSACTION 语句,该语句可以改变单个会话或全局的事务隔离级别。语法格式如下:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

其中,SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围:

  • SESSION:表示修改的事务隔离级别将应用于当前 session(当前 cmd 窗口)内的所有事务;

  • GLOBAL:表示修改的事务隔离级别将应用于所有 session(全局)中的所有事务,且当前已经存在的 session 不受影响;

  • 如果省略 SESSION 和 GLOBAL,表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别,但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

6.3 适用 JDBC 修改当前连接的隔离级别

connection 中提供了 setTransactionIsolation() 来修改当前连接的隔离级别,使用方法如下:

connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);



作者:双鬼带单
链接:https://www.jianshu.com/p/45177253f730

文章分类
后端
版权声明:本站是系统测试站点,无实际运营。本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 XXXXXXo@163.com 举报,一经查实,本站将立刻删除。
相关推荐