两文说透MySQL里的各种锁(上篇)

【MySQL系列相关】

1.聊一聊关于MySQL的count(*)

2.为什么MySQL分库分表后总存储大小变大了?

0.前言

MySQL按照加锁的范围,分为全局锁、表级锁、行m I o H级锁。

本文作为上篇,m ` I f a M d 6主要介绍MySQL的全局锁 和 表级锁。

重要的实战a h a n R r z总结为,如何安全地变更一个表的G . a X f d t表结构。

两文说透MySQL里的各种锁(上篇)

搞个图做封面5 T M 4 u

  1. 全局

定义:

全局锁就是对整个数据库实例加锁

全局锁语法:

Flush tablY ? 6 [ G Z ( I ues with read lock (FTWRL)

当你使用这个命令后,整个} F N库处于只读状态,之后其他线程的数据更新语句(DML)、数据定义语句(DDL)都会被阻塞。

场景:不支持事务的引擎(如MyISAM),做全库的逻辑备份。

不过我们一般使用innodb,这个锁不太会接触,就不展开详细介绍了。

  1. 表级锁

表级锁其实有两种,一种叫表锁,一种叫原数据锁(meta data lock, MDL)

2.1 表锁

表锁的语@ Y $ G法是:

锁表:ly & n r o d w m ock tables … read/write

释放锁:unlock tables 主动释放锁

表锁也能够在客户端断开的时候自动释放。

值得注意的是,lock ta0 s R ^ F 1 sbles 除了会限制别的线程的读写外,当前线程接下来的操作也会被限制。

同样的,对于innoDB这种支持行锁的引擎,我们一般也不会去使用用表锁,因此,这部分内容也只需要简m { J B ( } [单了解下即可。

2.2 MZ k i 5 $ lDL锁

原数据锁meta data lock 也是一种表级锁,在 MySQL 5.5 版本中引入。

当我们对一个表做CRUD操作的d w o 4时候,会加 MDL 读锁;当要对表做结构变更操作的时候,加 MDLc T [ $ 写锁。

两文说透MySQL里的各种锁(上篇)

MDL读写锁的互斥

读锁之间不互斥,所以我们可以有多个线程W V U M b = : $同时对一张表增删改查。
读锁和写锁之间、写锁和写锁8 9 C T G之间是互斥的,用来保证变更表结构操作的安全性。因此,只要有一个线程要对一个表加字段,K = j j Q N m那天其他线程必须等这个线程完成表结构变更以后才能执行。
i y P到这里,不得不给大家举例一个日常会踩的坑。

r c J - 4于大表DDL,大家一般比较谨慎,而对于小表,就会比较随意。但是小表DDL也能造成数据库崩溃。

两文说透MySQL里的各种锁(上篇)

表结构变更阻塞实例

Session A第一个启动,开启事务,select后没有立刻commit,模仿一个长事务,此时,session A对表tt的MDL读锁还没有释放。

Session B之后执行一个select. c o ,由于MDL读锁之间不互斥,因此执行成功。

Session C之后想对表tt 执行一个altek 7 =r语句,需要获得MDL写锁,但是由于Session A持i Z 3 | } G n有了MDL读锁,读写锁互斥,因此Session C被阻塞。

Session D这时候也i y 4 7 y f @ (想执C Q E 5行一个select,由于Session C被阻塞,所以Se0 b = t G P / Wssion D也会被阻塞。

所以这个时候t w =,后续的线程就都无法读写了。

如果接下来这个表上有频繁的查询,而且客户端有重试机制,那么超时后会再起一个新的session来查询,很快数据库的线程就溢出了。

这里有几个小问题需要稍微再解释一下:

1)从Session A的情况我们得知,事务中 的MDL锁,在语句开始时申请,但是并不是在语句结束后马上释放的,而是在整个事务提交后才会释放。

2)SessionC(DDL操作)被前面的Sessik M : 5 K G @ PonA和B(查询C C e 3 5 E Y操作,获取MDL 读锁)所阻塞,这里实际上并没有成功获取MDL写锁,为什么Session D的读操作会被sessionC所阻塞呢?这里的原因是,d f dMySQL ServU f 9 | G u [er端,对于Session C和SV + q dession D会有一个队列来决定谁先执行。

看到这里,我相信肯定有对MySQL比较熟悉的朋友会问了,MySQL 5.6不是号称支持Online DDL吗?怎么这里又会有各种阻塞呢?

首先o ? Y 4 =,我们先明确下什j 7 ! b么叫做Online DDL。

Online DDL的过程中,对于锁的获取= q F s L分为五步(具体online DDL过程比较复杂,本文不展开说明):

1)拿到MDL写锁

2)降级成MDL读锁

3)真正做DDL

4)升级成MDL写锁

5)释放MDL锁

1、2、4、5如果没有锁冲突,执行时间都是非常短的。绝大部分时间是第三步占用了,而这个期间,表可用正常读写,所以被称为Online DDL。

而我们上文的例子,实际上是在第C 7 7 O 3 b *一步就阻塞了。

  1. So,如何做一次安全的表结构变更

其实,无论大表小表的表结构变更,都应用引起我们重视。

总结了上文,大家应该都知道了最关键的三点:

避免长事务!

避免长事务!

避免长事务!

尤其是在执行表结构变更前,可以在informatio: 9 yn_schema库的innodb_trx表中,查看当前执行的事务。如果有长事务正在执行,要延迟等待执行变更,或者手动先kill长事务。

另外,尽量保证表结构变更在数据库流量低峰期操作,比如夜间,这样能更好地避免出现风险。

所以,如何做一次安全的表结构变更?

1)避免长事务

2)在流量低峰进行

就是这么简单。

下一期,我们就好好聊聊 行锁,不见不散~

参考:

丁奇《MySQL 实战45讲》

非常感谢您能看到这里,如果觉得文章还不错的话,
求在看,求关注,求分享,求留言。
希望能跟大家一起学习,共同进步。