MySql-05-事务

MySql Comments

概述

InnoDB存储引擎中的事务完全符合ACID特性：

  1. 原子性(atomicity)：整个事务是不可分割的工作单位，任何一个SQL语句执行失败，已经执行成功SQL语句也必须撤销，数据库状态退回到执行事务前的状态。默认情况下一条SQL就是一个单独事务，事务是自动提交的。只有显式的使用start transaction开启一个事务，才能将一个代码块放在事务中执行
  2. 一致性(consistency)：一致性事务指数据库从一个状态转变为下一种一致的状态。事务开始前和事务结束后，数据库完整性并没有被破坏。如事务执行后数据库唯一约束被破坏，则自动撤销事务，返回初始化状态。
  3. 隔离性(isolation)：事务的隔离性要求事务提交前对其他事务不可见
  4. 持久性(durability)：事务一旦提交，其结果就是永久性的。即使数据库崩崩溃而恢复，提交的数据也不会丢失

事务的分类：

  • 隐式事务：事务没有明显的开始和结束的标记。MySql的每一条DML(增删改)语句都是一个单独的事务，每条语句DML执行完毕自动提交事务。MySql默认自动提交事务 SHOW VARIABLES LIKE 'autocommit';
  • 显示事务：事务具有明显的开启和结束的标记，前提是必须设置自动提交功能为禁用
    • 开启事务 - 执行SQL语句 - 成功 - 提交事务
    • 开启事务 - 执行SQL语句 - 失败 - 回滚事务

还能设置回滚点：

1
2
savepoint <回滚点名字>
rollback to <回滚点名字>

以 update test set name = 'test' where id=2;为例事务的执行流程

  1. 事务开始

  2. 申请锁资源，对id=2这行数据上排他锁

  3. 将需要修改的data pages读取到innodb_buffer_cache

  4. 记录id=2的数据到undo log

  5. 记录id=2修改后的数据到redo log buffer

  6. 将buffer cache中id=2得name改为test

  7. commit，触发二阶段提交2pc

  8. 事务结束

WAL(write ahead logging):针对数据文件的修改，必须遵循日志先行原则。也即是将数据持久化到磁盘之前必须确保redo log落盘。

二阶段提交(2pc two phase commit):

  • 首先，redo log prepare，redo持久化到磁盘(redo group commit)，并将回滚段置为prepared状态，此时binlog不做操作
  • 然后写入 binlog
  • 最后redo log commit，innodb释放锁，释放回滚段，设置undo log提交状态，binlog持久化到磁盘，然后存储引擎层提交

主要是保证redo log事务写入顺序和binlog 事务顺序一致(通过事务id保证一致)。

隔离级别

并发可能产生的问题：

  • 脏读 dirty read: 读到其他事务未提交的数据(针对其他事务未提交的操作)
  • 不可重复读 non-repeatable read: 前后读取的记录内容不一致(针对其他事务修改或删除的操作)
  • 幻读 phantom read: 前后读取的记录数量不一致(针对其他事务新增的操作)

针对解决上述问题，将事务的隔离级别分为(由低到高)，假设两个事务按照下面的流程执行，初始记录 name = A

时刻 事务A 事务B
1 begin;
2 begin;
3 select name from user where id = 1;
4 select name from user where id = 1;
5 update user set name=‘B’ where id = 1
6 select name from user where id = 1;(N1)
7 commit;
8 select name from user where id = 1;(N2)
9 commit;
10 select name from user where id = 1;(N3)

  • 读未提交: 一个事务未提交，改动能被另一个事务看到 => 都执行。执行结果，N1 = B、N2 = B、N3 = B

  • 读提交: 一个事务提交了，改动才能被另一个事务看到 => 没有赃读。执行结果，N1 = A、N2 = B、N3 = B

  • 可重复读(默认级别): 一个事务提交了，改动也不能被另一个事务看到 => 只有幻读。执行结果，N1 = A、N2 = A、N3 = B

    为什么N1 与 N2 都是A，事务在执行期间看到的数据前后必须一致

  • 串行化 serializable: 后访问的事务必须等待前一个事务执行完成才能访问 => 都解决。事务A的执行结果，N1 = A、N2 = A、N3 = B

事务的隔离级别规定了一个事务中所做的修改，在事务内和事务间的可见性。较低级别的隔离通常可以执行更高的并发，系统开销也更低

隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED (读取未提交) 可能发生 可能发生 可能发生
READ-COMMITTED (读已提交) 解决 可能发生 可能发生
REPEATABLE-READ (可重复读) 解决 解决 可能发生
SERIALIZABLE (可串行化) 解决 解决 解决

修改隔离级别语句：

1
2
3
4
5
6
set session transaction isolation level read uncommitted; #读未提交
set session transaction isolation level read committed;   #读已提交
set session transaction isolation level repeatable read;  #可重复读
set session transaction isolation level serializable;		  #可串行化
set autocommit = 0;																				#取消自动提交
select @@tx_isolation; 																		#查询隔离级别

事务的基础

数据库的隔离性包括(ACID)，由不同的功能实现

  • Atomicity 原子性: 使用 undo log 实现回滚
  • Consistency 一致性: 通过原子性、持久性、隔离性实现数据一致性
  • Lsolation 隔离性: 使用锁以及MVCC 实现读写分离、读读并行、读写并行
  • Durability 持久性: 通过redo log 恢复，和在并发环境下的隔离做到一致性

redo log 称为重做日志，是物理日志，记录页的物理修改操作，用来恢复提交事务修改的页操作

undo log 称为回滚日志，是逻辑日志，根据每行记录进行记录。用来回滚行记录到某个特定版本

Redo Log

redo log 也做重做日志，日志文件由两部分组成：

  • 重做日志缓冲 redo log buffer
  • 重做日志文件 redo log file
image-20210624074448450 
1
2
3
4
5
6
7
start transaction;
select balance from bank where name="zhangsan";
#生成 重做日志 balance=600 
update bank set balance = balance - 400; 
# 生成 重做日志 amount=400 
update finance set amount = amount + 400;
commit;
image-20210725131644420

为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步，如果还没有来得及同步就宕机怎么办？

通过Force Log at Commit机制实现事务持久性，即当事务提交(COMMIT)时，必须先将该事务的所有日志写入到重做日志进行持久化，待事务的COMMIT操作完成才算完成

为了确保每次日志都写入重做日志，在每次将重做日志缓冲写入重做日志文件后，InnoDB存储引擎还需要调用一次fsync操作。由于重做日志文件打开并没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存，为了确保重做日志写入磁盘，必须进行一次fsync操作

系统重启之后在读取redo log恢复最新数据

通过 innodb_flush_log_at_trx_commit 来控制充足哦日志刷新到磁盘的策略

  • 1：事务提交时必须调用一次fsync操作(默认)
  • 0：事务提交时不进行写入重做日志，而是通过master thread 每1秒进行一次重做日志文件的fsync操作
  • 2： 事务提交时将重做日志写入重做日志文件，但仅仅写入文件系统缓存，不进行fsync操作

与二进制日志 binlog的区别是：

  1. redo log 是在InnoDB存储引擎层产生的，而二进制文件是服务层产生的

  2. binlog记录的是逻辑日志，记录的是对应的SQL日志。而redo log是物理日志，记录的是对每个页的修改

  3. 存入磁盘的时间点不一样

    • binlog 只在事务提交完成后进行一次写入

    • redo log在事务进行中不断地被写入，表现为日志并不是随事务提交的顺序进行写入的

      image-20210718161424316

总结：redo log是用来恢复数据的 用于保障，已提交事务的持久化特性

undo log

undo 也叫做回滚日志，为了回滚需要将之前的操作都记录下来

  • 每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log
  • 当发生系统错误或执行回滚的时候使用undo log
image-20210725132750501

undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态

(1) 如果在回滚日志里有新增数据记录，则生成删除该条的语句

(2) 如果在回滚日志里有删除数据记录，则生成生成该条的语句

(3) 如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句

总结：undo log是用来回滚数据的用于保障 未提交事务的原子性

