一文搞懂数据库中的“锁”(图文详解)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 数据库锁机制包括全局锁、表级锁和行级锁,用于管理并发访问数据时的一致性和有效性。全局锁锁定整个数据库实例,确保数据备份时的一致性,但可能导致长时间业务停摆。表级锁分为读锁和写锁,读锁允许多个并发读,写锁阻止其他读写。元数据锁(MDL)自动控制,防止DML和DDL冲突。行级锁是最细粒度的锁,分共享锁(读)和排他锁(写),防止行级别的并发冲突。InnoDB还使用意向锁和间隙锁/临键锁防止幻读,提高并发性能。

[toc]

1.锁

1.1.锁的概述

锁是什么

  • 锁是计算机协调多个进程或线程并发访问某一资源的机制。
  • 在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类

  • 全局锁:锁定数据库中的所有表。
  • 表级锁:每次操作锁住整张表。
  • 行级锁:每次操作锁住对应的行数据。

1.2.全局锁

全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML、DDL语句,已经更新操作的事务提交语句都将被阻塞

应用场景:

  • 做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
  • 如果不加全局锁,先后执行数据备份和业务的数据更新操作,会导致数据不一致

使用全局锁进行数据库逻辑备份的过程:

  • 加全局锁
flush tables with read lock;
  • mysqldump是数据库用于数据备份的工具,执行数据备份
  • 注意:mysqldump是MySQL提供的一个工具,不是sql语句,需要在windows命令行中执行
mysqldump -uroot -p123456 user>user.sql
  • 在加锁后,DML和DDL被阻塞,其他客户端不能写入数据,但是DQL可以执行,其他客户端可以查找数据
  • 备份结束,得到备份后的文件,释放锁
unlock tables;

image-20240329124300533.png

案例演示(模拟三个客户端):

  • 客户端A:对数据库加上全局锁
flush tables with read lock;

image-20240329115504047.png

  • 客户端B:执行select语句--->成功
select * from student;

执行update语句--->失败

update student set name = 'A' where id = 2;

image-20240329115917519.png

  • 客户端C:执行数据备份
mysqldump -h192.168.200.202 -uroot -p1234 db01 > D:/db01.sql

注意:mysqldump是MySQL提供的一个工具,不是sql语句,需要在windows命令行中执行

image-20240329120635761.png

在D盘中可以看到数据已经备份完成

image-20240329120732753.png

数据备份成功后,在客户端A中释放锁

unlock tables;

此时在客户端B中update就可以正常执行。

全局锁的好处:

  • 保证数据的完整性。

全局锁的弊端:

  • 粒度很大,如果在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆。
  • 如果业务数据库不是单机版而是主从结构,且做了读写分离,那么在从库上备份不会影响主库的读写操作,但是在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟。

其他实现一致性数据备份的方式:

在InnoDB引擎中可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。其底层是通过快照读实现。

mysqldump --single-transaction -uroot –p123456 database > database.sql

1.3.表级锁

1.3.1.概念

表级锁,顾名思义,在每次操作时锁住整张表。应用在MyISAM、InnoDB、BDB等存储引擎中

1.3.2.表级锁分类

  • 表锁
  • 元数据锁
  • 意向锁

1.3.3.表锁

表锁分类:

  • 表共享读锁(简称:读锁)

image-20240329131819217.png

  • 表独占写锁(简称:写锁)

image-20240329131849612.png

加锁的语法:

lock tables tb1 , tb2... read / write

释放锁的语法:

unlock tables 或者关闭客户端连接

写锁案例演示:

  • 假设有一张表score,有两个客户端A和B,模拟读锁,以下操作依次执行:
  • 在客户端A中给表score加读锁
lock tables score read ;
  • 在客户端A中执行查询语句--->查询成功
select * from score;
  • 在客户端A中执行更新语句--->更新失败,且报错
update score set math = 100 where id = 2;

在客户端B中执行查询语句--->查询成功

select * from score;

在客户端B中执行更新语句--->更新处于阻塞状态

update score set math = 100 where id = 2;

image-20240329132659663.png

  • 在客户端A中给表score释放读锁,此时B中阻塞状态解除,update更新成功

image-20240329132624126.png

读锁案例演示:

  • 假设有一张表score,有两个客户端A和B,模拟写锁,以下操作依次执行:
  • 在客户端A中给表score加写锁
lock tables score write ;
  • 在客户端A中执行查询语句--->查询成功
select * from score;
  • 在客户端A中执行更新语句--->更新成功
