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

在数据库实战开发的过程中，我们经常会遇到一些这样那样的问题，然后要卡好半天，等问题解决了才发现原来一些细节知识点还是没有掌握好。今天golang学习网就整理分享《记录一下MySql update会锁定哪些范围的数据》，聊聊MySQL、锁、Java、数据库、lock，希望可以帮助到正在努力赚钱的你。

1、背景

在项目中，我们经常使用到

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

2.2 数据库版本

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

2.3 数据库的存储引擎

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

2.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 查看数据库中当前的锁

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> update test_record_lock set name = 'aaa' where id >= 1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select LOCK_TYPE,INDEX_NAME,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;
+-----------+------------+---------------+------------------------+
| LOCK_TYPE | INDEX_NAME | LOCK_MODE     | LOCK_DATA              |
+-----------+------------+---------------+------------------------+
| TABLE     | NULL       | IX            | NULL                   |
| RECORD    | PRIMARY    | X,REC_NOT_GAP | 1                      |
| RECORD    | PRIMARY    | X             | supremum pseudo-record |
| RECORD    | PRIMARY    | X             | 8                      |
| RECORD    | PRIMARY    | X             | 5                      |
+-----------+------------+---------------+------------------------+
5 rows in set (0.01 sec)

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

3、正常范围

3.2 普通索引测试

3.2.1 等值更新-记录存在

解释：

1. 先对普通索引

   age

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

3.2.2 等值更新-记录不存在

解释：

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

3.2.3 范围更新

解释：

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

3.3 无索引更新

从上图中可知，无索引更新数据表危险，需要谨慎处理

next-key lock

3、参考链接

1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-intention-locks
2、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

今天关于《记录一下MySql update会锁定哪些范围的数据》的内容就介绍到这里了，是不是学起来一目了然！想要了解更多关于mysql的内容请关注golang学习网公众号！