轻松玩转MySQL之事务篇

最新推荐文章于 2023-03-26 21:39:47 发布

路人1024

最新推荐文章于 2023-03-26 21:39:47 发布

阅读量292

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/WCSDN402/article/details/105080114

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

事务简介

在MySQL中的事务是由存储引擎实现的，而且支持事务的存储引擎不多，我们主要说一下InnoDB存储引擎中的事务。
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。事务用来管理 DDL、DML、DCL 操作，比如insert,update,delete语句，

默认是自动提交的。

事务四大特性(ACID)

Atomicity(原子性)
构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行。
Consistency(一致性)
数据库在事务执行前后状态都必须是稳定的或者是一致的。
Isolation(隔离性)
事务之间不会相互影响。四种隔离级别为RU(读未提交)、RC(读已提交)、RR(可重复读)、SERIALIZABLE (串行化)
由锁机制和MVCC机制来实现的
MVCC(多版本并发控制):优化读写性能(读不加锁、读写不冲突)
Durability(持久性)
事务执行成功后必须全部写入磁盘。事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失

事务的隔离性由多版本控制机制和锁实现，而原子性、一致性和持久性通过InnoDB的redo log、undo log和Force Log at Commit机制来实现。redo log用于在崩溃时恢复数据，undo log用于对事务的影响进行撤销，也可以用于多版本控制。而Force Log at Commit机制保证事务提交后redo log日志都已经持久化。

如何开启一个事务

BEGIN 或 START TRANSACTION
显式地开启一个事务
COMMIT 或 COMMIT WORK
COMMIT会提交事务，并使已对数据库进行的，所有修改称为永久性的;
ROLLBACK 或 ROLLBACK WORK
回滚会结束用户的事务，并撤销正在进行的所有未提交的修改;

InnoDB架构

在这里插入图片描述

InnoDB存储引擎由内存池，后台线程和磁盘文件三大部分组成。

InnoDB内存结构

Buffer Pool缓冲池

处理数据，里面有数据页和索引页
Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，做增删改时缓存里的数据页和磁盘里的数据页不一致，该数据页为脏页

插入缓冲(Insert Buffer)

复杂 : 主键排序索引树状插入算法。。。

自适应哈希索引(Adaptive Hash Index)

hash结构 k-v InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率

锁信息(lock info)

行锁、表锁。。。

数据字典信息(Data Dictionary)

元数据信息包括表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容

redo log Buffer重做日志缓冲

重做日志: Redo Log 如果要存储数据则先存储数据的日志，一旦内存崩了则可以从日志找。
重做日志保证了数据的可靠性，InnoDB采用了Write Ahead Log(预写日志)策略，即当事务提交时，先写重做日志，然后再择时将脏页写入磁盘。如果发生宕机导致数据丢失，就通过重做日志进行数据恢复。
例如 insert into xxxx commit的时候，当Redo Log File写入成功认为commit成功
Redo Log文件名：ib_logfile0，ib_logfile1 默认为8MB。可通过配置参数innodb_log_buffer_size控制

redo log日志的落盘机制

Force Log at Commit机制实现事务的持久性

即当事务提交时，必须先将该事务的所有日志写入到redo log日志文件进行持久化，然后事务的提交操作完成才算完成。为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作(操作系统)，将缓冲文件从文件系统缓存中真正写入磁盘

通过innodb_flush_log_at_trx_commit参数控制重做日志刷新到磁盘的策略，该参数默认值为1
在这里插入图片描述

0表示事务提交时不进行写入重做日志操作，该操作只在主线程中完成
1表示事务提交必须进行一次fsync操作
2表示提交时写入重做日志，但是只写入文件系统缓存，不进行fsync操作

由此可见，设置为0时，性能最高，但是丧失了事务的一致性

Double Write双写

Double Write带给InnoDB存储引擎的是数据页的可靠性
在这里插入图片描述

