深入MySQL--表级锁算法

本帖最后由 lee 于 2020-6-16 12:13 编辑

MySQL 里面表级别的锁有两种:
一种是表锁(table lock),即我们经常谈论到的表锁结构
一种是元数据锁(meta data lock,MDL),即DDL操作中涉及到的表级别锁算法.

1:
表锁的语法写作:
  1. lock tables … read/write。
复制代码

表锁与 FTWRL (flush tables with  read lock)类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。
注意 : lock tables 语法除了会限制其他的线程的读写操作外,也限制了本线程后续的操作对象。

例如, 如果在某个线程 A 中执行         
  1. lock tables t1 read, t2 write;
复制代码

则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。

2:
另一类表级的锁算法是 MDL(meta data lock)。MDL 不需要显式的指定来使用,在访问操作一个表的时候会被自动添加。


MDL 的作用是,保证读写的正确性。例如,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

所以,MySQL官方在 MySQL 5.5 版本中引入了 MDL机制,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

故,MDL锁分为 MDL读锁和写 MDL写锁 。

MDL读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。

MDL读写锁之间、MDL写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

虽然 MDL 锁是系统默认会加的,但却是不能忽略的一个重要机制。

比如下面这个例子,
游客,如果您要查看本帖隐藏内容请回复
7cf6a3bf90d72d1f0fc156ececdfb0ce.jpg



我们可以看到 session A 先启动,这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁,因此可以正常执行。

之后 session C 会被 blocked,是因为 session A 的 MDL 读锁还没有释放,而 session C 需要 MDL 写锁,因此只能被阻塞。

游客,如果您要查看本帖隐藏内容请回复

但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?

这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。


游客,如果您要查看本帖隐藏内容请回复
标签: 暂无标签
lee

写了 75 篇文章,拥有财富 322,被 8 人关注

转播转播 分享分享 分享聚知识
回复

使用道具

P3 | 发表于 2020-6-18 19:19:02
66666666666666666666
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