标签：binlog 事务 log 数据库 笔记 提交 MySQL 日志 redo

MySQL 之 事务篇

---

事务

　　事务是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

事务的四大特性（ACID）：

• A 原子性：原子性是指包含事务的操作要么全部执行成功，要么全部失败回滚。
• C 一致性：一致性指事务在执行前后状态是一致的。
• I 隔离性：一个事务所进行的修改在最终提交之前，对其他事务是不可见的。
• D 持久性：数据一旦提交，其所作的修改将永久地保存到数据库中。

如何保证ACID？　

原子性由 undo log 日志保证，它记录了需要回滚的日志信息，事务回滚时撤销未提交的 SQL 语句；

一致性一般由代码层面来保证；

隔离性由 MVCC 来保证；

持久性由内存+ redo log 来保证，mysql 修改数据同时在内存和 redo log 记录这次操作，事务提交的时候通过 redo log 刷盘，宕机的时候可以从 redo log 恢复。

 

事务的使用

　　MySQL默认是自动提交模式，用户执行的每一条SQL语句都会被当成单独的事务自动提交。如果要将一组SQL语句作为一个事务，要经过以下步骤：

一、显式开启事务

BEGIN;
-- 或者
START TRANSACTION;

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

1. READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
2. READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。
3. WITH CONSISTENT SNAPSHOT ：启动一致性读。

二、输入一组SQL语句

三、提交事务 或 回滚事务

-- 提交事务。当提交事务后，对数据库的修改是永久性的。
COMMIT;

-- 回滚事务。即撤销正在进行的所有没有提交的修改
ROLLBACK;
-- 将事务回滚到某个保存点。
ROLLBACK TO SAVEPOINT 保存点名;

 

 关于保存点：

• 通过以下指令设置保存点：

SAVEPOINT 保存点名;

• 删除保存点

RELEASE SAVEPOINT 保存点名;

 

 注意：显式开启事务会暂时关闭自动提交模式，当然，用户也可以手动关闭：

-- 1 表示开启自动提交，0 表示关闭自动提交
SET AUTOCOMMIT =0;

　　关闭后，事务的提交只能手动 commit

 若要查看提交模式，可用以下指令：

SELECT  @@autocommit;

 

---

事务日志和bin 日志

　　MySQL 日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是 binlog 和 redo log 和 undo log。

bin 日志

　　binlog 是 MySQL 数据库级别的文件，记录对 MySQL 数据库执行修改的所有操作，不会记录 select 和 show 语句，主要用于数据备份和主从复制。

有3种格式：

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现。

STATEMENT 有动态函数的问题，比如用了 uuid 或者 now 这些函数，在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致。

• ROW：记录行数据最终被修改成什么样了，不会出现 STATEMENT 下动态函数的问题。

ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句。

• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式。

写入过程

1. 事务执行过程中将日志记录到 binlog cache（系统为binlog分配了一块内存，每个线程一份）
2. 事务提交时，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

binlog 刷盘时机

write：把日志写到文件系统的 page cache，没有写磁盘，速度快

fsync：将数据持久化到磁盘的操作，这时才占磁盘IOPS（刷盘）

MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的时机：

• sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
• sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
• sync_binlog = N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的，因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。

 

redo 日志

　　redo log 是 innodb 引擎级别，用来记录 innodb 存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innodb 存储引擎会使用 redo log 恢复到发生故障前的时刻，以此来保证数据的完整性。

格式

 　redo log包括两部分：

1. 内存中的日志缓冲 (redo log buffer)，
2. 磁盘上的日志文件 (redo log file)。

写入过程

redo log buffer 写入 redo log file 的过程，实际上是先写到 page cache上（write），然后再刷到redo log file中 （fsync）

1. mysql 每执行一条 DML 语句，先将记录写入redo log buffer
2. 待到刷盘时机，再写到 redo log file 中

