MySQL之事务

什么是事务

事务是伴随着交易类的业务场景出现的工作机制

事务的ACID特性

• A：atomicity，原子性，指事务是一个不可分割的工作单位，事务中的操作要么都发生（commit，提交），要么都不发生（undo，回滚）
• C：consistency，一致性，如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态；一致性表示事务完成后，符合逻辑运算
• I：isolation，隔离性，多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
• D：durability，持久性，一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

1. 事务生命周期管理

1.1 标准事务控制语句

-- 开启事务
begin;
start transaction;

-- 提交事务
commit;

-- 回滚事务
rollback;

-- 标准事务语句(DML)
insert
update
delete
select

1.2 自动提交功能

-- 在autocommit=1的时候，在执行DML时，没有加begin(没有显示地开启事务)
-- 在执行DML语句时会自动在这个DML之前加一个begin，在执行完语句后会自动commit
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

-- autocommit一般适用于非交易类业务场景
-- 如果是交易类的业务：
---- 1. autocommit=0，commit手工提交
---- 2. autocommit=1，每次想要发生事务型操作都要手工begin，commit

-- 临时生效
set global autocommit=0;
-- 永久生效
vim /etc/my.cnf
[mysqld]
autocommit=0

1.3 隐式事务控制语句

-- 隐式提交
1. 设置了autocommit=1
2. 导致提交的非事务语句：
	DDL语句： （ALTER、CREATE 和 DROP）
	DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
	锁定语句： （LOCK TABLES 和 UNLOCK TABLES）

-- 隐式回滚
1. 会话关闭
2. 数据库宕机
3. 事务语句执行失败

2. 保证事务的ACID

2.1 名词介绍

1. 重做日志

redo logs：重做日志 ib_logfile0~N 48M 轮询使用
	用于记录数据页的变化情况
redo logs buffer：redo在内存中的缓冲区域

2. 数据页存储位置

ibd：存储数据行和主键索引
buffer pool：缓冲区池，数据页和索引页的缓冲

3. LSN

log segment number，日志序列号，磁盘数据页、redo logs、buffer pool、redu buffer中都会记录
MySQL每次启动时都会比较磁盘数据页和redo logs的LSN，要求必须两者LSN一致时数据库才能正常启动

4. WAL

wait ahead log，日志优先于数据页写入，实现持久化

5. 脏页

内存中的数据发生了修改，在没回写到磁盘前把内存也称之为脏页

6. CKPT

checkpoint，检查点，在检查点会触发将脏页数据写到磁盘的动作

7. TXID

transaction id，事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务的生命周期

8. UNDO

保存在idata1中，存储了事务工作中的回滚信息(DML的反操作)

2.2 事务的工作流程

1. redo logs

(1) redo是什么
	顾名思意，“重做日志”，是事务日志的一种，存储的是在事务工作过程中数据页的变化，属于物理日志
(2) 作用是什么
	在事务的ACID过程中，实现的是"D"持久化的作用，对于AC也有相应的作用
(3) redo日志位置
	iblogfile0~N
(4) redo buffer
	存储数据页的变化信息+数据页当时的LSN号
(5) redo的刷新策略
	每次commit时，会将当前事务的redo buffer刷新到磁盘中，还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
(6) 工作流程
	在事务commit时redo会立即写入磁盘，日志落盘成功就代表commit
	在MySQL出现Crash异常宕机时，提供前滚功能(CSR)

MySQL在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致
情况一:
我们做了一个事务,begin;update;commit.
1. 在begin ,会立即分配一个TXID=tx_01.
2. update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中
3. DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102
4. LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redo buffer
5. 执行commit时,LGWR日志写线程会将redo buffer信息写入redo log日志文件中,基于WAL原则,
在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6. 假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7. MySQL再次重启时,必须要redo log和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102
MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正常启动
以上的工作过程,我们把它称之为基于REDO的"前滚操作"

