MySQL事务应用和原理



MySQL架构

  • 网络连接层

    客户端连接器(Client Connectors):提供与MySQL服务器建立的支持,它们通过各自API技术与MySQL建立连接。

  • 服务层(MySQL Server)
    服务层是MySQL Server的核心,主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

    1
    2
    3
    4
    5
    6
    1. 连接池(Connection Pool):负责存储和管理客户端与数据库的连接,一个线程负责管理一个连接。[官网性能测试报告:引入线程池,性能稳定性与性能会有很大得提升,128并发,读写模式, mysql高出60倍,只读18倍,若不引用线程池,线程创建关闭性能消耗大]
    2. 系统管理和控制工具(Management Services & Utilities):例如备份恢复、安全管理、集群管理等
    3. SQL接口(SQL Interface):用于接受客户端发送的各种SQL命令,并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
    4. 解析器(Parser):负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。
    5. 查询优化器(Optimizer):当“解析树”通过解析器语法检查后,将交由优化器将其转化成执行计划,然后与存储引擎交互。
    6. 缓存(Cache&Buffer): 缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,权限缓存,引擎缓存等。如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。

    SELECT uid, name FROMuser WHERE gender = 1;
    执行优化顺序
    1)select先根据where语句进行选取,并不是查询出全部数据再过滤
    2)select查询根据uid和name进行属性投影,并不是取出所有字段
    3)将前面选取和投影联接起来最终生成查询结果

  • 存储引擎层(Pluggable Storage Engines)
    存储引擎负责MySQL中数据的存储与提取,与底层系统文件进行交互。MySQL存储引擎是插件式的,服务器中的查询执行引擎通过接口与存储引擎进行通信,接口屏蔽了不同存储引擎之间的差异 。

    现在有很多种存储引擎,各有各的特点,最常见的是 InnoDBMyISAM

  • 系统文件层(File System)
    该层负责将数据库的数据和日志存储在文件系统之上,并完成与存储引擎的交互,是文件的物理存储层。主要包含日志文件,数据文件,配置文件,pid 文件,socket 文件等。

    MySQL的日志有很多种,如二进制日志、错误日志、查询日志、慢查询日志等,此外InnoDB存储引擎还提供了两种事务日志:redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性;undo log则是事务原子性和隔离性实现的基础。

日志

redo log

1、初识重做日志

  • 生命周期:

    事务开始之后,就开始产生 redo log 日志了,在事务执行的过程中,redo log 开始逐步落盘,当对应事务的脏页写入到磁盘之后,redo log 的使命就完成了,它所占用的空间也就可以被覆盖了。

  • 存储内容

    redo log 包括两部分:一是内存中的日志缓冲(redo log buffer),该部分日志是易失性的;二是磁盘上的重做日志文件(redo log file),该部分日志是持久的,redo log 存储的是物理格式的日志,记录的是物理数据页面的修改信息,它是顺序写入 redo log file 中的。

  • 落盘方式(将 innodb 日志缓冲区的日志刷新到磁盘)

    • Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件
    • 每个事务提交时会将重做日志刷新到重做日志文件
    • 当重做日志缓存可用空间少于一半时,重做日志缓存被刷新到重做日志文件

2、实现原理

redo log和undo log都属于InnoDB的事务日志。下面先聊一下redo log存在的背景。

InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),Buffer Pool中包含了磁盘中部分数据页的映射,作为访问数据库的缓冲:当从数据库读取数据时,会首先从Buffer Pool中读取,如果Buffer Pool中没有,则从磁盘读取后放入Buffer Pool;当向数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏)。

Buffer Pool的使用大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。

于是,redo log被引入来解决这个问题:当数据修改时,除了修改Buffer Pool中的数据,还会在redo log记录这次操作;当事务提交时,会调用fsync接口对redo log进行刷盘。如果MySQL宕机,重启时可以读取redo log中的数据,对数据库进行恢复。redo log采用的是WAL(Write-ahead logging,预写式日志),所有修改先写入日志,再更新到Buffer Pool,保证了数据不会因MySQL宕机而丢失,从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘,为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢?主要有以下两方面的原因:

(1)刷脏是随机IO,因为每次修改的数据位置随机,但写redo log是追加操作,属于顺序IO

(2)刷脏是以数据页(Page)为单位的,MySQL默认页大小是16KB,一个Page上一个小修改都要整页写入;而redo log中只包含真正需要写入的部分,无效IO大大减少

undo log

1、初识回滚日志

  • 生命周期

事务开始之前,将当前事务版本生成 undo log,undo log 也会产生 redo log 来保证 undo log 的可靠性。当事务提交之后,undo log 并不能立马被删除,而是放入待清理的链表,由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息,从而决定是否可以清理 undo log 的日志空间。

  • 存储内容

undo log 存储的是逻辑格式的日志,保存了事务发生之前的上一个版本的数据,可以用于回滚。当一个旧的事务需要读取数据时,为了能读取到老版本的数据,需要顺着 undo 链找到满足其可见性的记录。

  • 存储位置

默认情况下,undo 文件是保存在共享表空间的,也即 ibdatafile 文件中,当数据库中发生一些大的事务性操作的时候,要生成大量的 undo log 信息,这些信息全部保存在共享言七墨表空间中,因此共享表空间可能会变得很大,默认情况下,也就是 undo log 使用共享表空间的时候,被“撑大”的共享表空间是不会、也不能自动收缩的。因此,MySQL5.7 之后的“独立 undo 表空间”的配置就显得很有必要了。

2、实现原理

实现原子性的关键,是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚,靠的是undo log:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子

undo log属于逻辑日志,它记录的是sql执行相关的信息。当发生回滚时InnoDB会根据undo log的内容做与之前相反的工作:对于每个insert,回滚时会执行delete;对于每个delete,回滚时会执行insert;对于每个update,回滚时会执行一个相反的update,把数据改回去。

以update操作为例:当事务执行update时,其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息,回滚时便可以使用这些信息将数据还原到update之前的状态。

binlog

1、初识二进制日志

binlog 用于主从复制中,从库利用主库上的 binlog 进行重播,实现主从同步。用于数据库的基于时间点、位点等的还原操作。binlog 的模式分三种:Statement、Row、Mixed。

binlog 的三种模式

  • 生命周期

事务提交的时候,一次性将事务中的 sql 语句(一个事务可能对应多个 sql 语句)按照一定的格式记录到 binlog 中,这里与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候才刷新到磁盘,而是在事务开始之后就开始逐步写入磁盘。binlog 的默认保存时间是由参数 expire_logs_days 配置的,对于非活动的日志文件,在生成时间超过 expire_logs_days 配置的天数之后,会被自动删除

redo log与binlog

我们知道,在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复,但二者是有着根本的不同的:

(1)作用不同:redo log是用于crash recovery的,保证MySQL宕机也不会影响持久性;binlog是用于point-in-time recovery的,保证服务器可以基于时间点恢复数据,此外binlog还用于主从复制。

(2)层次不同:redo log是InnoDB存储引擎实现的,而binlog是MySQL的服务器层实现的,同时支持InnoDB和其他存储引擎。

(3)内容不同:redo log是物理日志,内容基于磁盘的Page;binlog的内容是二进制的,根据binlog_format参数的不同,可能基于sql语句、基于数据本身或者二者的混合。

(4)写入时机不同:binlog在事务提交时写入;redo log的写入时机相对多元:

  • 前面曾提到:当事务提交时会调用fsync对redo log进行刷盘;这是默认情况下的策略,修改innodb_flush_log_at_trx_commit参数可以改变该策略,但事务的持久性将无法保证。
  • 除了事务提交时,还有其他刷盘时机:如master thread每秒刷盘一次redo log等,这样的好处是不一定要等到commit时刷盘,commit速度大大加快。

