4.mysql - 锁

一：概念

1.全局锁

只读
flush tables with read lock;
unlock tables;

2.表锁

加在表上的锁， 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

加表锁:
lock tables tb_name read/write;
unlock tables;

3.行锁

加在行上， 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

4.共享锁（s）：

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

5.排他锁（Ｘ）：

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改

6.读锁：

共享锁
当MySQL的一个进程为某一表开启读锁之后，其他的进程包含自身都没有权利去修改这表表的内容。但是所有的进程还是可以读出表里面的内容的。但是不能实现更新。

7.写锁：

独占锁
当某一个进程在对某一张表实施写锁后，在该进程如果完成了更新（写、insert、update、delete）之后，如果不释放写锁，其他的进程连查看这张表的权限都没有，只有等它释放写锁值，其他的进程才可以完成相应的操作。如果该进程没有对该表进行更新操作，其他的进程只能做查询操作，但是无法实现更新操作。

8.MDL 锁

MDL 不需要显示使用，在访问一个表的时候就会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对应不上，肯定是不行的。
因此在 MySQL 5.5 之后加入了MDL，党对一个表做增删改查操作的时候，加 MDL 读锁，当对表做结构变更操作的时候，加了 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查
读写之间、写写之间是互斥的，用来保证变更结构操作的安全性。因此如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
虽然 MDL 锁是系统默认加的，但是却不能忽略一个机制，比如下面一个例子：可能会给一个小表加字段，导致整个库都挂了。
你肯定知道，给一个表加字段、或者修改字段、或者加索引，需要扫描全表的数据。对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出现问题。
比如sessionA、sessionB、sessionC、sessionD 依次执行，一个 sessionA 先启动，这个时候会对表 t 加一个 MDL 读锁，由于 sessionB 需要的也是 MDL 读锁，读锁之间不互斥，因此可以正常执行。之后 sessionC 会被阻塞，因为事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而是等到事务提交之后再释放。所以 sessionA 读锁还没有释放而 sessionC需要一个写锁，因此只能被阻塞。
如果说 sessionC 自己阻塞了没有关系，但是之后所有要在表 t 上心申请 MDL 读锁的请求也会被 sessionC 阻塞，就等于这个表完全不可读写了。如果某个表上的查询语句频繁，而且客户端有重试机制，超时之后再重启一个 session，这个库的线程很快就会爆满。
基于上面的分析，我们来讨论一个问题，如何安全地给小表加字段 ？
首先要解决长事务，事务不提交，就会一直占着 MDL 锁， 在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查看当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
但考虑一下这个场景，如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，你不得不加字段，你该怎么做呢？这个时候 kill 可能也未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里设定等待时间，如果在这个指定时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，之后开发人员或者 DBA 再通过重试命令重复这个过程。