Innodb行锁

Innodb支持三种行锁

1、行锁（Record Lock）：锁直接加在索引记录上面而不是行数据，锁住的是key。

2、间隙锁（Gap Lock）：锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别的。

3、Next-Key Lock： 行锁和间隙锁组合起来就叫做Next-Key Lock。

Innodb的默认使用Next-Key Lock

默认情况下，Innodb工作在可重复读隔离级别下，并且加锁时会以Next-Key Lock的方式对数据进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。

Innodb的Next-Key Lock加锁规则

优化1： 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

优化1具体说明和例子：

当使用“唯一索引”来“搜索唯一行”的语句时，不需要间隙锁定，比如name是唯一索引，只搜索name=“tom”，那么只会对这一行使用记录锁。

select * from t where name = "tom" for update;// 注意：普通查询是快照读，不需要加锁

如果name列没有建立索引或者是非唯一索引时，则语句会产生间隙锁。

如果搜索条件里有多个查询条件（即使每个列都有唯一索引），也是会有间隙锁的。

间隙锁不是互斥的

两个事务加间隙锁相互之间不是互斥的，a事务锁住（5，10）的数据不让操作，b事务也能锁住（5，10）不让操作，这是可能会发生死锁问题。

一些命令行操作

1）2种方式禁用间隙锁：

1、把隔离级别降为读已提交。 

2、开启参数innodb_locks_unsafe_for_binlog。

关闭间隙锁(gap lock)方法:

在my.cnf里面的[mysqld]添加

[mysqld]

innodb_locks_unsafe_for_binlog = 1

重启MySQL后生效.

2）查看是否开启间隙锁：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+
1 row in set (0.00 sec)

innodb_locks_unsafe_for_binlog：默认值为0，即启用gap lock。

3）innodb_locks_unsafe_for_binlog设置

这个参数最主要的作用就是控制innodb是否对gap加锁。

这个设置不影响外键和唯一索引（含主键）对gap进行加锁的需要。

开启innodb_locks_unsafe_for_binlog的REPEATABLE-READ事务隔离级别，很大程度上已经蜕变成了READ-COMMITTED。

Innodb的Next-lock加锁实践

一、非索引字段走主键索引

1）执行准备

CREATE TABLE `tb` (
    `id` INT ( 11 ) NOT NULL,
    `a` INT ( 11 ) DEFAULT NULL,
    `b` INT ( 11 ) DEFAULT NULL,
    PRIMARY KEY ( `id` ),
KEY `a` ( `a` )) ENGINE = INNODB;

INSERT INTO tb
VALUES
    ( 0, 0, 0 ),
    (10,10,10 ),
    (20,20,20 ),
    (30,30,30 ),
    (40,40,40 ),
    (50,50,50 );

2） 开启两个事务，左边执行select * from...for update，会加next-key lock。右边执行insert语句会阻塞。

3）执行分析

select * from tb where b = 10 for update;

加锁是要基于索引的，因为字段b不是索引，所以就选用主键索引，加锁就加在主键上。

遍历主键索引，发现b=10时，需要在前后记录之间加锁，所以在前一主键记录(0,0,0)和本记录主键加锁（0，10], 在后一条主键记录（20，20）和本记录之间加锁（10，20]。然后继续向右遍历，判断b=20，!=10, 满足优化2规则，next-key lock退化为间隙锁，边成（10，20）。同时b=10加了行锁，汇总范围（0，20），针对id主键。

二、用到二级索引， 还符合覆盖索引的情况

1）执行准备，同上

CREATE TABLE `tb` (
    `id` INT ( 11 ) NOT NULL,
    `a` INT ( 11 ) DEFAULT NULL,
    `b` INT ( 11 ) DEFAULT NULL,
    PRIMARY KEY ( `id` ),
KEY `a` ( `a` )) ENGINE = INNODB;

INSERT INTO tb
VALUES
    ( 0, 0, 0 ),
    (10,10,10 ),
    (20,20,20 ),
    (30,30,30 ),
    (40,40,40 ),
    (50,50,50 );

2） 开启两个事务

左边执行 select id from tb where a = 20 for update

右边执行insert语句，有四个，有个阻塞，有的可以。

3）分析 

因为条件用了索引a，查询字段id在索引a中，用到了覆盖索引，索引在搜索过程中，只用到了索引a，所以只会在索引a中间隙锁，同时命中条件对应的主键也要加上行锁，所以主键id=20被加了行锁。锁的范围是（a=10，id=10）和（a=30，id=30）之间。

如上图，

场景1，要插入的索引是（11，5），在范围内，所以阻塞。

场景2，要插入的索引是（10，15），在范围内，所以阻塞。

场景3，要插入的索引是（9，17），不再范围内。

场景4，要插入的索引是（35，25），不再范围内。