【MySQL】MySQL 事务

发表于2021-04-02更新于2022-02-25字数统计4.3k阅读时长24分阅读次数

image-20210913132511709

事务原则

TCL: Transaction Control Language 事务控制语言。

事务（Transaction）：事务由单独单元的一个或一组sql语句组成，在这个单元中，每个MySQL语句是相互依赖的，这个执行单元 要么全部执行，要么全部不执行

  • SQL语句1正在执行：A给B转账200 A：1000 ——> 200 B：200
  • SQL语句2正在执行：B收到A的钱 B：800 ——> B：400

将一组SQL放在一个批次中去执行。上述两条语句组成一组来执行，要么都成功，要么都失败，否则钱会凭空消失。

事务原则：ACID原则——原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)；脏读，幻读，不可重复读

1. 原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都执行，要么都不执行，没有中间状态。对于事务在执行中发生错误，所有的操作都会被回滚，整个事务就像从没被执行过一样。

2. 一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另一个一致性状态。保证数据库一致性是指当事务完成时，必须使所有数据都具有一致的状态。（例如A给B转账前后，数据库中二者余额之和相等，转账前为一个一致性状态，转账后也为一个一致性状态）。

3. 隔离性（Isolation）

隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他数据是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

其中，原子性和持久性靠undo和redo日志来实现，隔离性通过线程的控制来实现

事务隔离级别

参考链接：https://cloud.tencent.com/developer/article/1450773

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题（按照严重性从高到低排序）：

1. 脏写（修改未提交数据）

指一个事务修改了另一个事务未提交的数据。例如，两个事务T1和T2，T1修改了已经被T2更新但是还没有被提交的字段，并且提交了自己的事务，但之后，若T2回滚，则T1刚才修改的内容就是临时且无效的。

2. 脏读（读取未提交数据）：

指一个事务读取了另一个事务未提交的数据。例如，两个事务T1和T2，T1读取了已经被T2更新但是还没有被提交的字段，之后，若T2回滚，T1读取到的内容就是临时且无效的。

3. 不可重复读（前后多次读取，数据内容不一致）：

指在一个事务内读取表中的某一行数据，过段时间，该字段数据被另一事务修改，此时第一个事务再读时读取结果不同。例如，两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再此读取同一字段时，值就不同了。

4. 幻读（前后多次读取，数据总量不一致）：

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据。（类似班级里刚才看还是一个人，再看变成两个人，就像产生了幻觉）

不可重复读和幻读的区别:

(1) 不可重复读是读取了其他事务更改的数据，针对UPDATE操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

(2) 幻读是读取了其他事务新增的数据，针对INSERT操作

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

MySQL的JDBC中，默认开启事务，此时每一句sql语句都会在一个单独的事务中执行，例如两次查询语句都会在不同的事务中执行，执行完该语句都会立刻提交。

MySQL支持4种事务隔离级别，默认级别为 REPEATABLE READ

  • READ UNCOMMITTED（读未提交数据）：允许事务读取未被其他事务提交的变更。脏读、不可重复读和幻读都可能出现
  • READ COMMITTED（读已提交数据）：只允许事务读取已经被其他事务提交的变更。可以避免脏读，但不可重复读和幻读仍然可能出现
  • REPEATABLE READ（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。此时即使其他事务修改某字段并COMMIT，本事务查询时仍是原先值。可以避免脏读和不可重复读，但幻读仍然可能出现，即其他事务若插入了新的行，本事务查询时也会多出这些行，导致看起来像幻觉一样，每次读取的数据总量不同（注意，和一些其他数据库实现不同的是，可以简单认为 MySQL 在可重复读级别不会出现幻象读，因为其提供了 MVCC 与间隙锁的机制）
  • SERIALIZABLE（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作。所有并发问题都可以避免，但性能十分低下。在某个事务读取时，其他事务阻塞，无法对该表进行操作。该级别下无法进行并发。本质上是添加了行级锁。并发事务之间是串行化的，通常意味着读取需要获取共享读锁，更新需要获取排他写锁，如果 SQL 使用 WHERE 语句，还会获取区间锁（MySQL 以间隙锁形式实现，可重复读级别中默认也会使用），这是最高的隔离级别。