-- 相关参数
innodb_flush_log_at_trx_commit：日志刷写时机
	1：默认值，在每次事务提交时，会立即刷新redo到磁盘，commit才能成功
	0：每秒刷新redo buffer到os cache，再fsync到磁盘，异常宕机时，有可能丢失1s内的事务
	2：每次事务提交，都立即刷新redo buffer到os cache，再每秒fsync到磁盘，磁盘异常宕机时，有可能丢失1s内的事务

另外
1. redo buffer还和操作系统的缓存机制有关，所以刷写策略可能和innodb_flush_method参数有一定关系
2. redo也有group commit，可以理解为在每次刷新已提交的redo时，顺便可以将一些未提交的事务redo也一次性刷写到磁盘，此时为了区分不同状态的redo，会加一些特殊的标记(是否提交标记)

2. undo logs

(1) undo是什么
	顾名思意，“回滚日志”，是事务日志的一种，存储的是事务的反操作，属于逻辑日志
(2) 作用是什么
	在事务的ACID过程中，实现的是"A"原子性的作用，对于CI也有相应的作用
	在rollback时，会将数据恢复到修改之前的状态
	在CSR实现的是，将redo当中记录的未提交的时候进行回滚，先做redo前滚，在做undo回滚
	undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

3. 事务的隔离级别

3.1 数据库并发会引起的问题

• 脏读：A事务读取B事务尚未提交的更改数据，并在这个数据基础上操作。如果B事务回滚，那么A事务读到的数据根本不是合法的，称为脏读
• 不可重复读：A事务读取了B事务已经提交的更改（或删除）数据。比如A事务第一次读取数据，然后B事务更改该数据并提交，A事务再次读取数据，两次读取的数据不一样
• 幻读：A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别，这里是新增，不可重复读是更改（或删除）
• 第一类丢失更新：A事务撤销时，把已提交的B事务的数据覆盖掉
• 第二类丢失更新：A事务提交时，把已提交的B事务的数据覆盖掉

在MariaDB/MySQL中不会出现更新丢失的问题，因为独占锁一直持有直到事务结束。当1个会话开启事务A修改某记录，另一个会话也开启事务B修改该记录，该修改被阻塞，当事务A提交后，事务B中的更新立刻执行成功，但是执行成功后查询却发现数据并没有随着事务B的想法而改变，因为这时候事务B更新的那条记录已经不是原来的记录了。但是事务A回滚的话，事务B是可以正常更新的，但这没有丢失更新。

3.2 事务的隔离级别

-- 查看当前会话隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

3.2.1 read-uncommitted

读未提交，一个事务还没提交时，它做的变更就能被别的事务看到，即允许读取未提交的数据，会导致 脏读、不可重复读、幻读 的问题

在会话1执行：

create table ttt(id int);
insert into ttt select 1;
insert into ttt select 2;
begin;
update ttt set id=10 where id=1;

在会话2执行：

set transaction isolation level read uncommitted;
select * from ttt;
+------+
| id   |
+------+
|   10 |
|    2 |
+------+

发现查询的结果是update后的数据，但是这个数据是会话1未提交的数据。这是脏读的问题，即读取了未提交的脏数据。

如果此时会话1进行了回滚操作，那么会话2上查询的结果又变成了id=1。

在会话1上执行：

rollback;

在会话2上查询：

mysql> select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

这是读不一致问题。即同一个会话中对同一条记录的读取结果不一致

3.2.2 read-committed

读已提交，一个事务提交之后，它做的变更才会被其他事务看到，这里的读指的是 一致性非锁定读 ，即每次都读最新的快照数据，不加共享锁，解决了脏读问题，但仍会导致 不可重复读、幻读 问题

当前示例表ttt的记录如下：

mysql> select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

在会话1中执行：

begin;
update ttt set id=100 where id=1;

在会话2中执行：

set @@session.tx_isolation='read-committed';
begin;
select * from ttt;

会话2中查询得到的结果为id=1，因为查询的是最新提交的快照数据，而最新提交的快照数据就是id=1

