yangl的博客

积少成多

0%

MySQL 事务

发表于 更新于 分类于 Valine：
本文字数： 4.6k 阅读时长 ≈ 4 分钟

为什么要事务

一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的：

  • 为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库在异常状态下仍能保持一致性的方法。

  • 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，保证彼此的操作互相干扰。

事务

​ 简单的说，事务就是一组原子性的 SQL 查询，这一组 SQL 要么全部执行成功，要么全部执行失败。

事务特性

  • 原子性：一个事务是不可分割的最小工作单元，整个事务要么全部成功，要么全部失败，不可能只执行中间的一部分操作。

  • 一致性：执行事务是使得数据库从一个一致性状态到另一个一致性状态，如果事务最终没有被提交，那么事务所做的修改也不会保存到数据库中。

  • 隔离性：通常来说，一个事务提交之前对其他事务是不可见的，但是这里所说的不可见需要考虑隔离级别，比如未提交读在提交前对于其他事务来说也是可见的，隔离级别，在下面会详细讲。

  • 持久性：事务一旦被提交，那么对数据库的修改会被永久的保存，即使数据库崩溃修改后的数据也不会丢失。

事务的隔离级别

隔离级别 脏读 不可重复读 幻读
未提交读 ( Read uncommited ) √ √ √
已提交读 ( Read commited ) × √ √
可重复读 ( Repeatable read ) × × √
串行化 ( Serializable ) × × ×

  • 多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。SQL 标准中定义了四种隔离级别，这里简单介绍一下这四种隔离级别。

    • 未提交读：未提交读的意思是，事务中的修改，即使没有提交，对其他事务也都是可见的，但是这样会出现脏读，一般情况下，通常都不会使用未提交读。
    • 提交读：提交读的意思是，一个事务所做的修改在提交之前对其他事务都是不可见的，这个级别也叫做“不可重复读”，因为执行两次相同的操作，可能会得到不同的结果。
    • 可重复读：可重复读解决了脏读的问题，这个级别保证了同一个事务多次读取同样记录的结果是一致的，但是这个隔离级别无法解决幻读的问题，所谓幻读就是说，当某个事务读取范围数据时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围数据时，会产生幻行。InnoDB 存储引擎通过 MVCC 解决了幻读的问题，可重复读是 MySQL 默认的事务隔离级别。
    • 可串行化：是最高的隔离级别，避免了前面说到的幻读问题。可串行化会给读取的每一行都加锁，所以可能导致大量超时和锁争用的问题，实际中很少使用这个隔离级别。

  • 事务不考虑隔离性可能会引发的问题

    • 脏读：指一个事务读取了另外一个事务未提交的数据
    • 不可重复读：指在一个事务内读取表中的某一行数据，多次读取结果不同。
    • **虚读(幻读)**：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

MySQL 数据库中操作事务的命令

  • 开启事务：start transaction
  • 提交事务：commit
  • 回滚事务：rollback
  • 默认情况下， MySQL 事务是自动提交（Autocommit）的，若果需要明确的 Commit 和 Rollback 来提交和回滚事务，那么就需要明确的事务控制命令来开始事务。
  • SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了 set autocommi t= 0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。
  • 如果只是对某些语句需要进行事务控制，则使用 start transaction 语句开始一个事务比较方便，这样事务结束之后可以自动回到自动提交的方式，如果希望所有的事务都不是自动提交的，那么通过修改 AUTOCOMMIT 来控制事务比较方便，这样不用在每个事务开始的时候再执行 start transaction。

事务隔离级别验证

准备表

