事务隔离级别和MVCC

1. 事务隔离级别和MVCC

1.1 事务并发执行时的一致性问题

（1）脏写

事务修改了另一个未提交事务修改过的数据；

这里的一致性是：假设每个事务都遵守将变量 x 和 y 始终设置为相同值，操作序列如下所示：

T1(x = 1)、T2(x = 2)、T2(y = 2)、T1(y = 1)、C1、C2

事务提交后，x = 2，y = 1 并不满足一致性要求

在发生回滚时，也会影响原子性和持久性，例如，假设 x 和 y 初始值为 0

T1(x = 1)、T2(x = 2)、T2(y = 2)、C2、A1

T1 修改 x 时会记录旧值 0，T1 进行回滚时会将 x 恢复为 0，但 T2 中也修改了 x ，就会造成部分回滚的情况（不回滚 y），违背原子性；如果都回滚的话， T2 已经提交，违背持久性的要求。

（2）脏读

事务读取了另一个未提交事务修改过的数据；

（3）不可重复读

一个事务修改了另一个事务读取过的数据；

（4）幻读

一个事务先根据某些搜索条件查询一些记录，在该事务未提交时，另一个事务写入了一些符合前述搜索条件的数据（增删改操作）；

1.2 MySQL 设置隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL level;

不写 GLOBAL 或者 SESSION 只对执行 SET 语句后的下一个事务产生影响，下一个事务执行完后，后续事务恢复到之前的隔离级别，不能在已经开启的事务中执行，会报错。

（1）可重复读应用场景：事务启动时可以认为表是静态的

1.3 ReadView

对 READ UNCOMMITTED 直接读取记录最新的记录即可；
对 SERIALIZABLE 使用加锁的方式来访问记录；

对 READ COMMITTED 和 REPEATABLE READ 来说，需要保证读取的记录都是已经提交事务修改的记录，通过使用 ReadView 保证。

1. m_ids：生成 ReadView 时，当前系统中活跃的读写事务的事务 id 列表；
2. min_trx_id：生成 ReadView 时，当前系统中活跃的读写事务的中 最小的事务 id （m_ids 中的最小值）；
3. max_trx_id：生成 ReadView 时，系统应该分配给下一个事务的事务 id 值；
   注意，max_trx_id 并非 m_ids 中的最大值，因为可能事务 id 较大的事务已经提交了（id 为 3 的事务提交了，当前活跃的为 1 和 2）。
4. creator_trx_id：生成该 ReadView 的事务的事务 id；

如果被访问版本的 trx_id 与 ReadView 中的 creator_trx_id 相同，则表明当前事务正在访问它自己修改过的记录，可见；
如果被访问版本的 trx_id 小于 ReadView 中的 min_trx_id ，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，可见；
如果被访问版本的 trx_id 大于等于 ReadView 中的 max_trx_id，表明生成该版本的事务在当前事务生成 ReadView 后才开启，不可见；
如果被访问版本的 trx_id 大于等于 min_trx_id 并小于 max_trx_id，判断 trx_id 是否在 m_ids 列表中，如果在，不可见；否则，可见。

（1）READ COMMITTED 隔离级别下，每次读取数据前都会生成一个 ReadView；

（2）在 REPEATABLE READ 隔离级别下，第一次读取数据时生成一个 ReadView；

START TRANSACTION WITH CONSISTENT SNAPSHOT 语句开启事务，会立即生成一个 ReadView。

1.4 二级索引与 MVCC

1. 二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 的属性，记录了修改该二级索引页面的最大事务 id；当 SELECT 语句访问某个二级索引时，首先判断 ReadView 的 min_trx_id 是否大于 PAGE_MAX_TRX_ID，若大于，则该页面的所有记录都对该 ReadView 可见，否则需要进行回表后再判断可见性。