四种隔离级别都解决了脏写问题，因为脏写问题是最严重的，会导致自己修改的数据被别人覆盖，因此必须解决

查看隔离级别：

1
2
SELECT @@TX_ISOLATION           --（8.0以前） 
SELECT @@TRANSACTION_ISOLATION  --（8.0以后）

设置当前MySQL连接的隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

设置全局MySQL连接的隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

undo 和 redo 日志

http://www.zhdba.com/mysqlops/2012/04/06/innodb-log1/

在数据库系统中，既有存放数据的文件，也有存放日志的文件。在内存中既有日志缓存 log buffer，也有磁盘文件 log file。MySQL中的日志文件，有这么两种与事务有关：undo日志与redo日志。

undo 日志

https://www.bilibili.com/video/BV1n7411R7WQ?p=6&spm_id_from=333.1007.top_right_bar_window_history.content.click

undo日志的原理：为了满足事务的原子性，在操作任何数据之前，首先将数据备份到 undo log。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK 语句，系统可以利用 undo log 中的备份将数据恢复到事务开始之前的状态。并且数据库写入数据到磁盘之前，会把数据先缓存在内存中，事务提交时才会写入磁盘中。

用undo日志实现原子性的简化过程

假设有A、B两个数据，值分别为1,2。

  • A. 事务开始.
  • B. 记录A=1到 undo log.
  • C. 修改A=3.
  • D. 记录B=2到 undo log.
  • E. 修改B=4.
  • F. 将 undo log 写到磁盘。
  • G. 将数据写到磁盘。
  • H. 事务提交

1. 如何保证持久性？

事务提交前，会把修改后的数据保存到磁盘，也就是说只要事务提交了，数据肯定持久化了。

2. 如何保证原子性？

  • 每次对数据库修改，都会把修改前数据记录在 undo log，那么需要回滚时，可以读取 undo log，恢复数据。
  • 若系统在G和H之间崩溃，此时事务并未提交，需要回滚。而 undo log 已经被持久化，可以根据 undo log 来恢复数据
  • 若系统在G之前崩溃，此时数据并未持久化到硬盘，依然保持在事务之前的状态

缺陷：每个事务提交前将数据和 undo log 写入磁盘，这样会导致大量的磁盘IO，因此性能很低。

如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即 redo log。

redo日志

和 undo log 相反，redo log 记录的是新数据的备份。在事务提交前，只要将 redo log 持久化即可，不需要将数据库中的数据持久化，减少了IO的次数。

undo + redo 事务的简化过程

假设有A、B两个数据，值分别为1,2

  • A. 事务开始.
  • B. 记录 A=1 到 undo log buffer.
  • C. 修改 A=3.
  • D. 记录 A=3 到 redo log buffer.
  • E. 记录 B=2 到 undo log buffer
  • F. 修改 B=4.
  • G. 记录 B=4 到 redo log buffer.
  • H. 将 undo log 写入磁盘
  • I. 将 redo log 写入磁盘
  • J. 事务提交

1. 如何保证原子性？

如果在事务提交前故障，通过 undo log 日志恢复数据。如果 undo log 都还没写入，那么数据就尚未持久化，无需回滚

2. 如何保证持久化？

注意，这里并没有出现数据的持久化。因为数据已经写入 redo log，而 redo log 持久化到了硬盘，因此只要到了步骤I以后，事务是可以提交的。

3. 内存中的数据库数据何时持久化到磁盘？

因为 redo log 已经持久化，因此数据库数据写入磁盘与否影响不大，不过为了避免出现脏数据（内存中与磁盘不一致），事务提交后也会将内存数据刷入磁盘（也可以按照固设定的频率刷新内存数据到磁盘中）。

