关于mysql:MySQL事务学习笔记二-相识篇

48次阅读

共计 5906 个字符，预计需要花费 15 分钟才能阅读完成。

天色将晚，在我看着你的眼里色彩斑斓《娱乐天空》

序

在《MySQL 事务学习笔记 (一) 初遇篇》咱们曾经交代了如何开启事务、提交、回滚。然而还有一个小尾巴被脱漏了，就是如何设置事务的隔离级别。本篇咱们就来介绍 MySQL 中是如何设置隔离级别 ySQL 中是如何实现事务的 ACID 以及隔离级别。写作这篇文章的时候，我也在思考如何组织这些内容，是再组织一下本人看的材料上的内容，还是笔记式的，列举一下知识点。坦白的说我不是很喜爱列举知识点这种模式的，感觉没有一条线组织起来，我集体比拟喜爱的是像是树个别的常识组织构造，有一条骨干。所以本篇在介绍 MySQL 是实现事务实现的时候，会先从宏观上介绍其组织，局部知识点不会太具体，这样的形式能够让咱们先把握其骨干，不会迷失在细节中。

设置事务隔离级别 

select @@tx_isolation;

我的 MySQL 默认隔离级别为可反复读，SQL 事务的隔离级别:

  • 未提交读
  • 已提交读
  • 可反复读
  • 可串行化。

MySQL 反对在运行时和启动时设置隔离级别:

  • 启动时设置隔离级别:

    windows 下的配置文件取 my.ini

    Linux 下的配置文件取 my.cnf

    在配置文件中增加: transaction-isolation = 隔离级别

    隔离级别的候选值: READ COMMITTED，REPEATABLE READ，READ UNCOMMITTED，SERIALIZABLE

  • 运行时设置隔离级别

​ SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

​ LEVEL 的候选值: READ-COMMITTED，REPEATABLE READ，READ UNCOMMITTED，SERIALIZABLE

​ GLOBAL 的关键字在全局范畴内影响，在执行完上面语句之后:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

前面所有的会话的隔离级别都会变为可串行化；

​ 而 SESSION 关键字则是只在会话范畴内影响，如果事务还未提交则只对前面的事务无效。

​ 如果 GLOBAL 和 SESSION 都没有，则只对以后会话中下一个行将开启的事务无效，下一个事务执行结束，后序事务将复原到之前的隔离级别。该语句不能在曾经开启的事务中执行，会报错。

​ 上面咱们就来演示事务在不同的隔离级别会呈现的问题:

  • 事务的隔离级别为未提交读:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

​

​ 而后再关上一个窗口:

​

​ 产生了脏读

  • 事务的隔离级别为已提交读 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

再开一个会话:

​

呈现了不可反复读:

  • 隔离级别为可反复读 
SET GLOBAL TRANSACTION ISOLATION LEVEL  REPEATABLE READ;

下面咱们讲到 MySQL 在该级别下能够做到禁止幻读的，咱们这里来测试一下:

​ 这张图打错了，是 5 和 6 才对。

上面咱们来别离讲述 MySQL 是如何实现隔离级别、ACID 的。

redo 原子性 持久性

在《MySQL 优化学习手札 (一)》，咱们讲到 MySQL 以页为单位作为磁盘和内存的根本的交互单位，增删改查事实上都是在拜访页面 (读、写、创立新页面)，尽管咱们是拜访页面然而咱们拜访的并不是磁盘的页面，而是缓存池的页面，由工作线程定时将缓存池的更新页面刷新到磁盘上，那么问题来了，某个页面的数据被扭转，还没有来得及将此页面刷新到磁盘上，碰到了一些故障，MySQL 是如何保障持久性呢？所谓持久性就是指对一个曾经提交的事务，在事务提交后，即便零碎产生了解体，这个事务对数据库中所做的更改也不能失落。

简略而无脑的做法是在更新 buffer pool 的数据页之后，立即将该页刷新到磁盘上，然而刷新一个残缺的数据页太节约了，有的时候咱们可能只改变了某个页面中的某行数据的一个字段，这刷新到磁盘上破费的代价有点大。其次假如这个事务尽管只有一条语句，然而批改了很多页的数据，又不巧，这些页不相邻，这就很慢。

MySQL 的做法是存储批改数据的元信息，比方将 Student 的 id = 1 这一列的 name 改为张三, MySQL 就会存储这条数据在那个数据页的某某行数据的某某列改为张三，取增量，记录变动。这样在咱们事务提交后，咱们将扭转刷新到磁盘中，即便工作线程还没有来得及将缓存池的页刷新到磁盘上，零碎解体了，再重启的时候咱们依据这些记录的扭转再复原一下数据即可，记录扭转的数据在 MySQL 中被称为重做日志，特就是 redo log。与事务提交时将所有批改过的内存中页面刷新到磁盘中相比，间接将事务执行过程中产生的 redo 日志刷新到磁盘益处如下:

  • redo 日志占用的空间十分小
  • redo 日志按程序写入磁盘