MVCC

MVCC是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间，存储的不是实际的时间值而是系统版本号，主要实现思想是通过数据多版本来做到读写分离。从而实现不加锁读进而做到读写并行

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

  • undo log: unlog 中就某行数据的多个版本数据
  • read view: 用来判断当前版本数据的可行性
image-20210725152834866

原子性的实现

一个事务必须被视为不可分割的最小工作单位，一个事务中的所有操作要么全部成功提交，要么全部失败回滚，对于一个事务来说不可能只执行其中的部分操作，这就是事务的原子性，通过上述undo log 即可以实现

持久性的实现

事务一旦提交，其所作做的修改会永久保存到数据库中，此时即使系统崩溃修改的数据也不会丢失

InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用

读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池

写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中

如果数据已提交，但在缓冲池里还未来得及磁盘持久化，需要一种机制保存已提交事务的数据，为恢复数据使用。redo log 即可解决这个问题，既然redo log也需要存储，也涉及磁盘IO为啥还用它？

  • redo log 的存储是顺序存储，而缓存同步是随机操作
  • 缓存同步是以数据页为单位的，每次传输的数据大小大于redo log

隔离性的实现

读未提交

读未提交可以理解为没有隔离

串行话(读写锁)

串行话读的时候加共享锁，其他事务可以并发读但不能写。写的时候加排它锁，其他事务不能并发写也不能并发读

可重复读

MVCC:多版本并发控制，通过undo log版本链和read-view实现事务隔离

以可重复读为例 每条记录在更新时除了记录一条变更记录到redo log中，还会记录一条变更相反的回滚操作记录在undo log中

例如一个值从1被按顺序改成了2、3、4，在回滚日志里就会有如下记录：

image-20210624065626343

当前值是4，但查询这条记录的时候，不同时刻启动的事务有不同的read-view

  • 在视图 A、B、C 里面，这一个记录的值分别是 1、2、4
  • 对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到
  • 即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的

一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。

另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图(也叫快照)，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现

日志什么时候删除？

日志怎么存储

长事务导致undolog一致存在不被删除，什么是长事务

什么是视图、MVCC

对于一个视图(快照)来说，它能够读到那些版本数据，要遵循以下规则：

  1. 当前事务内的更新，可以读到；
  2. 版本未提交，不能读到；
  3. 版本已提交，但是却在快照创建后提交的，不能读到；
  4. 版本已提交，且是在快照创建前提交的，可以读到；

可重复读和读提交的区别在于：在快照的创建上，可重复读仅在事务开始是创建一次，而读提交每次执行语句的时候都要重新创建一次

什么时候删除回滚日志undo-log

  • 当没有比回滚日志更早的读视图（读视图在事务开启时创建）的时候，这个数据不会再有谁驱使它回滚了，这个回滚日志也就失去了用武之地，可以删除了
  • ibdata文件是共享表空间数据文件。 5.7版本支持单独配置undo log的路径和表空间文件。 为什么回滚到清理后，文件还是不会变小？这个“清理”的意思是 “逻辑上这些文件位置可以复用”，但是并没有删除文件，也没有把文件变小。那到底什么时候删除呢？

不同的事务拥有不同的Read view，如果一个事务长时间没有提交，意味着会存在很多老的事务视图，同时也保存了大量回滚日志，占用磁盘空间，且在mysql5.5之前，回滚日志和字典都保存在ibdata文件中，即使长事务被提交了，回滚段被清理，文件也不会变小。同时长事务还长期占用锁资源，降低并发效率

幻读

并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。

假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30

img

如图所示，分成了3 个区间，(负无穷,10]、(10,30]、(30,正无穷]，在这3个区间是可以加间隙锁的。

之后，我用下面的两个事务演示一下加锁过程

img

在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。当事务A执行update user set name='风筝2号’ where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录页无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。

这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入

参考链接

  1. https://time.geekbang.org/column/article/68963
  2. https://blog.csdn.net/youanyyou/article/details/108722263
  3. https://blog.csdn.net/kongliand/article/details/107953656
  4. 《MySQL技术内幕》