抛砖引玉
问题：给表加索引时，会不会阻塞写？会不会阻塞读？
答案：会阻塞写，某些情况下会阻塞读。
什么是元数据？
元数据，即表示数据的数据。
在数据库中，库、表空间、表、函数、存储过程、触发器、事件、列名，这些都是元数据。
information_schema库中的表，保存的都是Mysql的元数据。
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_CMP_RESET |
| INNODB_TRX |
| INNODB_CMPMEM_RESET |
| INNODB_LOCK_WAITS |
| INNODB_CMPMEM |
| INNODB_CMP |
| INNODB_LOCKS |
+---------------------------------------+
为什么要有元数据锁?
主要是为了解决两个问题：
1、事务隔离问题
2、主从同步问题
1、元数据锁解决不可重复读问题
| 事物并发问题 | |
|---|---|
| 脏读 | 事物A读取了事物B未提交的数据 |
| 不可重复读 | 一个事务多次查询同条记录得到不同的结果 |
| 幻读 | 一个事务里同个条件多次查询得到不同的结果集 |
SQl标准定义的事务隔离级别
| SQL标准定义事务隔离级别 | 解决的问题 | 允许存在的问题 | |
|---|---|---|---|
| 未提交读(READ UNCOMMITTED) | 一个事务可以读取另一个未提交事务的值 | - | 脏读 |
| 提交读(READ COMMITTED) | 一个事务只能读取其他事务已经提交的值 | 脏读 | 不可重复读 |
| 可重复读(REPEATABLE READ) | 一个事务多次查询同条记录得到相同的结果 | 不可重复读 | 幻读 |
| 串行化(SERIALIZABLE) | 完全串行化的读，一个事务的读会阻塞其他事务的读写 | 所有 | - |
Innodb的事务隔离级别实现
| Innodb的事务隔离级别 | 解决的问题 | 允许存在的问题 | 实现方式 | |
|---|---|---|---|---|
| 未提交读(READ UNCOMMITTED) | 一个事务可以读取另一个未提交事务的值 | - | 脏读 | 不加锁 |
| 提交读(READ COMMITTED) | 一个事务只能读取其他事务已经提交的值 | 脏读 | 不可重复读 | 加共享锁 |
| 可重复读(REPEATABLE READ) | 一个事务多次查询同条记录得到相同的结果 | 不可重复读、幻读 | - | 间隙锁(针对当前读)+MVCC(针对快照读) |
| 串行化(SERIALIZABLE) | 完全串行化的读，一个事务的读会阻塞其他事务的读写 | 所有 | - | 加排它锁 |
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
rows in set (0.00 sec)
session2> alter table t1 add c1 int;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
session1> select * from t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
| 2 | b | NULL |
+------+------+------+
rows in set (0.00 sec)
session1> commit;
Query OK, 0 rows affected (0.00 sec)
同个事物session1里的同个查询select from t1在alter table t1 add c1 int前后查询的结果不一样！这是因为在mysql5.5.3之前，虽然也有MDL锁，但是基于语句级别的，比如第一次select * from t1查询之后就释放了MDL锁，所以接下来的alter table t1能执行成功，导致了不可重复读的问题。
mysql5.5.3之后，MDL锁是事务级别的了，MDL只有在事务commit后才会释放，这样譬如alter之类的DDL就不能在事务执行过程中去修改表相关的元数据了，从而解决不可重复读的问题。
2.元数据锁解决数据主从同步的问题
若没有MDL锁的保护，则事务2可以直接执行DDL操作，并且导致事务1出错，5.1版本即是如此。5.5版本加入MDL锁就在于保护这种情况的发生，由于事务1开启了查询，那么获得了MDL锁，锁的模式为SHARED_READ，事务2要执行DDL，则需获得EXCLUSIVE锁，两者互斥，所以事务2需要等待。
注：支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。
元数据锁和Innodb锁的区别
InnoDB层已经有了IS、IX这样的意向锁，有同学觉得可以用来实现上述例子的并发控制(思考1：为什么?)。但由于MySQL是Server-Engine架构，所以MDL锁是在Server中实现。另外，MDL锁还能实现其他粒度级别的锁，比如全局锁、库级别的锁、表空间级别的锁，这是InnoDB存储引擎层不能直接实现的锁。
元数据锁类型
| 属性 | 含义 | 事例 |
|---|---|---|
| MDL_INTENTION_EXCLUSIVE(IX) | 意向排他锁用于global和commit的加锁。 | truncate table t1; insert into t1 values(3,’abcde’);会加如下锁(GLOBAL,MDL_STATEMENT,MDL_INTENTION_EXCLUSIVE） (SCHEMA,MDL_TRANSACTION,MDL_INTENTION_EXCLUSIVE) |
| MDL_SHARED(S) | 只访问元数据 比如表结构，不访问数据。 | set golbal_read_only =on 加锁 (GLOBAL，MDL_EXPLICIT，MDL_SHARED） |
| MDL_SHARED_HIGH_PRIO(SH) | 用于访问information_scheam表，不涉及数据。 | select * from information_schema.tables; show create table xx; desc xxx;会加如下锁： (TABLE,MDL_TRANSACTION,MDL_SHARED_HIGH_PRIO) |
| MDL_SHARED_READ(SR) | 访问表结构并且读表数据 | select * from t1; lock table t1 read; 会加如下锁: (TABLE，MDL_TRANSACTION，MDL_SHARE_READ） |
| MDL_SHARED_WRITE(SW) | 访问表结构并且写表数据 | insert/update/delete/select .. for update 会加如下锁： (TABLE，MDL_TRANSACTION，MDL_SHARE_WRITE) |
| MDL_SHARED_UPGRADABLE(SU) | 是mysql5.6引入的新的metadata lock, 在alter table/create index/drop index会加该锁;可以说是为了online ddl才引入的。特点是允许DML，防止DDL； | （TABLE，MDL_TRANSACTION，MDL_SHARED_UPGRADABLE） |
| MDL_SHARED_NO_WRITE(SNW) | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务写。 | alter table t1 modify c bigint;(非onlineddl) (TABLE，MDL_TRANSACTION，MDL_SHARED_NO_WRITE） |
| MDL_SHARED_NO_READ_WRITE(SNRW) | 可升级锁，访问表结构并且读写表数据，并且禁止其它事务读写。 | lock table t1 write;加锁 (TABLE，MDL_TRANSACTION,MDL_SHARED_NO_READ_WRITE |
| MDL_EXCLUSIVE(X) | 防止其他线程读写元数据 | CREATE/DROP/RENAME TABLE，其他online DDL在rename阶段也持有X锁 (TABLE，MDL_TRANSACTION，MDL_EXCLUSIVE） |
alter加锁流程
- Opening tables阶段，加共享锁
a) 加MDL_INTENTION_EXCLUSIVE锁
b) 加MDL_SHARED_UPGRADABLE锁，升级到MDL_SHARED_NO_WRITE锁
2.操作数据，copy data，流程如下：
a) 创建临时表tmp，重定义tmp为修改后的表结构
b) 从原表读取数据插入到tmp表
3.将MDL_SHARED_NO_WRITE读锁升级到MDL_EXCLUSIVE锁
a) 删除原表，将tmp重命名为原表名
4.事务提交阶段，释放MDL锁
a) 释放MDL_INTENTION_EXCLUSIVE锁
b) 释放MDL_EXCLUSIVE锁
常见MDL锁场景
当前有执行DML操作时执行ALTRE操作。
SESSION A
mysql> insert into sbtest2 select * from sbtest1;
SESSION B
mysql> alter table sbtest2 add test1 int; //等待SESSION A执行完;
SESSION C
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
| 267 | root | localhost | sbtest | Query | 7 | Sending data | insert into sbtest2 select * from sbtest1 |
| 271 | root | localhost | sbtest | Query | 3 | Waiting for table metadata lock | alter table sbtest2 add test1 int |
| 272 | root | localhost | NULL | Query | 0 | starting | show processlist |
+-----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------+
3 rows in set (0.00 sec)
当前有对表的长时间查询或使用mysqldump/mysqlpump时，使用alter会被堵住。
SESSION A
mysql> select *,sleep(10) from sbtest2;
SESSION B
mysql> alter table sbtest2 add test2 int; //等待SESSION A执行完;
SESSION C
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
| 267 | root | localhost | sbtest | Query | 12 | User sleep | select *,sleep(10) from sbtest2 |
| 271 | root | localhost | sbtest | Query | 8 | Waiting for table metadata lock | alter table sbtest2 add test3 int |
| 272 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 311 | root | localhost | NULL | Query | 3 | Waiting for table metadata lock | select * from sbtest.sbtest2 limit 10 |
+-----+------+-----------+--------+---------+------+---------------------------------+---------------------------------------+
显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，使用alter会被堵住。
SESSION A
mysql> begin;
mysql> select * from sbtest2;
SESSION B
mysql> alter table sbtest2 add test2 int; //等待SESSION A执行完;
SESSION C
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
| 267 | root | localhost | sbtest | Sleep | 36 | | NULL |
| 271 | root | localhost | sbtest | Query | 30 | Waiting for table metadata lock | alter table sbtest2 add test2 int |
| 272 | root | localhost | NULL | Query | 0 | starting | show processlist |
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
3 rows in set (0.00 sec)
表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时alter仍然会被堵住。
SESSION A
mysql> begin;
mysql> select error from sbtest2;
ERROR 1054 (42S22): Unknown column 'error' in 'field list'
SESSION B
mysql> alter table sbtest2 add test3 int; //等待SESSION A提交或回滚;
SESSION C
mysql> show processlist;
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
| 267 | root | localhost | sbtest | Sleep | 7 | | NULL |
| 271 | root | localhost | sbtest | Query | 3 | Waiting for table metadata lock | alter table sbtest2 add test3 int |
| 272 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 311 | root | localhost | NULL | Sleep | 413 | | NULL |
+-----+------+-----------+--------+---------+------+---------------------------------+-----------------------------------+
4 rows in set (0.00 sec)
其实SESSION A中的事务并未开启，但是由于select获取表元数据的语句，语法上是有效的，虽然执行失败了，但是任然不会释放元数据锁，故而导致SESSION B的alter动作被阻塞。
思考题：
1.为什么有些同学会觉得可以用InnoDB的IS、IX锁来实现上述例子的并发控制？
答案：首先要理解InnoDB的IS、IX锁是什么：意向锁。InnoDB内部实现的为了达到检测行锁和表锁冲突的目的，Innodb在事务获得行锁之前，会先申请意向锁，而这个意向锁是表级别的锁，目的就是为了检测事务在获取行锁时有没有和表锁冲突。
因为意向锁是表锁，看起来可以达到的目的。
意向锁会不会使得行锁退化成表锁？
不会，意向锁之间不会冲突！它的主要作用是防止获取行锁时和其它表锁冲突。
