mysql复习五-锁和MVCC

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

锁的分类：

从性能上分：

乐观锁-cas机制，放在一个while的循环中，获取版本号，然后在执行更新操作，更新操作条件里面要加入版本号，如果不是这个版本号，就会更新失败，又要重新获取，然后在用新的版本号更新，直到更新成功，才会打破循环，在这个过程中，支持其他事务操作的，这个更多应用于读多写少表中，否则要耗费更多性能。

悲观锁-只准当前事务操作，不允许其他事务操作，合适写多读少的表中

按颗粒度划分：

表锁，每次操作锁住整张表。开销小（直接在表上做个标志），加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最 低；一般用在整表数据迁移的场景。加锁方式-lock table 表名称 read(write),表名称2 read(write);、show open tables 查看表锁 ；unlock tables 解锁

页锁-只有BDB存储引擎支持页锁，可以锁住多个行数据，性能在排在中间、

行锁-每次操作锁住一行数据。开销大，（需要去表里遍历寻找），加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最 高。

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

nnoDB支持事务（TRANSACTION）

InnoDB支持行级锁

注意（重点）：InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的 锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁）

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

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过 的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所 以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表 锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，比如： 1 select * from T where id=1 lock in share mode

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都 会加写锁，查询也可以通过for update加写锁，比如：  select * from T where id=1 for update

意向锁：主要是为了提高加表锁的效率，是mysql数据库自己 加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事 务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不 该加表锁

间隙锁：锁住间隙，并且只针对索引，没有索引，在RR级别，会变成表锁，间隙锁是在可重复读隔离级别下才会生效，主要时防止幻读，

加锁方式：只要在间隙内比如10到20id,锁一条不存在的记录

比如在：select* from where id=18 for update,加个读锁，那么在10到20，就不能插入数据了，就能解决幻读问题，幻读是A事务读到B事务新增的数据，而现在增加不了了

总结

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

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行 锁。 另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更 高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb 的整体性能和MYISAM相比就会有比较明显的优势了。 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表 现不仅不能比MYISAM高，甚至可能会更差。

锁等待分析

show status like 'innodb_row_lock%'; 通过这个命令查看，可以获得这些注意字段

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等 待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

查看事务---select * from INFORMATION_SCHEMA.INNODB_TRX

查看锁--select * from INFORMATION_SCHEMA.INNODB_LOCKS;（8.0之后需要换成这张表performance_schema.data_locks）

查看锁等待-select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS（8.0之后需要换成这张表performance_schema.data_lock_waits）

都是 INFORMATION_SCHEMA.INNODB

死锁解决

如果产生死锁，我们可以kill trx_mysql_thread_id 释放对应的锁

两种方法查看死锁：

1,在查看事务里面，查看等待时间，如果无设置，超过50s还在等待，就有可能是死锁

2是通过show engine innodb status，查看锁的日志信息，在里面直接搜索deadlock,里面就会出现死锁对应的语句

show engine innodb status; 查看锁的精确日志信息

锁优化实

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

合理设计索引，尽量缩小锁的范围

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

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

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

MVCC

隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会 通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥 来实现的。

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

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

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日 志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链(见下图，需参考视频里的 例子理解）

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前永 远都不会变化(如果是读已提交隔离级别在每次执行查询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)，那就包括两种情况 a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的 事务是可见的)；

b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

个人理解：当开启一个事务时，会生成一致性视图（RR级别不会变，RC级每个事务提交会改变视图），这个视图数据结构由两部分构成，第一部分，当前最大的事务trx_id（trx_id是有序的，生成越晚的事务，trx_id越大），比如300，第二部分，生成一个数组，这个数组是由当前，此时未提交的事务id构成，比如这个[100,110,115,200]，整体是这样read-view：[100,110,115,200],300

那么只要不在数组里面，并且小于300的，那么就是已提交的事务，那是可见的

在这数组里面或者>300的，那都在未提交的事务，那就是不可见的

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