性能优化-06-MySQL锁机制与优化实践以及MVCC底层原理剖析

1 锁分类

乐观锁：CAS机制，不会阻塞；适合：读>>写（写操作较多，CPU会一直空转）

        下述是在java代码中：

悲观锁：多个事务对同一条记录同时执行update；适合：写>>读

行锁：MySQL中锁定一行记录；开销大，加锁慢（需要找到指定行）、锁冲突概率低、并发度高；Innodb才支持

        对主键进行update、执行sql ...for update

        innodb的行锁实际上是对索引进行加锁（索引的索引项上）；该索引如果失效，则会升级为表锁（RR级别升级为表锁，RC级别不升级为表锁）

表锁：锁定一张表；开销小，加锁快、锁冲突概率高、并发度低

        通常是数据迁移场景：

间隙锁： 锁两个值之间的间隙；本质也是锁索引

间隙包括哦：(1,3) (3,10)(10,20)(20,+∞)

当执行：

select * from account where id = 18 for update

会给id=18这个索引加写锁之外，还会锁定(10,20)这个区间

临键锁：在间隙锁的基础上锁定边界

2 死锁及问题分析

         两个事务针对同一行加X锁，且不释放已获得的现有资源而导致的循环等待现象

解决：

        kill 事务id = 释放事务占用资源

        注意增添X锁的顺序，尽量不会导致环形阻塞

3 锁优化实践

1 让所有数据的检索通过索引完成，避免将行锁升级为表锁（行锁、间隙锁针对索引加锁）

2 合理设计索引，缩小索引范围（行锁、间隙锁针对索引加锁）

3 减少索引范围（缩小间隙锁范围）

4 控制事务大小（减少加锁实践，更新操作尽量靠后执行）

5 尽可能用低的隔离级别

4 MVCC多版本并发控制机制

         begin/start并不是一个事务的开始，只生成一个临时的id；当加排它锁时，才真正向MySQL申请事务id

        1 解决问题：解决脏读、不可重复读问题；解决共享锁读写效率低的问题

        2 实现方式
        基于UndoLog版本链 + ReadView实现
        当事务对数据update、delete操作会记录在undolog日志，通过roll_pointer链接成undo链（版本链）
        查询时怎么知道应该查哪个版本？由ReadView记录

        3 示例
                undo链： 200->100->90，当前事务id trx_id=201

                在ReadView中的记录为：
                            m_ids=90 100 200 【活跃事务集合】
                            min_trx_id=90        【最小事务id】
                            max_trx_id=201      【版本链头事务id+1】
                            creator_trx_id=201 【创建ReadView的事务id】
                四步查找规则：
                    从undo链第一个开始遍历
                    1 若 creator_trx_id = 当前版本trx_id 说明是自己修改的数据，可以直接访问
                    2 若 当前版本trx_id<min_trx_id，说明该版本在ReadView生成之前已经提交，可以直接访问
                    3 若 当前版本trx_id>max_trx_id，说明该版本在ReadView生成之后才开启，不能被访问【跳过第4步，直接判断下个节点】
                    4 若 当前版本trx_id in m_ids，说明可以直接访问【第四步若仍是false，则判断下个节点】