MySQL学习笔记（1）——MySQL的并发、事务与MVCC

一、MySQL的并发、事务与MVCC

1.1 MySQL的架构

• 第一层：连接管理层
• 第二层：服务器层，MySQL的核心服务层，包括查询解析，优化，缓存，内置函数，存储过程、触发器、视图等。
• 第三层：存储引擎层

1.2 并发控制

1.2.1 读写锁

MySQL中通过读写锁实现并发控制，读锁是共享锁，写锁是排他锁

1.2.2 锁粒度

提高并发的方式是让锁定对象更有选择性，尽量锁定需要修改部分数据，而不是所有的数据。每种MySQL存储引擎都可以实现自己的锁策略与锁粒度，两种最重要的锁策略为

• 表锁

  一个用户在进行修改（插入、删除、更新等）是需要先获得写锁，这时会锁定整张表，阻塞其他对表的所有读写操作。只有没有写锁时，才能获得读锁，但是读锁之间不会互相阻塞（共享的）。因为不用考虑复杂的机制，直接锁定整张表，这也成为开销最小的策略。典型的如ALTER TABLE会使用表锁

• 行级锁

  行级锁只有在存储引擎（InnoDB、XtraDB）实现，MySQL服务器层未实现。行级锁最大程度的支持并发，但同时也是开销最大的策略。

1.3 事务

1.3.1 事务的四个特性ACID

• 原子性（Atomicity）

  事务是不可分割最小单元，要么全部成功，要么失败回滚。

• 一致性（Consistency）

  数据库总是从一个一致性状态转换为另一个一致性状态。

• 隔离性（Isolation ）

  通常来说一个事务在提交前，对其余事务是不可见的，事务之间相互隔离。

• 持久性（Durability ）

  一旦事务提交，其所做的修改将会永久保存在数据库中

1.3.2 死锁

1.3.3 事务日志

开启事务日志后，存储引擎在修改表时，只需要修改其内存拷贝，并将事务记录持久化到事务日志上，预写式日志

1.3.4 隔离级别产生的问题

1.3.4.1 三种因隔离级别产生的问题

• 脏读

  事务可以读取未提交的修改。一个事务读取另一个事务未提交的数据，之后另一个事务失败回滚后，此时事务之前读到的数据便不再存在了，为“脏数据”

• 不可重复读

  一个事务内两次执行同样的查询可能会得到不一样的结果。事务不能看到别的事务未提交的数据（避免了脏读），但是可以看到事务自己所做的修改，如果此事务有如下操作：1.读取一行记录；2.修改此行数据（事务未提交）；3.再次读取此行数据。此时1和3读的数据是不一致的，即不可重复读

• 幻读

  一个事务在读取某个范围内的数据是，另一个事务又在改范围内插入了新的数据，之前的事务再次读取改范围的记录，会产生幻行，发生在多行数据之间。

1.3.4.2 事务的四种隔离级别

• 未提交读（Read UNCOMMITTED）
• 提交读（READ COMMITTED）
• 可重复读(REPEATABLE READ)
• 串行化（SERIALIZABLE）

1.3.4.3 修改事务的隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

设置新的隔离级别，在下一个事务开始之前生效

1.3.5 事务的自动提交

• 查看事务自动提交模式

  SHOW VARIABLES LIKE '%autocommit%';
  

  

  启用：ON/1

  禁用：OFF/0

• 开启（关闭）自动提交

  SET AUTOCOMMIT = 1(0)
  

  关闭自动提交时，所有的查询都在一个事务中，直到显式执行COMMIT（提交）或ROLLBACK（回滚）

• 强制执行COMMIT的命令

  ALTER TABLE；

  LOCK TABLE；

1.3.6 事务型表与非事务型表

区别：非事务性表无法回滚。

1.3.7 InnoDB隐式与显式锁定

• 隐式锁定

  InnoDB在需要的时候自动加锁；

  锁只有在COMMIT和ROLLBACK后释放，且所有的锁都是在同一时刻释放

• 显式锁定

  特定的语句进行锁定

  SELECT .... LOCK IN SHARE MODE; -- 当前读
  SELECT ... FOR UPDATE;
  

1.4 多版本并发控制（MVCC）

MVCC是行级锁的一个变种，在很多情况下避免了加锁操作，开销更低。通过保存某个时间点的数据快照，使得每个事务不管执行多长时间，看到的数据都是一致的。实现方式有乐观并发控制与悲观并发控制。

1.4.1MVCC的特性

• MVCC只支持RC(读取已提交)和RR(可重复读)隔离级别。

• MVCC能解决脏读、不可重复读问题，不能解决丢失更新问题和幻读问题。

• MVCC是用来解决读写操作之间的阻塞问题。使得在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。

