MySQL数据库——锁-行级锁(行锁、间隙锁和临键锁)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库——锁-行级锁(行锁、间隙锁和临键锁)

介绍

行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。

应用在InnoDB存储引擎中。InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。

对于行级锁,主要分为以下三类:

  • 行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。


  • 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。

  • 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。

 

行锁

介绍

InnoDB实现了以下两种类型的行锁:

  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排它锁。
  • 排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。

两种行锁的兼容情况如下:

当前锁类型/请求锁类型 S(共享锁) X(排他锁)
S(共享锁) 兼容 冲突
X(排他锁) 冲突 冲突

常见的SQL语句,在执行时,所加的行锁如下: image.png 演示

默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜

索和索引扫描,以防止幻读。


  • 针对唯一索引进行检索时,对已存在的记录进行等值匹配时,将会自动优化为行锁。
  • InnoDB的行锁是针对于索引加的锁,不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时 就会升级为表锁。

可以通过以下SQL,查看意向锁及行锁的加锁情况:

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema.data_locks;

示例演示

数据准备:

CREATE TABLE 'stu' (
    'id' int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    'name' varchar(255) DEFAULT NULL,
    'age' int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
 
INSERT INTO 'stu' VALUES (1, 'tom', 1);
INSERT INTO 'stu' VALUES (3, 'cat', 3);
INSERT INTO 'stu' VALUES (8, 'rose', 8);
INSERT INTO 'stu' VALUES (11, 'jetty', 11);
INSERT INTO 'stu' VALUES (19, 'lily', 19);
INSERT INTO 'stu' VALUES (25, 'luci', 25);

演示行锁的时候,就通过上面这张表来演示一下。

A. 普通的select语句,执行时,不会加锁。

B. select...lock in share mode,加共享锁,共享锁与共享锁之间兼容。

共享锁与排他锁之间互斥。

客户端一获取的是id为1这行的共享锁,客户端二是可以获取id为3这行的排它锁的,因为不是同一行数据。

而如果客户端二想获取id为1这行的排他锁,会处于阻塞状态,因为共享锁与排他锁之间互斥。

C. 排它锁与排他锁之间互斥

当客户端一执行update语句,会为id为1的记录加排他锁;


客户端二如果也执行update语句,更新id为1的数据,也要为id为1的数据加排他锁,但是客户端二会处于阻塞状态,因为排他锁之间是互斥的。


直到客户端一把事务提交了,才会把这一行的行锁释放,此时客户端二解除阻塞。


D. 无索引行锁升级为表锁

stu表中数据如下:

我们在两个客户端中执行如下操作:


在客户端一中开启事务,并执行update语句,更新name为Lily的数据,也就是id为19的记录。


然后在客户端二中更新id为3的记录,却不能直接执行,会处于阻塞状态,为什么呢?


原因就是此时客户端一根据name字段进行更新时,name字段是没有索引的,如果没有索引,此时行锁会升级为表锁(因为行锁是对索引项加的锁,而name没有索引)。


接下来,我们再针对name字段建立索引,索引建立之后,再次做一个测试:

此时我们可以看到,客户端一开启事务,然后依然是根据name进行更新。

客户端二在更新id为3的数据时,更新成功,并未进入阻塞状态。

这样就说明,我们根据索引字段进行更新操作,就可以避免行锁升级为表锁的情况。

间隙锁&临键锁

介绍

默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜

索和索引扫描,以防止幻读。

  • 索引上的等值查询(当其索引为唯一索引时),给不存在的记录加锁时, 优化为间隙锁
  • 索引上的等值查询(当其索引为非唯一普通索引时),向右遍历时,最后一个值不满足查询需求时,next-keylock 退化为间隙锁。
  • 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
  • 注意:间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。


 

示例演示

A. 索引上的等值查询(当其索引为唯一索引时),给不存在的记录加锁时, 优化为间隙锁。


B. 索引上的等值查询(当其索引为非唯一普通索引时),向右遍历时最后一个值不满足查询需求时,next-key lock 退化为间隙锁。


介绍分析一下:

我们知道InnoDB的B+树索引,叶子节点是有序的双向链表。


假如我们要根据这个二级索引查询值为18的数据,并加上共享锁,我们是只锁定18这一行就可以了吗?


并不是,因为是非唯一索引,这个结构中可能有多个18的存在,所以,在加锁时会继续往后找,找到一个不满足条件的值(当前案例中也就是29)。此时会对18加临键锁,并对29之前的间隙加锁。


C. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。

查询的条件为id>=19,并添加共享锁。 此时我们可以根据数据库表中现有的数据,将数据分为三个部分:

  • [19]
  • (19,25]
  • (25,+∞]

所以数据库数据在加锁时,就是将19加了行锁,25的临键锁(包含25及25之前的间隙),正无穷的临键锁(正无穷及之前的间隙)。


END



相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇(锁)
MySQL数据库进阶第五篇(锁)
|
1月前
|
存储 关系型数据库 MySQL
深入浅出MySQL事务管理与锁机制
MySQL事务确保数据一致性,ACID特性包括原子性、一致性、隔离性和持久性。InnoDB引擎支持行锁、间隙锁和临键锁,提供四种隔离级别。通过示例展示了如何开启事务、设置隔离级别以及避免死锁。理解这些机制对优化并发性能和避免数据异常至关重要。【6月更文挑战第22天】
104 3
|
1月前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
65 2
|
1月前
|
SQL 存储 算法
【MySQL技术内幕】6.4-锁的算法
【MySQL技术内幕】6.4-锁的算法
36 1
|
28天前
|
SQL 存储 关系型数据库
Mysql-事务-锁-索引-sql优化-隔离级别
Mysql-事务-锁-索引-sql优化-隔离级别
|
1月前
|
SQL NoSQL 关系型数据库
Mysql锁及适用场景
Mysql锁及适用场景
33 0
|
18天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
16天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
16天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制
|
6天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。