MySQL数据库进阶第五篇(锁)

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

本篇博客深入详细地讨论了数据库锁的种类和使用场景。首先介绍了全局锁的含义和使用场景,如全库逻辑备份,但这将阻塞后续的写和更新操作。然后博客讨论了表级锁和元数据锁(MDL),它们在操作表和表结构时自动添加,以保持数据一致性。同时,博文也讲解了“意向锁”和行级锁的概念,解释了它们如何减少冲突和提高并发性能。最后,作者阐述了更加细粒度的“间隙锁”和“临键锁”,如何通过防止’幻读’来维护数据的一致性。本文涵盖了从更宏观的全局锁,到更微观的行级锁,甚至于间隙锁和临键锁的各种锁机制,为读者对数据库锁有了更全面深入的了解。

一、锁的概述

二、全局锁

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

使用场景:全库逻辑备份(将数据库中的数据备份成一个SQL文件保存在磁盘中

若执行备份过程中,还有数据的修改和插入,则备份的数据无法保证数据的一致性和完整性。

例如表订单,表库存,表订单日志,逐个备份该表的过程(备份顺序:订单->库存->日志)中依然有下单的操作,即备份了订单 但库存已经减少等操作 会导致数据库的数据不一致

flush tables with read lock ; # 加全局锁
sudo mysqldump -h 127.0.0.1 -uroot -p123456 -d db01 > db01.sql # 数据备份 shell界面中执行
unlock tables; # 再登录到MySQL,释放锁

加锁后,DML和DDL无法操作,DQL查询数据 依然可以使用

数据库中加全局锁,是一个比较重的操作,存在以下问题:

如果在主库上备份,那么在备份期间都不能执行更新

如果在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟

在InnoDB引擎中,可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份

sudo mysqldump -single-transaction -h 127.0.0.1 -uroot -p123456 -d db01 > db01.sql

三、表级锁

表锁

表共享读锁(read lock)

表独占写锁(write lock)

lock tables 表名... read/write  # 加锁
unlock tables / 客户端断开连接  # 释放锁

四、元数据锁(meta data lock, MDL)

MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上

主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。为了避免DML与 DDL冲突(增删改 DDL:数据库 表 表字段 ,DML:表中数据),保证读写的正确性。

元数据就是表结构,元数据锁就是维护表结构一致性的锁

当对一张表进行增删改查的时候 DML语句,加MDL读锁(共享)

当对表结构进行变 更操作的时候 DDL语句,加MDL写锁(排他)

查看元数据锁的加锁情况:
select object_type, object_schema, object_name,lock_type, lock_duration from performance_schema.metadata_locks ;

五、意向锁

若两个线程操作一张表,线程A对表加了行锁/表锁,之后 线程B要对表加表锁,线程B需要先遍历表 查看表是否已经被加行锁 或者 表锁。

为了减少类似以上,DML执行时加的行锁与后来要加的表锁冲突的现象。InnoDB引入了意向锁,使表锁不用再检查每行是否被加锁,使用意向锁减少了表锁的检查。

意向共享锁(IS):由语句select … lock in share mode添加

与表锁共享锁 (read)兼容,与表锁排他锁(write)互斥

意向排他锁(IX)由insert、update、delete、select…for update添加 。

与表锁共 享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥

通过SQL查看意向锁和行锁的加锁情况:select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

六、行级锁

七、行锁(Record Lock)

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

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

排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁

排他锁:所有客户端都不能进行对表的任何操作

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

八、间隙锁(Gap Lock)

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

举例:

# -----------------------------------------客户端1-----------------------------------------
begin;
update usr set age = 40 where id = 5; 
# 在客户端1 update了一个不存在的id=5后,加上了间隙锁 (3,8)
commit;
# -----------------------------------------客户端2-----------------------------------------
begin;
INSERT INTO usr VALUES (7,'安拉', '17799998819', 'jd1h@126.com', '城市规划', 51,'2', '0', '2001-09-15 00:00:00');  # 阻塞,被间隙锁,直到客户端1 commit
commit;

九、临键锁(Next-Key Lock)

行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。 在RR隔离级别下支持。

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

举例:

非唯一索引,为了其它事务向间隙中插入一条记录 出现幻读现象,因此会把(3,7)的间隙锁上,把3之前的这块间隙也锁住,3这行数据也锁住

注意:
索引上的等值查询(唯一索引),给不存在的记录加锁时, 优化为间隙锁 。
索引上的等值查询(非唯一普通索引),向右遍历时最后一个值不满足查询需求时,next-key lock 退化为间隙锁。
索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止
举例:锁19这行的数据,邻键锁锁上(19,25)以及25这行记录,还有邻键锁锁上正无穷大 以及 (25,+∞) 的间隙

间隙锁锁间隙,不包含对应的数据记录,只锁定该数据记录之前的这部分间隙

邻键锁即包含当前的数据记录,也会锁定该数据记录之前的这部分间隙

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存,一个事务采用的间隙锁不会 阻止另一个事务在同一间隙上采用间隙锁

十、锁总结

全局锁:锁定数据库中的所有表(数据库备份

表级锁:每次操作锁住整张表

读锁/写锁 lock tables 表名… read/write; unlock tables;

元数据锁 自动加 DML语句 数据的增删改查时 MDL读锁,DDL语句表结构变更 MDL写锁

MDL读锁:

shared_read:select, select … lock in share mode,

shared_write:insert ,update, select … for update

MDL写锁:alter table

意向共享锁/意向排它锁 自动加

加意向共享锁:shared_read:select, select … lock in share mode,

加意向排它锁:shared_write:insert ,update, select … for update

行级锁:每次操作锁住对应的行数据。只针对索引,如果不是索引 升级成表锁

行锁:共享锁/排它锁

排它:insert,update,delete, select … for update

共享:select … lock in shared mode

select 不加任何锁

间隙锁和邻键锁:间隙锁锁住间隙,邻键锁锁数据和间隙。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第三篇(MySQL性能优化)
MySQL数据库进阶第三篇(MySQL性能优化)
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
1天前
|
存储 SQL 关系型数据库
MySQL语句详解:从基础到进阶的全面指南
MySQL语句详解:从基础到进阶的全面指南
|
22天前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读:从基础到进阶的全方位指南
MySQL常用函数解读:从基础到进阶的全方位指南
|
24天前
|
存储 关系型数据库 MySQL
深入浅出MySQL事务管理与锁机制
MySQL事务确保数据一致性,ACID特性包括原子性、一致性、隔离性和持久性。InnoDB引擎支持行锁、间隙锁和临键锁,提供四种隔离级别。通过示例展示了如何开启事务、设置隔离级别以及避免死锁。理解这些机制对优化并发性能和避免数据异常至关重要。【6月更文挑战第22天】
|
1月前
|
SQL 关系型数据库 MySQL
Python进阶第二篇(Python与MySQL数据库)
Python进阶第二篇(Python与MySQL数据库)
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
1月前
|
存储 SQL 关系型数据库
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级
60 2