MySQL知识点总结（五）——锁

锁是每一个关系型数据库都肯定会有的一种处理并发读写冲突的机制。通过加锁，当前事务可以保证它加了锁的行记录，不被别的事务修改，保证了事务的隔离性。

保证事务隔离性的机制，除了加锁以外，还有MVCC，那什么时候加锁，什么时候使用MVCC机制呢？在一个事务中，当我们的查询语句显式的加锁，也就是“select … lock in share mode”或者“select … for update”，以及增删改操作，都会加锁；而普通的“select …”读操作，则通过MVCC来保证基本的隔离性。

锁分类

首先我们了解一下锁的分类。需要注意的是，基于不同的角度，有不同的锁分类方式。比较常见的锁分类方式有：表锁 & 行锁、读锁 & 写锁、行锁 & 间隙锁（gap lock） & 临键锁（next-key lock）。

表锁 & 行锁

这是基于锁粒度的一种锁分类方式，表锁锁的是整张表，而行锁锁的是一行或者多行记录。表锁的加锁速度较快，但是并发度不高，容易发生锁冲突；而行锁的加锁速度较慢，但是并发度较高，发生锁冲突的概率较小。

如何添加表锁？

如果是MyISAM存储引擎的话，由于MyISAM是不支持行锁的，一般的增删改SQL，以及我们显式加锁的查询语句，MyISAM都会加表锁，锁住整张表。

如果是InnoDB存储引擎的话，我们想要加表锁，需要通过SQL语句“lock tables … read/write”声明加表锁。

比如我们要对student表加一个写锁，那么SQL语句就是如下这样：

LOCK TABLES student WRITE;

如果我们要对student加一个读锁，那么SQL语句就是如下这样：

LOCK TABLES student READ;

如何添加行锁？

MyISAM存储引擎是不支持行锁的，所以如果要用MyISAM去加行锁的话，可以洗洗睡了。

InnoDB默认就是加行锁的，如果我们不显式声明加表锁，增删改操作，以及显式声明加锁的select语句都是加行锁的。

读锁 & 写锁

这是另一种锁分类的方式，读写也叫共享锁，读锁和读锁之间并不冲突，也就是说两个读锁可以同时对同一条记录上锁；而写锁与写锁之间是互斥的，一行记录同一时刻只能有一个写锁对其上锁，因此写锁也叫排他锁。

但是要注意的时，并非读操作就加读锁，写操作就加写锁，当我们提交的是“select … for update”语句时，虽然该语句是一个读操作，但是加的是写锁，也叫排他锁。

行锁 & 间隙锁（gap lock）& 临键锁（next-key lock）

这是InnoDB行锁里的分类：

• 行锁：只对一行记录加锁。
• 间隙锁（gap lock）：对两条记录中间的间隙加锁，锁住一个区间，但是不包含记录本身。
• 临键锁（next-key lock）：间隙锁 + 行锁，不仅对两条记录间的区间加锁，还会锁住记录本身，前开后闭，也就是锁住后面一条记录，前面的记录不会锁住。

我们通过画图去理解就知道了。

行锁是锁住一行记录：

间隙锁是锁住一段区间，但是不包含区间前后的记录本身：

next-key lock的加锁范围是前开后闭的，也就是两条记录间的区间（间隙），再加上该区间的后面一条记录：

加锁机制分析

接下来分析一下，各种不同情况下InnoDB都是如何加锁的，加的是行锁？间隙锁？还是next-key lock？然后分别对哪些行记录加锁。

在分析之前，我们首先要明白一点，InnoDB的行锁都是加在索引上的，也就是对索引上锁，InnoDB只会对扫描到的索引加锁，没有扫描到的索引是不会加锁的，并且只会对索引中被扫描到的行记录和间隙上锁。

可重复读隔离级别下的加锁机制

