MySql数据库锁机制和MVCC多版本并发控制

锁分类

性能分类

• 乐观锁,乐观锁适合读操作较多的时候,读读不互斥
• 悲观锁,适合写操作比较多的时候

比较:乐观锁是通过版本号等对比进行判断当前数据有没有被修改,是一种乐观机制,认为在读取这行数据的时候不会有别的线程进行操作当前数据,读读不互斥,读写互斥,悲观锁是互斥锁,在添加悲观锁的数据上,不能被别的线程进行修改,也不能被读取,如果乐观锁应用在写比较多的场景时会比较耗费性能,应为乐观锁需要对比.

从数据粒度上区分

• 表锁,InnerDB存储引擎
• 页锁,只有BDB存储引擎上有页锁
• 行锁,InnerDB存储引擎

对数据库操作区分

• 读锁,（共享锁，S锁(Shared)）对一个数据,多个读锁可以同时进行,.不会导致线程中断

• 写锁,（排它锁，X锁(eXclusive)）对于一份数据,同是智能存在一个线程进行操作,写锁和读锁进行互斥,写锁和写锁也会进行互斥

• 意向锁,又称I锁Mysql数据库自己加的,针对于表锁,是一种快速识别标志,在有事务给表加了共享锁或者互斥锁,就会在意向锁打上标记,来标识当前表内的数据有锁.其他事务想要加表锁的时候,就不用去逐行的判断数据是否有锁了.

  • 意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。
  • 意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

表锁

​ 每次操作会锁住一张表,开销小,速度快,操作粒度大,不会出现思索,发生锁冲突的概率最高,并发程度最低,一般用在整表迁移的操作

​ 在进行行锁的时候,如果是RR的事务级别,可能会使行锁升级为表锁.

页锁

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

行锁

每次操作锁住一行数据,开销大,操作慢,会出现死锁,锁定的力度小,并发程度最高,发生锁冲突的概率最低.

InnerDB相对于MyISAM存储引擎的最大不同有两点

• InnerDB存储引擎支持事务
• InnerDB存储引擎支持行锁

注意:InnerDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记),不是针对整行数据加的锁,如果加锁的索引失效,RR事务的时候那么行锁就会升级为表锁,RC事务不会升级为表锁

为什么RC事务不会升级为表锁?

​ 因为RR需要保证数据的重读行,所以为了保证数据的一致性,那么就会升级为表锁;RC事务不需要保证次特性,所以不会升级为表锁.

这里的表锁其实不是完整的表锁,因为他在操作的时候,也可能会有别的事务给比表内的数据加上行锁,不一定能够加上表锁,他是对已经扫描过的聚簇索引和间隙,加上锁.

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

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

间隙锁

间隙锁就是两行数据之间的空隙,间隙锁是在可重复读的隔离级别下才会生效,间隙锁可以解决幻读的问题

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行如下sql：

  select * from account where id = 18 for update;     
  这个sql语句,就可以锁定10-20之间的间隙.
  select * from account where id = 25 for update;
  则其他Session没法在这个(20,正无穷)这个间隙范围里插入任何数据。
  也就是说，只要在间隙范围内锁了一条不存在的记录会锁住整个间隙范围，不锁边界记录，这样就能防止其它Session   在这个间隙范围内插入数据，就解决了可重复读隔离级别的幻读问题。

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。

总结

MyISAM在执行查询语句select之前,会自动给表加读锁,在执行update,insert,delete操作的时候会自动给涉及的表加写锁

InnerDB在执行查询语句的时候(非串行化),不会加锁,在操作update,insert,delete会加行锁

另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

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

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

锁等待分析

-- 查看事务
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; 

死锁问题分析

set tx_isolation='repeatable-read';
Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;
查看近期死锁日志信息：show engine innodb status; 

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

锁优化实践

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

MVCC多版本并发控制机制

Mysql在可重复读隔离级别下如何保证事务较高的隔离性,同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。

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

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

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

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

在可重复读隔离级别,事务开启时,执行任何查询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),那就包括两种情况

• 若row的trx_id在视图数组中, 表示这个版本是由还没提交的事务生成的,不可见(若row的trx_id就是当前事务处理的,那么就是可见的);
• 若row的trx_id不在视图数组中,表示这个版本就是已经提交了事务的,数据是可见的.

MVCC可见性算法的操作示例，大家可以对照着示例理解：

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

关于readview和可见性算法的原理解释

readview和可见性算法其实就是记录了sql查询那个时刻数据库里提交和未提交所有的事务的状态.

要实现RR隔离级别,事务每次执行查询操作read-view,都是使用第一次查询时生成的readview,也就是都是第一次查询时当时数据库里面所有事务提交状态来对比数据是否可见,当然可以实现每次查询的可重复读的效果了.

要是先RC隔离级别,事务里,每次执行查询操作readview都会按照数据库当前状态重新生成readview,也就是每次查询都是跟数据库里当前所有事务提交状态,来对比数据是否可见,当然实现的就是每次都能查询到已提交的最新数据.

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

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。