MySql进阶-间隙锁（gap-key）

怼烎@ 2022-12-01 11:56 103阅读 0赞

> [可参考][Link 1]  
> 参考《InnoDB存储引擎》  
> 注意：gap-key是innodb存储引擎来解决幻读的。对于隔离级别下的可重复读仍然是无法解决幻读问题的。

> tips:SELECT…FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。  
> 使用这两种一致性锁定读的办法注意开启事务，提交事务，来锁定与释放锁。

## Innodb锁算法 ##

*  Record Lock：单个行记录上的锁
 *  Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
 *  Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

对于Record Lock会去锁索引，如果表在建立的时候没有设置任何一个索引，那么innodbhui使用隐式的主键来当这个索引来锁定。

Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内。

**InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。这种情况发生在查询的列是唯一索引的情况下。**  
若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么如果查询其实是范围类型查询，而不是精准类型查询，InnoDB存储引擎会使用Next-Key Lock进行锁定。

在InnoDB存储引擎中，对于INSERT的操作，其会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许查询。

## 关闭Gap Lock ##

*  将事务的隔离级别设置为READ COMMITTED
 *  将参数innodb\_locks\_unsafe\_for\_binlog设置为1

## Gap-key 解决的问题 ##

在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免`幻读`。

> 在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

举例（细节略）：

<table> 
 <thead> 
  <tr> 
   <th>事务1</th> 
   <th>事务2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>select * from table where int&gt;2 for update</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>结果 3 5</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>insert into table (4)</td> 
  </tr> 
  <tr> 
   <td>select * from table where int &gt;2 for update</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>结果3 4 5</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

在同一事务内，前后两次执行同一sql语句，结果不一样。而当使用Gap-key的时候，就可以在 （2，+∞）加上锁，禁止其他事务进行插入。

> InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，Innobb其采用Next-Key Locking的方式来加锁,解决幻读问题。

[Link 1]: https://www.jianshu.com/p/32904ee07e56