4. redo log 何时写入磁盘？

redo log 会在事务提交之前，或者 redo log buffer满了的时候写入磁盘

这里存在两个问题：

问题1：之前是写 undo 和数据库数据到硬盘，现在是写 undo 和 redo 到磁盘，似乎没有减少IO次数

  • 数据库数据写入是随机IO，性能很差
  • redo log 在初始化时会开辟一段连续的空间，写入是顺序IO，性能很好
  • 实际上 undo log 并不是直接写入磁盘，而是先写入到 redo log buffer 中，当 redo log 持久化时，undo log 就同时持久化到硬盘了。

因此事务提交前，只需要对 redo log 持久化即可。另外，redo log 并不是写入一次就持久化一次， redo log 在内存中也有自己的缓冲池：redo log buffer。每次写 redo log 都是写入到 buffer，在提交时一次性持久化到磁盘，减少IO次数。

问题2：redo log 数据是写入内存buffer中，当buffer满或者事务提交时，将 buffer 数据写入磁盘。redo log 中记录的数据，有可能包含尚未提交事务，如果此时数据库崩溃，那么如何完成数据恢复？

数据恢复有两种策略：

  • 恢复时，只重做已经提交了的事务
  • 恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过 undo log 回滚那些未提交的事务

Innodb 引擎采用的是第二种方案，因此 undo log 要在 redo log 前持久化

总结：

  • undo log 记录更新前的数据，用于保证事务原子性
  • redo log 记录更新后的数据，用于保证事务的持久性
  • redo log 有自己的内存buffer，先写入到 buffer，事务提交时写入磁盘
  • redo log 持久化之后，意味着事务是可提交的

悲观锁和乐观锁

参考链接：https://cloud.tencent.com/developer/article/1450773

悲观锁

image-20210912202607757

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机 制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁

image-20210912202630691

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。

何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。

此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。Redis就是利用这种check-and-set机制实现事务的。

执行事务顺序

  1. 关闭自动提交：SET autocommit = 0
  2. 开启一个事务：START TRANSACTION
    • 提交（执行成功）：COMMIT ，获得新的数据库
    • 回滚（执行失败）：ROLLBACK，返回原先数据库
  3. 开启自动提交：SET autocommit = 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- MySQL是默认开启事务自动提交的
SET autocommit = 0;   -- 关闭
SET autocommit = 1;	  -- 开启（默认）

-- 手动处理事务
SET autocommit = 0;   -- 关闭自动提交

-- 事务开启
START TRANSACTION;    -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT xx;
INSERT xx;

-- 提交：持久化（成功）
COMMIT;

-- 回滚：回到原来的样子（失败）
ROLLBACK;

-- 事务结束
SET autocommit = 1; -- 开启自动提交

SAVEPOINT 保存点名称a;              -- 设置一个事务的保存点
ROLLBACK TO 保存点名称a;            -- 回滚到保存点 
RELEASE SAVEPOINT 保存点名称;       -- 撤销保存点

测试案例：

1
2
3
4
5
6
7
8
9
10
11
12
-- 模拟转账
SET autocommit = 0;  -- 关闭自动提交

START TRANSACTION;    -- 开启一个事务

UPDATE `account` SET `money`=`money` - 500 WHERE `name` = 'zhangsan';
UPDATE `account` SET `money`=`money` + 500 WHERE `name` = 'lisi';

COMMIT;    -- 提交事务，执行后数据库内容才会修改
ROLLBACK;  -- 回滚，数据库内容不会修改

SET autocommit = 1;  -- 恢复默认值

DELETE 和 TRUNCATE 在事务中的区别

  • DELETE在事务提交前使用，若回滚，则数据会恢复
  • TRUNCATE事务提交前使用，若回滚，则数据依旧不会恢复