1.4.2 InnoDB的MVCC实现

InnoDB的MVCC的实现主要依赖于数据库在每个表中添加的三个隐藏字段以及事务在查询时创建的快照（read view）和数据库的数据版本链(undo log)。

1.4.2.1 三个隐藏字段

InnoDB会为每个使用InnoDB存储引擎的表添加三个隐藏字段，用于实现数据多版本以及聚集索引。

• DB_TRX_ID事务ID

  保存最近一次插入、删除和更新数据的事务的ID。

• DB_ROLL_PTR回滚指针

  回滚指针，指向当前记录行的undo log信息(指向该数据的前一个版本数据)

• DB_ROW_ID隐藏主键

  随着新行插入而单调递增的行ID。InnoDB使用聚集索引，数据存储是以聚集索引字段的大小顺序进行存储的，当表没有主键或唯一非空索引时，innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引，聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。

实际上还会有一个删除flag隐藏字段，即记录删除。实际上的删除一行数据，并不是真正的删除，而是将flag设置为删除标记。当InnoDB丢弃为删除而编写的更新撤消日志记录时，它才会物理删除相应的行及其索引记录。此删除操作称为清除，速度非常快。

1.4.2.2 undo log

undo日志，实际上时MySQL内存缓冲区里的一段空间，即buffer pool中的一段空间，用来保存日志数据，之后再刷新到磁盘中。当一个事务需要读取记录行时，如果当前记录行不可见，可以通过回滚指针顺着undo log链找到满足其可见性条件的记录行版本。

在InnoDB里，undo log分为如下两类：

• insert undo log

  事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。

• update undo log

  事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被purge线程删除。

Purge线程：上文提到了InnoDB删除一个行记录时，并不是立刻物理删除，而是将该行数据的DB_TRX_ID字段更新为做删除操作的事务ID，并将删除位deleted_bit设置为true(已删除)，将其放入update undo log中。为了节省磁盘空间，InnoDB有专门的purge线程来清理deleted_bit为true的记录。purge线程自己也维护了一个read view，如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

下面来模拟一下具体实现过程：

1. 假设有一张表person，里面有name和age两个字段，并且不指定主键（这样会存在DB_ROW_ID）则创建这张表后，MySQL会自动添加三个隐藏字段。插入一行数据后的表信息为：DB_TRX_ID和DB_ROLL_PTR假设为NULL

   

2. 有如下两个事务过程：

   

   • 事务1

     ①数据库先进行加锁操作；

     ②将该行数据保存在undo log中，即将原始数据拷贝一份；

     ③修改name字段，并将隐藏字段DB_TRX_ID设置为事务ID，DB_ROLL_PTR指向undo log的地址；

     ④事务提交，释放锁；

     

   • 事务2

     ①数据库先进行加锁操作；

     ②把该行数据拷贝到 undo log 中，作为旧记录。发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面

     ③修改age字段，并将隐藏字段DB_TRX_ID设置为事务ID，DB_ROLL_PTR指向最新的undo log的地址；

     ④事务提交，释放锁；

     

   可以看到，实际上通过DB_ROLL_PTR和undo log，会形成一个链表，即数据库的数据版本链(undo log)，用事务IDDB_TRX_ID看做版本号（当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大）。undo log 的链首就是最新的旧记录，链尾就是最早的旧记录。（当然就像之前说的该 undo log 的节点可能是会 purge 线程清除掉，向图中的第一条 insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

1.4.2.3 read view

Read view是事务进行快照读操作的时候生产的读视图(read view)。决定了这个select可以看到多少版本数据。这个读视图中，维护了系统当前活跃事务的id。

简单来说就是，在我们某个事务进行快照读时，对该记录创建一个read view，把他当作条件，用来判断当前事务能够看到哪个版本的数据。即，可能时最新版本，也可能是undo log中的版本。

Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本。

在read view中主要由以下四个成员：

• m_ids（trx_list）：相当于一个数组。记录read view生成时刻，系统正在执行的事务，即活跃的事务。
• up_limit_id：记录m_ids列表中事务最小的ID。
• low_limit_id：read view生成时刻，系统尚未分配的下一个事务ID。即目前已出现过的事务 ID 的最大值 + 1
• creator_trx_id：创建read view的事务id。

• creator_trx_id == DB_TRX_ID || DB_TRX_ID < up_limit_id

  在形成read view前已经提交的事务，形成的版本链数据，可以看到。

