MySQL锁（深入浅出）

发表于 3年以前 | 总阅读数：427 次

引言

为了解决多个进程访问内存或磁盘中的同一份数据造成的冲突，通常有两种解决方案，一种是多版本；另一种就是锁。MySQL作为一种关系型数据库，其实也是通过这两种方式来解决数据访问冲突的。MySQL数据多版本叫MVCC，同时MySQL使用了各种类型的锁来保证数据一致性。

锁的介绍

锁的分类：按行为分类

我们对数据库的操作分为读和写，MySQL在这个层面把锁分成了共享锁（Shared Lock）和独占锁（Exclusive Lock）也称为S锁和X锁。对于相同的数据S锁可以存在多个，但是不能和X锁共存；X锁本身也不可以多个共存。MySQL为了协调表和行数据读写关系还对两个动作设计了锁，那就是“想读”和“想写”；“想读”对应的是意向共享锁（Intention Shared Lock），“想写”对应的是意向独占锁（Intention Exclusive Lock），对于这两个意向锁我们后面再介绍具体用途。

概括来讲：MySQL从用户行为上分了四类锁

读：S锁

写：X锁

想读：IS锁

想写：IX锁锁的分类：按加锁对象按照加锁对象的不同，分为表锁（对一个表加锁）和行锁（对某行数据加锁）。

MySQL的MyISAM、MEMORY、MERGE三种存储引擎只支持表锁，只有InnoDB支持行锁。接下来我们主要结束下InnoDB中的一些表锁和行锁。

前面按照行为和对象给锁分类，都只是MySQL中锁的一些归类，并不代表具体的锁，MySQL中具体用到的锁我们接下来详细介绍。MySQL有四大存储引擎，MyISAM、MEMORY、MERGE、InnoDB，其中只有InnoDB支持事务和行锁，使用也最为广泛，我们接下来主要介绍InnoDB引擎中的锁。

InnoDB中的表锁：

1.MDL锁

我们在执行DML（SELECT INSERT UPDATE DELETE）语句时，是不会对表加S、X锁的；但是当我们执行DDL语句时，MySQL会加元数据锁（MetadataLock）简称MDL锁。

这个时候大家就需要注意了，如果有事务在对表执行DML操作，就会阻塞我们的DDL操作，同时MDL锁，又会阻塞后面的事务。

在使用“锁定读”或者写操作时，需要给InnoDB中行添加对应的S、X锁，在给行记录添加锁之前，需要对应的先给表添加IS和IX锁。IS和IX锁的作用是在需要添加表级别S、X锁时，判断表内是否有某些行数据存在S、X锁（避免遍历表的所有行）。这里额外解释一下锁定读：

我们想在读取记录时就为数据加锁的语句称为锁定读，MySQL支持下面两种锁定读的语句：

SELECT...LOCK IN SHARE MODE; 对记录加S锁

SELECT...FOR UPDATE; 对记录加X锁

2.自增主键相关的锁

我们经常会用到自增索引，AUTO_INCREMENT。为了保证插入的主键是连续递增的，当我们执行Insert语句时，“可能会”给表添加表锁，锁的名字叫：AUTO-INC。不过这个表锁在分配完ID后就释放了，不用等到事务提交。这里为什么说“可能会”。因为如果我们Insert语句插入的数据是可以确定条数的，其实可以使用一个单独的锁，为本次插入生成需要的自增主键，然后就立马释放锁。采用轻量级锁还是AUTO-INC也是可以配置的，MySQL有一个系统变量：innodb_autoinc_lock_mode。如果设置为0表示一律使用AUTO-INC锁；如果为1表示插入记录数确定时采用轻量级锁，不确定时采用AUTO-INC锁；如果为2表示一律采用轻量级锁。我们通常会设置为1

InnoDB中的行锁 1.对MySQL中的一条记录加锁，称为记录锁（LOCK_REC_NOT_GAP），记录锁有S、X两种

2. MySQL为了解决在REPEATABLE READ隔离级别下幻读的问题，引入了间隙锁（LOCK_GAP）。举一个例子，假设我们表中有两条记录：

id：5 name：小明

id：8 name：小张

当我们在5和8两条记录中间加GAP锁的时候，两条记录之间是不允许插入数据的。这里有个小问题，中间的记录可以有“间隙”，那第一条记录和最后一条记录怎么办呢？MySQL会给表生成两条伪记录，Infimum和Supermum，前者代表页面中的最小记录，后者代表页的最大记录，gap锁是为了防止插入的数据引起幻读而设计的。

3.如果即要锁住一条记录又要锁住记录的“间隙”，需要Next-key Lock。Next-key Lock本质上就是记录锁+间隙锁

4.插入意向锁，当我们在事务中想要插入一条记录时，需要判断此间隙是否存在gap锁，如果没有的话直接插入就可以了，如果存在gap锁，需要把这个插入数据的意向记录下来，并标记当前处于等待状态，我们把记录插入意向的锁结构称为插入意向锁（LOCK_INSERT_INTENTION）。当gap锁被释放的时候，此区间的插入意向锁们，会同时执行插入操作。

5.延迟加锁：MySQL对INSERT语句采用了延迟加锁的机制。

当执行一条INSERT语句时，（当然，如果插入间隙存在“间隙锁”，需要等到间隙锁的释放，并生成插入意向锁）对于主键索引来说是不加锁的，但是会为当前记录生成一个事务ID（trx_id），当其他事务想要对此语句加S、X锁时，首先看一下当前记录的trx_id是否活跃，如果已经完成则直接加锁，如果还没有完成就为当前记录上的trx_id代表的事务添加一把X锁，同时自己创建一个意向锁。

如果是二级索引，本身没有trx_id，但是二级索引的每一页都有当前页最大的trx_id，如果当前页最大trx_id小于当前最小的活跃trx_id则说明该页所做的修改已经提交，如果不是的话需要通过二级索引找到主键索引，对其做上一步的判断操作。

常用SQL的加锁过程

我们先构造一张curriculum(课程表)：其中id是主键 name是二级索引

id	name	teacher
1	思维	t1
3	英语	t3
5	AI	t5
7	绘画	t7

我们分别介绍下各种语句的加锁情况，默认是在REPESTABLE READ隔离级别和INNODB引擎下

1.普通查询：select * from curriculum where id = 3;

在REPESTABLE READ隔离级别下，只在第一次查询时，生成读的“数据视图”，来避免脏读、不可重复读和幻读。

但是这里有一种情况下是存在幻读的：

事务一：

第一步：select * from curriculum where id = 2;
第二步：update curriculum set name="音乐1" where id = 2；
第三步：select * from curriculum where id = 2;

事务二：

insert into curriculum value （2，“音乐0”，“t2”）

他们的执行顺序是:

事务一第一步 --> 事务二执行完 -->事务一第二步 --> 事务一第三步。

因为第一步执行完事务一并没对表里的记录加锁，事务二可以成功执行INSERT语句。这个时候事务一执行第二步，此时UPDATE语句需要更新“当前读”的数据，因此会更新事务二插入的id=2的这条记录；然后此时id=2这条记录的最新trx_id变成了事务一的trx_id，所以第三部就查询到了id=2的记录，也就出现了两次查询结果不一致的情况。

2.锁定读、UPDATE、DELETE：这几条语句的加锁方式基本是一样的，我们就只举一个UPDATE的例子

update curriculum set name = "音乐" where id >2 and id <=6;