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

2021-03-19 11:43:33 浏览数 (1)

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 的MVCC5. 混合存储引擎下的事务问题6. 查看和设置隔离级别6.1 查看隔离级别6.2 修改隔离级别6.3 适用 JDBC 修改当前连接的隔离级别

1. 数据库事务

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

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

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

这里主要说的是 隔离性

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

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

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

代码语言：javascript复制
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 元，这时就发生了脏读

时间、事务

事务一

事务二

1

begin

2

begin

3

update movie set price = 55 where id = 1;

4

select price from movie where id = 1;如果这里读到了 55 则出现了脏读

5

commit;

6

rollback;

2.2 不可重复读

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

时间、事务

事务一

事务二

1

begin

2

begin

4

select price from movie where id = 1; 此时为 70

3

update movie set price = 55 where id = 1;

4

select price from movie where id = 1;如果这里读到了 55 则出现不可重复读

5

commit;

6

rollback;

2.3 幻读

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

时间、事务

事务一

事务二

1

begin

2

begin

4

select name from movie where id <= 70; 此时应该查询到 2 条数据为 1. 唐探3， 2. 你好，李焕英

3

INSERT INTO movie (id, name, price) VALUES (3, '刺杀小说家', 50);

4

select name from movie where id <= 70; 此时查询到 3 条数据为 1. 唐探3， 2. 你好，李焕英 3. 刺杀小说家

5

commit;

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 执行完成后才能执行

时间、事务

事务一

事务二

1

begin

2

begin

3

update movie set price = 55 where id = 1;

4

select price from movie where id = 1;

5

commit;

6

rollback;

5

第 4 步中的 sql 要等到这里才能执行

6

第 5 步中的 sql 要等到这里才能执行

3.2 可重复读 （REPEATABLE READ）

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

时间、事务

事务一

事务二

1

begin

2

begin

4

select price from movie where id = 1; 此时为 70

3

update movie set price = 55 where id = 1;

4

select price from movie where id = 1; 此时读到还是 70

5

commit;

6

commit;

7

select 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 引擎(不支持事务) 假设以下操作在一个事务中：

代码语言：javascript复制
insert table_a ...    # 1
insert table_b ...    # 2
update table_a ...    # 3

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

6. 查看和设置隔离级别

6.1 查看隔离级别

查看当前的隔离级别

代码语言：javascript复制
-- 方式一
show variables like '%tx_isolation%';

-- 方式二
select @@tx_isolation;

查看会话的隔离级别

代码语言：javascript复制
SELECT @@session.tx_isolation;

查看全局的隔离级别

代码语言：javascript复制
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() 来修改当前连接的隔离级别，使用方法如下：

代码语言：javascript复制
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
mvc mvcc 云数据库SQLServer 数据库 sql

0 人点赞