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

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

文章目录

​​1、背景​​​​2、前置知识​​

​​2.1 数据库的隔离级别​​​​2.2 数据库版本​​​​2.3 数据库的存储引擎​​​​2.4 锁是加在记录上还是索引上​​​​2.5 update...where加锁的基本单位是​​​​2.6 行级锁​​

​​2.6.1 Record Locks​​​​2.6.2 Gap Locks​​​​2.6.3 Next-Key Locks​​​​2.6.4 测试锁表的表结构​​​​2.6.5 表中的测试数据​​​​2.7 查看数据库中当前的锁​​

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

​​3.1 唯一索引测试​​

​​3.1.1 等值更新-记录存在​​​​3.1.2 等值查询-记录不存在-01​​​​3.1.3 等值更新-记录不存在-02​​​​3.1.4 范围更新​​

​​1、小于或等于最大临界值​​​​2、大于或等于最小临界值​​​​3、正常范围​​

​​3.2 普通索引测试​​

​​3.2.1 等值更新-记录存在​​​​3.2.2 等值更新-记录不存在​​​​3.2.3 范围更新​​

​​3.3 无索引更新​​

​​3、参考链接​​

1、背景

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

2、前置知识

2.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 锁是加在记录上还是索引上

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

​​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​​

参考链接： ​​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​​。

​​间隙锁的目的​​：

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

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;​​

字段解释：

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

3、测试数据加锁

3.1 唯一索引测试

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

3.1.1 等值更新-记录存在

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

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

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

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

3.1.4 范围更新

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

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

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: 0mysql> 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 等值更新-记录存在

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

3.2.2 等值更新-记录不存在

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

3.2.3 范围更新

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

3.3 无索引更新

3、参考链接

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。