MySQL相关问答

最新推荐文章于 2024-10-12 00:08:56 发布

泰山与水

最新推荐文章于 2024-10-12 00:08:56 发布

阅读量156

点赞数

分类专栏： # MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/cqupt2012214390/article/details/120460352

版权

MySQL 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

1、为什么MySQL号称Online DDL？

2、readonly = true 无效？

3、更新全表操作数据是MDL表锁吗?

4、意向锁是表锁吗？与MDL锁主要区别是什么呢？

9、备库用–single-transaction做逻辑备份时，如果从主库的 binlog 传来一个DDL语句怎么处理？

10、死锁检测innodb_deadlock_detect，是每条事务执行前都会进行检测吗？

1、为什么MySQL号称Online DDL？

1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁

1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”

针对以上5个步骤，做进一步的详细说明：

1、拿MDL写锁：（该步执行时间短）此时当A、B线程都来做DDL的时候，如A拿到了MDL写锁，B就阻塞，其它（增删改查）线程拿MDL读锁阻塞；

2、DDL执行准备：（该步执行时间短）此时当A、B线程都来做DDL的时候，如A拿到了MDL写锁，B任然阻塞，其它（增删改查）线程拿MDL读锁阻塞；

3、降级成MDL读锁：（该步执行时间短）此时当A、B线程都来做DDL的时候，如A拿到了MDL写锁且已降级成MDL读锁，B拿MDL写锁任然阻塞，其它（增删改查）线程拿MDL读锁可以读取数据；

4、DDL核心执行：（该步耗时最多）此时当A、B线程都来做DDL的时候，如A拿到了MDL写锁且已降级成MDL读锁，B任然阻塞，其它（增删改查）线程拿MDL读锁可以读取数据
，该步执行时间长且此期间其它（增删改查）线程可以正常执行，业务影响小，因此总体上号称在线DDL；

5、升级成MDL写锁: （该步执行时间短）此时当A、B线程都来做DDL的时候，如A拿到了MDL写锁，B任然阻塞，其它（增删改查）线程拿MDL读锁阻塞；

6、DDL最终提交：（该步执行时间短）此时当A、B线程都来做DDL的时候，如A拿到了MDL写锁，B任然阻塞，其它（增删改查）线程拿MDL读锁阻塞；

7、释放MDL锁：（该步执行时间短）此时当A、B线程都来做DDL的时候，如A释放写锁，B拿到DDL锁，其它（增删改查）线程拿MDL读锁阻塞，继续循环上面个的步骤

online ddl 的copy方式和inplace方式，也都是需要拿MDL写锁、降成读锁、做DDL、升成写锁、释放MDL锁吗？

是，是否online都是第三步的区别，另外四步还是有的

可以用pt-online-schema-change在线DDL

2、readonly = true 无效？

如果 slave通过readonly = true设置全库只读，如果在 slave 上如果用户有超级权限的话 readonly 是失效的，所以不要通过readonly = true来判断数据库是否只读，建议用 Flush tables with read lock (FTWRL)
在从库执行readonly 会不会影响主从复制，也就是说会不会导致从节点无法写入master提供的数据了？
不会，readonly对super权限用户无效，执行binlog的线程是super权限

3、更新全表操作数据是MDL表锁吗?

update table set xx;

不是，还是行锁，只是“这个表的所有行

4、意向锁是表锁吗？与MDL锁主要区别是什么呢？

意向锁是表级锁，是实现在Innodb 的；

MDL锁是servier层的锁，还没走到引擎层，因此如果拿到了MDL锁，意向锁就先不用考虑了

5、怎么给大表加字段

Gh-ost现在用的比较多

6、怎么安全地给小表加字段？

非热点表：解决长事务，避免阻塞业务；
热点表：在 alter table 语句里面设定等待时间，避免阻塞业务；

7、做全库逻辑备份时怎么加锁？

需要使用全局锁，方法有二：

有的表使用了不支持事务的引擎：使用FTWRL
所有的表都使用事务引擎的库：使用 mysqldump 带 -single-transaction 参数

一个库被全局锁上以后，你要对里面任何一个表做加字段操作（包括DML、DDL），都是会被锁住的

8、元数据锁MDL定义

元数据锁MDL不需要显示使用；DDL的时候加MDL写锁，增删改查的时候加MDL读锁；
MDL 读锁之间不互斥，读写锁、写锁之间互斥；
事务中的 MDL 锁在事务提交后才释；
如果不显示开启事情，语句执行完自动释放MDL锁，因为语句执行完了事务自动提交；

9、备库用–single-transaction做逻辑备份时，如果从主库的 binlog 传来一个DDL语句怎么处理？

假设这个 DDL 是针对表 t1 的，这里我把备份过程中几个关键的语句列出来：
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);
启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；
设置一个保存点，这个很重要（Q3）；
show create 是为了拿到表结构 (Q4)，
然后正式导数据（Q5），
回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁（Q6）。当然这部分属于“超纲”，上文正文里面都没提到

DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成；

参考答案如下：

如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构；
如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成；
从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构；

10、死锁检测innodb_deadlock_detect，是每条事务执行前都会进行检测吗？

如果它要加锁访问的行上有锁，他才要检测；
一致性读不会加锁，就不需要做死锁检测；
并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：
B在等A，
D在等C，
现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A
将所有持有的锁构建一个等待图，形成环路就表示死锁；死锁检测的时间复杂度应该是O(n2)；单个事务判断的时候是O(n)；