如上图所示，Double Write由两部分组成，一部分是内存中的double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。
在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buffer区域，之后通过 double write buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。
在完成double write页的写入后，再将double wirite buffer中的页写入各个表空间文件中。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。
在这里插入图片描述

CheckPoint: 检查点

检查点，表示脏页写入到磁盘的时机，检查点就意味着脏数据的写入。

checkpoint的目的

1、缩短数据库的恢复时间
2、buffer pool空间不够用时，将脏页刷新到磁盘
3、redo log不可用时，刷新脏页

检查点分类

sharp checkpoint:
完全检查点，数据库正常关闭时，会触发把所有的脏页都写入到磁盘上

fuzzy checkpoint
正常使用是模糊检查点，部分页写入磁盘。master thread checkpoint、flush_lru_list checkpoint、async/sync flush checkpoint、dirty page too much checkpoint。

master thread checkpoint

以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的，
flush_lru_list checkpoint

读取lru (Least Recently Used) list，找到脏页，写入磁盘。最近最少使用
async/sync flush checkpoint

log file快满了，会批量的触发数据页回写，这个事件触发的时候又分
为异步和同步，不可被覆盖的redo log占log file的比值:75%—>异步、90%—>同步。
dirty page too much checkpoint :

默认是脏页占比75%的时候，就会触发刷盘，将脏页写入磁盘

InnoDB磁盘文件

系统表空间和用户表空间

在这里插入图片描述

重做日志文件和归档文件

在这里插入图片描述

UndoLog

数据库崩溃重启后需要从redo log中把未落盘的脏页数据恢复出来，重新写入磁盘，保证用户的数据不丢失。当然，在崩溃恢复中还需要回滚没有提交的事务。由于回滚操作需要undo log的支持，undo log的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo恢复数据，然后做undo回滚。

在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

数据和回滚日志的逻辑存储结构

在这里插入图片描述

undo log的存储不同于redo log，它存放在数据库内部的一个特殊的段(segment)中，这个段称为回滚段。
回滚段位于共享表空间中。undo段中以undo page为更小的组织单位。
undo page和存储数据库数据和索引的页类似。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undo log(也看成数据库数据)的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。如上图所示，表空间中有回滚段和叶节点段和非叶节点段，而三者都有对应的页结构。

我们再来总结一下数据库事务的整个流程，如下图所示。

在这里插入图片描述

事务进行过程中，每次sql语句执行，都会记录undo log和redo log，然后更新数据形成脏页，然后 redo log按照时间或者空间等条件进行落盘，undo log和脏页按照checkpoint进行落盘，落盘后相应的 redo log就可以删除了。此时，事务还未COMMIT，如果发生崩溃，则首先检查checkpoint记录，使用相应的redo log进行数据和undo log的恢复，然后查看undo log的状态发现事务尚未提交，然后就使用 undo log进行事务回滚。事务执行COMMIT操作时，会将本事务相关的所有redo log都进行落盘，只有所有redo log落盘成功，才算COMMIT成功。然后内存中的数据脏页继续按照checkpoint进行落盘。如果此时发生了崩溃，则只使用redo log恢复数据。

隔离性

事务并发问题

在事务的并发操作中可能会出现一些问题:

丢失更新

两个事务针对同一数据都发生修改操作时，会存在丢失更新问题
脏读

一个事务读取到另一个事务未提交的数据。
不可重复读

一个事务因读取到另一个事务已提交的update或者delete数据。导致对同一条记录读取两次以上的结果不一致。
幻读

一个事务因读取到另一个事务已提交的insert数据。导致一致。

事务隔离级别

四种隔离级别(SQL92标准):

Read uncommitted (读未提交)

最低级别，任何情况都无法保证。
Read committed (RC，读已提交)

可避免脏读的发生。
Repeatable read (RR，可重复读)

可避免脏读、不可重复读的发生。
InnoDB的RR还可以解决幻读，主要原因是Next-Key锁，只有RR才能使用Next- Key锁
Serializable (串行化)

