Mysql锁机制与优化实践以及MVCC底层原理剖析

标签：事务 加锁 行锁 lock Mysql 剖析 Innodb MVCC id

学习来源-图灵课堂

https://vip.tulingxueyuan.cn

锁学习参考：

https://juejin.cn/post/7307889500545253395 

 

锁机制

为了保证数据的一致性，当访问共享变量的时候我们可以针对共享数据加锁，但是加锁要时要注意加锁的成本，还有加锁的粒度，还有就是是否会发生死锁，还有就是发生了死锁要如何排查，如何解决这个死锁。

数据的修改，最好是在原来字段的基础上进行修改，而不是直接改成某个值，直接修改成一个值可能会导致数据的覆盖丢失；因为修改是使用当前最新的值进行修改。修改方式

例如要对a的值进行加十操作，最好是使用如下的SQL
update table set a = a + 10 where id = 1;

这种操作是在代码中对a加过了十，但是可能此时数据库的值已经被修改了
update table set a = x where id = 1;

 

MySQL的锁

• 从性能上分为乐观锁(用版本对比或CAS机制)和悲观锁，乐观锁适合读操作较多的场景，悲观锁适合写操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能
• 从对数据操作的粒度分，分为表锁、页锁、行锁
• 从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)，还有意向锁

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

针对同一份数据，多个读操作可以同时进行而不会互相影响，比如：

select * from T where id=1 lock in share mode; -- lock in share mode是手动加上读锁。

 

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

当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁，比如：

select * from T where id=1 for update; -- for update手动在查询的时候加上写锁。

针对同一条数据来说其读写锁：读读共享；读写互斥，写读互斥，写写互斥。共享时是可以并发操作的；互斥时就是一个执行，另外一个或者多个在阻塞中，可能超时死锁等。

MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型 

 

意向锁（Intention Lock）：

又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。而这个标识就是意向锁。 因为如果没有意向锁时，特别是表中的记录很多时，要对表加表锁需要逐行判断加表锁的方式效率很低。 意向锁主要分为： 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

表锁 

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

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

例如lock tables t1 read, t2 write; 命令，对t1加表级读锁；对t2加表级写锁。则其他线程写 t1、读写 t2 的语句都会被阻塞。读t1不阻塞。

同时，在执行 unlock tables 释放表锁之前，也只能执行读 t1、读写 t2 的操作。不能在unlock tables之前访问其他表。表级写锁中还是可以进行读的。

表级别的读写锁是严格互斥的。

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

页锁

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

行锁

每次操作锁住一行数据。开销大，加锁慢（因为要对数据进行遍历，获取到数据之后才能进行加锁操作）；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。 InnoDB相对于MYISAM的最大不同有两点：

• InnoDB支持事务（TRANSACTION）
• InnoDB支持行级锁

注意：innodb的行锁实际上是是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁，这样粒度小，效率高。但是要注意，索引不能失效；如果update语句，where条件使用了非索引的字段，或者字段索引失效，就会行锁升级为表锁；整个表都不能再进行写。这个一定要注意。这个是针对RR级别，RC级别不会。 读锁会阻塞写锁，但是读读共享；写锁会阻塞写锁和读锁。一行数据被加了写锁，再显式的加读锁是不行的。
注意，加了写锁并不是说就不能读了，只是不能加读锁了，还是可以select的，因为默认的select查询不会加锁。   关于RR级别行锁升级为表锁的原因分析 因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

 

查询锁(行锁)

类似于行读锁写锁。直接锁住了这条数据。

间隙锁(Gap Lock)

在RR隔离级别下才生效的。

是针对范围的加锁，并不锁边界，对这个范围加锁之后就可以阻止在这个访问中插入数据，可以有效的防止幻读的发生。

只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

 

SELECT * from account WHERE id = 5 for UPDATE;

这条SQL语句查询的id是5，因为不存在，就会锁住5，6，7这三个主键值，无法在这三个值中选取主键插入表中。

临键锁(Next-key Locks)

这个是连边界也锁住了，类似于大于等于，加上了等于。类似行锁+间隙锁。

总结

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。 另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。 Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。  

锁等待分析

通过检查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（等待总时长）

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

-- 查看事务 
select * from INFORMATION_SCHEMA.INNODB_TRX; 
-- 查看锁 
select * from performance_schema.data_locks; 
-- 查看锁等待 
select * from performance_schema.data_lock_waits;
-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 
kill trx_mysql_thread_id 
-- 查看锁等待详细信息 
show engine innodb status\G;  

注意：版本是MySQL8.0

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。   锁优化实践

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

 

使用版本号对比的时候，除了要加上where后面的查询条件，还要在后面加上一个版本号，如果版本号不一致，就不能进行修改；

CAS机制，就是比较并交换，在while true循环中，先对比本线程缓存的值和要修改的目标值是否相等，如果相等就修改；如果不等，就终止本次修改，去循环判断，直到能成功修改。

 

MVCC机制

MVCC(Multi-Version Concurrency Control)机制来保证事务的隔离性，可以在不加锁的情况下保证读写互不影响。提高MySQL的整体效率。MySQL的读已提交RC和可重复读RR都是实现了MVCC。 可串行化为了绝对的安全，令所有的操作都串行，加锁，效率很低下。   undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链   可重复读RR，就是通过undo log日志版本链和read view来实现的。RR的read view更像是一个历史的快照版本，当这个时间点进行快照之后，这个快照之后所有的提交默认对这个事务都不可见，所有能保证可重复读级别。 实际上读已提交RC也使用了read view；只不过是RC每次读取都会重置版本链中的版本号，会找到最近一条已经commit的记录开始读取最新的值。也可以说是当前读，每次读取都是拿最新提交的值。   默认MySQL的事务id是单调递增的。 可以认为read-view视图中保留了创建快照时最小的一个事务id，和其中所有未提交的事务id，同时还有一个最大的事务id，这些id组成了一个数组；主要是通过这些事务id来判断该事务id的修改是否对当前事务可见。小于最小事务id，那么肯定都是提交的，可见；大于最大事务id，肯定还没有发生，不可见；在范围内的，如果匹配上了范围内的值，当前事务默认该事务永远未提交，修改不可见，如果没有匹配上范围内的值，默认是提交的，可见。   关于readview和可见性算法的原理解释

• readview和可见性算法其实就是记录了sql查询那个时刻数据库里提交和未提交所有事务的状态。
• 要实现RR隔离级别，事务里每次执行查询操作readview都是使用第一次查询时生成的readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。
• 要实现RC隔离级别，事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview，也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。

 

 

 

 

 

 

RR级别下，读是快照读，一直都是历史版本的数据，别的事务的任何修改都不能获取到；但是如果是本事务进行编辑，那么就是拿最新的数据去编辑，如果没有别的事务进行编辑或者别的事务编辑已经提交或回滚，此时该事务就进行编辑，此时锁就变成了行写锁，排他锁，如果一直不提交，就会阻塞别的事务的写操作。这个要注意。

标签：事务,加锁,行锁,lock,Mysql,剖析,Innodb,MVCC,id
From： https://www.cnblogs.com/0630sun/p/18166262