文档章节

06.MySQL实战45讲学习笔记---全局锁和表锁

scgaopan
 scgaopan
发布于 2019/12/12 00:15
字数 2055
阅读 12
收藏 0

根据加锁的范围,MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法,命令是flush tables with read lock(FTWRL),执行这个命令后就可以使整个库处于只读状态(断开此连接后,全局锁会自动释放,也可以执行unlock tables进行主动解锁),其它线程的以下语句会被阻塞:数据库更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构)和更新类事务的提交语句。全局锁的典型的使用场景是做全库逻辑备份,执行FTWRL后确保不会有其它线程对数据做更新,然后对整个库做备份,注意在备份过程中整个库完全处理只读状态。

mysqldump 是官方自带的逻辑备份工具,当mysqldump使用参数-single-transaction的时候,导数据之前会启动一个事务,来确保拿到一致性视图,而由于MVCC的支持,这个过程中数据是可以正常更新的。有了这个功能,为什么还需要FTWRL呢?一致性读是好,但是前提是引擎要支持这个隔离级别,比如,对于MySAM这种不支持事务的引擎,如果备份数据中有更新,总是能取到最新的数据,那么就破坏了备份的一致性,这时我们就需要使用FTWRL了。

  所以 -single-transaction方法只适用于所有的表使用事务引擎的库。既然要全库只读,为什么不使用set global readonly=true的方式呢?这个方式的确可以让全库进入只读状态,但是建议用FTWRL方式,原因如下:

  • 在一些系统中,readonly的值会被用来做其它逻辑,比如用来判断一个库是主库还是从库。
  • 在异常处理机制上有差异。如果执行FTWRL后,由于客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。如果把整个库设置成readonly之后,如果客户端发生异常,则数据库会一直保持readonly状态,风险较高。

业务的更新不只是增删改数据(DML),还有可能是加字段等修改表结构的操作(DDL)。不管是哪种方式,一个库被全局锁上之后你要对里面任何一个表做加字段操作,都会被锁住。即使没有被全局锁住,加字段也不是能一帆风顺的,因为你还会碰到表级锁。

表级锁

MySQL里面表级别的锁有两种:一种是表锁,一种是无数据锁(meta data lock,MDL)。

  • 表级锁的语法是 lock tables <表名> read/write。与FTWRL类似,可以用unlock tables 主动释放锁,可以在客户端断开连接时自动释放。lock tables语法会限制别的线程读写外,也限定了本线程接下来的操作对象。如:在某个线程A中执行lock tables t1 read,t2 write 这个语句,则其它线程写t1,读写t2的语句会被阻塞。同时线程A在执行unlock tables之前,也只能执行读t1,读写t2的操作,连写t1都不允许,自然也不允许访问其它表。
  • MDL不需要显示使用,在访问一个表的时候会自动加上。MDL的作用是保证读写的正确性,你可以想象一下,一个查询正在遍历一个表中的数据,而在执行期间另一个线程对这个表结构做了变更,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。所以,当一个表做增删改查操作的时候,加MDL读锁;当对表结构做变更的时候加DML写锁
  1. 读锁之间不互斥,因此你可以有多个线程同时对同一张表做增删改查,
  2. 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。如果两个线程要同时对一个表加字段,其中一个要等另一个执行完成后才能开始执行。

虽然DML锁是系统默认会加的,但是你不能忽略一个机制,比如下面这个例子:给一个小表加字段,导致整个库挂了。

你肯定知道,给一个表加字段,或者修改字段,或者加索引都需要扫描全表的数据。在对大表做操作时,你肯定会特别小心,以免对线上服务造成影响。而实际上,即使是小表,操作不慎也会出问题。我们来看以下操作(MySQL版本是5.7)

可以看到,我们先启动一个sessionA,这个时候对表t加一个MDL读锁。由于sessionB需要的也是MDL读锁,因此可以正常执行。之后session C会被blocked,因为sessionA的MDL读锁还没有释放,而session C需要MDL写锁,因此只能被阻塞。如果只有session C自己被阻塞还没关系,但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。因为对表的增删改查都要先申请MDL读锁,所以都会被阻塞,等于这个表完全不可以读写了。

你现在应该知道了,事务中的MDL锁在语句执行开始时申请,但语句结束后并不会马上释放,而会等到整个事务提交后再释放。

基于上面的分析,那如何安全地给小表加字段呢?

 首先我们要解决长事务,事务不提交,就会一直占着MDL锁。在MySQL的information_schema库的innodb_trx表中,你可以查一当前执行中的事务。如果你要做DDL变更的表刚好有长事务在执行,要考虑先暂停DDL,或者kill掉这个长事务。

问题讨论:

备份一般在备库上执行,你在用-single-transaction方法做逻辑备份的过程中,如果主库上的一个小表做了DDL,比如给一个表上加了一列,这时候从备库上会看到什么现象?

