mysql间隙锁 打开_MySQL数据库间隙锁

概念

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)。

举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

select * from user where user_id > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于101(这些记录并不存在)的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要

开启和关闭

查看是否开启间隙锁：

mysql> show variables like 'innodb_locks_unsafe_for_binlog';

+--------------------------------+-------+

| Variable_name                  | Value |

+--------------------------------+-------+

| innodb_locks_unsafe_for_binlog | OFF   |

+--------------------------------+-------+

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

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

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

[mysqld]

innodb_locks_unsafe_for_binlog = 1

重启MySQL后生效.

测试

建表innodb_lock：

DROP TABLE IF EXISTS`innodb_lock`;CREATE TABLE`innodb_lock` (

`a`int(10) NOT NULL,

`b`varchar(255) NOT NULL DEFAULT '',KEY`index_a` (`a`),KEY`index_b` (`b`)

) ENGINE=InnoDB;

插入数据，注意这里边没有a为2的数据：

INSERT INTO `innodb_lock` VALUES ('1', 'b2');INSERT INTO `innodb_lock` VALUES ('3', '3');INSERT INTO `innodb_lock` VALUES ('4', '4000');INSERT INTO `innodb_lock` VALUES ('5', '5000');INSERT INTO `innodb_lock` VALUES ('6', '6000');INSERT INTO `innodb_lock` VALUES ('7', '7000');INSERT INTO `innodb_lock` VALUES ('8', '8000');INSERT INTO `innodb_lock` VALUES ('9', '9000');

(1)开启两个客户端，修改事务隔离级别为可重复读

(2)开启事务，在左侧客户端批量修改a为1~6范围内的数据。在右侧客户端插入a为2的数据。右侧操作被阻塞。说明有间隙锁。

(3)重复(2)，事务隔离级别依然是repeatable read，只不过变成在右侧客户端插入a为10的数据，成功。

(4)事务隔离级别设置为read committed，重复步骤(2)，发现右侧客户端的操作成功。说明该隔离级别无间隙锁。

(5)还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！左侧客户端给不存在的记录加锁，右侧客户端的增加操作阻塞。

但是，如果a是唯一索引，不会升级全表锁。先添加唯一索引：

(6)重复步骤(5)，发现右侧客户端不会被阻塞，数据插入成功

小结：

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁，仔细想想的话，这个并不难理解，大家也可以自己测试一下。

要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。