• • redo log file 由两个文件 ib_logfile0 和 ib_logfile1 组成，循环写入
  • 先写 logfile0 文件，当 logfile0 文件被写满的时候，会切换至 logfile1 文件，当 logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

redo log 刷盘时机

1. log buffer 的大小是有限的（通过系统变量 innodb_log_buffer_size 指定），如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。InnoDB 设计者认为如果当前写入 log buffer 的日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。
2. MySQL 提供一个 innodb_flush_log_at_trx_commit 参数来控制：

• • 当设置为1时：每次事务提交都要做一次刷盘，这是最安全的配置，即使宕机也不会丢失事务；是 InnoDB 的默认值（实时write，实时fsync）
  • 当设置为2时：每次事务提交都会把 log buffer 的数据写入文件系统的 page cache，但是刷盘操作并不会同时进行，而是每秒执行一次刷盘操作（实时write，延迟fsync）
  • 当设置为0时：每秒钟将 log buffer 中的数据更新到磁盘中。因此实例崩溃将最多丢失1秒钟内的事务。（延迟write，延迟fsync）

 

binlog 和 redo log 的关系

binlog 和 redo log 的区别

　　适用对象不同：binlog 是 MySQL 的 Server 层实现的，所有存储引擎都可以使用；redo log 是 Innodb 存储引擎实现的日志。

　　文件格式不同：redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；binlog是逻辑日志，记录的是sql语句。

　　写入方式不同：binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

　　用途不同：binlog 用于备份恢复、主从复制；redo log 用于数据恢复。

为什么不用 binlog 做数据恢复？

　　原因一：binlog 是逻辑日志，使用 binlog 恢复数据，需要解析日志，且无法确定哪些日志刷盘了，哪些没刷盘。但是用 redo log 恢复数据的话，就不需要再去解析日志的操作，在 redo log 里的都是没刷盘的，直接对数据页进行操作，速度会更快。

　　原因二：只有完整提交的事务才会在 binlog 里。如果一个事务 在执行完之前就宕机了，一部分的脏数据可能会写入到磁盘中，重启的时候就需要把这部分未提交的脏数据给回滚掉；而仅依靠 binlog 没有办法回滚这部分的数据，需要和 redo log 配合回滚。  

undo 日志

　　当进行数据修改时会记录 undo log，undo log 用于数据的撤回操作，它保留了记录修改前的内容。通过 undo log 可以实现事务回滚，并且可以根据 undo log 回溯到某个特定的版本的数据。

格式

　　undo log的存储由InnoDB存储引擎实现，数据保存在InnoDB的数据文件中。在InnoDB存储引擎中，undo log是采用分段(segment)的方式进行存储的。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。在MySQL5.5之前，只支持1个rollback segment，也就是只能记录1024个undo操作。在MySQL5.5之后，可以支持128个rollback segment，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成，即总共可以记录128 * 1024个undo操作。

　　undo log日志里面不仅存放着数据更新前的记录，还记录着RowID、事务ID、回滚指针。其中事务ID每次递增，回滚指针第一次如果是insert语句的话，回滚指针为NULL，第二次update之后的undo log的回滚指针就会指向刚刚那一条undo log日志，依次类推，就会形成一条undo log的版本链，方便找到该条记录的历史版本：

日志类型

写入过程

 如何刷盘？

 　　通过 redo log 刷盘：InnoDB 将 undo log 看作数据，因此记录 undo log 的操作也会记录到 redo log 中，随着 redo log 一起刷盘。

 

---

多线程事务

并发一致性问题：

 当多个事务并发执行时，有三种并发关系：

1. 读-读并发
2. 读-写并发
3. 写-写并发

 这三种并发关系中，读-读并发不存在并发问题，而读-写并发和写-写并发可能会出现以下问题：

