MySQL高级事务篇

十三、事务基础知识

1.数据库事务概述

**事务：**一组逻辑操作单元，使数据从一种状态变换到另一种状态

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback)到最初状态

a.事物的ACID特性

• 原子性（atomicity）：

  • 原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚

• 一致性（consistency）：

  • 根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关

• 隔离型（isolation）：

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

• 持久性（durability）：

  • 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响，持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。当通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性

总结

在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是目的

b.事务的状态

MySQL根据这些操作所执行的不同阶段把事务大致划分成几个状态

• 活动的（active）

  事务对应的数据库操作正在执行过程中时，称该事务处在活动的状态

• 部分提交的（partially committed）

  当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，称该事务处在部分提交的状态

• 失败的（failed）

  当事务处在活动的或者部分提交的状态时，可能遇到了某些错误而无法继续执行，或者人为的停止当前事务的执行，称该事务处在失败的状态

• 中止的（aborted）

  如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。即撤销失败事务对当前数据库造成的影响。把这个撤销的过程称之为回滚。当回滚操作执行完毕时，即数据库恢复到了执行事务之前的状态，称该事务处在了中止的状态

• 提交的（committed）

  当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后，称该事务处在了提交的状态

  一个基本的状态转换图如下所示：

  

  图中可见，只有当事物处于提交的或者中止的状态时，一个事务的生命周期才算是结束。对于已经提交的事务来说，该事务对数据库所做的修改将永久生效，对于处于中止状态的事物，该事务对数据库所做的所有修改都会被回滚到没执行该事物之前的状态

2.如何使用事务

使用事务有两种方式，分别为显式事务和隐式事务

a.显示事务

步骤1：START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务

BEGIN;
START TRANSACTION;

START TRANSACTION语句相较于BEGIN特别之处在于，后边能跟随几个修饰符：

READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据

READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据

WITH CONSISTENT SNAPSHOT：启动一致性读

比如：

START TRANSACTION READ ONLY; #开启一个只读事务

START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT #开启只读事务和一致性读

START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT #开启读写事务和一致性读

注意：

• READ ONLY和READ WRITE是用来设置所谓的事物访问模式的，以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时即设置为只读的也设置为读写的，START TRANSACTION语句后边只能跟READ ONLY或READ WRITE
• 如果不显式指定事务的访问模式，那么该事务的访问模式就是读写模式

步骤2：一系列事务中的操作（主要是DML，不含DDL）

**步骤3：**提交事务或中止事务（即回滚事务）

#提交事务
COMMIT;

#回滚事务。即撤销正在进行的所有没有提交的修改
ROLLBACK;

#将事务回滚到某个保存点
mysql> ROLLBACK TO [SAVEPOINT]

SAVEPOINT相关操作

#在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
SAVEPOINT 保存点名称;

#删除某个保存点
RELEASE SAVEPOINT 保存点名称;

b.隐式事务

事务的自动提交autocommit(系统变量)，默认开启

SHOW VARIABLES LIKE 'autocommit';

关闭自动提交事务

• 方式1：显式的的使用START TRANSACTION或者BEGIN语句开启一个事务，此时在本次事务提交或者回滚前会暂时关闭掉自动提交的功能

• 方式2：系统变量autocommit的值设置为OFF

  SET autocommit = OFF;
  #或
  SET autocommit = 0;
  

c.隐式提交数据

• DDL(CREATE、ALTER、DROP等语句修改数据库对象数据库、表、视图、存储过程等结构时会自动的隐式调教事务)
• 当使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务
• 事务控制或关于锁定的语句
  1. 当一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务
  2. 当前的 autocommit 系统变量的值为 OFF ，手动调为 ON 时，也会隐式的提交前边语句所属的事务
  3. 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句时会隐式的提交前边语句所属的事务
• 加载数据的语句，使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务
• MySQL复制的一些语句，使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句会隐式的提交前边语句所属的事务

d.举例

