记录一下MySql update会锁定哪些范围的数据

梦里梦外; 2023-09-28 16:24 15阅读 0赞

## 1、背景 ##

在项目中，我们经常使用到`update`语句，那么`update`语句会锁定表中的那些记录呢？此处我们通过一些简单的案例来模拟下。`此处是我自己的一个理解，如果那个地方理解错了，欢迎指出`

## 2、前置知识 ##

### 2.1 数据库的隔离级别 ###

### 2.2 数据库版本 ###

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.28    |
    +-----------+
    1 row in set (0.00 sec)
    复制代码

### 2.3 数据库的存储引擎 ###

### 2.4 锁是加在记录上还是索引上 ###

`锁是加在索引上`，那如果表中没有建立索引，是否就是加在表上的呢？其实不是，也是加在索引的，会存在一个默认的。

`Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking`

参考链接： [dev.mysql.com/doc/refman/…][dev.mysql.com_doc_refman]

### 2.5 update...where加锁的基本单位是 ###

`UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters` 此处可以理解加锁的单位是: `next-key`锁

### 2.6 行级锁 ###

#### 2.6.1 Record Locks ####

`记录锁`，即只会锁定一条记录。其实是锁定这条记录的索引。 `A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.`

#### 2.6.2 Gap Locks ####

`间隙锁`，间隙锁是在索引记录之间的间隙上的锁，即锁定一个区间。`前开后开区间`，不包括记录本身。

`间隙锁`如果是使用`单列唯一索引值`进行更新的话，是会`退化`成`Record Lock`。

`间隙锁的目的`：

1.  防止新的数据插入到间隙中
2.  防止已经存在的数据被更新到间隙中。

> Gap locking is not needed for statements that lock rows using a unique index to search > for a unique row. (This `does not include` the case that the search condition includes only > some columns of a `multiple-column unique index`; in that case, gap locking does occur.)

#### 2.6.3 Next-Key Locks ####

Next-Key Lock 是`索引记录上`的`记录锁`和`索引记录之前`的`间隙上的间隙锁`的组合。也是锁定一个区间，`前开后闭区间`。包括记录本身。

如果索引值包括 `1,5,10,30`，那么next key 锁可能涵盖如下区间