可避免脏读、不可重复读、幻读的发生由MVCC降级为Locking-Base CC

在这里插入图片描述

--创建账户表并初始化数据
create table tacount(id int , aname varchar(100),acount int , primary key(id)); 
alter table tacount add index idx_name(aname);
insert into tacount values(1,'a',1000);
insert into tacount values(2,'b',1000);
--设置隔离级读未提交(read-uncommitted)
set session transaction isolation level read uncommitted;
--session 1
start transaction ; 
select * from tacount where aname='a';
--session 2
start transaction; 
update tacount set acount=1100 where aname='b';
--session 1
select * from tacount where aname='b';

在这里插入图片描述

--设置隔离级别为串行化(serializable) 死锁演示
set session transaction isolation level serializable;
--session 1
start transaction;
select * from tacount where aname='a';
--session 2
start transaction ; 
update tacount set acount=900 where aname='b'; 
-- session 1
select * from tacount where aname='b';
-- session 2
update tacount set acount=1100 where aname='a';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

使用MVCC机制可以解决这个问题。查询总额事务先读取了用户A的账户存款，然后转账事务会修改用户A和用户B账户存款，查询总额事务读取用户B存款时不会读取转账事务修改后的数据，而是读取本事务开始时的数据副本(在REPEATABLE READ隔离等级下)。
在这里插入图片描述

-- 显示当前隔离级别为 REPEATABLE-READ MySQL默认隔离级别
mysql> select @@tx_isolation;
-- session 1
mysql> start transaction ; 
select * from tacount where aname='a'; 
+----+-------+--------+
| id | aname | acount | 
+----+-------+--------+ 
|1|a |1000| 
+----+-------+--------+ 
-- session 2
start transaction; 
update tacount set acount=1100 where aname='a'; 
-- session 1
select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount | 
+----+-------+--------+ 
|1|a |1000| 
+----+-------+--------+ 
-- session 2 提交事务 
commit;
-- session 1 显示在session 1 事务开始时的数据 
select * from tacount where aname='a'; 
+----+-------+--------+
| id | aname | acount | 
+----+-------+--------+
|1|a |1000| 
+----+-------+--------+
-- 设置事务隔离级别为REPEATABLE-COMMITTED 读已提交
-- session 1
set session transaction isolation level read committed;
start transaction ; select * from tacount where aname='a'; 
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
|1|a |1000|
+----+-------+--------+
-- session 2
mysql> set session transaction isolation level read committed;
mysql> start transaction; update tacount set acount=1100 where aname='a'; 
-- session 1
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
|1|a |1000|
+----+-------+--------+
-- session 2 提交事务
mysql> commit;
-- session 1 显示最新事务提交后的数据
mysql> select * from tacount where aname='a';
+----+-------+--------+
| id | aname | acount |
+----+-------+--------+
|1|a |1100|
+----+-------+--------+

InnoDB的MVCC实现

当前读和快照读

在MVCC并发控制中，读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
当前读，读取的是记录的最新版本，并且当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读?哪些操作又是当前读呢?
以MySQL InnoDB为例:
快照读:简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析) 不加读锁读历史版本
当前读:特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。加行写锁读当前版本

一致性非锁定读

一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式。
如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地， InnoDB会去读取行的一个最新可见快照。
在这里插入图片描述

在这里插入图片描述

如上图所示，当会话B提交事务后，会话A再次运行 SELECT * FROM test WHERE id = 1 的SQL语句时，两个事务隔离级别下得到的结果就不一样了。

MVCC 在mysql 中的实现依赖的是 undo log 与 read view 。

Undo Log

