06全局锁和表锁：给表加个字段怎么这么难

Posted 2021-04-23 19:00 +0800 by ZhangJie ‐ 1 min read

根据加锁的范围，mysql中的锁可以分为：全局锁、表锁、行锁 3类。

全局锁，是对整个数据库实例进行加锁，如通过命令Flush tables with read lock (FTWRL)加全局读锁，锁定后，数据更新（增删改）、数据定义（建表、修改表等）都会被阻塞。

其作用，主要是做全库逻辑备份，也就是把全表select出来存成文本。

加全局读锁之后，再开始备份，但是有风险：

如果是对主库备份，开了全局读锁之后，库不能写入，意味着业务基本不可用；
如果是对从库备份，开了全局读锁之后，从库新同步过来的binlog假如有表结构修改的操作，会导致因为拿不到MDL（metadata lock）而阻塞，无法修改表结构这一个阻塞还好，更严重的是会导致后续所有的拿MDL读锁的操作失败，包括正常的更新数据。因此这种方法容易造成主从同步延迟；

**备份数据，为什么要加锁，能不能不加锁？**不能！数据一致性，这个很好理解，不解释！

有没有不加全局锁的方法，有，但是要看引擎是否支持事务：

MyISAM引擎，不支持事务，只能用加全局读锁的方式锁定之后再开始备份
InnoDB引擎，支持事务，主库备份的时候通过–single-transaction，开启独立的事务进行备份：
- 因为备份时候设定的事务隔离级别是RR（可重复读），一致性问题不用担心了；
- 备份过程中也会拿MDL lock读锁，如果备份过程中有表结构更新操作，也可能会因为拿不到MDL写锁而阻塞，也会阻塞后续的所有数据更新动作；
- 针对上面问题，AliSQL提了个PR已经合入MariaDB，即尝试修改表的时候加个超时时间，如果过了超时时间还没有拿到MDL锁，则失败，等后续重试，这样至少不会阻塞正常的数据更新操作；
这里涉及了表锁中的一种：MDL锁

加全局读锁，可以将数据库设置为只读，还有一种办法，设置全局变量set global readonly=true，但是这种方式，风险更高：

通常这个属性还用来区分一个数据库是主库还是从从库，如果贸然修改这个变量，可能会造成一些其他应用的误判；
假如客户端申请了加全局锁且成功之后，如果客户端崩溃了，这个全局锁还是可以自动被释放掉的，库还是可以写入的。但是，如果客户端通过全局变量将库设置为了只读，那么客户端崩溃后，库也是只读的，不可写入的；

加表锁，主要有两种形式，lock tables… 和 MDL lock。

这种加锁方式，对应的解锁方式是 unlock tables
这种加锁方式，对其他线程能否读写、当前线程能否读写都做了明确的限制。假定当前线程p读表t1加读锁、对表t2加写锁，那么：
- 其他线程q是不能对t1执行写操作的，对t2也不能执行读操作，这个好理解；
- 当前线程p也是不能对t1执行写操作的，也不能对t2执行读操作；
可以理解成没有考虑锁的重入、读写排他性；

表的定义都记录在表的元信息里，要对表执行增删改查等DML操作，或者对表执行表结构修改等DDL操作时，都需要现获取表的MDL锁。增删改查就是MDL读锁，修改表结构就是拿MDL写锁。

MDL锁也是是有可能导致数据库操作阻塞的。在前面描述数据库备份操作时，我们介绍了InnoDB通过–single-transaction来进行备份的方法，其中描述了MDL读写锁排他性对备份过程、binlog主备同步延迟的影响。

也介绍了如何尽量规避这个问题：尝试修改表结构（MDL写锁）时加个超时时间，避免长时间阻塞进而导致后续的数据更新操作失败（或者，导致主备同步延迟过大）。

行锁，InnoDB执行引擎支持对加行锁，以前不支持行锁的时候，就要通过表锁来解决，先锁表，再修改、再解锁表，这种效率比较低。