1
2
3
4
5
6
7
-- 创建账户表
CREATE TABLE `t_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `money` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

验证事务隔离级别可能需要用到的 sql 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 设置 RU（未提交读）
set session transaction isolation level read uncommitted;
-- 设置 RC（已提交读）
set session transaction isolation level read committed;
-- 设置 RR（可重复读）
set session transaction isolation level repeatable read;

select @@session.tx_isolation;

start transaction;
commit;
rollback;

-- 查询与关闭事务自动提交
SHOW VARIABLES LIKE 'autocommit';
set @@autocommit=0;

select * from t_account;

INSERT INTO `t_account` VALUES ('1', 'A', '1000');
INSERT INTO `t_account` VALUES ('2', 'B', '1000');
INSERT INTO `t_account` VALUES ('3', 'C', '1000');

UPDATE t_account set money = money - 100 where id = 1;

未提交读（RU）

会话1 会话2
设置隔离级别 设置隔离级别
开启事务 开启事务
查询表数据（结果为空）
插入数据
查询表数据（有 “会话1” 的插入结果）
回滚事务
查询数据（结果为空）

1611658431742

小结：

​ 在RU模式下，一个事务可以读取到另一个未提交事务的数据，导致了脏读。如果另一个事务回滚了，就会造成数据的不一致性。RU是事务隔离级别中最低的。

读提交（RC）

会话1 会话2
设置隔离级别 设置隔离级别
开启事务 开启事务（先开启事务）
查询数据 查询数据
修改数据
查询数据（数据改变） 查询数据（数据无改变）
提交事务
查询数据（数据改变）

1611659480293

小结：

​ 在RC模式下，我们发现，当另一个事务没有提交数据修改时，当前事务时读不到修改后的数据的，这就避免了读未提交模式的脏读。但有一个问题，在当前事务中，两次select的数据不一样，这就存在了不可重复读的问题。

​ PS：RC隔离级别是Oracle数据库默认的隔离级别。

可重复读（RR）

会话1 会话2
设置隔离级别 设置隔离级别
开启事务 开启事务
查询数据 查询数据
修改数据
查询数据（数据无改变）
修改数据
提交事务
查询数据（数据无改变）
提交事务
查询数据（数据改变）

1611645479829

小结：

​ 在 RR 模式下，我们解决了不可重复读的问题，即在这种隔离级别下，一个事务中我们能够保证获取一样的数据（即使有其他事务正在改当前的数据行）。但是无法避免幻读，幻读简单的解释就是在数据有新增的时候，也无法保证两次得到的数据不一致但是不同数据库对不同的RR级别有不同的实现，有时候加上间隙锁来避免幻读。

InnoDB解决了幻读

在RR的隔离级别下，InnoDB使用MVCC和next-key locks(间隙锁)解决幻读。MVCC解决的是普通读（快照读）的幻读，间隙锁解决的是当前读情况下的幻读。

幻读是什么

​ 幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

以下是修改情况下的幻读问题： mvcc 默认解决了读情况下的幻读，但是未解决修改情况下的幻读。

幻读现象：事务2中没有id为4的数据，但是能修改成功。

1611662492067

幻读和不可重复读的区别

  • 不可重复读：多次读取一条记录，发现该记录中某些列值被修改过。
  • 幻读：只要是说多次读取一个范围内的记录（包括直接查询所有记录结果或者做聚合统计），发现结果不一致（一般指的是记录增多，记录的减少应该也算是幻读）。

避免幻读

MVCC

  • MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事物看到的数据都是一致的。

  • 根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB 的 MVCC 实现(隔离级别为可重复读)

InnoDB 的 MVCC 通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或者删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号作对比。下面详细介绍一下在可重复读隔离级别下（RR级别下），MVCC 的具体是如何操作的。

  • 系统版本号：一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。

  • 事务版本号：事务开始时的系统版本号。

  • 创建版本号：创建一行数据时，将当前系统版本号作为创建版本号赋值

  • 删除版本号：删除一行数据时，将当前系统版本号作为删除版本号赋值

  • SELECT

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

      • 只查找版本小于或等于事务的系统版本号的行。（这样可以确保事务读取的行，要么是在事务开始前已存在的，要么是事务自身插入或者修改过的）
      • 行的删除版本要么未定义，要么大于当前事务版本号。（这样可以确保事务读取到的行在事务开始之前未被删除）

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

  • INSERT

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

  • DELETE

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

  • UPDATE

    • InnoDB 新增一条记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

优点：

  1. 因为有了两个隐藏列来记录数据的状态，所以大多数读操作都可以不加锁
  2. 性能好，同时可以保证读取的数据是正确的

缺点：

  1. 需要额外的空间记录每行的状态
  2. 需要行状态的维护和检查

如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题，如果我们的项目中需要解决幻读的话也有两个办法：

  • 使用串行化读的隔离级别
  • MVCC + next-key locks ： next-key locks 由 record locks (索引加锁) 和 gap locks (间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

实际上很多的项目中是不会使用到上面的两种方法的，串行化读的性能太差，而且其实幻读很多时候是我们完全可以接受的。

参考：

https://juejin.cn/post/6844903827255066631

https://juejin.cn/post/6844903994188365831

https://juejin.cn/post/6844903799534911496

《高性能MySQL》第三版


----------- 本文结束啦感谢您阅读 -----------