mysql事务、日志

一、事务

1、操作事务

1.1、手动提交

set autocommit=0:当前session禁用自动提交事物.

begin或start transaction：显式的开启一个事务.

commit：提交事务，并使已对数据库进行的所有修改成为永久性的。

rollback：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改.

1.2、自动提交

set autocommit=1开启自动提交

MySQL的每⼀条DML(增删改)语句都是⼀个单独的事务，每条语句都会⾃动开启⼀个事务，执⾏完毕⾃
动提交事务，MySQL默认开始⾃动提交事务

1.3、回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前⾯操作都已经成功，可以在当前成功的位置设置⼀个回滚点。
可以供后续失败操作返回到该位置，⽽不是返回所有操作，这个点称之为回滚点。

设置回滚点语法： savepoint 回滚点名字

回到回滚点语法：rollback to 回滚点名字

总结：设置回滚点可以让我们在失败的时候回到回滚点，⽽不是回到事务开启的时候。

2、隔离级别

2.1、查看

show variables like '%isolation%'

2.2、设置

set global transaction isolation level '级别字符串'

2.3、级别对比

名字	隔离级别	脏读	不可重复读	幻读	实现	默认级别
读未提交	READ UNCOMMITTED	√	√	√	每次都读取最新的数据
读已提交	READ COMMITTED	×	√	√	每次读取前都创建read view， 通过mvcc控制	Oracle、Sql Server
可重复读	REPEATABLE READ	×	×	√	第一次读取之前创建read view， 通过mvcc控制	Mysql
串行化	SERIALIZABLE	×	×	×	加锁

2.4、不同级别出现的问题

脏读

一个事务读到了另一个事务未提交的事务

不可重复读

一个事务多次读取数据结果不一致

幻读

一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。

图中第四步的update是当前读，更新了数据的同时也更新了隐藏字段的DB_TRX_ID，之后在进行select快照读时，此数据对于当前事务课件，所以会将该数据读出。

如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读。

时间	事务1	事务2
	begin;
T1	select * from user where age =20 for update;
T2		insert into user values(25,’25’,20);此时会阻塞等待锁
T3	select * from user where age =20 for update;

此时，可以看到事务2被阻塞了，需要等待事务1提交事务之后才能完成，其实本质上来说采用的是间隙锁的机制解决幻读问题。

2.5、如何实现？

读未提交，每次都读取最新的数据
读已提交，每次读取之前创建read view，通过mvcc控制
可重复读，第一次读取才创建read view，通过mvcc控制
序列化，加锁

​ 总的来说，数据库的隔离级别依靠MVCC和锁实现；而MVCC依靠隐藏字段、read view、undo log实现。

3、事务四大特点

事务特性	含义	实现
A-原子性	事务中的多条SQL语句是⼀个整体，不可再分割， 一起成功，一起失败。	通过undo log来保证
C-一致性	事务前后的数据要保证⼀致	通过原子性、隔离性、持久性来保证
I-隔离性	⼀个事务执⾏的结果不能影响另⼀个事务。	通过mvcc、间隙锁来保证
D-持久性	事务⼀旦提交，对数据库的数据影响是永久了。	通过redo log来保证

---

二、log

1、bin log（二进制日志、归档日志）

server层面的日志，属于逻辑日志，以二进制形式记录相关语句的原始逻辑，没有crash-safe能力

2、redo log（重做日志）

• 是innodb存储引擎的日志，当发生数据修改时，先写到redolog中并更新内存；
• 该文件的大小是固定的，所以当记录满后从头循环继续写；
• 有了redolog日志，可以保证异常重启时数据不丢失,具有crash-safe能力

checkpoint，在检查点之前的日志已经被记录到磁盘并擦除；
write post表示当前写的位置；
当两者相遇表示redo log日志已经写满，此时数据库停止进行相关的语句更新，转而进行redo log日志同步到磁盘的操作

redo log刷盘

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

如右图所示，redo log的刷盘存在三种方式，可以通过innodb_flush_log_at_trx_commit参数进行调整，主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2

• 值0：在提交事务时，InnoDB不会立即触发将缓存日志写到磁盘文件的操作，而是等待后台线程每秒将缓存日志回写入文件缓存系统(OS Cache)，并fsync()刷盘。

  为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。

• 值1：在事务提交时，InnoDB立即将缓存日志写入文件缓存系统(OS Cache)，并fsync()刷盘。

  为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

  如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。

• 值2：在每个事务提交时，InnoDB立即将缓存中的redo日志回写到文件缓存系统(OS Cache)，但后台线程刷盘。

  为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。

  如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

执行SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'命令可知，mysql默认值为1

总结如下：

默认设置为1，这是为了保证数据的持久性。当然，我们可以将这个配置项设置为1以外的值来换取更高的性能，但是在系统崩溃的时候，将会丢失1秒的数据。

设置为0的话，Mysql进程崩溃的时候，就会丢失最后1秒的事务。

设置为2的话，只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

3、relo log两阶段提交

流程

a、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
b、执行器拿到数据之后会先修改数据，然后调用引擎接口重新写入数据
c、引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
d、执行器生成这个操作的bin log
f、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成

问题

先写redo log后写bin log