• 脏读：事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后的数据，然后事务A回滚了，事务B读取到的数据就成为脏数据了。属于读-写并发问题。
• 不可重复读：事务A对数据进行多次读取（期间没有提交），事务B在事务A多次读取的过程中执行了更新操作并提交了，导致事务A多次读取到的数据并不一致。属于读-写并发问题。
• 幻读：事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时发现多了几条数据，和之前读取的数据不一致。属于读-写并发问题。
• 丢失修改：属于写-写并发问题。
  • 第一类丢失（回滚丢失）：事务A和事务B都对同一个数据进行修改，事务A提交后，事务B回滚了，回滚数据覆盖了事务A的修改。
  • 第二类丢失（提交丢失）：事务A和事务B都对同一个数据进行修改，事务A先提交，事务B随后提交，事务B的修改覆盖了事务A的修改。

 

MySQL的隔离机制

 为了解决并发一致性问题，数据库设计了事务隔离机制：

隔离级别	脏读	不可重复读	幻读	第一类丢失	第二类丢失
未提交读	允许	允许	允许	不允许	允许
提交读	不允许	允许	允许	不允许	允许
可重复读	不允许	不允许	允许	不允许	（多数）不允许，（MySQL允许）
串行化	不允许	不允许	不允许	不允许	不允许

 

• 为了避免“脏读”，MVCC 禁止写时读，对应隔离级别 “提交读”
• 为了避免“不可重复读”，MVCC 禁止读时写，对应隔离级别 “可重复读”
• 为了避免“幻读”，干脆把整个表给锁住，强制事务串行执行，对应隔离级别 “串行化”
• 至于”丢失修改“问题：
  • 标准定义的所有隔离级别都不允许第一类丢失更新发生
  • 而第二类丢失，在 PostgreSQL 的可重复读，Oracle 的可串行化和 SQL Server 的快照隔离级别，都能自动检测到丢失更新，并中止违规的事务。但 MySQL/InnoDB 的可重复读并不会检测丢失更新。

事务隔离级别的设置

-- global 是全局设置，session 是当前客户端设置

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;

-- 其中，隔离级别格式：
-- READ UNCOMMITTED
-- READ COMMITTED
-- REPEATABLE READ
-- SERIALIZABLE

MySQL 的默认隔离级别是可重复读

 事务的隔离机制主要是依靠锁机制和 MVCC 实现的，提交读和可重复读可以通过 MVCC 实现，串行化可以通过锁机制实现。

 

---

MVCC

　　多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，是通过 undo log 的版本链和 read view 来实现的。

前提知识

Read View

　　对使用提交读和可重复读隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，因此需要访问版本链中的旧版本。

　　但这就有个问题，怎么判断哪些版本是当前事务能读取的？即怎么判断哪些版本的事务是在当前事务开始前提交的？因此 read view 被提出。

read view 有四个比较重要的内容：

• m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。
• min_limit_id:表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• max_limit_id:表示生成ReadView时，系统中应该分配给下一个事务的id值。
• creator_trx_id: 创建当前read view的事务ID

read view 按照下面的步骤判断记录的某个版本是否可见：

• 如果数据事务 ID trx_id < min_limit_id，表明生成该版本的事务在生成Read View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。
• 如果 trx_id >= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
• 如果 min_limit_id =< trx_id <  max_limit_id, 需要分3种情况讨论：

1. 1. 如果m_ids包含 trx_id ,则代表 Read View 生成时刻，这个事务还未提交，但是如果数据的 trx_id 等于 creator_trx_id 的话，表明数据是自己生成的，因此是可见的。
   2. 如果m_ids包含 trx_id ，并且 trx_id 不等于creator_trx_id，则 Read View 生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；
   3. 如果m_ids不包含 trx_id ，则说明你这个事务在 Read View 生成之前就已经提交了，修改的结果，当前事务是能看见的。

 

快照读和当前读

表记录有两种读取方式：

• 快照读：读取的是快照版本。普通的SELECT就是快照读。通过 mvcc 来进行并发控制的，不用加锁。
• 当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE 是当前读。

 　　快照读情况下，InnoDB 通过 mvcc 机制避免了幻读现象。而 mvcc 机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。（可重复读级别）

 　　当前读情况下，MySQL 通过 next-key 来避免幻读，其包括两部分：记录锁和间隙锁；记录锁是加在索引上的锁，间隙锁是加在索引之间的。（可重复读级别）

 

实现过程

流程：

1. 获取事务自己的版本号，即事务ID
2. 获取 Read View
3. 查询得到的数据，然后 Read View 中的事务版本号进行比较。
4. 如果不符合 Read View 的可见性规则， 即就需要 Undo log 中历史快照;
5. 最后返回符合规则的数据

提交读：

每次读取数据前都生成一个ReadView，解决脏读。

事务C没有执行更改操作，事务id默认为0

最后一个 select 有 read view！上图忽略了

 

可重复读：

在第一次读取数据时生成一个 ReadView，解决不可重复读：

 

---

锁

表锁

表级别的读/写锁

　　一般情况下，只有 MyISAM 存储引擎会使用表级别的 S锁（读锁） 和 X锁 （写锁）；只有在某些特殊情况，如崩溃恢复过程中，InnoDB 存储引擎才用上。

　　但在 InnoDB 存储引擎中，我们也可以手动加锁：

LOCK TABLES 表名 READ;    --S锁
LOCK TABLES 表名 WRITE;    --X锁

 

意向锁

　　InnoDB 支持多粒度锁，它允许行级锁与表级锁共存，即不与行级锁冲突，而意向锁就是其中的一种表锁。

类型：

• 意向共享锁（IS）

SELECT 字段名 FROM 表名 ... LOCK IN SHARE MODE;

• 意向排他锁（IX）

SELECT 字段名 FROM 表名 ... FOR UPDATE;

兼容性：

	IS 锁	IX锁	S锁	X锁
IS锁	兼容	兼容	兼容	互斥
IX锁	兼容	兼容	互斥	互斥
S锁	兼容	互斥	兼容	互斥
X锁	互斥	互斥	互斥	互斥

S/X锁是处理并发，意向锁能干嘛？（意向锁的作用）

　　InnoDB 的读锁、写锁都是行级锁，假如想要加表级别的读/写锁，等事务操作到某行，才发现被锁了，这就很耗费资源。所以意向锁就是用来告诉表级别的读/写锁，这个表已经有锁了，避免事务要一行行查询锁。

注意：申请意向锁的动作是数据库完成的，就是说，事务 A 申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

 

自增锁

　　在使用 MySQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值。

系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个：

1. 采用 AUTO-INC 锁（自增锁），也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
2. 采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的 AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。

 

MDL锁（元数据锁）

　　在对某个表执行一些诸如 ALTER TABLE、DROP TABLE 这类的 DDL 语句时，其他事务对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE 的语句会发生阻塞，同理，某个事务中对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。

　　这种情况是元数据锁来实现的。当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

 

行锁

行锁在 InnoDB 中是基于索引实现的，但是如果某个加锁操作没有使用索引，那么该锁就会退化为表锁。

record locks（记录锁） 

　　锁住的是一条记录，记录锁分为排他锁和共享锁。

 

gap locks（间隙锁）

　　间隙锁是 MySQL 在可重复读隔离级别下，为了避免幻读而提出的。可以锁住记录之间的间隙，防止其他事务插入记录。

 

next-key locks（临建锁）

 　　是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

 

插入意向锁

　　 一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止，在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

 

乐观锁

　　假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加version字段，在修改提交之前检查version与原来取到的version值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。

　　实现方式：乐观锁一般使用版本号机制或CAS算法实现。

 

悲观锁

　　假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。

　　实现方式：使用数据库中的锁机制。

标签：binlog,事务,log,数据库,笔记,提交,MySQL,日志,redo
From： https://www.cnblogs.com/Owhy/p/17965149