CREATE TABLE user(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;

SET @@completion_type = 1;
BEGIN;
INSERT INTO user SELECT '张三';
COMMIT;

INSERT INTO user SELECT '李四';
INSERT INTO user SELECT '李四';
ROLLBACK;

SELECT * FROM user;

运行结果（1 行数据）：

SELECT * FROM user;
+--------+
| name |
+--------+
|张三|
+--------+

当设置 autocommit=0 时，不论是否采用 START TRANSACTION 或者 BEGIN 的方式来开启事务，都需要用 COMMIT 进行提交，让事务生效，使用 ROLLBACK 对事务进行回滚。

当设置 autocommit=1 时，每条 SQL 语句都会自动进行提交。不过这时，如果你采用 START TRANSACTION 或者 BEGIN 的方式来显式地开启事务，那么这个事务只有在 COMMIT 时才会生效，在 ROLLBACK 时才会回滚

e.SAVEPOINT

CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(15),
balance DECIMAL(10,2)
);

INSERT INTO account(NAME,balance)
VALUES
('张三',1000),
('李四',1000);

BEGIN;
UPDATE account SET balance = balance - 100 WHERE NAME = '张三';
UPDATE account SET balance = balance - 100 WHERE NAME = '张三';
SAVEPOINT s1; # 设置保存点
UPDATE account SET balance = balance + 1 WHERE NAME = '张三';
ROLLBACK TO s1; # 回滚到保存点

结果：张三：800.00

ROLLBACK;

结果：张三：1000.00

注意：

• 开启事务后，要么提交事务，要么回滚事务，不能在提交后，直接使用回滚
• SAVEPOINT name只能设置在开启事务后，回滚事务前
• 回滚事务时，可以选择ROLLBACK TO name或ROLLBACK，其中ROLLBACK TO name是回滚到设置保存点时的状态，ROLLBACK回滚到开启事务前的状态
• ROLLBACK可以在执行回滚保存点后，再执行一次，即为取消回滚到保存点，直接回滚到开启事务前的状态
• 终止状态只有ROLLBACK，没有ROLLBACK TO name

3.事务的隔离级别

MySQL是一个 客户端／服务器 架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接后，称为一个会话（ Session ）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。事务有 隔离性 的特性，理论上在某个事务 对某个数据进行访问 时，其他事务应该进行排队 ，当该事务提交之后，其他事务才可以继续访问这个数据

CREATE TABLE student (
    studentno INT,
    name VARCHAR(20),
    class varchar(20),
    PRIMARY KEY (studentno)
);
INSERT INTO student VALUES(1, '张三', '1班');

a.数据并发问题

1)脏写

两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写

eg：

发生时间顺序	SessionA	SessionB
1	BEGIN;
2		BEGIN;
3		UPDATE student SET name = 'zhangsan ’ where stu_id = 1;
4	UPDATE student SET name = 'zhangsan ’ where stu_id = 1;
5	COMMIT;
6		ROLLBACK;

Session A 和 Session B 各开启了一个事务，Sesssion B 中的事务先将studentno列为1的记录的name列更新为’李四’，然后Session A中的事务接着又把这条studentno列为1的记录的name列更新为’张三’。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象称之为脏写

2)脏读

两个事务 Session A、Session B，Session A 读取了已经被Session B更新但还没有被提交的字段。之后若 Session B回滚 ，则Session A 读取的内容为临时且无效

eg：

发生时间顺序	Session A	Session B
1	BEGIN;
2		BEGIN;
3		UPDATE student SET name = ‘zhangsan’ WHERE stu_id = 1;
4	SELECT * FROM student WHERE stu_id = 1;
5	COMMIT;
6		ROLLBACK;

Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name列更新 为’张三’，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为’张三’，而 Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为 脏读

3)不可重复读

两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。 之后 Session A 再次读取 同一个字段， 值就不同 了。那就意味着发生了不可重复读

eg：

发生时间顺序	SessionA	SessionB
1	BEGIN;
2	SELECT * FROM student WHERE stu_id= 1; 此时值为’wangwu’
3		UPDATE student SET name = ‘zhangsan’ WHERE stu_id = 1;
4	SELECT * FROM student WHERE stu_id =1;此时值为’zhangsan’
5		UPDATE student SET name = ‘lisi’ WHERE stu_id =1;
6	SELECT * FROM student WHERE stu_id =1;此时值为’lisi’

4)幻读

两个事务Session A、Session B, Session A 从一个表中读取了一个字段,然后 Session B 在该表中插入了一些新的行。之后,如果 Session A 再次读取同一个表,就会多出几行。那就意味着发生了幻读

eg：

发生时间编号	SessionA	SessionB
1	BEGIN;
2	SELECT * FROM student WHERE stu_id > 0;
3		INSERT INTO student VALUES(2,‘zhaoliu’,‘2班’);
4	SELECT * FROM student WHERE stu_id > 0;

