MySQL事务

最新推荐文章于 2024-05-21 09:46:31 发布

风舞红叶

最新推荐文章于 2024-05-21 09:46:31 发布

阅读量800

点赞数 11

分类专栏： mysql 文章标签： mysql oracle 数据库

本文链接：https://blog.csdn.net/weixin_46488959/article/details/138637340

版权

mysql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

事务

ACID(原子性、一致性、隔离性、持久性)

隔离性

隔离级别	说明
read uncommitted	一个事务还没提交时，它做的变更就不能被别的事务看到
read committed	一个事务提交之后。它做的变更才会被其他事务看到
repeatable read	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据时一致的
serializable	对于同一行记录，”写“会加写锁，”读“会加读锁。当出现读写锁冲突的时候，后访问的事务必须等待前一个事务执行完成，才能继续执行

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
“可重复读” 隔离级别下，这个视图是在事务启动时创建，整个事务存在期间都用这个视图。
“读提交” 隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
“读未提交“ 隔离级别下直接返回记录上的最新值，没有视图概念。
“串行化” 隔离级别直接用加锁的方式来避免并行访问

显示启动事务；begin 或 start transaction。提交 commit，回滚 rollback
查询长事务

-- 查询持续时间超过60s的事务
select *
from information_schema.innodb_trx where time_to_sec(timediff(now(),trx_started))>60;

如何避免长事务对业务的影响

确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
确认是否有不必要的只读事务
监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill
在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题

可重复读的实现

可重复读的核心就是一致性读（consistent read）；
而事务更新数据的时候，只能用当前读。
如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；

在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

系统会判断，当没有事务再需要用到这些回滚日志时(当系统里没有比这个回滚日志更早的 read-view 的时候)，回滚日志会被删除

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。

如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

事务的启动时机
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动;一致性视图是在第执行第一个快照读语句时创建的

transaction with consistent snapshot 马上启动一个事务,一致性视图是在执行 start transaction with consistent snapshot 时创建的

InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

“快照”在 MVCC 里是怎么工作的

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它

数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id

图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被 transaction id 为 25 的事务更新的，因此它的 row trx_id 也是 25。

图 2 中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。

当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

数据版本可见性规则

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

如果落在黄色部分，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

案例

CREATE TABLE `t`
(
    `id` int(11) NOT NULL,
    `k`  int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k)
values (1, 1),
       (2, 2);

事务的执行流程

假设：

事务 A 开始前，系统里面只有一个活跃事务 ID 是 99；
事务 A、B、C 的版本号分别是 100、101、102，且当前系统里只有这四个事务；
三个事务开始前，(1,1）这一行数据的 row trx_id 是 90。
这样，事务 A 的视图数组就是 [99,100], 事务 B 的视图数组是 [99,100,101], 事务 C 的视图数组是 [99,100,101,102]。

分析下事务 A 的语句返回的结果

第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。

第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。

在事务 A 查询的时候，其实事务 B 还没有提交，但是它生成的 (1,3) 这个版本已经变成当前版本了。但这个版本对事务 A 必须是不可见的，否则就变成脏读了。

事务 A 要来读数据了，它的视图数组是 [99,100]。当然了，读数据都是从当前版本读起的。所以，事务 A 查询语句的读数据流程是这样的：

找到 (1,3) 的时候，判断出 row trx_id=101，比高水位大，处于红色区域，不可见；

接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；

再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

一个数据版本，对于一个事务视图总结：

1、版本未提交，不可见；
2、版本已提交，但是是在视图创建后提交的，不可见；
3、版本已提交，而且是在视图创建前提交的，可见；
4、自己的更新总是见。

分析下事务 B 的语句返回的结果

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）

select 语句如果加锁，也是当前读，否则为“快照读”（一致性读）
```
-- 读锁（S 锁，共享锁）
select k from t where id=1 lock in share mode;
-- 写锁（X 锁，排他锁）
select k from t where id=1 for update;
```
如果事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1

在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101

所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。

在更新的时候，当前读拿到的数据是 (1,2)，更新后生成了新版本的数据 (1,3)，这个新版本的 row trx_id 是 101
所以，在执行事务 B 查询语句的时候，一看自己的版本号是 101，最新数据的版本号也是 101，是自己的更新，可以直接使用，所以查询得到的 k 的值是 3。