关于MySQL建表对DML的影响【转】

本文来自这里

今天一位同学问到线上曾经碰到过连续建表，导致阻塞普通的insert、update等。不过也没有保留现场。因此有疑问为什么建表会影响DML？

分析

         首先这个现象不是在所有场景都会碰到（否则MySQL的用户们早就跳起来了）。

一来建表这个操作本身很快，只涉及到写表定义文件和初始化表空间。中间涉及到redo和undo的操作也很少（这里只讨论InnoDB表）。因此除非碰到磁盘IO响应不了，否则多数情况下建表操作很快结束，不会“稳定复现”

二来即使由于io原因，建表过程执行时间较长，建表操作也不会阻塞一些DML操作。

         因此只能从代码出发看冲突的case。

         假设session 1正在执行一个create table操作，且由于io原因阻塞在写表空间文件这个步骤上。讨论session2作如下操作的场景。

无主键表insert

         此时insert操作由于需要申请系统自增主键，需要对dict_sys->mutex加锁。而这个锁需要等session1建表操作完成后才释放，因此出现等待。

有外键表的操作

         此时session2需要判断外键一致性，需要对dict_sys->mutex加锁。

         这里包含几个方面：外键约束的child表插入数据时和parent表删除数据时，已经这两个表的关联外键字段被修改时，均会触发等待。

有同学会说我们线上这两种情况都禁止了，是不是就不会因为这个锁的原因导致阻塞dml？

新打开表时

         若这个insert操作需要新打开一个表时，需要根据表名从字典中取出信息，也会触发等待。

         即使原来已经打开过的表，也会因为执行了flush table或者表空间淘汰而要求下次访问需要重新打开。

影响的其他操作

         顺着dict_sys->mutex我们还可以发现有以下几个操作，若发生在session2，都会被阻塞

1)                 1) Flush tables

2)                    select * from information_schema.tables;

        2) 以上两个因为都要访问到表对象列表，还比较好理解

3)                 select * from information_schema.innodb_sys_tables;

       3)实际上可以用另外一个锁来单独处理sys_tables

4)                  show create table another_table

    这个是因为必须判断是否有外键关联

         简单留个问题：为什么show tables并不会被阻塞？