2. 利用二级索引的主键进行回表，找到对该 ReadView 可见的第一个版本，判断该版本中相应的二级索引列的值是否与二级索引查询时的值相同，若是，返回（WHERE 中若有其他条件还需继续判断其他条件），否则接着沿着版本链判断。

1.5 关于 purge

（1）当一个事务提交后，会把这个事务执行过程中产生的一组 update undo log 插入到 History 链表头部。

每个回滚段都有一个 History 链表

（2）为了支持 MVCC ，delete mark 仅仅设置一个标志位，并没有真正删除；

（3）何时进行 purge

1. 在事务提交时，会为该事务生成一个名为 trx_no 的值，表示事务提交的顺序；
   一组 undo log 中对应的 Undo Log Header 部分有一个属性 TRX_UNDO_TRX_NO，当事务提交时，记录了事务的 trx_no；
   History 链表是根据事务提交顺序来存放各组 undo log 的；

2. 生成 ReadView 时，还会包含一个 trx_no 属性，表示当前系统中最大的 trx_no + 1 赋给该值。 （用来表明生成该 ReadView 时，哪些事务已经提交）

InnoDB 把当前系统中所有的 ReadView 按照创建时间连成一个链表，当执行 purge 操作时，就把系统中最早生成的 ReadView 取出来（如果没有则新创建一个 ReadView ），然后从各回滚段的 History 链表取出 trx_no 较小的各组 undo log，如果该组日志的 trx_no 小于 ReadView 中的 trx_no，就会释放该组日志，如果该组日志包含因 delete mark 操作产生的 undo log，也要把相应记录真正删除。

（4）因此尽量少用长事务，因为这会保留很老的 ReadView，从而导致 undo log 变得特别大。

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，其中 trx_started 列记录了事务启动时间

1.6 幻读问题

（1）在 REPEATABLE READ 隔离级别下，只有当前读会产生幻读问题

在 READ COMMITTED 隔离级别下，其他事务 UPDATE 记录产生的查询前后不一致行为，不属于幻读

2. 锁

（1）锁结构
trx 信息：该锁结构与哪个事务关联；
is_waiting：当前事务是否在等待；

获取锁成功或者加锁成功，在内存中生成了与该记录对应的锁结构，而且该锁结构的 is_waiting 属性为 false；
加锁失败，在内存中生成了与该记录对应的锁结构，而且该锁结构的 is_waiting 属性为 true；
不加锁，不需要在内存中生成对应的锁结构，可以直接执行操作；

2.1 写操作

（1）DELETE：先在 B+ 树中定位这条记录的位置，获取记录的 X 锁（获取 X 锁的锁定读），执行 delete mark 操作；
（2）INSERT：一般情况下，新插入的一条记录受隐式锁保护，不需要在内存中生成对应的锁结构；
（3）UPDATE：

不修改主键，且被更新的各个列占用存储空间与之前相同
未修改主键，且至少有一个被更新的列占用存储空间与之前不同，

修改主键，相当于在原纪录上执行 DELETE 操作后再来一次 INSERT 操作，加锁操作需要按照 DELETE 和 INSERT 的规则进行。

2.2 MySQL 中的行锁和表锁

MyISAM、MEMROY 这些存储引擎一般支支持表级锁，而且一般都是针对当前会话来说的，因此最好用在只读场景下。

3. 主备库

建议将备考设置为只读（readonly）模式；

带来的好处是：
一些查询语句可能在备库执行；
防止切换逻辑 bug，比如切换过程中出现双写，造成主备库不一致；
可以通过 readonly 状态判断是主库还是备库；

readonly 设置对超级权限用户是无效的，而同步更新的线程就拥有超级权限；

3.1 如何进行

主库和备库之间维持了一个长连接；

主库 A 从本地读取 binlog 发送给 从库 B（这里是从本地读取是指**读文件 read **这个操作，可能在操作系统的 page cache 中，也可能需要从磁盘读取）；

3.2 可能导致主备延迟的情况