假设这个DDL是针对表t1的,这里我把备份过程中几个关键的语句列出来:

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT;

Q3:SAVEPOINT sp;
/*时刻1*/
Q4:show create table `t1`;
/*时刻2*/
Q5:select * from `t1`;
/*时刻3*/
Q6:ROLLBACK TO SAVEPOINT sp;
/*时刻4*/
  • Q1:在备份的时候,为了确保RR(可重复读)隔离级别,再次设置RR隔离级别。
  • Q2:用START TRANSACTION WITH CONSISTENT SNAPSHOT启动事务,确保这个语句执行完就可以得到一个一致性视图。
  • Q3:设置一个保存点,这个很重要。
  • Q4:show create 是为了拿到表结构
  • Q5:正式导数据
  • Q6:回滚到SAVEPOINT sp,在这里的作用是释放t1的MDL锁。

DDL从主库传过来的时候按效果不同,我打了4个时刻。

1.如果在Q4语句执行之前到达,现象:没有影响,备份拿到的是DDL之后的表结构。

2.如果在“时刻2”到达,则表结构被改过,Q5执行的时候,报Table definition has changed,please retry transaction,现象:mysqldump终止;

3.如果在“时刻2”和“时刻3”之间到达,mysqldump占着t1的MDL读锁,binlog被阻塞,现象:主从延迟,真到Q6执行完成。

4.从“时刻4”开始,mysqldump释放了MDL读锁,现象:没有影响,备份拿到的是DDL前的表结构。

 

© 著作权归作者所有

scgaopan
粉丝 4
博文 64
码字总数 42784
作品 0
成都
私信 提问
jstack 和 jmap 分析生产 CPU 100% 问题

第一次做 Java 生产环境的问题排查,借机学习了 jstack / jmap / MAT 等工具的使用,也对 MySQL 事务和锁的理解更进一步。 整个过程累计用了 10 个小时,大量的时间用在了读 jstack log 和 ...

gucs
2019/05/26
0
0
对于MySQL你必须要了解的锁知识

一、前言 MySQL 的锁按照范围可以分为全局锁、表锁、行锁,其中行锁是由数据库引擎实现的,并不是所有的引擎都提供行锁,MyISAM 就不支持行锁,所以文章介绍行锁会以InnoDB引擎为例来介绍行锁...

木木匠
2019/04/15
810
4
MySQL 笔记整理(19) --为什么我只查一行的语句,也执行这么慢?

MySQL 笔记整理(19) --为什么我只查一行的语句,也执行这么慢? 笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》 (本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除) 19) --...

优惠券活动
2019/04/21
0
0
Mysql心路历程:Mysql各种锁机制(入门篇)

这一篇文章是本人数据库的第二篇,也是对数据库学习的阶段性总结。对于数据库锁的了解,是区分程序员,尤其是Java程序员,中高级的一个重要标志。也是日常,我们开发中,经常碰到坑的地方。往...

心中的理想乡
2019/06/03
1.1K
0
Java可重入锁学习笔记

目录 什么是可重入锁 为什么要可重入 如何实现可重入锁 有不可重入锁吗 demo代码展示 参考文章 1 . 什么是可重入锁 锁的概念就不用多解释了,当某个线程A已经持有了一个锁,当线程B尝试进入被这...

squanchao
2016/07/12
56
1

没有更多内容

加载失败,请刷新页面

加载更多

如何在PHP中获取字符串的最后一个字符?

我需要获取字符串的最后一个字符。 假设我有“测试人员”作为输入字符串,并且我希望结果为“ s”。 如何在PHP中做到这一点? #1楼 请记住,如果有一个字符串使用fgets()函数从文本文件中读取...

javail
26分钟前
36
0
DaY07方法重写的面试题

方法重写的面试题 * Override和Overload的区别?Overload能改变返回值类型吗? * overload可以改变返回值类型,只看参数列表 * 方法重写:子类中出现了和父类中方法声明一模一样的方法。与返回值...

Lao鹰
29分钟前
43
0
用Markdown编程之类型

类型就是约定。而现有的类型是单纬度的。用标注法编程好处就是可以多维度。 类型基础分为: 虚 实 在此之上分为: 根 寄存器级 联 内存级 外 网络级 虚:说白了就是指针或索引之类的概念。之...

dwcz
47分钟前
66
0
每天AC系列(四):四数之和

1 题目 Leetcode第18题,给定一个数组与一个target,找出数组中的四个数之和为target的不重复的所有四个数. 2 暴力 List<List<Integer>> result = new ArrayList<>();if (nums.length == 4 &......

Blueeeeeee
今天
70
0
git clone --mirror和git clone --bare有什么区别

git clone帮助页面上有关于--mirror : 设置远程存储库的镜像。 这意味着--bare 。 但没有详细介绍--mirror克隆与--bare克隆--mirror不同。 #1楼 克隆将从远程服务器复制参考,并将其填充到名...

技术盛宴
今天
86
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部