InnoDB行记录有三个隐藏字段:分别对应该行的rowid、事务号db_trx_id和回滚指针db_roll_ptr，其中db_trx_id表示最近修改的事务的id，db_roll_ptr指向回滚段中的undo log。
根据行为的不同，undo log分为两种:insert undo log和update undo log
insert undo log:
是在 insert 操作中产生的 undo log。因为 insert 操作的记录只对事务本身可见， rollback 在该事务中直接删除，不需要进行 purge 操作
update undo log :
rollback MVCC机制会找他的历史版本进行恢复
是 update 或 delete 操作中产生的 undo log，因为会对已经存在的记录产生影响，为了提供 MVCC机制，因此 update undo log 不能在事务提交时就进行删除，而是将事务提交时放到入 history list 上，等待 purge 线程进行最后的删除操作。

如下图所示(初始状态):
在这里插入图片描述

当事务2使用UPDATE语句修改该行数据时，会首先使用排他锁锁定改行，将该行当前的值复制到undo log中，然后再真正地修改当前行的值，最后填写事务ID，使用回滚指针指向undo log中修改前的行。
如下图所示(第一次修改):

在这里插入图片描述

当事务3进行修改与事务2的处理过程类似，如下图所示(第二次修改):

在这里插入图片描述

事务链表

MySQL中的事务在开始到提交这段过程中，都会被保存到一个叫trx_sys的事务链表中，这是一个基本的链表结构:
ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;
事务链表中保存的都是还未提交的事务，事务一旦被提交，则会被从事务链表中摘除。
RR隔离级别下，在每个事务开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(read
view)
RC隔离级别下，在每个语句开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)
show engine innodb status ,就能够看到事务列表。

ReadView

当前事务(读)能读哪个历史版本?
Read View是事务开启时当前所有事务的一个集合，这个类中存储了当前Read View中最大事务ID及最
小事务ID。
这就是当前活跃的事务列表。如下所示，
ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;
ct-trx 表示当前事务的id，对应上面的read_view数据结构如下，
read_view->creator_trx_id = ct-trx;
read_view->up_limit_id = trx3; 低水位
read_view->low_limit_id = trx11; 高水位
read_view->trx_ids = [trx11, trx9, trx6, trx5, trx3];

low_limit_id是“高水位”，即当时活跃事务的最大id，如果读到row的db_trx_id>=low_limit_id，说明这些id在此之前的数据都没有提交，如注释中的描述，这些数据都不可见。

if (trx_id >= view->low_limit_id) { return(FALSE);
}
注:readview 部分源码

up_limit_id是“低水位”，即当时活跃事务列表的最小事务id，如果row的db_trx_id<up_limit_id,说明这些数据在事务创建的id时都已经提交，如注释中的描述，这些数据均可见。

if (trx_id < view->up_limit_id) { return(TRUE);
}

row的db_trx_id在low_limit_id和up_limit_id之间，则查找该记录的db_trx_id是否在自己事务的 read_view->trx_ids列表中，如果在则该记录的当前版本不可见，否则该记录的当前版本可见。
不同隔离级别ReadView实现方式
read-commited:

函数:ha_innobase::external_lock
if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
&& trx->global_read_view) {
/ At low transaction isolation levels we let each consistent read set its own snapshot /
   read_view_close_for_mysql(trx);

即:在每次语句执行的过程中，都关闭read_view, 重新在row_search_for_mysql函数中创建当前的一份read_view。这样就会产生不可重复读现象发生。

repeatable read:
在repeatable read的隔离级别下，创建事务trx结构的时候，就生成了当前的global read view。使用trx_assign_read_view函数创建，一直维持到事务结束。在事务结束这段时间内每一次查询都不会重新重建Read View ，从而实现了可重复读。

路人1024

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
轻松玩转MySQL之事务篇

事务简介在MySQL中的事务是由存储引擎实现的，而且支持事务的存储引擎不多，我们主要说一下InnoDB存储引擎中的事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 DDL、DML、DCL 操作，比如 insert,update,delete 语句，默认是自动提交的。事务四大特性(ACID)Atomicity(原子性)构成事务...
复制链接

扫一扫