update score set chinese = 100 where id = 2;
  • 在客户端B中执行查询语句--->读取处于阻塞状态
select * from score;
  • 在客户端B中执行更新语句--->更新处于阻塞状态
update score set chinese = 100 where id = 2;

image-20240329132536902.png

1.3.4.元数据锁

  • 元数据锁(meta data lock,MDL),该锁是系统自动控制的,在访问一张表的时候自动上锁。
  • 元数据可以简单理解为表结构,元数据锁的作用是维护表结构的数据一致性,避免DML和DDL之间发生冲突,保证读写正确性
  • 在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL共享读锁和共享写锁(shared_read / shared_write);当对表结构进行变更操作的时候,加MDL排他锁(exclusive)。
  • 共享锁之间相互兼容,表示可以边读边写;共享锁与排他锁互斥,表示在进行增删改查时,不能同时执行表结构的变更。

image-20240329144721314.png

元数据锁的案例演示:

  • 以下操作按顺序依次演示。
  • 客户端A开启事务,并对表score执行select查询操作--->成功执行
begin
select * from score;
  • 客户端B也开启事务,并对表score执行select查询、update更新操作--->成功执行
begin
select * from score;
update score set math = 88 where id = 1;
  • 可以发现上述操作都能成功执行,原因是共享锁之间相互兼容,可并发进行读写操作

image-20240329142440934.png

  • 依次提交客户端A、B的事务
commit;
  • 客户端A再次开启事务,并执行select查询操作,此时MDL自动给表结构加上共享读锁(shared_read)
begin
select * from score;
  • 客户端B此时对表结构进行alter修改操作,结果处于阻塞状态,原因是在执行alter语句时,MDL会自动给表结构加上排他锁exclusive,该锁与共享锁互斥。
alter table score add column java int;

image-20240329144030170.png

  • 此时客户端A提交事务,客户端B的阻塞状态解除,原因是客户端A的共享读锁释放

image-20240329144359846.png

  • 我们可以通过以下语句查看元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;
  • 在客户端未执行任何增删改查语句前,底层只有一把元数据锁,锁的类型是一把共享锁(shared_read),属于元数据表metadata_locks

image-20240329150521237.png

  • 在客户端A执行select语句,然后在客户端B中查看元数据锁:可以发现MDL自动加上了一把共享读锁share_read

image-20240329150317363.png

1.3.5.意向锁

  • 为了避免DML在执行时,客户端A加的行锁与客户端B加的表锁的冲突,在InnoDB中引入了意向锁
  • 意向锁使得客户端B在尝试加表锁时不用检查每行数据是否加了锁,直接根据是否有意向锁以及意向锁的类型来决定表锁是否可以添加成功,减少了表锁的检查。

image-20240329162853949.png

意向锁的分类:

  • 意向共享锁(IS):与表锁共享锁(shared_read)兼容,与表锁排他锁(write)互斥。 由语句select ... lock in share mode添加 。
  • 意向排他锁(IX):与表锁共享锁(shared_read)及排他锁(write)都互斥,意向锁之间不会互斥。 由insert、update、delete、select...for update添加 。

我们可以通过以下语句查看意向锁是否添加成功:

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

案例演示:

  • 客户端A开启事务,然后执行select语句,会自动为id=1这行加上行锁,随后手动通过lock in share mode加上意向共享锁IS:
begin;
select * from score where id = 1 lock in share mode;
  • 客户端B加上表锁共享读锁(shared_read),此时表锁添加成功:
lock tables score read;
  • 客户端B加上表锁写锁(write),此时发现被阻塞:
lock tables score write;

image-20240329160014424.png

  • 客户端A开启事务,然后执行update语句,会自动加上行锁和意向锁IX
update score set math = 66 where id = 1;
  • 在客户端B中查看意向锁的情况,可以发现意向锁IX已经加上:
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
  • 在客户端B中加上表锁(read),结果显示被阻塞,原因是意向锁IX和表锁读锁(read)互斥
lock tables score read;

image-20240329161907703.png

  • 在客户端A中提交事务,意向锁IX释放,随后客户端B中的读锁阻塞状态解除

1.4.行级锁

  • 行级锁:每次加锁锁住对应的数据行和行间的间隙,锁的粒度最小,并发度最高。
  • InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。

1.4.1.行级锁的分类

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

image-20240329171944784.png

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

image-20240329171936610.png

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

image-20240329171926207.png