• up_limit_id <= DB_TRX_ID < low_limit_id

  在形成read view时，正在执行的事务id保存在m_ids中。不在m_id中说明在形成快照前提交了，但是仍然在up_limit_id和low_limit_id范围内

  注意：m_ids中的事务id不一定是连续的，可能由中间的事务ID已经提交了。比如：我们有11，12，13，14，15号事务。在形成快照前12，14号事务提交了。形成快照后，m_ids中保存的就是11，13，15。up_limit_id等于11，low_limit_id等于16。

  此时如果DB_TRX_ID没有m_ids中，说明已经提交(commit)，可以被查询。如果DB_TRX_ID在m_ids中，说明还没有被提交，不可以被查询。并且可重复读和读提交，是因为read view不同，所以呈现的现象不同。

• DB_TRX_ID >= low_limit_id

  说明是形成read view后，形成的事务，对数据进行了修改。插入到了版本链中。此种数据不可见。

1.4.2.4 MVCC整体流程

用以下事务进行模拟：

当事务 2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务 ID 为 2，此时还有事务1和事务3在活跃中，事务 4在事务 2快照读前一刻提交更新了，所以 Read View 记录了系统当前活跃事务 1，3 的 ID，维护在一个m_ids列表上。

-- 系统当前活跃事务 1，3 
m_ids=1,3
-- m_ids中的最小值为1
up_limit_id=1
-- 事务id中的最大值+1为5
low_limit_id=5
-- 事务2创建的read view
creator_trx_id=2

只有事务 4 修改过该行记录，并在事务 2 执行快照读前，就提交了事务，所以当前该行当前数据的 undo log 如下图所示；

我们的事务 2 在快照读该行记录的时候，就会拿该行记录的 DB_TRX_ID 去跟 up_limit_id , low_limit_id 和活跃事务 ID 列表(m_ids )进行比较，判断当前事务 2能看到该记录的版本是哪个。

先拿该记录 DB_TRX_ID 字段记录的事务 ID 4 去跟 Read View 的 up_limit_id 比较，看 4 是否小于 up_limit_id( 1 )，所以不符合条件，继续判断 4 是否大于等于 low_limit_id( 5 )，也不符合条件，最后判断 4 是否处于m_ids中的活跃事务, 最后发现事务 ID 为 4 的事务不在当前活跃事务列表中, 符合可见性条件，所以事务 4修改后提交的最新结果对事务 2 快照读时是可见的，所以事务 2 能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本。

1.4.2.5 可重复读（RR） 是如何在提交读（RC）级的基础上解决不可重复读的？

当前读和快照读在 RR 级别下的区别

• 表1：

在上表的顺序下，事务 B 的在事务 A 提交修改后的快照读是旧版本数据，而当前读是实时新数据 400

• 表2：

而在表 2这里的顺序中，事务 B 在事务 A 提交后的快照读和当前读都是实时的新数据 400，这是为什么呢？

这里与上表的唯一区别仅仅是表 1的事务 B 在事务 A 修改金额前快照读过一次金额数据，而表 2的事务B在事务A修改金额前没有进行过快照读。

所以我们知道事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力

我们这里测试的是更新，同时删除和更新也是一样的，如果事务B的快照读是在事务A操作之后进行的，事务B的快照读也是能读取到最新的数据的

RC , RR 级别下的 InnoDB 快照读有什么不同？

正是 Read View 生成时机的不同，从而造成 RC , RR 级别下快照读的结果的不同

在 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。

1.5 MySQL的三个日志redolog、undolog 和 binlog

1.5.1 什么是redo log？

• redolog是重做日志，用来保证事务的原子性与持久性，通过重写日志缓冲（redo log buf）与重写日志文件(redo log)来实现
• 事务提交后InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。最后在空闲时间通过fsync将内存写入磁盘日志文件。

• 通过innodb_flush_log_at_trx_commit用来控制fsync执行时机。
  • innodb_flush_log_at_trx_commit = 0：master thread控制每秒执行fsync
  • innodb_flush_log_at_trx_commit = 1：每次事务提交后执行fsync
  • innodb_flush_log_at_trx_commit =2：有操作系统控制fsync时机
• InnoDB的redo log是固定大小的，由日志块log block（默认512字节）与日志组log group组成，默认只有一个日志组。write pos是当前记录的位置，一边写一边后移。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos和checkpoint之间的是redo log中还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示redo log满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

1.5.2 什么是undo log？

• undo log用来保证事务的回滚与MVCC功能。
• undo log会产生redo log，也需要持久化的保护。
• 事务提交后undo log会被放入一个链表中，由purge线程删除
• undo log分为
  • insert undo log：因为insert操作只对事务本身可见，对其他事务不可见，可以在提交后直接删除。
  • update undo log：存放delete和update操作记录，由于MVCC回滚使用，所以不能马上删除，放入undo log链表由purge删除

1.5.3 什么是binlog？

• binlog是二进制日志，用来进行POINT-IN-TIME恢复和主从复制的建立