MySQL高级之事务

最新推荐文章于 2024-06-20 16:05:59 发布

原创最新推荐文章于 2024-06-20 16:05:59 发布 · 207 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

19 篇文章

订阅专栏

概念

事务是访问并更新数据库中各种数据项的一个程序执行单元。可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都做修改，要么都不做。

特性

原子性(Atomicity) :事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) :在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
隔离性(Isolation) :数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) :事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

事务的并发问题

脏读

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B 事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了。即：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。即：事务A读取到了事务B提交的新增数据，不符合隔离性

这里幻读需要注意，由于MySQL中的Mvcc控制，需要在select XXX 后for update 或直接执行update 操作才能复现幻读。这是因为select读的数据库的快照版本(即会话在开启事务并执行select时的版本)，而for update 或 update读的是当前版本(即数据库实际状态)如下图的执行流程

时刻	会话1	会话2
T1	begin
T2		begin
T3	SELECT * FROM t1 WHERE b = 10; --
T4		INSERT into t1 (a,b) VALUES(10001,10)
T5		COMMIT;
T6	SELECT * FROM t1 WHERE b = 10 for update; 或 UPDATE t1 SET a = 3 WHERE b = 10; -- 看到最新插入的(10001,10)
T7	COMMIT

隔离级别

隔离级别	脏读(Dirty Read)	不可重复读 (NonRepeatable Read)	幻读(Phantom Read)
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(Repeatable read)	不可能	不可能	可能
串行化(Repeatable read)	不可能	不可能	不可能

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

提交过程

事务的提交主要讨论update更新操作，更新操作涉及两个重要的日志模块，redo log(重做日志) 和binlog(归档日志)。重做日志用来实现事务的持久性，即事务ACID中的D，一是内存中的重做日志缓存(redo log buffer)，其是易丢失的，二是重做日志文件(redo log file)，InnoDB是事务的存储引擎，其通过Force Log at Commit 机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的COMMIT操作完成才算完成。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做（这样做的好处是redo log是磁盘顺序写效率高，直接更改数据库是随机IO,性能较低）。

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。有了redo log, InnoDB可以保证即使数据库发生异常重启，之前的提交记录都不会丢失，在这个能力称为crash-safe;

为了保证每次日志都写入redo log file中，InnoDB存储引擎m都需要调用一次fsync操作，参数innodb_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略，为0时表示每秒将buffer 刷新到磁盘日志文件中，为1时表示每次提交都写入磁盘日志文件，为2时表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存，不进行fsync操作，这种设置会导致重启数据库后丢失未从文件系统缓存刷新到重做日志文件那部分事务。

InnoDB中海油一个重要的日志模块，binlog他与redo log有如下异同

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

这里我们来看下一个update语句的执行流程；

1.执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2.执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3.引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4.执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5.执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

如上图所示，redo log采用了两阶段提交，prepare 和commit ,这样做的目的是为了保证两份日志的逻辑一致。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

先写 redo log 后写 binlog。

假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

先写 binlog 后写 redo log。

如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

总结：1 prepare阶段 2 写binlog 3 commit，当在2之前崩溃时重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。一致。当在3之前崩溃重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致。