MySQL当中的Lock

1. 总览锁的类型

锁的类型：

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//对读取的记录加独占锁(X型锁)
select ... for update;

1.1.1. 锁兼容性矩阵

• 规则：意向锁（IS/IX）之间兼容，但与行级锁（S/X）存在互斥。

2. 全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。

3. 表锁

表锁的语法是 lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

- MDL(元数据锁)

另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

- IS/IX(意向锁)

还有就是意向锁。

• 在使用 InnoDB 引擎的表里对某些记录加上共享锁之前，需要先在表级别加上一个意向共享锁【IS】；
• 在使用 InnoDB 引擎的表里对某些纪录加上独占锁之前，需要先在表级别加上一个意向独占锁【IX】；

也就是，当执行插入、更新、删除操作，需要先对表加上意向独占锁，然后对该记录加独占锁。

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

4. 行级锁

通过下面这条语句可以查看当前mysql所有的锁

SELECT * FROM performance_schema.data_locks;

表中数据

4.1. 记录锁（X,REC_NOT_GAP）

当你的执行语句在唯一索引时，仅仅只会锁住当前记录.(一定是唯一索引，主键当然也是).因为能确定唯一一条记录所以只需要锁住一条。控制锁的最小粒度来提高性能。

例如查询时:

select * from user where uid = 3 for update;

更新时

update user set username = 'zhangsan' where uid = 3;

插入时

insert into user(uid,username,password) values(3,'zhangsan','123456');

删除同样的，这样都是仅仅会锁住当前记录.这只会锁住uid=3这一行

当然上述情况是当你锁住的值是存在的时候，如果不存在的时候。

4.2. 间隙锁

如果使用未加索引的列查询，例如

select * from user where username = 'zhangsan' for update;

将会锁住所有列，任何DML操作均被阻塞（性能极差). 不过这里区分细一点

当数据存在的时候，例如uid= 1,2,3等实际存在的数据，update与delete都会阻塞。

当数据不存在的时候，对于任何插入操作来说，他都会阻塞。但是对于delete与update来操作一条不存在的记录时,不会阻塞，因为没有影响到任意一条数据。执行情况为（没有任何记录影响）

原因：无索引时走全表扫描，所有数据间隙均被锁定.很容易理解，你找这个记录没有索引那就得找完一张表的记录才确认。所以就得锁完整张表的记录。一定要记住的是，行级锁存在于索引处，没有索引就是所有。

如果是锁住有索引的列，例如(表中9到12之间是没有数据的)

select * from user where uid > 9 and uid < 12 for update;

此时将会加上X,GAP锁，这个锁的范围是在（9,12），不包括9与12.

如果是以下sql

select * from user where uid > 9 and uid <= 12 for update;

加上的是X(临键锁)。此时你应该对临键锁与间隙锁之间的差别有点感觉啦。

4.2.1. 普通索引的等值匹配

还有一种特殊情况，这个索引是普通索引而不是唯一索引。此时执行以下sql

select * from user where username = 'test15' for update;

他锁住了这行记录的主键id,因为主键id是唯一的，来标记这行记录已经锁住（其实就是加了记录锁）。但是普通索引并不是唯一的，所以还有可能存在其他的记录，还得锁住与其他记录的间隙。

这里其实还是有点不容易理解的，为什么等值匹配还有间隙锁？

注意：普通索引他并不是唯一索引，所以这个值是可以重复的。如果你仅仅使用REC_NOT_GAP（记录锁），锁住的只是对应的一条记录，例如你想锁住username = test15的记录。

采用记录锁：

• 其他事务还是可以插入username = test15的值，因为你是记录锁，你锁住仅仅只是当你查询那一刻表中存在的test15的行数据。
• 那么此时就会出现幻读问题，因为此时此刻你在当前事务查到的test15只有两条数据，但是由于另一个事务的insert,那么test15便有了三条数据。产生了幻读现象。

所以我们得采用间隙锁，来避免其他的事务插入相同的索引值。

验证记录锁：另一个事务修改uid=15的数据，阻塞。

验证间隙锁：

username = test12 左区间临界值。阻塞

username = test13 中间不存在的值。阻塞

username = test15 右区间临界值。阻塞

而对于普通索引或者唯一索引的范围锁定基本都是一致的。例如

select * from user where uid > 17 for update;

将会锁住uid [主键/唯一索引]（15，20), [20,+∞)这个间隙。

select * from user where username > 'test17' for update;

将会锁住username [普通索引]（test15，test20), [test20,+∞)这个间隙。

4.3. 临键锁

如果查询语句为

select * from user where uid > 15 for update;

如果uid在15之后没有数据，那么将会呈现

这里出现了supremum pseudo-record。这是什么意思呢？

supremum pseudo-record： 代表表示当前事务对索引中 最大值之后的所有间隙施加了锁，当前uid最大为15，那么他将会在（15,+∞）这个区间加上间隙锁。你可以理解为他就是+∞

再举一个例子

select * from user where uid > 9 for update;

此时锁的记录为

每一个为临键值的数据，他的范围都是自身到自身左边第一个比他小的范围。与间隙锁唯一不同的是他会包括自身。例如此时的情况，uid=15这条记录会向前找14，那么他锁住的就是（14，15]这个间隙。其实这种多个间隙的情况，我们要找到间隙的左区间最小值，首先就是找到lock data最小值，这里就是12.然后顺着12再找到比他小的第一个值，也就是9.

所以锁住的范围为(9,12] , [12,15] , (15,+∞）。

4.4. 三者的区别

记录锁只会锁住具有唯一索引的行数据，所以记录锁只存在于唯一索引或者主键。

而对于间隙锁与临键锁两者只不过是右区间的临界值的开闭关系罢了

5. 彩蛋

5.1. 锁资源的处理

在我去尝试上锁和DML的尝试之后，发现啦MySQL的一些特点。

例如：当一条记录被上锁后，另一个事务来修改的时候会阻塞等待。会经历一段时间后超时而取消执行，同样我们使用ctrl+c也能终止掉这个操作，但是，此时我去查看锁的状态时，发现事务修改时他持有的锁并没有释放，也就是说在一个大事务操作里面，一个小事务中可能由于一些原因失效了，没有执行，但是他持有的锁的资源并没有被释放，只有当commit与rollback的时候才会去释放锁资源。

5.2. 一点问题

当对于普通索引执行以下语句

select * from user where username > 'test15'
and username < 'test20' for update;

锁的情况为

这里显示的是X临键锁，依照我们的经验锁的范围应该是（test15，test20],此时应该是不可以修改添加test20的值。但是是可以的，理论也是应该可以的，因为我们并没有去锁住test20这条记录，是严格小于符号。

而我执行小于等于时

select * from user where username > 'test15'
and username <= 'test20' for update;

此时依旧时X间隙锁，这是没问题的。是不可以修改添加test20以及test20之后的值的（普通索引等值匹配规则）。

所以猜测MySQL只是这里输出显示有问题，内部还是严格控制锁的范围。