【锁】MySQL间隙锁

【锁】MySQL间隙锁

前段时间系统老是出现insert死锁，很是纠结。经过排查发现是间隙锁！间隙锁是innodb中行锁的一种，但是这种锁锁住的却不止一行数据，他锁住的是多行，是一个数据范围。间隙锁的主要作用是为了防止出现幻读，但是它会把锁定范围扩大，有时候也会给我们带来麻烦，我们就遇到了。在数据库参数中，控制间隙锁的参数是：innodb_locks_unsafe_for_binlog，这个参数默认值是OFF，也就是启用间隙锁，他是一个bool值，当值为true时表示disable间隙锁。那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢？不一定！而且这个参数会影响到主从复制及灾难恢复，这个方法还尚待商量。

间隙锁的出现主要集中在同一个事务中先delete 后 insert的情况下，当我们通过一个参数去删除一条记录的时候，如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这个记录，然后删除，然后释放锁。如果这条记录不存在，问题就来了，数据库会扫描索引，发现这个记录不存在，这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间，锁住整个区间内的数据，一个特别容易出现死锁的间隙锁诞生了。

举个例子：
表task_queue
Id          taskId
1             2
3             9
10          20
40          41

开启一个会话： session 1

sql> set autocommit=0;

##

取消自动提交

sql> delete from task_queue where taskId = 20;
sql> insert into task_queue values(20, 20);

在开启一个会话： session 2

sql> set autocommit=0;

##

取消自动提交

sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(30, 25);

在没有并发，或是极少并发的情况下，这样会可能会正常执行，在Mysql中，事务最终都是穿行执行，但是在高并发的情况下，执行的顺序就极有可能发生改变，变成下面这个样子：
sql> delete from task_queue where taskId = 20;
sql> delete from task_queue where taskId = 25;
sql> insert into task_queue values(20, 20);
sql> insert into task_queue values(30, 25);

这个时候最后一条语句：insert into task_queue values(30, 25); 执行时就会爆出死锁错误。因为删除taskId = 20这条记录的时候，20 -- 41 都被锁住了，他们都取得了这一个数据段的共享锁，所以在获取这个数据段的排它锁时出现死锁。

这种问题的解决办法：前面说了，通过修改innodb_locaks_unsafe_for_binlog参数来取消间隙锁从而达到避免这种情况的死锁的方式尚待商量，那就只有修改代码逻辑，存在才删除，尽量不去删除不存在的记录。

20.3.5 间隙锁（Next-Key锁）

《深入浅出MySQL——数据库开发、优化与管理维护》从数据库的基础、开发、优化、管理4方面对MySQL进行了详细的介绍，其中每一部分都独立成篇，每一篇又包括多个章节。本书面向实用，内容覆盖广泛，讲解由浅入深，适合于各个层次的读者。本文介绍了InnoDB锁。

20.3.5 间隙锁（Next-Key锁）

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

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

Select * from  emp where empid > 100 for update;

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

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

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

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

在如表20-13所示的例子中，假如emp表中只有101条记录，其empid的值分别是1,2,......,100,101。

表20-13 InnoDB存储引擎的间隙锁阻塞例子

tbody>

session_1	session_2
mysql> select @@tx_isolation; +-----------------+ \| @@tx_isolation \| +-----------------+ \| REPEATABLE-READ \| +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +-----------------+ \| @@tx_isolation \| +-----------------+ \| REPEATABLE-READ \| +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
当前session对不存在的记录加for update的锁： mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec)
	这时，如果其他session插入empid为201的记录（注意：这条记录并不存在），也会出现锁等待： mysql>insert into emp(empid,...) values(201,...); 阻塞等待
Session_1 执行rollback： mysql> rollback; Query OK, 0 rows affected (13.04 sec)
	由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录： mysql>insert into emp(empid,...) values(201,...); Query OK, 1 row affected (13.35 sec)

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks

Next-Key Locks

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:
(negative infinity, 10]