MySQL-当前读、快照读、MVCC

前言：事务的开启与提交模式

对于一个MYSQL数据库（InnoDB），事务的开启与提交模式无非下面这两种情况：

• 若参数autocommit=0，事务则在用户本次对数据进行操作时自动开启，在用户执行commit命令时提交，用户本次对数据库开始进行操作到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。总而言之，当前情况下事务的状态是自动开启手动提交。
• 若参数autocommit=1（系统默认值），事务的开启与提交又分为两种状态：
  ①手动开启手动提交：当用户执行start transaction命令时（事务初始化），一个事务开启，当用户执行commit命令时当前事务提交。从用户执行start transaction命令到用户执行commit命令之间的一系列操作为一个完整的事务周期。若不执行commit命令，系统则默认事务回滚。
  ②自动开启自动提交：如果用户在当前情况下（参数autocommit=1）未执行start transaction命令而对数据库进行了操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚。这种情况下用户的每一个操作都是一个完整的事务周期。

1. 当前读

当前读，读取的是最新版本，并且对读取的记录加锁，阻塞其他事务同时改动相同记录，避免出现安全问题。

1.1哪些形式的SQL属于当前读：

• select...lock in share mode (共享读锁)
• select...for update
• update , delete , insert

例如，假设要update一条记录，但是另一个事务已经delete这条数据并且commit了，如果不加锁就会产生冲突。
所以update的时候肯定要是当前读，得到最新的信息并且锁定相应的记录。

关于for update

利用select * for update 可以锁表/锁行。
自然锁表的压力远大于锁行。所以我们应尽量采用锁行。
FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效
那么什么时候锁表呢？

• 例1: (明确指定主键，并且有此笔资料，row lock)
  SELECT * FROM wallet WHERE id=’3′ FOR UPDATE;
• 例2: (明确指定主键，若查无此笔资料，无lock)
  SELECT * FROM wallet WHERE id=’-1′ FOR UPDATE;
• 例3: (无主键，table lock)
  SELECT * FROM wallet WHERE name=’Mouse’ FOR UPDATE;
• 例4: (主键不明确，table lock)
  SELECT * FROM wallet WHERE id<>’3′ FOR UPDATE;
• 例5: (主键不明确，table lock)
  SELECT * FROM wallet WHERE id LIKE ‘3’ FOR UPDATE;

1.2当前读的实现方式

当前读使用next-key锁(行记录锁+Gap间隙锁)实现
间隙锁：只有在Read Repeatable、Serializable隔离级别才有，就是锁定范围空间的数据，假设id有3,4,5，锁定id>3的数据，是指的4，5及后面的数字都会被锁定，因为此时如果不锁定没有的数据，例如当加入了新的数据id=6，就会出现幻读，间隙锁避免了幻读。

• 对主键或唯一索引，如果当前读时，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。
• 没有索引的列，当前读操作时，会加全表gap锁，生产环境要注意。
• 非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。例如，某表数据如下，非唯一索引2,6,9,9,11,15。如下语句要操作非唯一索引列9的数据，gap锁将会锁定的列是(6,11]，该区间内无法插入数据。

2. 快照读

单纯的select操作，不包括上述 select ... lock in share mode, select ... for update。　　　　
Read Committed隔离级别：每次select都生成一个快照读
Read Repeatable隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读

快照读的实现方式：undolog和多版本并发控制MVCC

下图右侧绿色的是数据：一行数据记录，主键ID是10，name='Jack'，age=10, 被update更新set为name= 'Tom'，age=23。
事务会先使用“排他锁”锁定该行，将该行当前的值复制到undo log中，然后再真正地修改当前行的值，最后填写事务的DB_TRX_ID，使用回滚指针DB_ROLL_PTR指向undo log中修改前的行DB_ROW_ID。

image

• DB_TRX_ID: 6字节DB_TRX_ID字段，表示最后更新的事务id(update,delete,insert)。
  此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。

• DB_ROLL_PTR: 7字节回滚指针，指向前一个版本的undolog记录，组成undo链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。

• DB_ROW_ID: 6字节的DB_ROW_ID字段，包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。
  如果表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 聚簇索引会使用DB_ROW_ID的值来作为主键; 如果表中有主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID了 。

• 其它：insert undo log只在事务回滚时需要, 事务提交就可以删掉了。update undo log包括update 和 delete , 回滚和快照读 都需要。