那原子性呢，其实也是借助 redo 日志，在执行这些保障原子性的操作时必须以组的模式来记录 redo 日志，在进行数据恢复的时候，零碎中的某个组的日志要么全副复原，要么全副不复原。redo 日志也有本人的缓存区，也并不是间接刷新到磁盘上。

undo 日志 回滚

如果事务执行了一半，零碎断电了怎么办，又或者手动执行了回滚，咱们该如何回滚，答案是记录一下扭转，行将什么扭转成了什么 (这里的改变指的是 UPDATE INSERT，UPDATE)，MySQL 将这些记录扭转的数据称为 undo log，不同类型的 update log 不同。如果某个事务对某个表执行了增、删、改这样的操作，InnoDB 引擎为这个事务调配一个惟一的事务 id。下面咱们唠叨了，MySQL 以页为单位作为磁盘和内存的根本交互单位，页外面是行记录，每行会有多个暗藏列:

  • trx_id：每次一个事务对某条聚簇索引记录进行改变时，都会把该事务失去事务 id 赋值给 trx_id 暗藏列。
  • roll_pointer: 每次对某条聚簇索引记录进行改变时，都会把旧的版本写入到 undo 日志中，而后这个暗藏列就相当于一个指针，能够通过它来记录批改前的信息。

那可能有同学会问，那多条事务更新一条记录怎么办，MySQL 会让他们排队执行，能够了解为锁，咱们来试试看，两个事务同时更新一条记录怎么办?

过了一会就会呈现 Lock wait timeout exceeded; try restarting transaction.

每次对记录进行改变，都会记录一条 undo 日志，每条 undo 日志 也都会有 roll_pointer 属性，这些日志能够串起来成一条链表。版本链的头结点记录的是以后记录最新的值，每个版本还蕴含一个事务 ID。对于隔离级别是 READ UNCOMMITED 的事务来说，因为能够读取到未提交事务批改过的数据，所以间接读取最新版本就好。对于 READ COMMITED 和 REPEATABLE READ 隔离级别的事务来说，都必须保障读到曾经提交过的事务，也就是说如果以后事务未提交，是不能读取最新的版本记录的，那当初的问题就是该读取链表中的哪条记录，由此咱们就引出 READ VIEW 这个概念。

READ VIEW 的生成机会 MVCC

READ VIEW 有四个比拟重要的内容:

  • m_ids: 示意在生成 ReadView 时以后零碎中沉闷的读写事务的事务 ID 列表
  • min_trx_id: 示意在生成 ReadView 时过后零碎沉闷的读写事务中的最小事务 ID，也就是 m_ids 的最小值。
  • max_trx_id: 示意在生成 ReadView 时零碎应该调配给下一个事务的 ID。

  • creator_trx_id: 示意生成该 ReadView 的事务的事务 Id。

    如果拜访版本的 trx_id 与 READ VIEW 中的 creator_trx_id 表名以后事务再拜访它本人批改的记录，间接拜访链表最新的头结点即可。

    如果被拜访版本的 trx_id 小于 Read View 中的 min_trx_id 值，表明生成该版本的事务在以后事务生成 ReadView 之前曾经提交，所以该版本能够被以后事务拜访。

如果被拜访版本的 trx_id 大于等于或 Read View 中的 max_trx_id，表明生成版本的事务在以后事务生成 Read View 才开启，所以该版本不能够被以后事务拜访。

如果被拜访版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就须要判断 trx_id 的属性值在不在 m_ids 中，如果在，阐明创立 ReadView 时生成该版本的事务还是沉闷的，该版本不能够拜访，如果不在，阐明创立 ReadView 时生成该版本的事务曾经被提交。

当初拜访数据的形式就是在遍历数据对应的 undo 链表，依照步骤判断可见性，如果遍历到最初都不可见，那就是真的不可见。

在 MySQL 中，READ COMMITED 和 REPEATABLE READ 隔离级别的一个十分大的区别就是生成 ReadView 机会不同。事务在执行过程中，只有在第一次真正批改记录时 (INSERT DELETE UPDATE)，才会被调配一个独自的事务 id, 这个事务 id 是递增的。

上面咱们举一些例子来阐明在不同隔离级别下，查问时的过程。在故事的开始咱们仍然是筹备一个表:

CREATE TABLE `student`  (`id` int(11) NOT NULL COMMENT '惟一标识',
  `name` varchar(255) COMMENT '姓名',
  `number` varchar(255) COMMENT '学号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB

READ COMMITTED 每次查问都生成一个 Read View

当初有两个事务 ID 为 200 和 300 的正在执行，像上面这样:

# 事务 ID 为 200，id = 1 的 name 在这个事务开始之前为 王哈哈
BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
# 事务 ID 为 300
BEGIN;
# 做更新其余表的操作

这个时候 id 为 1 这行记录的版本链如下图所示:

当初另一个事务开始查问 id= 1 这条记录，执行 SELECT 语句即会生成一个 Read View，Read View 的值为 [200,300],min_trx_id 为 200，max_trx_id 为 301，creator_trx_id 为 200。而后开始遍历 undo 链表，最新的版本是王五，trx_id = 200, 在 min_ids 中不合乎可见性准则，拜访下一条记录，下一条记录的 trx_id 为 200，跳到下一个记录。王哈哈的 trx_id 小于 min_trx_id，而后将这行记录返回给用户。

REPEATABLE READ 第一次读的时候生成一个 Read View

还是下面的更新语句：

# 事务 ID 为 200，id = 1 的 name 在这个事务开始之前为 王哈哈
BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
# 事务 ID 为 300
BEGIN;
# 做更新其余表的操作

而后应用 REPEATABLE READ 的隔离级别来查问:

begin;
SELECT * FROM Student Where id = '1';

下面这个 SELECT 查问会生成一个 Read View: m_ids[200,300], min_trx_id=200,max_trx_id=301,creator_trx_id=0。

最新的版本的 trx=id 在 min_ids 中，该版本不可见，到下一条记录，李四的 trx_id 也为 200，也在 min_id 中，也不可见。王哈哈的版本 id 小于 read view 中的 min_trx_id, 表明这个记录在 Reada View 之前产生，返回该记录。而后提交一下事务 ID=200 的操作。

BEGIN;
update  student set name =  '李四' where id = 1;
update  student set name =  '王五' where id = 1;
COMMIT;

而后事务 ID 为 300 也对 id = 1 进行批改。

begin;
update  student set name =  '徐四' where id = 1;
update  student set name =  '赵一' where id = 1;

当初的版本链就如下图所示:

查问 id = 1 的记录:

begin;
SELECT * FROM Student Where id = '1';

之前曾经产生过 read view 了，复用下面的 read view，而后以后记录的事务 id 在 min_ids[200,300] 中，该记录不可见, 跳到下一条记录中，下一条的 trx_id 为 300，也在 min_ids 中，不可见，而后跳到下一条记录，下条记录的 trx_id 也在 min_ids 中，不可见。直到“王哈哈”，这也就是可反复读的含意。即便事务 ID 为 300 的事务提交了，其余事务读到了也会是“王哈哈”。对于这条记录的事务全副提交之后，再次查问该记录会从新再产生 Read View。这也就是 MVCC(Multi-Version Concurrency Control 多版本并发访问控制)，在 READ COMMITTED、REPEATABLE READ 隔离级别，防止脏读、不可反复读所采取的策略。READ COMMITE 每次查问都会生成一个 Read View。而 REPEATABLE READ 则是在第一次进行相干的记录查问的时候生成 Read View，之后查问复用这个 Read View，被这些事务中查问操作复用的 Read View，在提交之后。再查问对应的记录的时候，再从新产生。

总结一下

MySQL 下借助 undo、redo 实现原子性、持久性、已提交读，可反复读。redo 记录记录产生了什么扭转，undo 用于回滚。为了反对 MVCC，对应的记录删除之后删掉不会立马删除而是会打上标记。如同是在刚毕业的时候就接触到了 MVCC，那个时候感觉这个很是高端和简单，明天在写这篇文章的时候，还是先从宏观动手，没有介绍之前打算 undo、redo 日志的格局相干的细节，依据我的教训，介绍这些格局会很让人头晕，迷失在细节之中，其实初衷只是为了理解 MySQL 对 ACID 和事务的实现。所以本文只介绍了必要的内容，到前面的文章如果必须援用这些日志的具体介绍的时候会再介绍一遍。

参考资料

  • MySQL 事务的四种隔离级别 https://blog.51cto.com/moerji…
  • MySQL 是怎么运行的：从根儿上了解 MySQL https://juejin.cn/book/684473…
  • [Mysql]——通过例子了解事务的 4 种隔离级别 https://www.cnblogs.com/snsdz…

正文完
mysql
发表至： mysql
2022-03-12
 0
关于mysql:从一条更新SQL的执行过程窥探InnoDB之REDOLOG
排查一次sql-order-by未使用到索引utf8mb4unicode
关于mysql:SAP-HANA-错误消息-SYSXSA-authentication-failed-SQLSTATE-28000
关于mysql:MySQL-Undo-Log-撤销日志