Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
git-zjx opened this issue Jul 19, 2019 · 9 comments
Open
Assignees
Projects

Comments

@git-zjx
Copy link
Owner

@git-zjx git-zjx commented Jul 19, 2019

全局锁

对整个数据库实例加锁,MySQL 提供一个加全局读锁的命令:Flush tables with read lock(FTWRL),命令执行后,数据更新语句(增删改)、数据定义语句(建表和修改表结构等)和更新类事务都会被阻塞,经常用于数据库备份场景

备份也可以使用 mysqldump 官方自带的逻辑备份工具,参数 -single-transaction 会在导数据之前启动一个事务,确保拿到一致性视图,而由于 MVCC 的支持,这个过程中是可以正常更新的

参数 -single-transaction 需要数据库引擎支持事务,如果不支持就只能使用 FTWRL 方案

为什么不使用 set global readonly = true 方式?

  1. 有些系统中 readonly 会被用作其他逻辑,例如判断是主库还是从库
  2. 异常处理机制有差异,FTWRL 后客户端异常断开,MySQL 会自动释放全局锁,库恢复正常;设置readonly,客户端异常,则会保持 readonly,会导致长时间处于不可写状态,风险较高
  3. readonly 对 super 权限无效

表级锁

MySQL 的表级锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)

lock tables … read/write 语句用于加表锁,使用 unlock tables 可以释放锁,客户端断开连接时也会释放锁
需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

在 MySQL 5.5 版本中引入了 MDL,MDL 不需要显式调用,在访问一个表时自动调用,主要保证读写的正确性,防止DDL和DML并发的冲突

当表做增删改查操作时,加MDL读锁;当对表结构变更的时候,加MDL写锁

  • 读锁之间不互斥,多线程可对同一张表增删改查
  • 读写锁之间、写锁之间互斥。两个线程同时给一个表增加字段,则第二个需要等待第一个执行完才能继续

小表加字段为什么会导致整个库崩溃?
image
Session A 会给表 t 加 MDL 读锁,Session B 需要的也是读锁,可以执行
Session C 需要 MDL 写锁,但 Session A 的 MDL 读锁还未释放,需要等待
Session D 需要 MDL 读锁,但是因为 MySQL 内部维护了一个队列决定执行顺序,Session C 阻塞了 Session D
这里可以知道事务中的 MDL 锁会等到整个事务结束后再释放

如何安全的给小表加字段?

  • 解决长事务
  • 在 alter table 语句中设置等待时间
@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Jul 23, 2019
@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

MySQL 5.6不是支持 online ddl 了吗?也就是对表操作增加字段等功能,实际上不会阻塞读写?

Online DDL的过程是这样的:
①拿MDL写锁
②降级成MDL读锁
③真正做DDL
④升级成MDL写锁
⑤释放MDL锁;
①、②、④、⑤如果没有锁冲突,执行时间非常短。
第③步占用了 DDL 绝大部分时间,这期间这个表可以正常读写数据,是因此称为“online ”

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

关于MDL写锁,有个更好的处理方法不知道是否稳妥?先基于原表创建一个临时表,完成字段更新修改;然后对原表加MDL写锁,加锁成功后把临时表更名为原表,这样加锁时间很短

最早的 DDL 内部实现就是这么做的,不过加锁时间也不短。
“先基于原表创建一个临时表,完成字段更新修改;” 这个过程,要包含“把原表的数据导入临时表”,导入的过程还是对原表加 MDL 锁的。

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

各个锁的读锁、写锁会产生什么效果?

读锁之间兼容,读写、写写之间需要互相等待

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

Online DDL 完成后,为啥要升级 MDL 写锁再释放?

需要切表名

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

Online DDL 过程中,假设线程 A 正在执行第 3 步,给表加字段,这时线程 B 查询数据,正在遍历表中的数据。这样拿到的数据会不会对不上?

在DDL执行过程中读数据,看到的是修改之前的的表结构

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

Online DDL 为什么可以降为MDL读锁?

改元数据是在第四步和第五步之间做的,这时候确实持有写锁

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

为什么要先拿写锁再降为读锁再升级为写锁,好像也不符合二阶段锁协议的锁上升和下降的约束?

为了并发

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

如果autocommit=1设置了,select之前不显示的开启一个事务,也没有相应的commit是不是就不存在DML锁了?

DML一般指的是增删改,不过若果select ... lock in share mode; select ... for update也会有行锁

@git-zjx
Copy link
Owner Author

@git-zjx git-zjx commented Feb 26, 2020

二级索引会把索引列+主键值作为排序键来排序?

是的,有保证主键算进去,还是有序的

@git-zjx git-zjx self-assigned this Mar 2, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
MySQL
MySQL实战45讲
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant