【MySQL锁机制与MVCC详解】

一、MySQL锁机制

1、锁分类

2、锁介绍

2.1 悲观锁与乐观锁

1、悲观锁：悲观锁在操作数据时比较悲观，认为别人会同时修改数据。因此操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据
2、乐观锁：乐观锁在操作数据时非常乐观，认为别人不会同时修改数据。因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据：如果别人修改了数据则放弃操作，否则执行操作

2.2 表锁

每次操作锁住整张数据表、开销小，加锁快，不会出现死锁，锁的颗粒度大，发生锁冲突的概率高，并发低，一般都是用在数据迁移的场景中，操作如下示例：

 ‐‐手动增加表锁
 lock table 表名称 read(write),表名称2 read(write);
 ‐‐查看表上加过的锁
 show open tables;
 ‐‐删除表锁
 unlock tables;

2.3 页锁

只有DBD存储引擎支持页锁，页锁就是在页的颗粒度上进行数据的锁定，相比表锁，范围会小很多，但是比行锁多，其开销在表锁与行锁之间，会出现死锁，并发一般

2.4 行锁

每次操作会锁定当前行数据，开销大，加锁慢，会出现死锁，锁的颗粒度小，出现锁冲突的概率低，并发高；
InnoDB中的行锁其实是针对索引加的锁，并不是对整个行记录加的，所以，该索引不能失效，否则会从行锁升级为表锁（RR级别会升级==>因为需要解决不可重复读和幻读问题，RC不会）
具体操作如下示例：

select * from account where name = 'lilei' for update; ‐‐where条件里的name字段无索引

2.5 读锁（共享锁，S锁(Shared)）

针对同一份数据，多个读操作是可以同时进行二互不影响的，例：

select * from T where id=1 lock in share mode

2.6 写锁（排它锁，X锁(eXclusive)）

当前写操作没有结束之前，会阻断其他事物的写操作和读操作，数据修改的操作都会加写锁，查询时，也可以通过for update就加写锁，例：

select * from T where id=1 for update

2.7 意向锁（Intention Lock）

又称I锁，针对表锁，主要是为了提高加表锁的效率，其实就是给表设置一个标识，代表当前表是否已加行锁，避免了事物在加表锁时，需要逐个遍历索引判断是否加锁，提高表锁的加锁效率

2.8 间隙锁(Gap Lock)

间隙锁是在可重复读隔离级别下才会生效，间隙，就是锁住两个值之间的间隙，加间隙锁的操作，只需要在两只值的区间锁一条不存在的记录，就会锁住整个间隙范围。例如，数据库数据存在区间（10,20），那加入间隙锁的示例如下：

 select * from account where id = 18 for update;

2.9 临键锁(Next-key Locks)

临键锁是行锁与间隙锁的组合，也就是说，间隙锁包含了两个区间的边界值

3、锁等待分析

1、我们可以通过检查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: 系统启动后到现在总共等待的次数
我们需要重点关注这5个状态变量：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）

我们还可以查看INFORMATION_SCHEMA系统库锁相关数据表读锁进行分析，例：

‐‐ 查看事务
 select * from INFORMATION_SCHEMA.INNODB_TRX;
 ‐‐ 查看锁，8.0之后需要换成这张表performance_schema.data_locks
 select * from INFORMATION_SCHEMA.INNODB_LOCKS;
 ‐‐ 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
 select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

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

 ‐‐ 查看锁等待详细信息
 show engine innodb status;

4、部分锁优化总结

4.1、尽可能让所有的数据检索都通过索引来完成，避免无索引导致行锁升级为表锁（RR级别下）

4.2、合理设计索引，尽可能缩小范围

4.3、尽可能减少检索条件的范围值，避免过大的间隙锁

4.4、尽可能用低的事务隔离级别

4.5、尽量控制事务的大小，减少锁定资源的时间和长度，涉及事务加锁的SQL尽量放在事务的最后执行

二、MVCC多版本并发控制详解

undo日志版本链与read view机制详解，如下图，undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

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

其比对的规则为（可见性算法）：

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)，那就包括两种情况：

1. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见（如果row的trx_id就是当前自己的事务，那事务修改的数据是可见的）
2. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见

总结：MVCC机制的实现就是通过read-view机制与undo版本链比对机制,当然不同的隔离级别（RR和RC）生成的read-view方式不一样，然后根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。