seconds_behind_master衡量主备库延迟情况，单位是秒

（1）主备库部署的机器不同

（2）备库压力比较大，需要处理读请求；

一般采用一主多从，让这些从库分担读请求

（3）大事务；

delete 删除太多数据
大表 DDL

（4）备库并行复制能力

（5）什么情况备库的主备延迟会表现为一个45度的线段？
出现大事务
备库执行一个长事务操作某个表 t，但此时主库要对表 t 加字段，这个 binlog 在备库应用时就会一直被堵住；

3.3 切换策略

（1）可靠性优先

1. 判断备库的 SBM 是否小于 5，若小于，则继续，否则重试；
2. 将主库改为只读状态；
3. 等待备库的 SBM 降为 0；
4. 将备库改为可读性状态
5. 将业务切换到备库

在第 3 步时，整个系统是只读状态，因此需要 SBM 足够小才行；

（2）可用性优先

把 4、5 步调到最开始执行；

缺点：可能会导致数据不一致问题；

3.4 并行复制

备库上的 sql_thread 执行中转日志（relay log）

coordinator 负责读取中转日志和分发事务，多个 worker 线程真正执行；

分发原则：

1. 更新同一行的事务，必须分到同一个 worker 中；
2. 同一个事务不能被拆分开；

3.4.1 分发策略

（1）按表分发

每个 work 分配一个 hash 表，key为 库名 + 表名，value 为修改该表的事务数量；
coordinator 分发时，判断某个事务是否与某个 work 修改同一个表；
如果都没有，则分配给最空闲的 worker；
如果有一个，那就分配给它；
如果有多个，就需要等待；

（2）按行分发

要求 binlog 格式必须是 row；
hash 表的 key 为 库名 + 表名 + 索引名 + 索引值；

（3）模拟主库并发模式，即同时进入 commit 状态的事务可以并发执行；

1. 能够在同一组提交的事务，一定能并发执行；（有二阶段锁的保证，不会出现更新冲突情况）
2. 主库可以并发执行的事务，从库也可以；

4. 主备库

对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用mysql_store_result这个接口，直接把查询结果保存到本地内存

5. 用户权限

MySQL 中 用户名 + 地址 才表示一个用户；

5.1 全局权限

（1）新建一个用户时

磁盘上：往 mysql.user 表中插入了一行
内存中：数字 acl_users 中的插入了一个 acl_user 对象，该对象的 access 字段表示其权限值

（2）grant命令会同时更新磁盘和内存

在 grant 命令执行完成后，如果有新的客户端使用用户名登录成功，MySQL会为新连接维护一个线程对象，然后从 acl_users 数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。

5.2 库级别权限

（1）授予用户某个库的权限时

磁盘上：往 mysql.db 表中插入了一行
内存中：数字 acl_dbs 中的插入了一个 acl_db 对象，该对象的 access 字段表示其权限值

（2）每次需要判断一个用户对一个数据库的读写权限时，都需要遍历一次 acl_dbs ；

如果当前会话处于某个库中，之前 use db 时会将拿到的库权限会保存在会话变量，在切出之前一直保持这个权限；

5.3 表权限和列权限

磁盘上：表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。

内存中：二者组合存放在 hash 结构 column_priv_hash

5.4 内存和磁盘不一致场景

（1）flush privileges 原理：会清空 acl_users 数组，从磁盘上重新读取

对 库权限、表权限和列权限 也都是如此；

规范地使用 grant 和 revoke 语句时，后面不需要加flush privileges；

（2）不规范的方式：直接操作 mysql.user 系统表

6. 分区表

1. MySQL在第一次打开分区表的时候，需要访问所有的分区； open_files_limit 限制了 MySQL 中 server层打开文件的个数，超过该值会报错；
2. 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
3. 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

删除分区 alter table t drop partition xxx

innodb_open_files 限制了 InnoDB 打开文件的数量，超过后会关闭一些之前打开的文件；