浅谈MySql update会锁定哪些范围的数据
作者:huan1993的技术分享 发布时间:2024-01-26 21:40:34
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
参考链接: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
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
。
间隙锁的目的
:
防止新的数据插入到间隙中
防止已经存在的数据被更新到间隙中。
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;
字段解释:
字段 | 值 | 解释 |
---|---|---|
lock_type | TABLE | 锁是加在表上 |
RECORD | 锁加在记录上 | |
lock_mode | IX | 意向排他锁 |
X或者S | next-key lock 锁定记录本身和记录之前的间隙 | |
X,REC_NOT_GAP | Record Lock 只锁记录自身 | |
S,REC_NOT_GAP | Record Lock 只锁记录自身 | |
X,GAP | gap lock | |
X,INSERT_INTENTION | 插入意向锁 | |
lock_data | 具体的某个数字 | 表示主键的值 |
值,值 | 第一个值:普通索引的值 第二个值:主键值 |
疑问: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、小于或等于最大临界值
此时可以发现表中扫描到的记录都加上了next key lock(锁加在索引上)
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: 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 等值更新-记录存在
解释:
先对普通索引
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 无索引更新
从上图中可知,无索引更新数据表危险,需要谨慎处理
。无索引更新,会导致全表扫描,导致将扫描到的所有记录都加上next-key lock
。
4、参考链接
1、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
2、https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
来源:https://www.cnblogs.com/huan1993/p/16407440.html
猜你喜欢
- 1、引言小丝:鱼哥,还记得上次写的把数据库的查询结果写入到excel这个脚本不。小鱼:嗯… 可以说不记得吗小丝:我猜你
- django admin管理工具有很多好用的功能,例如搜索框、筛选器等,编码简单,功能强大。但是常规的时间筛选有一定局限性,只能显示一定时间
- 引言Python 是一个强大的语言,提供了许多内置函数以帮助开发者编写高效、简洁的代码。在这篇文章中,我们将深入探讨三个内置函数:map、f
- Git的工作方式分为集中式工作流、功能分支工作流、Gitflow工作流和Forking,其中集中式工作流和功能分支工作流是已经使用过的,Gi
- 视频本教程的视频选择图形我们谈到了 Opengameart.org,这是免费游戏艺术的重要来源,也是我们最喜欢的艺术家之一&ldqu
- Session 对象 可以使用 Session 对象存储特定用户会话所需的信息。这样,当用户在应用程序的 Web 页之间跳转时,存储在 Se
- 一、概述1、描述变量类型注解是用来对变量和函数的参数返回值类型做注解,让调用方减少类型方面的错误,也可以提高代码的可读性和易用性。但是,变量
- 一、功能简述番茄钟即番茄工作法,番茄工作法是简单易行的时间管理工具,使用番茄工作法即一个番茄时间共30分钟,25分钟工作,5分钟休息;特点一
- 前言手里有一点点公司的股票, 拿不准在什么时机抛售, 程序员也没时间天天盯着看,不如动手写个小程序, 把股票趋势每天早上发到邮箱里,用 py
- 本文实例讲述了Python实现约瑟夫环问题的方法。分享给大家供大家参考,具体如下:题目:0,1,...,n-1这n个数字排成一个圆圈,从数字
- 1.去官网下载PyGame 注意:要下载对应版本的包 官网地址:http://www.pyg
- 1.二进制读取模式rt 读取文本文件(默认值)rb 读取二进制文件file_name = "C:/Users/cheng/Desk
- 这篇文章主要介绍了python正则表达式匹配IP代码实例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的
- Oracle 数据库启动Oracle shutdown的时候突然断电,导致使用sql/plus启动时无法连接到数据库,具体描述为: conn
- 重要提示:本文并非一篇简单地介绍内联格式模型(inline formatting model)的文章。相反,它是对内联格式(inline f
- 此系列意在记录于一些有趣的程序及对其的总结。问题来源:https://github.com/Yixiaohan/show-me-the-co
- 简介观察者模式是行为型模式的一种,定义了对象间一对多的关系。当对象的状态发生变化时候,依赖于它的对象会得到通知。适用场景类似触发钩子事件,可
- 采集开始第一步是分析要采集的页面。使用浏览器打开要采集的页面(如:http://sports.sina.com.cn/k/2008-09-1
- 1.学习sql之前回忆一下,什么是变量?变量:能存储数据的值。变量是一块内存空间的表示。数组一连串空间变量是存储数据的容器(通俗讲)2.变量
- 目录一、简介思路高德地图API二、获取经纬度三、路线规划(四种方式)获取出行路线数据处理四、演示效果五、结尾一、简介路径规划中包括步行、公交