所以该级别解决了脏读问题

mysql> select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

现在将会话1中的事务提交。

在会话1中执行：

commit;

在会话2中查询记录：

select * from ttt;
+------+
| id   |
+------+
|  100 |
|    2 |
+------+

结果为id=100，因为这个值是最新提交的，这就是不可重复读问题

3.2.3 repeatable-read

可重复读，MySQL的默认隔离级别，总是会在事务开启的时候读取最新提交的行版本，并将该行版本一直持有到事务结束，未提交变更对其他事务也是不可见的，解决了不可重复读问题，但仍有可能发生幻读问题

当前示例表ttt的记录如下：

mysql> select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

在会话1执行：

begin;
update ttt set id=100 where id=1

在会话2中执行：

set @@session.tx_isolation='repeatable-read';
begin;select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

查询的结果和预期的一样，来自开启事务前最新提交的行版本数据。

回到会话1提交事务：

commit;

再回到会话2中查询：

select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

再次去会话1更新该记录：

begin;
update ttt set id=1000 where id=100;
commit;

再回到会话2执行查询：

select * from ttt;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+

发现结果根本就不会改变，因为会话2开启事务时获取的行版本的id=1，所以之后读取的一直都是id=1所在的行版本

但若在会话1将所有记录修改

begin;
alter ttt set id=6;

在会话2添加一条记录并提交

begin;
insert into ttt values(66);
commit;

此时会话1提交并查询：

commit;
select * from ttt;
+------+
| id   |
+------+
|    6 |
|    6 |
|   66 |
+------+

会发现多了一条记录没有被修改到，这就是幻读问题

3.2.4 serializable

可串行化，这种隔离级别不会造成任何并发问题，但并发性能极低

3.2.5 避免幻读

• Gap Lock：间隙锁，加锁后如果更新的范围会影响到间隙锁的范围，则操作会被挂起，直到间隙锁释放或等待超时，针对事务隔离级别为可重复读或以上
• Next-Key Lock：行锁+间隙锁=下键锁

默认情况下，InnoDB工作在可重复读隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录

4. 事务的一致性问题总结

• A：原子性，UNDO、REDO
• D：持久性，REDO(WAL机制)
• I：隔离性，ISOLATION LEVEL、Lock、MVCC(UNDO)
• C：一致性，以上所有特性都是用来保证一致性的
  • 写一致性：REDO、UNDO、LOCK
  • 读一致性：ISOLATION LEVEL、MVCC(UNDO)
  • 数据页一致性：DOUBLE WRITE BUFFER

核心参数

• redo log刷写时机参数

innodb_flush_log_at_trx_commit
	1：默认值，在每次事务提交时，会立即刷新redo到磁盘，commit才能成功
	0：每秒刷新redo buffer到os cache，再fsync到磁盘，异常宕机时，有可能丢失1s内的事务
	2：每次事务提交，都立即刷新redo buffer到os cache，再每秒fsync到磁盘，磁盘异常宕机时，有可能丢失1s内的事务

• 文件系统缓冲参数

innodb_flush_method
	null：默认值，在unix-like系统中体现为fsync
		buffer poll的数据写磁盘时需要先经历os buffer然后再写到磁盘
		redo buffer的数据写磁盘时需要先经历os buffer然后再写到磁盘
	O_DSYNC：
		buffer poll的数据写磁盘时需要先经历os buffer然后再写到磁盘
		redo buffer的数据写磁盘时跨过os buffer直接写到磁盘
	O_DIRECT:建议使用
		buffer poll的数据写磁盘时跨过os buffer直接写到磁盘
		redo buffer的数据写磁盘时需要先经历os buffer然后再写到磁盘

• 数据缓冲区大小参数

innodb_buffer_size
	缓冲数据页和索引页，是MySQL最大的内存区域
	默认：128M
	官方建议：80~90%
	生产建议：低于75％，按需调配