Session A中的事务先根据条件 studentno > 0这个条件查询表student，得到了name列值为’张三’的记录；之后Session B中提交了一个隐式事务，该事务向表student中插入了一条新记录；之后Session A中的事务再根据相同的条件 studentno > 0查询表student，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。把新插入的那些记录称之为幻影记录

注意：

• 只有在插入数据时才会发生幻读，在删除数据时不会发生幻读
• 幻读指的是一个事务按照相同的条件多次读取记录时，后读取到了之前没有读取到的记录
• 不可重复读指的是一个事务按照相同的条件多次读取记录时，后读取不到了之前读取到的记录

b.SQL中的四种隔离级别

隔离级别越低，并发问题发生的就越多

SQL标准 中设立了4个 隔离级别 (从低到高)：

• READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果
• READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容
• SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，性能低下

隔离级别	是否可以避免脏读	是否可以避免不可重复读	是否可以避免幻读	是否可以避免加锁读
READ UNCOMMITTED	不可避免	不可避免	不可避免	可以避免
READ COMMITTED	可以避免	不可避免	不可避免	可以避免
REPEATABLE READ	可以避免	可以避免	不可避免	可以避免
SERIALIZABLE	可以避免	可以避免	可以避免	不可避免

MySQL的默认隔离级别为REPEATABLE READ

查看隔离级别 SHOW VARIABLES LIKE 'transaction_isolation';

e.设置隔离级别

通过下面的语句修改事务的隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#其中，隔离级别格式：
> READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE

或者：

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
#其中，隔离级别格式：
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

关于设置时使用GLOBAL或SESSION的影响：

• 使用 GLOBAL 关键字（在全局范围影响）
  • 当前已经存在的会话无效
  • 只对执行完该语句之后产生的会话起作用
• 使用 SESSION 关键字（在会话范围影响）
  • 对当前会话的所有后续的事务有效
  • 如果在事务之间执行，则对后续的事务有效
  • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

4.事务的常见分类

从事务理论的角度来看，可以把事务分为以下几种类型：

• 扁平事务（Flat Transactions）
• 带有保存点的扁平事务（Flat Transactions with Savepoints）
• 链事务（Chained Transactions）
• 嵌套事务（Nested Transactions）
• 分布式事务（Distributed Transactions）

十四、MySQL事务日志

• 事务的隔离性由 锁机制 实现

• 事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证

  • REDO LOG 称为 重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性
  • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性

• redo log: 是存储引擎层 (innodb) 生成的日志，记录的是物理级别上的页修改操作，比如页号xxx，偏移量yyy写入了’zzz’数据。主要为了保证数据的可靠性

• undo log: 是存储引擎层 (innodb) 生成的日志，记录的是 逻辑操作 日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一条与之相反的DELETE操作。主要用于 事务的回滚 (undo log 记录的是每个修改操作的 逆操作) 和 一致性非锁定读

1.Redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘 (checkPoint机制)，通过缓冲池来优化CPU和磁盘之间的差距

a. Redo日志的必要性

InnoDB引擎的事务采用了WAL技术 (Write-Ahead Logging)，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷到磁盘的时候，可以通过redo log来恢复，保证ACID中的D，这就是redo log的作用

b. Redo日志的特点、优势

1)特点

• redo日志是顺序写入磁盘的

  在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序ID，效率比随机IO快

• 事务执行过程中，redo log不断记录

  redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中

2)优势

• redo日志降低了刷盘频率
• redo日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快

c. Redo的组成

• 重做日志的缓冲 (redo log buffer) ，保存在内存中，易丢失
  • 在服务器启动时就会向操作系统申请了一大片称之为 redo log buffer 的 连续内存 空间，这片内存空间被划分为若干个连续的redo log block。一个redo log block占用512字节大小
  • 参数设置：innodb_log_buffer_size：
  • redo log buffer 大小，默认 16M ，最大值是4096M，最小值为1M
• 重做日志文件 (redo log file) ，保存在硬盘中，是持久的

d. Redo的整体流程

• 第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
• 第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
• 第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
• 第4步：定期将内存中修改的数据刷新到磁盘中

e. Redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以一定的频率刷入到真正的redo log file中

InnoDB通过设置 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务，将redo log buffer 中的日志刷新到redo log file中

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

