《丁奇-MySQL45讲-06》之归纳总结

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍

• 全局锁：对整个数据库实例加读锁，命令是Flush tables with read lock，使用这个命令后其他线程的增删改、修改表结构、建表将会被阻塞（使用unlock tables可以解除）。主要用来做全局备份。

• 为什么使用全局锁而不要使用全库只读（set global readonly = true）：一方面是readonly通常会被用来做其他的逻辑，比如说用来判断主备库，随意修改全局变量可能会影响；另外一方面使用全局锁的客户端如果崩溃了的话，MySQL会自动释放锁，整个库可以回到正常更新的状态，而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，导致整个库长时间处于不可写状态。

• 表锁：加表锁的语法是lock tables t1 read/write，如果加的是读锁，当前线程和其他线程都可以读，但是都不能修改（增删改），当前线程会直接报错提示你指定表加了读锁无法更新，而其他线程是被阻塞，只有在当前线程unlock tables解锁后才会继续执行；如果加的是写锁的话，当前线程可以读写（增删改查），其他线程一直阻塞直到解锁。

• 元数据锁（metadata lock）：属于Server层的锁，表级锁，主要用于隔离DML和DDL操作之间的干扰，比如一个线程在查询数据的时候，另外一个线程肯定不能修改表结构，不然就乱套了，所以DML操作需要申请MDL读锁，而DDL操作需要申请MDL写锁，读读之间共享，读写之间互斥，写写之间互斥。来看一个现象：

1. 首先先将自动开启事务的开关关闭，set global autocommit=0；

2. 可以看到手动开启SessionA，那么会申请到MDL的读锁，注意此时还未释放读锁，因为事务还没有提交，接着手动开启SessionB，此时申请的也是MDL的读锁，读读共享，自然也能正常执行了。

3. SessionA、SessionB仍然还未提交，此时手动开启SessionC，执行DDL申请的是MDL写锁，因为读写互斥，所以就进入到阻塞。

4. 接着手动开启SessionD，好奇的是它申请的是读锁，为啥会被阻塞呢？因为申请MDL的锁会有一个队列，队列中写锁优先级高于读锁，一旦出现写锁等待，不仅会阻塞当前操作，还会阻塞后续该表的所有操作。

5. 接着提交SessionA、SessionB后，发现SessionD可以正常操作了，而SessionC仍然被阻塞，这不是出现了插队现象吗，等到SessionD提交后，SessionC才可以正常操作。

其实我们可以想一下，如果有一个线程申请了写锁，后续的所有读写都将被阻塞，如果查询语句在频繁点，那性能就会大打折扣，于是MySQL提出了Online DDL概念，如下表示：

1. 获取MDL写锁

2. 降级成MDL读锁（降级后申请读锁的会话就可以正常操作了，就好比上面的SessionD）

3. 真正做DDL

4. 升级成MDL写锁（由于SessionD还未提交事务，也就是读锁还没有释放，所以在获取写锁的过程就被阻塞了，等到SessionD提交后就可以正常操作了）

5. 释放MDL写锁（隐式提交）

所以这就很好解释了上面的现象了。注意了，在没有开启事务的情况下MDL会自动释放，还有一点就是如果先申请的是MDL写锁，在DDL语句之后就会隐式提交事务，所以看到的现象是其他会话的DML没有被阻塞住。