Mysql事务及MVCC机制

事务概念及其ACID属性

事务：由一组SQL语句组成的逻辑处理单元
属性：

• 原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改，要么全都执行,要么全都不执行。
• 一致性(Consistent) ：在事务开始和完成时,数据都必须保持一则都必须应用于事务的修改,以保持数据的完整性。
• 隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在独立环境执行。这意味着事务处理过程中的中间状态对外部是不可见
• 持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的

并发事务处理带来的问题

• 更新丢失(Lost Update)或脏写：当两个或多个事务选择同一行数据进行更新，最后的更新覆盖了由其他事务所做的更新。
• 脏读（Dirty Reads）：事务A读取到了事务B已经修改但尚未提交的数据，如果这个事务回滚，A读取的数据无效，不符合一致性要求。
• 不可重读（Non-Repeatable Reads）：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性。
• 幻读（Phantom Reads）：事务A读取到了事务B提交的新增数据，不符合隔离性。

事务隔离级别

查看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;
设置事务隔离级别：set tx_isolation=‘REPEATABLE-READ’;
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔
离级别，如果Spring设置了就用已经设置的隔离级别。

问题1：在可重复读（REPEATABLE-READ），如何解决幻读问题？

快照读（单纯的select操作，不包括上述 lock in share mode, for update；Read-Committed隔离级别：每次select都生成一个快照读；Repeatable-Read隔离级别：开启事务后第一个select语句才是快照读的地方，而不是一开启事务就快照读），是通过MVCC(多版本并发控制)来避免幻读。

当前读（读取的是最新版本，并对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题），是通过临键锁(Next-key Locks)来避免幻读。

锁（Lock）

• 间隙锁(Gap Lock):锁的就是两个值之间的空隙。
  间隙锁是在可重复读隔离级别下才会生效
  
  间隙为 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间

• 临键锁(Next-key Locks)：是行锁与间隙锁的组合。像图片里的这个(3,20]的整个区间可以叫做临键锁。
  例：当执行 update account set name = ‘zhuge’ where id > 8 and id <18;
  其他Session没法在id为(3,20]区间修改数据，注意最后那个20也是包含在内的。

• 行锁分析
  通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';
对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数

 ‐‐ 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;

‐‐ 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;

‐‐ 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

‐‐ 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

‐‐ 查看锁等待详细信息(死锁)
show engine innodb status\G;

注意：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

• 锁优化：

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能减少检索条件范围，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
5. 尽可能低级别事务隔离

MVCC多版本并发控制机制

定义：同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

实现原理：通过read-view机制与undo日志版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

undo日志版本链与read view机制详解

• undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链：
  
  版本链比对规则：
  1.如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若row 的 trx_id 就是当前自己的事务是可见的）；

3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况：
   a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；
   b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

4. 对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

• 在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

• 注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

• 总结：
  MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。