(negative infinity, 1]
    (1, 115
    (5, 10]
    (10, 30]
    (30, positive infinity)
    复制代码

`negative infinity`指的是负无穷。`positive infinity`指的是正无穷。

#### 2.6.4 测试锁表的表结构 ####

create table test_record_lock
    (
        id   int         not null comment '主键',
        age  int         null comment '年龄，普通索引',
        name varchar(10) null comment '姓名，无索引',
        constraint test_record_lock_pk
            primary key (id)
    )
        comment '测试记录锁';
    
    create index test_record_lock_age_index
        on test_record_lock (age);
    复制代码

#### 2.6.5 表中的测试数据 ####

mysql> select * from test_record_lock;
    +----+------+--------+
    | id | age  | name   |
    +----+------+--------+
    |  1 |   10 | 张三   |
    |  5 |   20 | 李四   |
    |  8 |   25 | 王五   |
    +----+------+--------+
    3 rows in set (0.00 sec)
    复制代码

#### 2.7 查看数据库中当前的锁 ####

`select * from performance_schema.data_locks;`

字段解释：

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>值</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>lock_type</code></td> 
   <td>TABLE</td> 
   <td>锁是加在表上</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>RECORD</td> 
   <td>锁加在记录上</td> 
  </tr> 
  <tr> 
   <td><code>lock_mode</code></td> 
   <td>IX</td> 
   <td>意向排他锁</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>X或者S</td> 
   <td>next-key lock<br> 锁定记录本身和记录之前的间隙</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>X,REC_NOT_GAP</td> 
   <td>Record Lock 只锁记录自身</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>S,REC_NOT_GAP</td> 
   <td>Record Lock 只锁记录自身</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>X,GAP</td> 
   <td>gap lock</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>X,INSERT_INTENTION</td> 
   <td>插入意向锁</td> 
  </tr> 
  <tr> 
   <td><code>lock_data</code></td> 
   <td>具体的某个数字</td> 
   <td>表示主键的值</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>值,值</td> 
   <td>第一个值：普通索引的值<br> 第二个值：主键值</td> 
  </tr> 
 </tbody> 
</table>

疑问：`X,GAP`是否可以理解成`X`锁退化成了`GAP`锁。

### 3、测试数据加锁 ###

#### 3.1 唯一索引测试 ####

> 此处适用单个字段的唯一索引，不适合多个字段的唯一索引

3.1.1 等值更新-记录存在

![format_png][]

**解释：**

1.  加next-key lock，那么锁定的记录范围为 (1,5\]。
2.  因为是唯一索引，且查询的值存在，next-key lock退化成record lock，即最终只锁定了id=5的这一行数据。其余的数据不影响。

3.1.2 等值查询-记录不存在-01

![format_png 1][]

**解释：**

1.  加next-key lock，那么锁定的记录范围为 (5,8\]。
2.  因为是唯一索引，且查询的值不存在，next-key lock退化成gap，即最终锁定的数据范围为(5,8)。其余的数据不影响。

3.1.3 等值更新-记录不存在-02

![format_png 2][]

3.1.4 范围更新

1、小于或等于最大临界值

![format_png 3][]

此时可以发现表中扫描到的记录都加上了next key lock(锁加在索引上)

2、大于或等于最小临界值

此时只可向表中插入比最小临界值小的记录。

3、正常范围

![format_png 4][]

#### 3.2 普通索引测试 ####

3.2.1 等值更新-记录存在

![format_png 5][]

**解释：**

1.  先对普通索引`age`加上next-key lock，锁定的范围是(10,20\]
2.  next-key lock还会锁住本记录，因此在id索引的值等于5上加了Record Lock
3.  因为是普通索引并且值还存在，因此还会对本记录的下一个区间增加间隙锁 Gap Lock，锁定的范围为 (20,25)

3.2.2 等值更新-记录不存在

![format_png 6][]

**解释：**

1.  获取next-key lock 锁定的范围为 (10,20\]
2.  因为需要更新的记录不存在，next-key lock退化成 gap lock，所以锁定的范围为(10,20)
3.  因为是普通索引且记录不存在，所以不需要再次查找下一个区间。

3.2.3 范围更新

![format_png 7][]

**解释：**

1.  普通索引的范围更新，next-key-lock不回退化成 gap lock。

3.3 无索引更新

![format_png 8][]

`从上图中可知，无索引更新数据表危险，需要谨慎处理`。无索引更新，会导致全表扫描，导致将扫描到的所有记录都加上`next-key lock`。

![27f50c93aa45492eaec7197b7fca8e58.jpeg][]

[dev.mysql.com_doc_refman]: https://link.juejin.cn?target=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Finnodb-locking.html%23innodb-intention-locks
[format_png]: https://img-blog.csdnimg.cn/img_convert/1c8a36cd0144c2e045bd6de87b9590c5.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/e73910030c9d17e6475a26d994011fac.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/5552a1ebca1990ded974422a8fa63c1f.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/013be373c5365147aea3afd9506c938f.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/a743f3d0fd89856c789773f2e25d3cfe.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/bc228d566978bdfc1e7ea835b4425c56.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/6201b34a3dd321335f5fbd8c63da3312.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/b5742a830979c2ebc27b6c449d201633.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/06e2553bf5a023d2e26a034575dd6ea4.webp?x-oss-process=image/format,png
[27f50c93aa45492eaec7197b7fca8e58.jpeg]: https://img-blog.csdnimg.cn/27f50c93aa45492eaec7197b7fca8e58.jpeg