MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224872 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?

安全地给小表加字段
长事务和MDL锁
MDL锁的互斥性
写锁
读锁
unlock tables
lock tables ... read/write
表级锁的限制和替代方案
备份过程中的一致性问题
MDL锁的重要性
全局锁和表级锁的应用场景
元数据锁(MDL)
表锁
readonly方式 vs FTWRL方式
使用--single-transaction参数
备份的一致性问题
全库逻辑备份
Flush tables with read lock (FTWRL)
总结
表级锁
全局锁
MySQL的锁

该思维导图由 AI 生成,仅供参考

今天我要跟你聊聊 MySQL 的锁。数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类今天这篇文章,我会和你分享全局锁和表级锁。而关于行锁的内容,我会留着在下一篇文章中再和你详细介绍。
这里需要说明的是,锁的设计比较复杂,这两篇文章不会涉及锁的具体实现细节,主要介绍的是碰到锁时的现象和其背后的原理。

全局锁

顾名思义,全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。
全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
以前有一种做法,是通过 FTWRL 确保不会有其他线程对数据库做更新,然后对整个库做备份。注意,在备份过程中整个库完全处于只读状态。
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

MySQL的锁设计旨在处理并发访问问题,主要分为全局锁、表级锁和行锁。全局锁通过Flush tables with read lock (FTWRL)命令实现对整个数据库实例的加锁,常用于全库逻辑备份。然而,全局锁会导致数据库处于只读状态,影响业务更新和主从同步。为了解决备份过程中的逻辑不一致问题,可使用mysqldump工具的--single-transaction参数,通过启动事务来确保一致性视图。然而,对于不支持事务的引擎,如MyISAM,需要使用FTWRL命令。建议使用FTWRL方式而非修改global变量的方式,因为前者在异常处理机制上更可靠。无论使用何种方式,一旦库被全局锁上,对其中任何一个表做加字段操作都会被锁住。此外,即使没有全局锁,加字段操作也可能遇到表级锁的阻碍。因此,全局锁和表级锁给表加字段时都会带来阻碍,需要谨慎处理。 全局锁主要用在逻辑备份过程中,而表级锁一般是在数据库引擎不支持行锁的时候才会被用到。MDL(metadata lock)的作用是保证读写的正确性,对于表的增删改查操作会加MDL读锁,而对表做结构变更操作会加MDL写锁。在进行表结构变更时,需要小心不要导致锁住线上查询和更新。对于小表的字段变更,需要注意长事务可能导致的MDL锁问题,可以考虑设定等待时间或暂停长事务来避免影响业务。 总的来说,全局锁和表级锁在处理并发访问时需要注意对业务的影响,特别是在进行逻辑备份和表结构变更时,需要谨慎处理以避免影响业务正常运行。 这篇文章还提到了对联合主键索引和InnoDB索引组织表的理解,以及一些读者的评论和回复。文章内容涉及MySQL数据库锁设计的技术特点,对读者快速了解MySQL锁设计提供了全面的概览。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(433)

  • 最新
  • 精选
  • 哈哈哈
    置顶
    MDL作用是防止DDL和DML并发的冲突,个人感觉应该写清楚,一开始理解为select和update之间的并发。

    作者回复: 嗯 特意写了是MDL“读锁”。 把你的留言置顶了,希望有疑问的同学能看到这个😆

    2019-01-24
    11
    298
  • miche
    置顶
    1. 上面的那个因为mdl锁把整个库搞挂的例子里,如果用pt工具来操作,会出现同样的情况吗? 2. 那个例子里显示select语句前加了begin,是不是select的时候不加begin,就不会出现同样的情况呢? 3. online ddl 的copy方式和inplace方式,也都是需要 拿MDL写锁、降成读锁、做DDL、升成写锁、释放MDL锁吗?

    作者回复: 1. Pt的过程也是有操作表结构的,所以会类似 2. 对,没有begin的话,这样select执行完成以后,MDL就自动释放了哦 3. 是,是否online都是第三步(结合置顶评论看哈)的区别,另外四步还是有的

    2018-11-28
    3
    40
  • skyoo
    置顶
    FTWRL 前有读写的话 ,FTWRL 都会等待 读写执行完毕后才执行 FTWRL 执行的时候要刷脏页的数据到磁盘,因为要保持数据的一致性 ,理解的执行FTWRL时候是 所有事务 都提交完毕的时候 mysqldump + -single-transaction 也是保证事务的一致性,但他只针对 有支持事务 引擎,比如 innodb 所以 还是强烈建议大家在创建实例,表时候需要innodb 引擎 为好 全库只读 readonly = true 还有个情况在 slave 上 如果用户有超级权限的话 readonly 是失效的 表级别 锁 :一个直接就是表锁 lock table 建议不要使用, 影响太大,另个就是 MDL 元数据锁 MDL 是并发情况下维护数据的一致性,在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在server层面实现的 当你做 dml 时候增加的 MDL 读锁, update table set id=Y where id=X; 并且由于隔离级别的原因 读锁之间不冲突 当你DDL 时候 增加对表的写锁, 同时操作两个alter table 操作 这个要出现等待情况。 但是 如果是 dml 与ddl 之间的交互 就更容易出现不可读写情况,这个情况容易session 爆满,session是占用内存的,也会导致内存升高 MDL 释放的情况就是 事务提交. 主库上的一个小表做了一个 DDL, 同步给slave ,由于这个时候有了先前的 single-transaction,所以slave 就会出现 该表的 锁等待, 并且slave 出现延迟

    作者回复: 分析得很好。 尤其readonly 对 super 权限无效这句。

    2018-11-26
    17
    246
  • echo_陈
    置顶
    mysql 5.6不是支持online ddl了吗?也就是对表操作增加字段等功能,实际上不会阻塞读写?

    作者回复: Online DDL的过程是这样的: 1. 拿MDL写锁 2. 降级成MDL读锁 3. 真正做DDL 4. 升级成MDL写锁 5. 释放MDL锁 1、2、4、5如果没有锁冲突,执行时间非常短。第3步占用了DDL绝大部分时间,这期间这个表可以正常读写数据,是因此称为“online ” 我们文中的例子,是在第一步就堵住了

    2018-11-26
    76
    508
  • 马涛
    索引问题答案解释这个是不是再详细一点,我看还有人和我一样,还是搞不清楚为什么c索引和ca索引一样。

    作者回复: InnoDB会把主键字段放到索引定义字段后面, 当然同时也会去重。 所以,当主键是(a,b)的时候, 定义为c的索引,实际上是(c,a,b); 定义为(c,a)的索引,实际上是(c,a,b) 你看着加是相同的 ps 定义为(c,b)的索引,实际上是(c,b,a)

    2019-03-02
    39
    417
  • 壹笙☞漂泊
    总结: 根据加锁范围:MySQL里面的锁可以分为:全局锁、表级锁、行级锁 一、全局锁: 对整个数据库实例加锁。 MySQL提供加全局读锁的方法:Flush tables with read lock(FTWRL) 这个命令可以使整个库处于只读状态。使用该命令之后,数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。 使用场景:全库逻辑备份。 风险: 1.如果在主库备份,在备份期间不能更新,业务停摆 2.如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟 官方自带的逻辑备份工具mysqldump,当mysqldump使用参数--single-transaction的时候,会启动一个事务,确保拿到一致性视图。而由于MVCC的支持,这个过程中数据是可以正常更新的。 一致性读是好,但是前提是引擎要支持这个隔离级别。 如果要全库只读,为什么不使用set global readonly=true的方式? 1.在有些系统中,readonly的值会被用来做其他逻辑,比如判断主备库。所以修改global变量的方式影响太大。 2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持readonly状态,这样会导致整个库长时间处于不可写状态,风险较高。 二、表级锁 MySQL里面表级锁有两种,一种是表锁,一种是元数据所(meta data lock,MDL) 表锁的语法是:lock tables ... read/write 可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。 对于InnoDB这种支持行锁的引擎,一般不使用lock tables命令来控制并发,毕竟锁住整个表的影响面还是太大。 MDL:不需要显式使用,在访问一个表的时候会被自动加上。 MDL的作用:保证读写的正确性。 在对一个表做增删改查操作的时候,加MDL读锁;当要对表做结构变更操作的时候,加MDL写锁。 读锁之间不互斥。读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。 MDL 会直到事务提交才会释放,在做表结构变更的时候,一定要小心不要导致锁住线上查询和更新。

    作者回复: 早啊今天😄

    2018-11-26
    6
    228
  • Mr.Strive.Z.H.L
    关于文中小表DDL的疑惑: sessionC(DDL操作)被前面的sessionA和B(查询操作,获取MDL 读锁)所阻塞。这里sessionC的DDL操作任务肯定是处于等待的,后续来的sessionD(查询操作)为什么会被sessionC所阻塞? 我理解的是sessionC现在都还没有进行DDL操作,没有获取到MDL写锁,为什么sessionD会被C阻塞?难道mysql Server端对于sessionC,D有一个 队列 来决定谁先执行?

    作者回复: “难道”正确😄

    2018-12-07
    21
    184
  • Tony Du
    基于文中的例子MDL(metadata lock),自己做了一个实验(稍微有一些小改动在session D上), session A: begin; select * from t limit 1; 最先启动sessionA session B: begin; select * from t limit 1; 紧接着启动sessionB session C: alter table t add f int; 然后再是启动sessionC session D: begin; select * from t limit 1; 最后是启动sessionD 如文中例子,session A和B正常启动,然后session C被block,之后session D也被block。当把 session A 和 session B 都commit掉后,发现session C依然是block的(被 session D阻塞),只有当把 session D 也commit掉后,session C才执行下去。同样的实验,重复了三遍,结果也是一样。 从现象上看,session D会先拿到MDL读锁,当session D commit掉后,然后再是session C获得MDL写锁。请问老师,这里对于MDL锁的获取顺序(也就是说 是session C先获取MDL写锁还是session D先获取MDL读锁)有什么原则?是随机的还是有什么讲究? 另外,在一开始的获取MDL锁的阶段,session A(MDL读锁,正常执行)-> session B (MDL读锁,正常执行) -> session C (MDL写锁,被block) -> session D (MDL读锁,被MDL写锁 block) 。是不是说加MDL锁是看时间先后顺序的,一旦出现过需要获取MDL写锁(即使被block),后续再需要获取MDL读锁,则发现之前已经有获取MDL写锁(即使被block),需要获取读锁的session都会被block。感觉上像进入一个锁的”队列“,根据时间先后顺序。请问老师,这里可以更细节和深入的说明下吗?  作者回复 你这个例子里面,sessionD 被C堵住后是不能输入命令的,之后是什么动作之后,sessionD才能输入commit语句呢 我的回复: session D被C堵住后,会卡在select * from t limit 1这句。然后当我把A和B都commit掉,session D就会执行select * from t limit 1这句,此时,session C依旧会被堵着。然后把session D commit掉,session C才能执行。实验的时候,我是把sql语句都写在MySQL workbench里的,通过workbench连服务器的,session D的commit语句是写在workbench里执行的。我的问题是,为什么是session D先获取的MDL读锁,而不是session C先获取MDL写锁,对于MDL锁的获取顺序有什么原则?是随机的还是有什么讲究?

    作者回复: 你用MySQL 客户端试试,我跑出来是文中的顺序哈。给我一下你的MySQL 版本号和workbench版本号

    2018-11-27
    49
    69
  • 柳树
    既然session C blocked,拿不到写锁,那么session D为什么会被blocked呢?

    作者回复: 如果说设计初衷,是为了防饿死吧

    2019-01-05
    6
    47
  • S@Y@M
    全局锁和表锁是Server层实现的吗

    作者回复: 是的

    2018-11-28
    5
    38
收起评论
显示
设置
留言
99+
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部