• 设置为0 ：表示每次事务提交时不进行刷盘操作
  • 第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝
  • 第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
  • 第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
  • 第4步：定期将内存中修改的数据刷新到磁盘中
• 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
• 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由OS决定什么时候同步到磁盘文件

• 另外，InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存(page cache)，然后调用刷盘操作

• 即一个没有提交事务的redo log记录，也可能会刷盘。因为在事务执行过程 redo log 记录是会写入 redo log buffer中，这些redo log 记录会被后台线程刷盘

f. 写入Redo log buffer过程

1)Mini-Transaction

MySQL把对底层页面中的一次原子访问过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction。一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志可以作为一个不可分割的整体

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo日志

2)redo 日志写入log buffer

不同的事务可能是 并发 执行的，所以 两个不同的事务 之间的 mtr 可能是 交替执行 的。没当一个mtr执行完成时，伴随该mtr生成的一组redo日志就需要被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer的

3)redo log block的结构

一个redo log block是由日志头、日志体、日志尾组成

g. Redo log file

1) 相关参数设置

• innodb_log_group_home_dir ：指定 redo log 文件组所在的路径，默认值为 ./ ，表示在数据库 的数据目录下。MySQL的默认数据目录（ var/lib/mysql）下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。此redo日志 文件位置还可以修改
• innodb_log_files_in_group：指明redo log file的个数，命名方式如：ib_logfile0，iblogfile1… iblogfilen。默认2个，最大100个
• innodb_flush_log_at_trx_commit：控制 redo log 刷新到磁盘的策略，默认为1
• innodb_log_file_size：单个 redo log 文件设置大小，默认值为 48M 。最大值为512G，注意最大值 指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大 于最大值512G

2)日志文件组

总共的redo日志文件大小其实就是： innodb_log_file_size × innodb_log_files_in_group

采用循环使用的方式向redo日志文件组里写数据的话，会导致后写入的redo日志覆盖掉前边写的redo日志

3)checkpoint

在整个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos是当前记录的位置，一边写一边后移
• checkpoint是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次MySQL加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把check point后移更新。write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录

2.Undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log

a.概念

事务需要保证 原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：

• 情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误 ， 操作系统错误 ，甚至是突然 断电 导致的错误
• 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行

以上情况出现，需要把数据改回原先的样子，这个过程称之为 回滚

b.Undo日志的作用

• 作用1：回滚数据
• 作用2：MVCC
  • undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录以及被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取

c.undo的存储结构

1)回滚段与undo页

InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，而在每个undo log segment段中进行 undo页 的申请

2)回滚段与事务

1. 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务

2. 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数 据会被复制到回滚段

3. 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够 用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘 区或者在回滚段允许的情况下扩展新的盘区来使用

4. 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个 undo表空间

   mysql> show variables like 'innodb_undo_tablespaces';
   # undo log的数量，最少为2. undo log的truncate操作有purge协调线程发起。在truncate某个undo log表空间的过程中，保证有一个可用的undo log可用。
   

5. 当事务提交时，InnoDB存储引擎会做以下两件事情：

   • 将undo log放入列表中，以供之后的purge操作
   • 判断undo log所在的页是否可以重用，若可以分配给下个事务使用

3)回滚段中的数据分类

1. 未提交的回滚数据(uncommitted undo information)：该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖
2. 已经提交但未过期的回滚数据(committed undo information)：该数据关联的事务已经提交，但是仍受到undo retention参数的保持时间的影响
3. 事务已经提交并过期的数据(expired undo information)：事务已经提交，而且数据保存时间已经超过 undo retention参数指定的时间，属于已经过期的数据。当回滚段满了之后，就优先覆盖“事务已经提交并过期的数据"

事务提交后不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log以undo log所在页由purge线程来判断

d.undo的类型

• insert undo log

  insert undo log是指insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作

• update undo log

  update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除

e.undo log的生命周期

1)生成过程

#假设A=1,B=2;
1. start transaction;
2. 记录A=1到undo log;
3. update A = 3;
4. 记录A=3到redo log;
5. 记录B=2到undo log;
6.  update B=4;
7. 记录B=4到redo log;
8.将redo log刷新到磁盘;
9. commit;

• 如果在1-8任意一步系统宕机，事务未提交，则该事务不会对磁盘上的数据做改变
• 如果在8-9系统宕机，恢复后可以选择回滚或者提交事务，此时的redo log已经持久化
• 如果在9后宕机，系统恢复后会根据redo log把数据刷回磁盘