ACID特性及其实现原理

原子性(Atomicity):语句要么全执行,要么全不执行,是事务最核心的特性,事务本身就是以原子性来定义的;实现主要基于undo log

持久性(Consistency):保证事务提交后不会因为宕机等原因导致数据丢失;实现主要基于redo log

隔离性(Isolation):保证事务执行尽可能不受其他事务影响;InnoDB默认的隔离级别是RR,RR的实现主要基于锁机制(包含next-key lock)、MVCC(包括数据的隐藏列、基于undo log的版本链、ReadView)

一致性(Durability):事务追求的最终目标,一致性的实现既需要数据库层面的保障,也需要应用层面的保障

原子性

1、定义

原子性是指一个事务是一个不可分割的工作单位,其中的操作要么都成功,要么都失败;如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。

2、实现原理:undo log

持久性

1、定义

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

2、实现原理:redo log

隔离性

与原子性、持久性侧重于研究事务本身不同,隔离性研究的是不同事务之间的相互影响。隔离性是指,事务内部的操作与其他事务是隔离的,并发执行的各个事务之间不能互相干扰。严格的隔离性,对应了事务隔离级别中的Serializable (可串行化),但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见,我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明),那么隔离性的探讨,主要可以分为两个方面:

  • (一个事务)写操作对(另一个事务)写操作的影响:锁机制保证隔离性
  • (一个事务)写操作对(另一个事务)读操作的影响:MVCC保证隔离性

一致性

数据库通过原子性(A)、隔离性(I)、持久性(D)来保证一致性(C)。其中一致性是目的,原子性、隔离性、持久性是手段。因此数据库必须实现AID三大特性才有可能实现一致性。

什么是MVCC机制

简介

MVCC是指多版本并发控制。MVCC是在并发访问数据库时,通过对数据进行多版本控制,避免因写锁而导致读操作的堵塞,从而很好的优化并发堵塞问题。解决并发问题的通用方案有:

(1)对并发访问的数据添加一把排它锁,添加锁之后,其他的读和写操作都需等待锁释放后才能访问。

(2)添加一把共享锁,读读操作不需要等待锁的释放,读写和写写操作需要等待锁的释放。

(3)通过对并发数据进行快照备份,从而达到无锁数据的并发访问。

通俗的讲就是MVCC通过对数据进行多版本保存,根据比较版本号来控制数据是否展示,从而达到读取数据时无需加锁就可以实现事务的隔离性。

示例

在事务 A 提交前后,事务 B 读取到的 x 的值是什么呢?答案是:事务 B 在不同的隔离级别下,读取到的值不一样。

  • 如果事务 B 的隔离级别是读未提交(RU),那么两次读取均读取到 x 的最新值,即 20。
  • 如果事务 B 的隔离级别是读已提交(RC),那么第一次读取到旧值 10,第二次因为事务 A 已经提交,则读取到新值 20。
  • 如果事务 B 的隔离级别是可重复读或者串行(RR/RS),则两次均读到旧值 10,不论事务 A 是否已经提交。

作用

InnoDB 相比 MyISAM 有两大特点,一是支持事务而是支持行级锁,事务的引入带来了一些新的挑战。相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题,主要包括以下几种情况:

  • 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题 —— 最后的更新覆盖了其他事务所做的更新。如何避免这个问题呢,最好在一个事务对数据进行更改但还未提交时,其他事务不能访问修改同一个数据。
  • 脏读(Dirty Reads)
  • 不可重复读(Non-Repeatable Reads)
  • 幻读(Phantom Reads)

实现隔离机制的方法主要有两种:

  • 加读写锁;
  • 一致性快照读,即MVCC;

实现原理

总体上来讲MVCC的实现是基于ReadView版本链以及Undo日志实现的。

