【MySQL】事务与锁（五）：行锁到底锁住的是什么？记录？字段？索引？

悠悠 2022-11-21 11:59 191阅读 0赞

首先我们有三张表 t1,t2,t3，它们都是只有两个字段， int类型的id和varchar类型的name；区别是t1没有索引，t2有主键索引，t3有唯一索引。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]

> 再强调一次，在实验前必须提前关闭自动提交，`set autocommit=off`。然后`show variables like 'autocommit'`查看自动提交是否是off。

我们先假设InnoDB的锁锁住了是一行数据或者一条记录。

## 1.假设锁住记录 ##

### 1.1 实验一：没有索引的表(t1) ###

这个实验操作是操作没有索引的t1，t1里面有4条数据：1、2、3、4。

现在我们在两个会话里面手工开启两个事务。在第一个事务里面，我们通过where id =1 锁住第一行数据。在第二个事务里面，我们尝试给id=3的这一行数据加锁，大家觉得能成功吗？

<table> 
 <thead> 
  <tr> 
   <th align="left">Transaction1</th> 
   <th align="left">Transaction2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">Begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">SELECT * FROM t1 where id=1 FOR UPDATE;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">Begin;</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">SELECT * FROM t1 where id=3 FOR UPDATE; // BLOCKED</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">INSERT INTO t1 (id, name) VALUES (5, ‘5’); // BLOCKED</td> 
  </tr> 
 </tbody> 
</table>

这就有点奇怪了，第一个事务锁住了id=1的这行数据，为什么我不能操作id=3的数据呢？我们再来操作一条不存在的数据，插入id=5。它也被阻塞了。实际上这里整张表都被锁住了。所以，我们的第一个猜想被推翻了，InnoDB的锁锁住的应该不是Record。

那为什么在没有索引或者没有用到索引的情况下，会锁住整张表？这个问题我们先留在这里。下面继续看第二个实验。

### 1.2 实验二：有主键索引的表(t2) ###

我们先看一下t2的表结构。字段是一样的，不同的地方是id上创建了一个主键索引。里面的数据是 1、4、7、10。

<table> 
 <thead> 
  <tr> 
   <th align="left">Transaction1</th> 
   <th align="left">Transaction2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">Begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">SELECT * FROM t2 where id=1 FOR UPDATE;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">Begin;</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">SELECT * FROM t2 where id=1 FOR UPDATE; // BLOCKED</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">SELECT * FROM t2 where id=4 FOR UPDATE; // OK</td> 
  </tr> 
 </tbody> 
</table>

第一种情况，使用相同的id值去加锁，冲突；使用不同的id加锁，可以加锁成功。

那么出现问题了，从实验一中得到锁定的不是一行数据，但是实验二操作不同记录的数据又可以成功，那有没有可能是锁住了id的这个字段呢？

## 2.假设锁住字段 ##

我们看一下 t3 的表结构。字段还是一样的， id上创建了一个主键索引，name上创建了一个唯一索引。里面的数据是1、4、7、10。

在第一个事务里面，我们通过name字段去锁定值是4的这行数据。在第二个事务里面，尝试获取一样的排它锁，肯定是失败的，这个不用怀疑。在这里我们怀疑InnoDB锁住的是字段，所以这次我换一个字段，用id=4去给这行数据加锁，大家觉得能成功吗？

<table> 
 <thead> 
  <tr> 
   <th align="left">Transaction1</th> 
   <th align="left">Transaction2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">Begin;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left">SELECT * FROM t3 where name=‘4’ FOR UPDATE;</td> 
   <td align="left"></td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">Begin;</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">SELECT * FROM t3 where name=‘4’ FOR UPDATE; // BLOCKED</td> 
  </tr> 
  <tr> 
   <td align="left"></td> 
   <td align="left">SELECT * FROM t3 where id=4 FOR UPDATE; // BLOCKED</td> 
  </tr> 
 </tbody> 
</table>

很遗憾，又被阻塞了，说明锁住的是字段的这个推测也是错的，否则就不会出现第一个事务锁住了name，第二个字段锁住id失败的情况。

既然锁住的不是record，也不是column， InnoDB里面锁住的到底是什么呢？

## 3.其实，锁的是索引 ##

在这三个案例里面，我们要去分析一下他们的差异在哪里，也就是这三张表的结构，是什么区别导致了加锁的行为的差异？其实答案就是索引。 **InnoDB 的行锁，就是通过锁住索引来实现的**。

> PS：那索引又是个什么东西？为什么它可以被锁住？我们在 [【MySQL】详谈索引存储结构推演过程][MySQL] 已经分析过了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1][]

那么我们还有两个问题没有解决：

**问题一**：为什么表里面没有索引的时候，实验一锁住一行数据会导致锁表？或者说，如果锁住的是索引，一张表没有索引怎么办？所以，一张表有没有可能没有索引？

1.  如果我们定义了主键(PRIMARYKEY)，那么 InnoDB 会选择主键作为聚集索引
2.  如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引
3.  如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增

所以，实验一为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

**问题二**：实验二为什么通过唯一索引给数据行加锁，主键索引也会被锁住？

在辅助索引里面， 索引存储的是二级索引和主键的值。 比如name=4，存储的是name的索引和主键id的值4。而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]

> 总结一下：
> 
> 1.  行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁
> 2.  除了直接在主键索引加锁，我们还可以通过辅助索引找到相应主键索引后再加锁

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/b6cc01bc54ce4229a2f1fe45934c188e.png
[MySQL]: https://blog.csdn.net/weixin_43935927/article/details/109346359
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20210223200632661.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201101024127446.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw==,size_16,color_FFFFFF,t_70#pic_center