每个行记录的隐藏列：

1. DB_ROW_ID：如果没有为表显示的定义主键，并且表中没有定义唯一索引，则会自动为表添加一个ROW_ID的隐藏列为主键
2. DB_TRX_ID：每个事物都会分配一个事务ID，当对某条记录发生变更时，就会将这个事务的ID写入TRX_ID中
3. DB_ROLL_PTR：回滚指针，指向undo log的指针

执行insert时：每插入一条数据都会生成生成一条insert undo log，并且数据的回滚指针会指向它。undo log会记录undo log的序号、插入主键的列和值…，那么在进行rollback的时候，通过主键直接把对应的数据删除即可

begin;
INSERT INTO user (name) VALUES ("tom");

执行update时：对应更新的操作会产生update undo log，并且会分更新主键和不更新主键

假设现在执行：

UPDATE user SET name="Sun" WHERE id=1;

这时会把老的记录写入新的undo log，让回滚指针指向新的undo log，它的undo no是1，并且新的undo log会指向老的undo log（undo no=0）

假设现在执行：

UPDATE user SET id=2 WHERE id=1;

对于更新主键的操作，会先把原来的数据deletemark标识打开，这时并没有真正的删除数据，真正的删除会交给清理线程去判断，然后在后面插入一条新的数据，新的数据也会产生undo log，并且undo log的序号会递增

可以发现每次对数据的变更都会产生一个undo log，当一条记录被变更多次时，那么就会产生多条undo log，undo log记录的是变更前的日志，并且每个undo log的序号是递增的，那么当要回滚的时候，按照序号依次向前推，就可以找到原始数据了

2)undo log的回滚

当执行rollback时

1. 通过undo no=3的日志把id=2的数据删除
2. 通过undo no=2的日志把id=1的数据的deletemark还原成0
3. 通过undo no=1的日志把id=1的数据的name还原成Tom
4. 通过undo no=0的日志把id=1的数据删除

3)undo log的删除

• 针对于insert undo log

  因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作

• 针对于update undo log

  该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除

3.总结

• undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子

• redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程

十五、锁

1.概念

协调多个进程或线程并发访问某一资源的机制，当多个线程并发访问某个数据时，需要保证这个数据在任何时刻都保持只有一个线程在访问吗，保证数据的完整性和一致性，此时需要对并发操作进行控制，所以产生了锁

2.MySQL并发事务访问相同记录

并发事务访问相同记录的情况大致可以划分为3种：

a.读-读

即并发事务相继读取相同的记录，读取操作本身不会对记录产生影响，所以允许这种情况发生

b.写-写

即并发事务相继对相同的记录做出改变

在这种情况下回发生脏读的问题，所以在多个未提交事务相继改动一条记录时，需要对所有事务通过锁机制排队

锁其实是一个内存中的结构 ，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的

当一个事务对记录进行改动时，首先会看内存中有没有与这条记录关联的锁结构，当没有时会在内存中生成一个锁结构与之关联比如，事务T1 要对这条记录做改动，就需要生成一个 锁结构 与之关联：

• trx信息：代表这个锁结构是哪个事务生成的
• is_waiting：代表当前事务是否在等待

在事务T1改动了这条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false，称值为获取锁成功，或者加锁成功，然后就可以继续执行操作了

在事务T1提交之前，另一个事务T2也想对该记录做改动，会首先看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过锁结构的is_waiting属性值为true，表示当前事务需要等待，称之为获取锁失败，或者加锁失败，图示：

在事务T1提交之后，就会把该事务生成的锁结构释放掉，然后看看还有没有别的事务在等待获取锁，发现了事务T2还在等待获取锁，所以把事务T2对应的锁结构的is_waiting属性设置为false，然后把该事务对应的线程唤醒，让它继续执行，此时事务T2就算获取到锁

小结几种说法：

• 不加锁

  意思就是不需要在内存中生成对应的 锁结构 ，可以直接执行操作

• 获取锁成功，或者加锁成功

  意思就是在内存中生成了对应的锁结构，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作

• 获取锁失败，或者加锁失败，或者没有获取到锁

  意思就是在内存中生成了对应的锁结构，不过锁结构的is_waiting属性为true ，也就是事务需要等待，不可以继续执行操作

c.读-写或写-读

