性能调优专题-mysql锁机制与优化实践以及MVCC底层原理剖析

一. mysql各种锁详解：

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如cpu，ram，i/o等）的征用外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性。有效性也是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类：

从性能上分为乐观锁（版本控制和MVCC-------表中增加字段version或者时间戳）和悲观锁，

乐观锁适用于读操作较多的场景，如果在写操作的场景中使用乐观锁会导致对比次数过多，影响性能。

举个例子，乐观锁是基于版本号来区分数据的，当update操作时，其他事务一直更新数据导致这个版本号一直在变，那update操作的版本号就不对，导致一直更新失败，就这一直占用内存。

悲观锁适用于写操作较多的场景，悲观锁直接在sql里面更新操作，当其他事务更新时只能等下面这条语句执行完成才能实现。乐观锁不会等，如果查询不成功它会获得最新的版本号继续查询。乐观锁类似于一个while循环。

从对数据操作的粒度分：表锁，页锁，行锁，

表锁：每次操作锁住整张表，开销小，加锁快，不会出现死锁；锁定粒度大；发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景中。

name_locked表名称加锁。

读写互斥，写写互斥，读读不互斥。

页锁：

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象。但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁与行锁之间，并发度一般。

行锁：

每次操作都会锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB相对于MYISAM的最大不同有亮点：

-- InnoDB支持事务（TRANSACTION）

-- InnoDB支持行级锁；

注意：InnoDB的行锁实际上是针对索引加的锁（在索引对应的索引项上做标记）真正锁的这个索引字段，就是索引字段等于多少这条记录被锁上了，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。（RR级别会升级为表锁，RC级别则不会升级为表锁）

PS: 关于RR级别行锁升级为表锁的原因分析：

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其他事务修改（不可重复读问题）或间隙被其他事务插入记录（幻读问题），从而导致数据不一致，所以mysql的解决方案就是把所有扫描过的索引记录和间隙都锁上。

总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update,insert,delete操作会自动给涉及的表加写锁。

InnoDB在执行查询语句SELECT时（非串行隔离级别），不会加锁。但是update，insert，delete等操作会加行锁。

另外，读锁会阻塞写，但不会阻塞读，写锁对于读和写都会阻塞。

从对数据库操作的类型分：读锁和写锁（都属于悲观锁），还有意向锁。

间歇锁：（Gap Lock）和行锁一样也是针对索引的。实际加的是索引与索引之间的间隙。

上面25，在20到正无穷这个区间，所以上面就把这个区间给锁住了，就不能插入这个区间的数据。注意这个区间是开区间。

间隙区间是可以变化的。

幻读：b事务新增了一条记录，a事务查不到，但是能更新到那条记录。

提交事务所有的锁释放了。

试验：下面会把中间的空隙都锁住吗？

间隙锁是对RC读已提交是不生效的。

临键锁（Next-key Locks）:

是行锁与间隙锁的组合。

Select * from account where id>3 and id <=10 for update； 左边或者右边是闭合的。

锁等待分析：

锁等待是当对某一行加锁了，另一事务对这条数据进行操作：

尤其当等待次数很高，而且每次等待时长不小的时候，分析系统中为什么有这么多的等待进行优化。

-- 查看事务：

select * from INFORMATION_SCHEMA.INNODB_TRX;

-- 查看锁：

第二张表：

lock_mode锁模式：X：排它锁，共享锁，

第三张表：

如果发现某条事务等待时间过长，可以释放锁：

锁等待是有时间限制的，如果超时就会自动重启事务。

死锁问题分析：

锁优化实践：

1. 尽可能让所有数据检索都通过索引来完成，避免无索引锁升级为表锁；

2. 合理设计索引，尽量缩小表的范围；

3. 尽可能减少检索条件范围，避免间隙锁；

4. 尽量控制事务大小，减少锁定资源量和时间长度，设计事务加锁的sql尽量放在事务最后执行；

5. 尽可能用低的事务隔离级别；

MVCC多版本并发控制机制：

多版本：多个undo日志链的记录；

并发：对一条数据进行并发读写操作；

为什么mysql基于RR机制的可以重复读，读取到的数据都是同一个，别的事务修改了这条数据，查到的也是一样的数值？

答案：因为mysql底层的可见性算法：

RR隔离级别MVCC可见性算法：

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

可见性算法就是先将事务id进行分为视图数组（所有未提交的事务）和最大的事务id；然后查询时根据上述的版本链比对规则进行判断是否可见。

如果小于100，事务已经提交了，

如果大于最大的300，说明事务是由将来的启动的事务生成的。

RR这个视图数组就是保存当前那个时间点事务的活跃状态（包括提交和未提交），记录这段时间内这个状态不会变。

RC：每次查询都会更新这个视图数组，所以查到的包括了其他事务提交的数据。

每条记录都有自己的版本链。写操作的事务导致生成版本链。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到他们之后的第一个修改操作或加排它锁操作（比如select... for update）的语句，事务才算成功，才会想mysql申请真正的事务id，mysql内部是严格按照事务的启动顺序来分派事务id的。

理解：单纯的查询也会生成事务，但是事务id是临时的，属于临时事务，其实就是假的。

只有加排它锁和修改操作，才会生成真正的事务id。

疑问：？锁冲突？

什么时候用到锁？应用场景？

串行化是怎么解决幻读的？

二. mysql间隙锁与详解：

三. mysql可重复读隔离级别如何解决幻读

四.mysql 锁等待与死锁问题分析以及解决

五. mysql锁优化实践

六. MVCC多版本并发控制机制深入剖析：