mysql> set global read_only=1;
复制代码
能使用它来让全库只读么?不行或者说是不建议,主要原因有三点:
-
影响业务逻辑;set global read_only=1 可能会用于一些业务判断,比如:主从的判断,从库只读。
-
异常不释放状态;FTRWL 命令在异常发生时,会自动释放全局锁;而 set global read_only=1 在异常时,数据库会一直保持只读状态,这时候业务就完犊子了。
-
set global read_only=1 这个命令对超级管理员角色无效;备份期间,超管更新数据库还是会导致数据不一致问题。
03 表级锁
======
MySQL 有两种表级锁:表锁以及元数据锁(meta data lock,MDL)
3.1 表锁
表锁的语法是这样的:lock tables … read/write,它是显式使用的,同样也是通过 unlock tables 主动释放锁;当然,客户算断开或者异常时也会释放。
mysql> lock tables student read,course read;
mysql> SELECT count(1) FROM student;
mysql> SELECT count(1) FROM course;
mysql> unlock tables;
复制代码
需要注意一点:lock tables 除了会限制别的线程读写以外,也限定了本线程接下来操作的对象。举个栗子:
线程 A 执行 lock tables student read,course write; 语句,其他线程读 student、读写 course 都会被阻塞。同时,线程 A 在执行 unlock tables 之后,也只能读 student、读写 course;不能访问其他表。整个表格更直观:
| | student 表 | course 表 | 其他表 |
| — | — | — | — |
| 线程A | 读 | 读写 | 不允许 |
| 其他线程 | 阻塞 | 阻塞 | 随便 |
PS:在没有更细粒度的年代,表锁是最常用与处理并发的方式。但是对于 InnDB 来说,一般不使用 lock tables 控制并发,因为粒度太大了。
3.2 MDL 元数据锁
MDL 不需要我们记命令,它是隐式使用的,访问表会自动加上。它的主要作用是防止 DDL(改表结构) 和 DML(CRUD 表数据) 并发的冲突。
举个栗子,线程 A 遍历查询表数据,这期间线程 B 删了表的某一列,这时 A 拿到的数据就跟表结构对不上,MySQL 不允许这种事发生,所以在 5.5 版本引入了 MDL。
它的逻辑很简单,对表进行 CRUD 操作,加 MDL 读锁;对表结构下手时,加 MDL 写锁。因此:
-
读读不互斥,可以多线程对一张表增删改查。
-
读写互斥、写写互斥,保证对表结构下手时只能有一个线程操作,另一个进入阻塞。
3.2.1 加个字段就搞挂数据库?
我们知道 MDL 默认是系统加的,对表结构下手时(加字段、该字段、加索引等等),需要全表扫描。对大表操作时,你肯定会选月黑凤高,系统使用人数最少时进行,以免遭投诉。
但不只是大表,有时候对小表进行操作时,也会有这样的问题。比如下面的例子:4 个session 对表进行操作。
PS:版本是 MySQL 5.7
前提:注意,我这里的事务是手动开启和提交的。而 MDL 锁是语句开始时申请,事务提交才释放。所以,如果是自动提交就不会出现下面的问题。
-
T1、T2 时刻 session A 事务启动,加个 MDL 读锁,然后执行 select 语句。注意:这时事务并没有提交;
-
T3 时刻 session B 也是读操作,可以共享 MDL 读锁,顺利执行;
-
T4 时刻 session C 不讲武德,对表执行 DDL (改表结构)操作,需要的是 MDL 写锁,所以被阻塞;
-
T5 时刻 session D 也是读操作,按道理说 session C 阻塞应该没影响。
但是 MySQL 有一个队列会根据时间先后决定哪个 Session 先执行。所以,不管是 D 还是之后的 session 都会被 C 阻塞。而恰巧 student 又是访问频率很高的表,如此这个库的线程数很快就打满了。
此时,数据库完全不能读写,甚至导致宕机,在用户界面看来就是没响应了。
3.2.2 安全地更改表
相信你都看出来了,出现上面问题是因为使用了长事务(一个事务包括 session A、B、C、D 的操作)。事务一直不提交,MDL 锁就会一直被占用。
所以,遇到这种情况就要在 MySQL 的 information_schema 表中先找出长事务对应的线程,把它 kill 掉。
// MySQL 长事务请看这篇:cnblogs.com/mysqljs/p/11552646.html
// 查询事务
select * from information_schema.INNODB_TRX;
复制代码
那你可能又问了。我的表就是热点表访问很高频,但我又不得不加个字段。那应该咋办呢?回想下多线程业务操作时,线程一直拿不到锁,我们是怎么处理的?
没错,就是加超时时间。比如在 alter 语句里面加个等待时间,超过了这时间还拿不到锁。也不要阻塞后面的业务查询语句,先放弃更改。之后再交由你司 DBA 重复这个过程,直到更改成功。加等待时间语句,像下面这样的:
// N 以秒为单位
ALTER TABLE tbl_name WAIT N add column …
复制代码
04 行锁
=====
mysql 的行索是在引擎实现的,但并不是所有引擎都支持行锁,不支持行锁的引擎只能使用表锁。
行锁比较容易理解:行锁就是针对数据表中行记录的锁。比如:事务 A 先更新一行,同时事务 B 也要更新同一行,则必须等事务 A 的操作完成后才能进行更新。
4.1 两阶段提交
先举个栗子:事务 A 和 B 对 student 中的记录进行操作。
其中事务 A 先启动,在这个事务中更新两条数据;事务 B 后启动,更新 id = 1 的数据。由于 A 更新的也是 id = 1 的数据,所以事务 B 的 update 语句从事务 A 开始就会被阻塞,直到事务 A 执行 commit 之后,事务 B 才能继续执行。
在事务期间,事务 A 实际上持有 id = 1 和 id = 2 这两行的行锁。如果事务 B 更新的是 id = 2 的数据,那么它阻塞的时间就是从 A 更新 id = 2 这行开始(事务 A 更新 id = 1 时,它并没有阻塞),到事务 A 提交结束,比更新 id = 1 数据阻塞的时间要短。PS:理解这句话很重要。
在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。锁的添加与释放分到两个阶段进行,之间不允许交叉加锁和释放锁。
根据这个特性,对于高并发的行记录的操作语句就可以尽可能的安排到最后面,以减少锁等待的时间,提高并发性能。
举个栗子:广州长隆乐园卖票系统。卖出一张票的逻辑应该分三步:
-
1、扣除用户账户余额
-
2、增加长隆账户收入
-
3、插入一条交易记录
三个操作必须是要放在同一个事务当中,那应该怎么安排它们的执行顺序呢?做个分析:
-
用户余额表是个人的,并发度很低;
-
长隆账户表每个用户买票都要访问,并发度最高;
-
交易记录表是插入操作问题不大;
这时将事务步骤安排成 3、1、2 这样的顺序是最佳的。因为此时如果有别的用户买票,它的事务在顺序 1、2 并不会阻塞,而是到了顺序 3 更新长隆账户表才会引起阻塞。但它的阻塞时间是最短的。
4.2 死锁
不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。
举个行锁死锁的例子:两个事物相互等待对方持有的锁。
操作开始,事务 A 持有 id = 1 的行锁,事务 B 持有 id = 2 的行锁;事务 A 想更新 id = 2 行数据,不料事务 B 已持有,事务 A 只能等待事务 B 释放 id = 2 的行锁;同理,事务 B 想更新 id = 1 行数据,不料事务 A 已持有,事务 B 只能等事务 A 释放 id = 1 的行锁。
两者互相等待,一直到完犊子。这就是死锁,懂了么?
4.3 如何解决死锁?
那出现了死锁怎么办?有两个解决策略:
-
进入等待,直到超时
-
进行死锁检测,主动回滚某个事务
4.2.2 加入等待时间
首先是第一种:直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 设置。 这个参数,默认设置的锁等待时间是 50s
在 MySQL 中,像下面这样执行即可:
// 设置等待时间
mysql> set global innodb_lock_wait_timeout = 500;
复制代码
上面这个语句表示:当出现死锁以后,第一个被锁住的线程要过 500s 才会超时退出,然后其他线程才有可能继续执行。
你可能说这不解决啦?真简单。别得意,这里还有个坑。到底设置多长的过期时间合适呢?
我设置 1s 吧,有些线程可能并没有发生死锁,只是正常的等待锁。这就会造成本来正常的线程让我给干掉了。
4.2.3 死锁检测
再看第二种:死锁检测,主动回滚某个事务。MySQL 通过设置 innodb_deadlock_detect 的值决定是否开启检测,默认值是 on(开启)。
主动死锁检测在发生死锁的时候,可以快速发现并进行处理的,但是它也有额外负担。
什么负担呢?循环依赖检测,过程如下图:
新来的线程 F,被锁了后就要检查锁住 F 的线程(假设为 D)是否被锁,如果没有被锁,则没有死锁,如果被锁了,还要查看锁住线程 D 的是谁,如果是 F,那么肯定死锁了,如果不是 F(假设为 B),那么就要继续判断锁住线程 B 的是谁,一直走知道发现线程没有被锁(无死锁)或者被 F 锁住(死锁)才会终止
如果大量并发修改同一行数据,死锁检测又会怎样呢?
假设有 1000 个并发线程同时更新同一行,那么死锁检测操作就是 1000 x 1000 达到 100 万量级的。即便最终检测结果没有死锁,但这期间要消耗大量 CPU 资源。所以,你就会看到 CPU 利用率很高,但是每秒却执行不了几个事务的情况。
4.2.4 解决热点行更新问题
那前面两种方案都有弊端,死锁的问题应该怎么解决呢?
一种比较依赖运气的方法就是:如果你能确保这个业务一定不会出现死锁,可以临时把死锁检测关掉。但是这可能会影响到业务:开启死锁检测,出现死锁就回滚重试,不会影响到业务。如果关闭,可能就会大量超时,严重就会拖垮数据库。
另一种就是在服务端(消息队列或者数据库服务端)控制并发度:之所以担心死锁检测会造成额外的负担,是因为并发线程很多的时候,假设我们能在服务端做下限流,比如同一样最多只能允许 10 个线程同时修改。
一个思想:减少死锁的主要方向,就是控制访问相同资源的并发事务量。
05 巨人的肩膀
========
-
《高性能 MySQL》
-
time.geekbang.org/column/article/69862
-
cnblogs.com/dyh004/p/11264569.html
-
cnblogs.com/mysqljs/p/11552646.html
-
blog.csdn.net/Annie_ya/article/details/104938829
-
blog.csdn.net/u012483153/article/details/107308715
06 总结
=====
本文详细介绍了 MySQL 的全局锁、表级锁、元数据锁以及行锁和死锁。其中全局锁撩到了应用场景、为什么备份要加全局锁?如何利用一致性视图备份以及为啥 readonly = 1 不适合用来做备份?
表级锁聊了表锁、MDL 元数据锁以及怎么利用 MDL 锁安全快速更改表结构;行锁聊了两阶段提交、死锁的定义、死锁的检测以及给怎么解决死锁,提供了两种思路。
好啦,以上就是狗哥关于 MySQL 锁的总结。感谢各技术社区大佬们的付出,尤其是极客时间,真的牛逼。如果说我看得更远,那是因为我站在你们的肩膀上。希望这篇文章对你有帮助,我们下篇文章见~
作者:一个优秀的废人
链接:https://juejin.cn/post/6953043927670882340
来源:掘金
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数Java工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Java开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上Java开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注Java获取)
![img](https://i-blog.csdnimg.cn/blog_migrate/3e62a281fbd07d77256253b83396f1e2.jpeg)
最后
在面试前我整理归纳了一些面试学习资料,文中结合我的朋友同学面试美团滴滴这类大厂的资料及案例
由于篇幅限制,文档的详解资料太全面,细节内容太多,所以只把部分知识点截图出来粗略的介绍,每个小节点里面都有更细化的内容!
大家看完有什么不懂的可以在下方留言讨论也可以关注。
觉得文章对你有帮助的话记得关注我点个赞支持一下!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!
5c14a7895254671a72faed303032d36.jpg" alt=“img” style=“zoom: 33%;” />
最后
在面试前我整理归纳了一些面试学习资料,文中结合我的朋友同学面试美团滴滴这类大厂的资料及案例
[外链图片转存中…(img-tcEM1EVP-1713464818644)]
[外链图片转存中…(img-c2B6QQem-1713464818644)]
由于篇幅限制,文档的详解资料太全面,细节内容太多,所以只把部分知识点截图出来粗略的介绍,每个小节点里面都有更细化的内容!
大家看完有什么不懂的可以在下方留言讨论也可以关注。
觉得文章对你有帮助的话记得关注我点个赞支持一下!
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取!