即一个事务进行读取操作，另一个事务进行改动操作，可能会发生脏读、不可重复读、幻读的问题

d.解决方案

• 方案一，读操作利用多版本并发控制(MVCC)，对写操作进行加锁，性能高
• 方案二，对读写操作都加锁，影响性能

3.锁的分类

a.从数据操作的类型划分：读锁、写锁

• 读锁：也称为共享锁，用S表示，针对同一份记录，多个事务的读操作可以同时进行而不互相影响和阻塞
• 写锁：也称为排它锁，用X表示，在当前写操作没有完成前，会阻止其他写锁和读锁的操作，以确保在一段时间内只有一个事物对记录执行写入操作

1)锁定读

对读取的记录加S锁

SELECT ... FOR SHARE;

如果当前事务执行了该语句，则会为读取到的记录加S锁，允许别的事务继续获取这些记录的S锁，但不能获取X锁，如果需要获取X锁，则需要排队等待S锁的释放

对读取的记录加X锁

SELECT ... FOR UPDATE;

如果当前事务执行了该语句，会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁，也不允许获取这些记录的X锁，如果想要获取S锁或X锁，则需要排队等待X锁的释放

2)写操作

• DELETE
  • 对一条记录做DELETE操作的过程：首先在B+树中定位到这条记录的位置，然后获取这条记录的X锁，在执行delete mark操作。把定位待删除记录在B+树中位置的过程看做是一个获取X锁的锁定读
• UPDATE
  • 情况1：未修改记录的键值，并且被更新的列占用的存储空间前后没有变化
    • 首先在B+树中定位到位置，然后获取记录的X锁，最后在原纪录的位置进行修改操作。把定位待修改记录在B+树中位置的过程看做是一个获取X锁的锁定读
  • 情况2：未修改记录的键值，并且至少有一个被更新的列的存储空间前后有变化
    • 首先在B+树中定位到位置，然后获取记录的X锁，将该记录彻底删除，然后在插入一条新纪录。定位待修改记录在B+树中位置的过程看做是一个获取X锁的锁定读，新插入的记录有INSERT操作提供的隐式锁进行保护
  • 情况3：修改了记录的键值，则相当于在原记录上做DELETE操作之后，再来一次INSERT操作
• INSERT
  • 一般情况下，新插入一条记录不会加锁，通过隐式锁对其进行保护

b.从对待锁的态度划分:乐观锁、悲观锁

从对待锁的态度来看锁，将锁分成乐观锁和悲观锁，这两种锁是两种看待 数据并发的思维方式 。注意：乐观锁和悲观锁并不是锁，而是锁的 设计思想

1)悲观锁

对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现

2)乐观锁

对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型， 这样可以提高吞吐量。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS实现的

3)两种锁的适用场景

1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁 问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作
2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层 面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突

十六、多版本并发控制

1.概念(MVCC)

MVCC（Multiversion Concurrency Control），多版本并发控制。MVCC是通过数据行的多个版本管理来实现数据库的 并发控制。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。即为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样 在做查询的时候就不用等待另一个事务释放锁

2.快照读和当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，能够更好的处理读-写冲突，使遇到读-写冲突时，不加锁，非阻塞并发读中的读指的就是快照读，而非当前读。

当前读是一种加锁的操作，是悲观锁的实现，MVCC的本质是乐观锁的思想

a.快照读

又称为一致性读，读取的数据是快照数据，不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读

快照读是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下， 避免了加锁操作，降低了开销

b.当前读

当前读读取的是记录的最新数据，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的SELECT或者对数据进行增删改都会进行当前读

3.MVCC实现原理–ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View

a.ReadView

ReadView是事务在使用MVCC机制进行快照读操作时产生的读视图，当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录和维护当前启动但未提交的事务的ID

ReadView中主要包含4个比较重要的内容

1. creator_trx_id ，创建这个 Read View 的事务 ID

   只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0

2. trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表`

3. up_limit_id ，活跃的事务中最小的事务ID

4. low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id 是系 统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID

b.ReadView规则

有了ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见

• 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
• 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
• 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
• 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，就需要判断一下trx_id属性值是不是在 trx_ids 列表中
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表

3. up_limit_id ，活跃的事务中最小的事务ID

4. low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的id值。low_limit_id 是系 统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID

b.ReadView规则

有了ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见

• 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
• 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
• 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
• 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，就需要判断一下trx_id属性值是不是在 trx_ids 列表中
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问