ReadView(可读视图)

RR 下的 ReadView 生成

在 RR 隔离级别下,每个事务 touch first read 时(本质上就是执行第一个 SELECT 语句时,后续所有的 SELECT 都是复用这个 ReadView,其它 update, delete, insert 语句和一致性读 snapshot 的建立没有关系),会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView。

RC 下的 ReadView 生成

在 RC 隔离级别下,每个 SELECT 语句开始时,都会重新将当前系统中的所有的活跃事务拷贝到一个列表生成 ReadView。二者的区别就在于生成 ReadView 的时间点不同,RR是事务之后第一个 SELECT 语句开始、RC是事务中每条 SELECT 语句开始

快照读和当前读

在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的 trx_id 前的可见版本 (有可能是历史版本),不用加锁当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

快照读:简单的select操作,属于快照读,不加锁。当然除了 排他锁(写锁)for update。

当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。

1、开启排他锁

1
2
3
START TRANSACTION;

SELECT * FROM `student` WHERE sid = '01' FOR UPDATE;

2、其他事务修改数据

1
UPDATE student SET SNAME = '赵雷11' WHERE sid = '01';

3、commit - 释放锁,然后再次执行update

什么是脏读?幻读?不可重复读?

  • 脏读(Drity Read):一个事务正在对一条记录做修改,在这个事务并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些尚未提交的脏数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做 “脏读”。
  • 不可重复读(Non-repeatable read): 一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了。
  • 幻读(Phantom Read): 一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,

事务的隔离级别

  1. 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到.
  2. 读已提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到.
  3. 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的.
  4. 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个 事务执行完成才能继续执行.

MySQL8查看当前事务隔离级别

1
2
3
select @@transaction_isolation;

-- select @@tx_isolation;8以下版本命令
1
2
3
4
5
6
7
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ | 可重复读
+-------------------------+
1 row in set (0.04 sec)

修改事务隔离级别

1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

注意:MySQL的InnoDB默认事务隔离级别是可重复读,不满足隔离性;Oracle默认的事务隔离级别为READ COMMITTED,不满足隔离性。

MySQL事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 默认自动提交事务
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

# 查看当前正在执行的事务,要 start transaction; 后执行select语句。
select * from information_schema.innodb_trx;

提交和回滚

典型的MySQL事务是如下操作的:

1
2
3
start transaction;
…… #一条或多条sql语句
commit;

其中start transaction标识事务开始,commit提交事务,将执行结果写入到数据库。如果sql语句执行出现问题,会调用rollback,回滚所有已经执行成功的sql语句。当然,也可以在事务中直接使用rollback语句进行回滚。

自动提交

MySQL中默认采用的是自动提交(autocommit)模式,如下所示:

img

在自动提交模式下,如果没有start transaction显式地开始一个事务,那么每个sql语句都会被当做一个事务执行提交操作。

通过如下方式,可以关闭autocommit;需要注意的是,autocommit参数是针对连接的,在一个连接中修改了参数,不会对其他连接产生影响。

img

如果关闭了autocommit,则所有的sql语句都在一个事务中,直到执行了commit或rollback,该事务结束,同时开始了另外一个事务。

特殊操作

在MySQL中,存在一些特殊的命令,如果在事务中执行了这些命令,会马上强制执行commit提交事务;如DDL语句(create table/drop table/alter/table)、lock tables语句等等。

不过,常用的select、insert、update和delete命令,都不会强制提交事务。

默认隔离级别(RR)下的两个事务

1、开启第一个事务

1
2
3
4
5
6
7
8
START TRANSACTION;

-- 第一select后才真正开启事务,有Readview快照读,当然我进行update/delete/insert这种也是能开启事务
SELECT * FROM `student`

UPDATE student SET SNAME = 'Lauy' WHERE sid = '01';

ROLLBACK;

2、在第一个事务开启后且update前开启第二个事务