假设在redo log写完，bin log还没写完的时候，mysql进程异常启动。
由于redo log写完之后，系统即使崩溃，仍然能把数据恢复回来，所以恢复后这一行数据值。
但是由于bin log没写完就crash了，这时候bin log里面没有记录这个语句。
因此，备份日志的时候，存起来的bin log里面就没有这条语句。
之后如果使用这个bin log来恢复时，由于这条语句的丢失，恢复过来的数据会缺少此次的修改，与原数据值不同。

先写bin log后写redo log

如果在bin log写完之后creash，由于redo log还没完，崩溃恢复以后这个事务无效，所以这样记录的值没有恢复。
但是bin log里面已经记录了此次修改。
在之后用bin log来恢复时就多出了一个事务，恢复的数据多出了这个值，与原库不一致

4、bin log和redo log区别

redo log是属于innoDB层面；
binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

redo log是物理日志，记录该数据页更新的内容；
binlog是逻辑日志，记录的是这个更新语句的原始逻辑

redo log是循环写，日志空间大小固定；
binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

binlog在事务提交后才会写入，可以作为恢复数据使用，主从复制搭建，在事务进行中数据修改前写入，然后才对缓存中的数据进行修改;
redo log作为异常宕机或者介质故障后的数据恢复使用。

5、undo log（回滚日志）

备份数据，实现事务的回滚操作保证原子性，当出现错误或者执行rollback语句，系统利用undo log中的备份数据恢复到事务开始之前的状态
提供innodb中实现MVCC功能
undo log采用回滚段方式记录，5.5之后每个回滚段中有1024个undo log segment
当删除时不会从undo log直接删除，只会记录delete_flag，之后再由purge线程判断是否删除

---

三、MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。
MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

当前读

像select lock in share mode(共享锁), select...for update; update, insert ,delete(排他锁)这些操作都是一种当前读;

为什么叫当前读？
就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；
之所以出现快照读的情况，是基于提高并发性能的考虑;
快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；
既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当前读、快照读、MVCC关系

MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。
MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。

3个隐式字段

DB_TRX_ID：6byte,最近修改事务ID，记录最后一次操作（首次创建、最后一个更新）这条记录的事务id
DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment）
DB_ROW_ID：6byte，隐藏主键，当表没有主键或者唯一键时，会自动创建该隐藏的自增id

read view

概念

• Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。

• 其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据

• Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID（当前事务id）取出来，与系统当前其他活跃事务的id去对比，如果DB_TRX_ID跟Read View的属性做了比较，不符合可见性，那么就通过DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较，即遍历链表中的DB_TRX_ID，直到找到满足条件的DB_TRX_ID，这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。

read view生成时机

RC-读已提交：每次读取数据前，都生成read view
RR-可重复读：在第一次读取数据前，生成read view

• 在RR（可重复读）级别下的某个事务的对某条记录的第一次快照读会创建一个快照即Read View，将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View；只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见

• 在RR（可重复读）级别下，快照读生成Read View时，Read View会记录此时所有其他活动和事务的快照，这些事务的修改对于当前事务都是不可见的，而早于Read View创建的事务所做的修改均是可见

• 在RC（可重复读）级别下，事务中，每次快照读都会新生成一个快照和Read View,这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。

​ 总结：在RC（可重复读）隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR（可重复读）隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View.

Read View的全局属性

​ trx_list：一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID

​ up_limit_id：记录trx_list列表中事务ID最小的ID，以下用min_trx_id 来表示方便理解

​ low_limit_id：Read View生成时刻系统尚未分配的下一个事务ID，以下用max_trx_id来表示方便理解

​ create_trx_id：当前事务id

数据可见性判断

​ 情况一：DB_TRX_ID与create_trx_id相等

• 表明该数据是当前事务修改的记录

​ 当出现数据事务id不等于当前事务id时，根据undo log中的数据版本链往上追溯进行判断

​ 情况二：DB_TRX_ID <min_trx_id

• 当前DB_TRX_ID(记录的事务id)小于min_trx_id(最小活跃事务id)，这表明这条记录在其他活跃事务事前已经提交，所以数据对于当前事务可见

情况三：DB_TRX_ID >= max_trx_id

• 当前DB_TRX_ID (记录的事务id)大于max_trx_id(未分配事务id)，这表明在当前事务开启之后又有新的事务开启，并且修改提交了该数据，所以数据对于当前事务不可见

情况四：min_trx_id <= DB_TRX_ID < max_trx_id，数据在两者之间需要分情况分析

• 在区间内，DB_TRX_ID 也在trx_list中，那么当前事务不能读取。这表明该行数据是与当前事务一起开启的事务提交后生成的数据，所以数据对当前事务不可见

• 在区间内，但是DB_TRX_ID 不在trx_list中，在区间内表明之前存在多个开启事务，不在DB_TRX_ID 表明在之前的多个热点事务中，id处于中间的某个事务提交，所以当前的事务读取的行数据为之前提交的事务数据，所以数据对于当前事务可见

• 总得来说就是判断DB_TRX_ID 是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，未修改的数据，当前事务也是看不到；如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。

purge与read view

​ 为了节省磁盘空间，innodb有专门的purge线程来清理delete_bit为true的记录。为了不影响mvcc的正常工作，purge线程自己维护一个read view；如果某个记录的delete_bit为true，同时它的DB_TRX_ID（事务id）存在于purge线程的read view中，那么该记录可以被安全删除