MySQL的全局锁和表级锁的具体使用
来源:脚本之家
时间:2023-01-07 21:44:43 301浏览 收藏
亲爱的编程学习爱好者,如果你点开了这篇文章,说明你对《MySQL的全局锁和表级锁的具体使用》很感兴趣。本篇文章就来给大家详细解析一下,主要介绍一下MySQL全局锁、表级锁,希望所有认真读完的童鞋们,都有实质性的提高。
前言
在真实的企业开发环境中使用MySQL,MySQL肯定不会只有我一个人使用,而是一个团队显式的使用MySQL,或者是业务隐式的使用MySQL,那么多个用户或者客户端连接使用的时候,我们应该考虑一个问题:如果保证数据并发访问的一致性呢?这一篇我就来聊聊MySQL的锁,不涉及MySQL的事务隔离级别。
全局锁
MySQL的全局锁会关闭所有打开的表,并使全部的表处于只读状态,它们的命令为:
# 全局锁,简称FTWRL FLUSH TABLES WITH READ LOCK; # 解锁命令 UNLOCK TABLES;
对FTWRL进行实验:(以下的所有实验都是在MySQL8.0.22完成的)
session1 | session2 |
---|---|
FLUSH TABLES WITH READ LOCK; | |
select * from test limit 1; (正常返回结果) |
|
select * from test limit 1; (正常返回结果) |
|
insert into test(a,b,c) values(6,6,6); (报错) |
|
insert into test(a,b,c) values(8,8,8);# sql1 (阻塞) |
|
UNLOCK TABLES; | |
insert into test(a,b,c) values(8,8,8);# sql1 (session1解锁后,sql1立马执行成功) |
从以上实验可以得出:当执行FTWRL后,所有的表变成了只读状态,其他更新的操作将会被阻塞。
全局锁的主要作用就是做全库逻辑备份,也就是把数据库的每个表都select出来存成文本。
当备份过程中,整个数据库处于只读状态,风险也是及其的大。如果是在主库备份,将会导致所有的业务表都不能修改数据;如果是在从库备份,这个时候从库不能执行主库传过来的binlog,会导致主从延迟。
好在InnoDB存储引擎支持事务,mysqldump有一个参数single-transaction,可以在事务中创建一致性快照,然后进行所有表备份。在有这个参数下,备份期间可以进行数据修改,所以正常开发中建议使用InnoDB存储引擎。
表级锁
表级锁分为两种,一种是表锁,另一种是元数据锁。
表锁
表锁分为表读锁和表写锁,在MySQL的命令是:
# 表读锁 lock tables test read; # 表写锁 lock tables test write;
接下来通过实验看下表读锁和表写锁有什么区别吧
表读锁
session1 | session2 |
---|---|
lock tables test read; | |
select * from test limit1; (正常返回结果) |
|
select * from test limit 1; (正常返回结果) |
|
insert into test(a,b,c) values(6,6,6); (报错) |
|
insert into test(a,b,c) values(8,8,8); # sql1 (阻塞) |
|
unlock tables; | |
insert into test(a,b,c) values(8,8,8); # sql1 (session1解锁后,sql1立马写入成功) |
在session1会话加上了表读锁,这个时候session1和session2都可以正常的读数据,但是session1写数据会报错,session2写数据会被阻塞,等到session1解锁了,session2的写数据才能执行成功。
从这个实验可以得出:表加上了表读锁之后,本线程和其他线程都可以读数据,本线程写数据会报错,其他线程写数据会阻塞。
表写锁
session1 | session2 |
---|---|
lock tables test write; | |
select * from test limi1; (正常返回结果) |
|
select * from test limit 1; # sql1 (阻塞) |
|
unlock tables; | |
select * from test limit; # sql1 (session1解锁后,sql1立马返回结果) |
|
lock tables test write; | |
insert into test(a,b,c) values(6,6,6); (插入成功) |
|
insert into test(a,b,c) values(8,8,8);# sql 2 (阻塞) |
|
unlock tables; | |
insert into test(a,b,c) values(8,8,8);# sql2 (session1解锁后,sql2立马执行成功) |
从以上实验可以得出:表加上了表写锁之后,本线程可以进行读写操作,其他线程的读写操作都会被阻塞。
元数据锁(Metadata Locking,简称:MDL锁)
在MySQL中,数据库的DDL不属于事务范畴,如果你在session1中select一行数据,这个时候session2给这张表新增了一列xxx,这个时候可能会出现事务特性被破坏、binlog顺序错乱等bug(MySQL官网上有公布出类似的bug,感兴趣可以自行去了解)。
为了解决以上的问题,从MySQL5.5.3引入了元数据锁,MDL锁不需要显式使用,MySQL会默认加上,它的作用就是保证数据库读写正确性。以下全部用MDL表示元数据锁。
当你对一张表进行增删查改的时候会默认加上MDL读锁;当你对一张表进行表结构更改的时候会默认加上MDL写锁。
session1 | session2 | session3 | session4 |
---|---|---|---|
begin; | |||
select * from test lmi1; (正常返回结果) |
|||
select * from test limit 1; (正常返回结果) |
|||
alter table test add d int; (阻塞) |
|||
select * from test limit 1; (阻塞) |
一开始session1会话查询test的时候,获取到了MDL读锁,可以正常查询到数据。然后session2会话查询数据也会获取MDL读锁,不冲突,也可以正常查询到数据返回。
但是到了session3会话的时候,需要获取MDL写锁,这个时候因为session1的MDL读锁没有释放,所以会阻塞。后面session4也需要MDL读锁,但是因为session3被阻塞了,所以session4也会被阻塞。
假如这是一张线上业务表,这种场景将会使后面的任何操作都失效,表现出来就是这张表变得无法写和读。如果客户端配置了MySQL重试机制的话,会在超时的时候重新建立一个session会话重新请求,然后MySQL就会因为线程不停新增而崩溃。
从上面的例子可以知道MDL锁是在语句执行的时候默认加上的,但是语句执行完是不会释放的,只有等整个事务提交了才会释放MDL锁。
所以对于我们开发者来说,在工作中应该尽量避免慢查询、尽量保证事务及时提交、避免大事务等,对于DBA来说,也应该尽量避免在业务高峰期执行DDL操作。
总结
- 全局锁会让所有的表变成只读状态,所有更新操作都会被阻塞
- 表读锁是本线程和其他线程都可以读,本线程写会报错,其他线程写会阻塞
- 表写锁是本线程可以读写,其他线程读写都会阻塞
- 引入MDL锁解决事务和DDL同时执行引发的bug
参考资料
- 《深入浅出MySQL》第二版:20.3.8 什么时候使用表锁
- 《MySQL实战45讲》林晓斌
以上就是《MySQL的全局锁和表级锁的具体使用》的详细内容,更多关于mysql的资料请关注golang学习网公众号!
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
501 收藏
-
463 收藏
-
206 收藏
-
132 收藏
-
188 收藏
-
404 收藏
-
101 收藏
-
265 收藏
-
209 收藏
-
446 收藏
-
339 收藏
-
285 收藏
-
259 收藏
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 508次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习