MYSQL（04）-间隙锁详解

怼烎@ 2023-10-04 13:09 13阅读 0赞

间隙锁（Gap Lock）是Innodb在提交下为了解决幻读问题时引入的锁机制，（下面的所有案例没有特意强调都使用可重复读隔离级别）幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现的

加锁规则有以下特性，我们会在后面的案例中逐一解释：

*  1.加锁的基本单位是（next-key lock）,他是前开后闭原则
 *  2.插叙过程中访问的对象会增加锁
 *  3.索引上的等值查询--给唯一索引加锁的时候，next-key lock升级为行锁
 *  4.索引上的等值查询--向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
 *  5.唯一索引上的范围查询会访问到不满足条件的第一个值为止

案例数据

以上数据为了解决幻读问题，更新的时候不只是对上述的五条数据增加行锁，还对于中间的取值范围增加了6间隙锁，（-∞，5\]（5，10\]（10，15\]（15，20\]（20，25\]（25，+supernum\] （其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。）

案例一：间隙锁简单案例

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> select * from t where id = 11 for update;</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>insert into user value(12,12,12)</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>commit;</td> 
   <td>-</td> 
  </tr> 
 </tbody> 
</table>

当有如下事务A和事务B时，事务A会对数据库表增加（10，15\]这个区间锁，这时insert id = 12 的数据的时候就会因为区间锁（10，15\]而被锁住无法执行。

#### 案例二： 间隙锁死锁问题 ####

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> select * from t where id = 9 for update;</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>begin;<br> select * from t where id = 6 for update;</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>-</td> 
   <td>insert into user value(7,7,7)</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>insert into user value(7,7,7)</td> 
   <td>-</td> 
  </tr> 
 </tbody> 
</table>

不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务A获取到了（5,10\]之间的间隙锁，另一个事务B也可以获取到（5,10\]之间的间隙锁。这时就可能会发生死锁问题，如下案例。  
事务A获取到（5,10\]之间的间隙锁不允许其他的DDL操作，在事务提交，间隙锁释放之前，事务B也获取到了间隙锁（5,10\]，这时两个事务就处于死锁状态

#### 案例三： 等值查询—唯一索引 ####

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
   <th>事务C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> update u set d= d+ 1 where id = 7;</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>insert into u (8,8,8);</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>-</td> 
   <td>-</td> 
   <td>update set d = d+ 1 where id = 10</td> 
  </tr> 
 </tbody> 
</table>

1.加锁的范围是（5,10\]的范围锁  
2.由于数据是等值查询，并且表中最后数据id = 10 不满足id= 7的查询要求，故id=10 的行级锁退化为间隙锁，（5,10）  
3.所以事务B中id=8会被锁住，而id=10的时候不会被锁住

#### 案例四： 等值查询—普通索引 ####

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
   <th>事务C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> select id form t where c = 5 lock in share mode;</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>update t set d = d + 1 where id = 5</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>-</td> 
   <td>-</td> 
   <td>insert into values (7,7,7)</td> 
  </tr> 
 </tbody> 
</table>

1.加锁的范围是（0,5\]，（5,10\]的范围锁  
2.由于c是普通索引，根据原则4，搜索到5后继续向后遍历直到搜索到10才放弃，故加锁范围为（5,10\]  
3.由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为（5,10）  
4.因为加锁是对普通索引c加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务B执行正常  
5.因为加锁范围（5,10）故事务C执行阻塞  
6.需要注意的是，lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住

#### 案例五： 范围查询—唯一索引 ####

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
   <th>事务C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> select * form t where id &gt;= 10 and id &lt;11 for update</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>insert into values(8,8,8)<br> insert into values(13,13,13)</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>-</td> 
   <td>-</td> 
   <td>update t set d = d+ 1 where id = 15</td> 
  </tr> 
 </tbody> 
</table>

1.  next-key lock 增加范围锁（5,10\]
2.  根据原则5，唯一索引的范围查询会到第一个不符合的值位置，故增加（10，15\]  
    3.因为等值查询有id =10 根据原则3间隙锁升级为行锁，故剩余锁\[10,15\]  
    4.因为查询并不是等值查询，故\[10,15\]不会退化成\[10,15)  
    5.故事务B（13,13,13）阻塞，事务C阻塞

#### 案例六： 范围查询—普通索引 ####

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
   <th>事务C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> select * form t where c &gt;= 10 and c &lt;11 for update</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>insert into values(8,8,8)</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>-</td> 
   <td>-</td> 
   <td>update t set d = d+ 1 where c = 15</td> 
  </tr> 
 </tbody> 
</table>

1.  next-key lock 增加范围锁（5,10\]，（10，15\]  
    2.因为c是非唯一索引，故（5,10\]不会退化为10  
    3.因为查询并不是等值查询，故\[10,15\]不会退化成\[10,15)  
    4.所以事务B和事务C全部堵塞

#### 案例八： 普通索引-等值问题 ####

上面的数据增加一行（30,10,30），这样在数据库中存在的c=10的就有两条记录

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
   <th>事务C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> delete from t where c = 10</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>insert into values(12,12,12)</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>-</td> 
   <td>-</td> 
   <td>update t set d = d+ 1 where c = 15</td> 
  </tr> 
 </tbody> 
</table>

1.  next-key lock 增加范围锁（5,10\]，（10，15\]  
    2.因为是等值查询故退化为（5,10\]，（10，15），故事务B阻塞，事务C执行成功  
    加锁的范围如下图

#### 案例九： 普通索引-等值Limit问题 ####

<table> 
 <thead> 
  <tr> 
   <th>步骤</th> 
   <th>事务A</th> 
   <th>事务B</th> 
   <th>事务C</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>begin;<br> delete from t where c = 10 limit 2</td> 
   <td>-</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>-</td> 
   <td>insert into values(12,12,12)</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>-</td> 
   <td>-</td> 
   <td>update t set d = d+ 1 where c = 15</td> 
  </tr> 
 </tbody> 
</table>

1.根据上面案例8改造，将delete增加limit操作2的操作  
2.因为知道了数据加锁值加2条，故在加锁（5，10\]之后发现已经有两条数据，故后面不在向后匹配加锁。所以事务B执行成功，加锁范围如下

作者：小亮\_\_  
链接：https://www.jianshu.com/p/32904ee07e56  
来源：简书  
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

来源：[https://www.jianshu.com/p/32904ee07e56][https_www.jianshu.com_p_32904ee07e56]

[https_www.jianshu.com_p_32904ee07e56]: https://www.jianshu.com/p/32904ee07e56