1.4.2.行锁

  • 行锁有两种,分为共享锁排它锁
  • 共享锁(S):允许一个事务去读一行,阻止其他事务获得同一数据集的排它锁(X)。即共享锁与共享锁之间兼容,共享锁和排它锁之间互斥。
  • 排它锁(X):允许获取了排它锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排它锁。

锁之间的兼容和排斥情况:

image-20240329172359385.png

SQL增删改查语句对应加的行锁:

image-20240329200957732.png

默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用临键锁进行搜索和索引扫描,以防止幻读。

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

我们可以通过以下语句查看行锁是否添加成功:

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

1.4.3.间隙锁和临键锁

  • 默认情况下,InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用临键锁进行搜索和索引扫描,以防止幻读。
  • 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

案例演示:

  • 根据索引进行等值查询,且索引是唯一索引(例如主键索引),给一个不存在的记录加行锁时, 行锁会优化为间隙锁 。
    • 间隙锁的作用:防止其他并发事务在间隙中插入数据

image-20240329182101663.png

如果根据索引进行等值查询,且该索引是普通索引(字段上的记录值有可能重复),那么在叶子节点中向右遍历的最后一个值不满足查询需求时,临键锁退化为间隙锁。

  • 行锁的作用:防止单行记录被并发修改
  • 临键锁/间隙锁的作用:防止单行前后间隙插入记录,引起幻读

image-20240329200052808.png

如果根据索引进行范围查询,且该索引是唯一索引(如主键索引),那么会加上临键锁,会访问到不满足条件的第一个值为止。

  • 行锁的作用:防止该行被并发修改
  • 两个临键锁的作用:防止范围内的数据记录被并发修改

image-20240329200511770.png

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
SQL 存储 关系型数据库
【MySQL 数据库】11、学习 MySQL 中的【锁】
【MySQL 数据库】11、学习 MySQL 中的【锁】
88 0
|
12天前
|
供应链 数据库 开发者
深入了解数据库锁:类型、应用和最佳实践
深入了解数据库锁:类型、应用和最佳实践
|
12天前
|
存储 关系型数据库 MySQL
了解MySQL 数据库的锁机制
了解MySQL 数据库的锁机制。
41 0
|
12天前
|
存储 关系型数据库 MySQL
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题(含答案):存储引擎+数据库锁+索引+SQL优化等
270 0
|
8月前
|
存储 关系型数据库 MySQL
Mysql数据库—事务和锁
Mysql数据库—事务和锁
|
12天前
|
SQL 关系型数据库 数据库
OceanBase数据库常见问题之密码输入错误次数多被锁了如何解决
OceanBase 是一款由阿里巴巴集团研发的企业级分布式关系型数据库,它具有高可用、高性能、可水平扩展等特点。以下是OceanBase 数据库使用过程中可能遇到的一些常见问题及其解答的汇总,以帮助用户更好地理解和使用这款数据库产品。
|
12天前
|
SQL Oracle 安全
Oracle数据库中的事务和锁
【4月更文挑战第19天】Oracle数据库的事务和锁是确保数据完整性和并发控制的核心机制。事务遵循ACID原则,保证操作的原子性、一致性、隔离性和持久性。通过COMMIT或ROLLBACK来管理事务更改。锁包括共享锁(读)、排他锁(写)、行级锁和表级锁,用于控制并发访问。自动锁机制在DML操作时生效,防止数据冲突。事务和锁共同维护数据库的稳定和安全。
|
12天前
|
关系型数据库 MySQL 数据库
【后端面经】【数据库与MySQL】12|数据库锁:明明有行锁,怎么突然就加了表锁?
【4月更文挑战第15天】在MySQL的InnoDB引擎中,锁通过索引实现,主要锁定叶子节点。查询使用哪个索引,就锁哪个;无索引时锁全表。若查询值不存在,InnoDB会构造临键锁阻止插入。锁在事务结束(Rollback或Commit)时释放。乐观锁在尝试更新时检查数据变化,适合读多写少场景,悲观锁一开始就加锁,适用于写多读少。InnoDB支持行锁和表锁,行锁依赖索引。共享锁允许多个线程加同类型锁,排它锁则独占。
45 1
|
12天前
|
数据库
使用Navicat Premium 12进行数据库定期自动备份(定时任务)--图文详解
使用Navicat Premium 12进行数据库定期自动备份(定时任务)--图文详解
40 0
|
8月前
|
关系型数据库 MySQL 数据库
MySQL数据库中的事务机制与锁机制
MySQL数据库中的事务机制与锁机制。
79 2