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

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

根据加锁的范围,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方式,原因如下:

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

表级锁

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

  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*/

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前的表结构。

 

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看