MySQL记录锁、间隙锁、临键锁（Next-Key Locks）加锁过程
- innodb一定存在聚簇索引，默认以主键作为聚簇索引
- 有几个索引，就有几棵B+树(不考虑hash索引的情形)
- 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引（id主键值），指向聚簇索引B+树。
锁类型:
共享锁(S锁):假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。
排他锁(X锁):假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A。
我们通过update、delete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。
意向共享锁(IS锁):一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。
意向排他锁(IX锁):一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。
加锁算法:
Record Locks：简单翻译为行锁吧，即锁住一条记录。注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！
Gap Locks：简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read Uncommited时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。即锁定一个区间，左开右开。
Next-Key Locks：这个理解为Record Lock+索引前面的Gap Lock，记录锁+间隙锁锁定的区间，左开右闭。记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13和20。那么，间隙锁的范围如下：
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
加锁过程
加锁的基本单位为next-key lock = 间隙锁+行锁
无论什么情况下，InnoDB 会往前扫描到第一个不满足条件的行为止。
数据准备
| id | a | b |
|---|---|---|
| 0 | 0 | 0 |
| 4 | 4 | 4 |
| 8 | 8 | 8 |
| 16 | 16 | 16 |
| 32 | 32 | 32 |
其中，id 是主键索引（唯一索引），b 是普通索引（非唯一索引），a 是普通的列
唯一索引等值查询
结论
当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」。
当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」。
Demo(唯一索引等值查询记录存在)
会话1加锁变化过程如下：
- 加锁的基本单位是 next-key lock，因此会话1的加锁范围是(8, 16];
- 但是由于是用唯一索引进行等值查询，且查询的记录存在，所以 next-key lock 退化成记录锁，因此最终加锁的范围是 id = 16 这一行。
所以，会话 2 在修改 id=16 的记录时会被锁住，而会话 3 插入 id=9 的记录可以被正常执行
Demo(唯一索引等值查询记录不存在)
会话1加锁变化过程如下：
- 加锁的基本单位是 next-key lock，因此主键索引 id 的加锁范围是(8, 16];
- 但是由于查询记录不存在，next-key lock 退化成间隙锁，因此最终加锁的范围是 (8,16)。
所以，会话 2 要往这个间隙里面插入 id=9 的记录会被锁住，但是会话 3 修改 id =16 是可以正常执行的，因为 id = 16 这条记录并没有加锁。
唯一索引范围查询
会话 1 加锁变化过程如下：
- 最开始要找的第一行是 id = 8，因此 next-key lock(4,8]，但是由于 id 是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 8 这一行加锁；
- 由于是范围查找，就会继续往后找存在的记录，也就是会找到 id = 16 这一行停下来，然后加 next-key lock (8, 16]，但由于 id = 16 不满足 id < 9，所以会退化成间隙锁，加锁范围变为 (8, 16)。
所以，会话 1 这时候主键索引的锁是记录锁 id=8 和间隙锁(8, 16)。
会话 2 由于往间隙锁里插入了 id = 9 的记录，所以会被锁住了，而 id = 8 是被加锁的，因此会话 3 的语句也会被阻塞。
由于 id = 16 并没有加锁，所以会话 4 是可以正常被执行。
普通索引等值查询
结论
当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
Demo(普通索引等值查找记录存在)
会话 1 加锁变化过程如下：
- 先会对普通索引 b 加上 next-key lock，范围是(4,8];
- 然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是(8,16)。
所以，会话1的普通索引 b 上共有两个锁，分别是 next-key lock (4,8] 和间隙锁 (8,16) 。
那么，当会话 2 往间隙锁里插入 id = 9 的记录就会被锁住，而会话 4 是因为更改了 next-key lock 范围里的记录而被锁住的。
然后因为 b = 16 这条记录没有加锁，所以会话 5 是可以正常执行的。
Demo(普通索引等值查找记录不存在)
会话 1 加锁变化过程如下：
- 先会对普通索引 b 加上 next-key lock，范围是(8,16];
- 但是由于查询的记录是不存在的，所以不会再额外加个间隙锁，但是 next-key lock 会退化为间隙锁，最终加锁范围是 (8,16)。
会话 2 因为往间隙锁里插入了 b = 9 的记录，所以会被锁住，而 b = 16 是没有被加锁的，因此会话 3 的语句可以正常执行。
普通索引范围查找
会话 1 加锁变化过程如下：
- 最开始要找的第一行是 b = 8，因此 next-key lock(4,8]，但是由于 b 不是唯一索引，并不会退化成记录锁。
- 但是由于是范围查找，就会继续往后找存在的记录，也就是会找到 b = 16 这一行停下来，然后加 next-key lock (8, 16]，因为是普通索引查询，所以并不会退化成间隙锁。
所以，会话 1 的普通索引 b 有两个 next-key lock，分别是 (4,8] 和(8, 16]。这样，你就明白为什么会话 2 、会话 3 、会话 4 的语句都会被锁住了。
总结
唯一索引等值查询：
1.当查询的记录是存在的，next-key lock 会退化成「记录锁」。
2.当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
非唯一索引等值查询：
1.当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
2.当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于：
唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。
怎样加锁的
非唯一索引等值查询
例如：在下面这张表当中，id为主键索引，age普通索引。
| id | age | name | sex |
|---|---|---|---|
| 1 | 15 | abc | 男 |
| 2 | 20 | def | 女 |
| 3 | 25 | ghi | 男 |
| 4 | 29 | lmn | 女 |
| 5 | 35 | opq | 男 |
当查询记录不存在的时候:锁住这个二级索引最近的范围(使用间隙锁)
例如:
select*from table where age=16 for update;
这个时候,age这一列对应查询的值16不在表当中。
因此，加锁的过程就是这样的:首先根据age,name构成的这两列构成的索引列进行扫描，扫描到的第一条不符合条件的二级索引记录就会退化为间隙锁.
也就是间隙锁的范围是(15,20)，锁住的对象是age这一列的索引，不会对主键索引加锁。
当查询记录存在的时候:先锁住二级索引(next-key lock)，然后锁住主键索引(Record-lock)，最后锁住二级索引的下一行（间隙锁)
例如：执行
select*from table where age=22 for update;
这个时候，age=22在这一个索引当中。
| id(主键) | (age | name) | sex |
|---|---|---|---|
| 1 | 19 | abc | 男 |
| 2 | 20 | def | 女 |
| 4 | 22 | lmn | 女 |
| 5 | 39 | opq | 男 |
因此，加锁的过程分为3个步骤:
| 步骤 | 对于二级索引 | 对于主键索引 |
|---|---|---|
| 1 | 对于age=22往下的范围，加锁。范围是(20,22]的X型的next-key lock | |
| 2 | age=22对应的主键索引为id=4;于是对id=4的主键索引加上X型的记录锁(record-lock) | |
| 3 | 对于(22,39)的二级索引范围加上X型间隙锁。 |
非唯一索引范围查询
例如查询的sql语句为:
首先，开启一个事物:
然后执行下面的查询语句
select*from table where age>=25 for update
| id | (age | name) | sex |
|---|---|---|---|
| 1 | 15 | abc | 男 |
| 2 | 20 | def | 女 |
| 3 | 25 | ghi | 男 |
| 4 | 30 | opq | 男 |
一共涉及5次加锁
| 步骤 | 说明 |
|---|---|
| 第一步 | 由于涉及等值查询，因此对于二级索引age加上next-key lock，锁住的范围为(20,25]. |
| 第二步 | 对于age=25这一行数据的主键索引加锁，主键索引为3。 |
| 第三步 | 因为涉及范围查询，因此需要扫描已经存在的，最近的一行二级索引的记录， 对于id=4这一行的主键索引加记录锁(record_lock) |
| 第四步 | 对于(25,30]的范围的二级索引加上间隙锁(next-key lock) |
| 第五步 | 对于(30,＋∞）的二级索引加上间隙锁 |
没有加索引的查询
| id | (age | name) | sex |
|---|---|---|---|
| 1 | 15 | abc | 男 |
| 2 | 20 | def | 女 |
例如，在上表当中，对于sex这一列，没有设计索引，那么查询：
select*from table where sex='男' for update
这样的语句的时候，就会导致全表扫描。因此，就会对于每一条记录的主键索引加上next-key lock,这样其他事物都没有办法对于这张表进行任何增删改以及携带update的查询操作。
因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。
delete删除数据的语句条件上加索引
从性能和加锁来看delete操作；
一般来说，DELETE的加锁和SELECT FOR UPDATE 或 UPDATE 并没有太大的差异；
因为，在MySQL数据库中，执行DELETE语句其实并没有直接删除记录，而是在记录上打上一个删除标记，然后通过后台的一个叫做purge的线程来清理；从这一点来看，DELETE和UPDATE确实是非常相像；事实上，DELETE和UPDATE的加锁也几乎是一样的；
如果更新条件可以走索引，则间隙锁会加在条件所在的索引位置的前后间隙；如果查询条件没走索引走全表扫描，则对全表所有行之间加间隙锁；
MySQL delete语句的加锁分析总结可参考：
因此建议删除语句的条件尽量走索引查询，或者先查出这条记录的主键ID，再根据主键ID（唯一索引）条件删除；
在mysql中select分为快照读和当前读，执行下面的语句：
1、下面这个sql 执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为Serializable中不成立。）
select * from table where id = ?;
2、那么这个sql 是当前读，会对读取记录加S锁 (共享锁)。
select * from table where id = ? lock in share mode;
3、最后下面这个sql 会对读取记录加X锁（排它锁），这是悲观锁的一种实现形式。
select * from table where id = ? for update
现在，大家考虑下，上面两个加锁查询的sql，是加的表锁（将整个表锁住）还是加的行锁（将行记录锁住）呢？
针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示:
Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是*一定程度上*，并没有完全避免! 另外就是记住从该级别才开始加入间隙锁!Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。
关于是表锁还是行锁
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
这句话本身有错误！
错误:并不是用表锁来实现锁表的操作，而是利用了*Next-Key Locks*，也可以理解为是用了行锁+间隙锁来实现锁表的操作!**
为了便于说明，我来个例子，假设有表数据如下，pId为主键索引
| pId(int) | name(varchar) | num(int) |
|---|---|---|
| 1 | aaa | 100 |
| 2 | bbb | 200 |
| 7 | ccc | 200\ |
执行语句(name列无索引)
select * from table where name = `aaa` for update
那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此， 给人一种整个表锁住的错觉！
分析
下面来对开始的问题作出解答，假设有表如下，pId为主键索引
| pId(int) | name(varchar) | num(int) |
|---|---|---|
| 1 | aaa | 100 |
| 2 | bbb | 200 |
| 3 | bbb | 300 |
| 7 | ccc | 200 |
RR/Serializable+条件列非索引
RR级别需要多考虑的就是gap lock 间隙锁，他的加锁特征在于，无论你怎么查都是锁全表( 使用行锁+间隙锁实现锁全表 )。
如下所示，接下来分析开始
(1)、在RR级别下，不加任何锁，是快照读。
在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num = 200
(2)、在RR级别下，不加任何锁，是快照读。
在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num > 200
(3)、在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num = 200 lock in share mode
(4)、在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num > 200 lock in share mode
(5)、在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num = 200 for update
(6)、在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
select * from table where num > 200 for update
RR/Serializable+条件列是聚簇索引
恩，大家应该知道pId是主键列，因此pId用的就是聚簇索引。该情况的加锁特征在于，如果where后的条件为精确查询(=的情况)，那么只存在record lock。如果where后的条件为范围查询(>或<的情况)，那么存在的是record lock + gap lock。
(1)、在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock
select * from table where pId = 2
(2)、在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
select * from table where pId > 2
(3)、是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。
select * from table where pId = 2 lock in share mode
(4)、是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock
select * from table where pId > 2 lock in share mode
(5)、是当前读，在pId=2的聚簇索引上加X锁。
select * from table where pId = 2 for update
(6)、在pId=3,7的聚簇索引上加X锁。在(2,3)(3,7)(7,+∞)加上gap lock
select * from table where pId > 2 for update
(7)、注意了，pId=6是不存在的列，这种情况会在(3,7)上加gap lock。
select * from table where pId = 6 [lock in share mode|for update]
(8)、注意了，pId>18，查询结果是空的。在这种情况下，是在(7,+∞)上加gap lock。
select * from table where pId > 18 [lock in share mode|for update]
RR/Serializable+条件列是非聚簇索引
这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。
先说一下，唯一索引的情况。如果是唯一索引，情况和RR/Serializable+条件列是聚簇索引类似，唯一有区别的是:这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上！
下面说一下，非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在record lock，还存在gap lock。而通过唯一索引进行精确查询后，只存在record lock，不存在gap lock。
老规矩在num列建立非唯一索引：
(1)、在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。
select * from table where num = 200
(2)、在RR级别下，不加任何锁，是快照读。
在Serializable级别下，是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock
select * from table where num > 200
(3)、是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。
select * from table where num = 200 lock in share mode
(4)、是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock。
select * from table where num > 200 lock in share mode
(5)、是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加X锁，在(100,200)(200,300)加上gap lock。
select * from table where num = 200 for update
(6)、是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加X锁。在(200,300)(300,+∞)加上gap lock
select * from table where num > 200 for update
(7)、注意了，num=250是不存在的列，这种情况会在(200,300)上加gap lock。
select * from table where num = 250 [lock in share mode|for update]
(8)、注意了，pId>400，查询结果是空的。在这种情况下，是在(400,+∞)上加gap lock。
select * from table where num > 400 [lock in share mode|for update]