可重复读隔离级别下，加锁的原则是对被扫描到的索引行记录以及它前面的区间加临键锁next-key lock，但是等值查询时扫描到最后的一条不满足条件的记录则不用上锁，范围查询则连同最后一条扫描到的不满足条件的行记录也给锁上。

除此以外，唯一索引的等值查询，如果命中索引中的某个行记录，则只对该索引行记录加行锁，不再加间隙锁，相当于是临键锁优化成了行锁。

下面一一分析。

唯一索引等值查询

比如我们现在有一张表student，我们查询id等于5的这一条记录：

select * from student where id = 5 for update;

由于是唯一索引，InnoDB当匹配到满足条件的索引行记录时，就不会再扫描，因此在唯一索引等值查询匹配到满足条件的记录时，会对该记录加行锁，不会加间隙锁。

如果匹配不成功，就会加间隙锁，但是不会加next-key lock，因为是等值查询，扫描到最后的一条不满足条件的记录是不用上锁的。

非唯一索引等值查询

比如我们的表student中有一列age年龄字段，age字段建立了非唯一索引，现在我们查询age等于30的记录：

select id,age from student age = 30 lock in share mode;

假设student表存在age=30的行记录，并且是这样的排列顺序：

在age字段的索引中，与age=30的行记录紧挨着的分别是前面的age=20的行记录和后面的age=40的行记录。

那么加锁情况就是这样：

首先通过age字段的索引，定位到了age=30的行记录，由于不是唯一索引等值查询，因此要加临键锁，那么首先对age=30的字段，连同它前面大于age=20的这一段区间，也就是(20, 30]这一段左开右闭的区间加一个临键锁。然后由于该索引不是唯一索引，因此还要继续往后扫描。

然后扫描到age=40这一行记录，发现不满足条件，由于当前查询是等值查询，不需要对age=40这一行记录上锁，因此对(30,40)这一段左开右开的区间上间隙锁。

由于这里的sql使用的是“lock in share mode”加的读锁，所以只对二级索引加锁，如果使用的时“for update”加的写锁，还会给主键索引上满足条件的行加上行锁。

范围查询

比如我们查询student表中id大于等于5，小于10的记录：

select * from student where id >= 5 and id < 10 for update;

假设student表中的行记录是这样：

id字段是student表中的主键，在student表的主键索引中，存在id=5的行记录，然后id=5的行记录前面是id=1的行记录，后面是id=10的行记录。

那么加锁情况是这样：

由于主键id的索引是唯一索引，查找首先匹配到id=5的行记录，会加一个行锁。然后因为查询是范围查询，查找id大于等于5又小于10的记录。于是扫描到id=10的行记录，发现不满足“id < 10”的查询条件，于是不再往后扫描，但是由于不是等值查询，所以会把最后扫描到的id=10的行记录锁上，于是就添加了一个id在(5, 10]区间范围的临键锁。

读已提交隔离级别下的加锁机制

读已提交隔离级别下的加锁规则就相对简单了，读已提交隔离级别下只会加行锁，不会加间隙锁和临键锁。也就是说，无论是等值查询，还是范围查询，都只会对扫描到的行记录加行锁。

比如上面这个范围查询的例子，如果是在读已提交隔离级别下，加锁情况就变成这样：

如何强行释放锁

如果我们遇到了SQL执行一直被卡住，执行不下去，然后报了等待锁超时的错误，比如下面这种情况：

此时，如果我们想强行把被持有的锁释放掉，该如何操作呢？

如果是MySQL 5.7 版本，我们可以通过下面这个SQL查询到锁被哪个线程占有：

select * from sys.innodb_lock_waits where locked_table = '`库名`.`表名`' \G;

这个SQL会显示是几号线程占有着锁导致阻塞，然后我们可以使用KILL命令加上对应线程的pid（上面的查询语句会显示线程的pid），强行断开该线程对应的连接，比如占有锁的线程的pid是5，那么我们执行如下命令断开pid为5的线程的连接：

KILL 5;

连接一旦断开，占有的锁就自动被释放。