1
2
3
4
5
6
7
8
9
10
11
START TRANSACTION;

SELECT * FROM `student`

-- 报错:> 1205 - Lock wait timeout exceeded; try restarting transaction 当前表被锁住了,等待持有锁的第一个事务释放才行。
UPDATE student SET SNAME = '赵雷' WHERE sid = '01' AND SNAME = '赵雷';

-- 修改其他表无影响
update sc c set c.CId = '02' WHERE c.SId = '01' LIMIT 1;

COMMIT;

可重复读基本下,第一个事务修该了数据,对第二个事务是不可见的,所以会出现明明我们看着数据是对的且条件成立,可就是无法修改。

读未提交(RU)

1、第一个事务开启并修改sid为01的sname为赵雷

1
2
3
4
5
6
-- 修改事务隔离级别为:READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

SELECT * FROM `student` WHERE sid = '01';

1
2
3
4
-- 修改sid=1的值
UPDATE student SET SNAME = '赵雷' WHERE sid = '01';

-- COMMIT; 这里先不提交,我们看第二个事务可以看到步

2、第二个事务开启

1
SELECT * FROM `student` WHERE sid = '01'; -- 查询

可见在读未提交级别下,即便我们没有被commit的数据操作,其他事务下也可以看见。

读已提交(RC)

1、第一个事务开启,依旧修改sid=01的值

1
2
3
4
5
-- READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT * FROM `student` WHERE sid = '01';

2、修改sid为01的sname

1
UPDATE student SET SNAME = '赵雷' WHERE sid = '01';

3、第二个事务开启

1
2
3
4
5
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT * FROM `student` WHERE sid = '01';

4、第一个事务提交,后第二个事务再查询,就发现改成sname=赵磊了

1
SELECT * FROM `student` WHERE  sid = '01';

串行化

1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

串行化是4种事务隔离级别中隔离效果最好的,解决了脏读、可重复读、幻读的问题,但是效果最差,它将事务的执行变为顺序执行,与其他三个隔离级别相比,它就相当于单线程,后一个事务的执行必须等待前一个事务结束。

Spring事务

aop,默认捕获运行时异常,如果在同一个类中,一个a事物方法调用当前类的另外的b事物方法 ,最后只会a方法创建事物,b方法不会。因为生成的代理类会调用a方法生成事物,但是使用 this.b 方法不会经过代理,可以采用 service.b 方法,这样就会经过 service的代理类新建事物。

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
@PutMapping("/setRoles")
@ResponseBody
public Result updateRolesById(@RequestBody UserInfo userInfo) {
userInfoService.updateUserById(userInfo);
userInfoService.deleteById("1");
// 捕获异常,事务失效,因为无法try catch
// try {
// int i = 1 / 0;
// } catch (Exception e) {
// e.printStackTrace();
// }
this.methodA(userInfo);
return Result.success().setCode(200).setMsg("修改成功");
}

@Transactional
public void methodA(UserInfo userInfo) {
System.out.println("A");
userInfoService.findAll(userInfo);
}

@Transactional
public void methodB() {
System.out.println("B");
}

mysql 网站执行的 SQL 语句日志

1
2
3
SHOW VARIABLES LIKE "general_log%";
-- 如果日志是OFF,说明没有打开日志记录,可以使用以下语句开启
SET GLOBAL general_log = 'ON'

如果日志是OFF,说明没有打开日志记录,可以使用以下语句开启

然后使用notepad ++打开以上日志地址,即可跟踪网站所执行的SQ命令

参考链接:

MySQL 中 redo log、undo log、binlog 的总结

MySQL架构二 MySQL体系架构

MySQL-MVCC机制

深入学习MySQL事务:ACID特性的实现原理

打赏
  • 版权声明: 本网站所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!
  1. © 2020-2021 Lauy    湘ICP备20003709号-1

请我喝杯咖啡吧~

支付宝
微信