数据库锁的设计初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。
全局锁
顾名思义，全局锁就是对整个数据库实例加锁。
MySQL提供了一个加全局锁的方法，命令是 flush tables with read loack(FTWRL)。当你需要让这个库处于只读状态时，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（DML，数据的增删改）、数据定义语句（DDL，包括建表、修改表结构等）和更新类事务的提交语句。会出现以下错误：
1223 - Can't execute the query because you have a conflicting read lock, Time: 0.000000s
全局锁的典型使用场景是，做全库逻辑备份。
通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。在备份过程中，整个库完全处于只读状态。这种做法存在以下问题：
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上得停摆
- 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟
看起来加全局锁不太好。但是如果不加全局锁，备份表是按照执行顺序进行备份，可能会存在一个事务被拆断备份的可能。比如A、B两张表，A先备份，此时一个事务修改了B和A，变成B‘和A’，再备份了B‘，我们想备份A、B，结果备份了A、B’，事务被拆断了。
在这种情况下就需要一致性视图。
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 -single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
说到这里，你可能存在疑惑，有了一致性视图，为什么还要有 FTWRL？
因为不是所有的引擎都支持一致性视图。比如MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能读取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。
还有一种可以实现数据库全局加锁 set global readonly=true ，与FTWRL的区别：
- 有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global 变量的方式影响面更大，不建议使用。
- 在异常处理机制上有差异。如果执行FTWRL 命令之后，由于客户端发生异常断开，那么MySQL 会自动释放这个全局锁，这个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户短发生异常，不会改变整个库为 readonly 的状态。
业务的更新不只是增删改数据（DML），还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方式，一个库被全局锁定之后，你要对里面任何一个表做加字段操作，都是会被锁住的。
对于表的控制，还有更细粒度的锁：表级锁。
表级锁
MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）
1、表锁
表锁的语法是 lock tables ... read / write. 与FTRWL类似，可以用 unlock tables主动释放锁，也可以在客户端断开的时候自动释放。
需要注意，lock tables 除了会限制其他线程的读写外，也限定了本线程接下来的操作对象，如：
线程A 执行 lock tables t1 read, t2 write; 其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行 unlock tables前，也只能执行读t1、读写t2的操作，不能访问其他表。
在还没有出现更细粒度锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一半不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
2、元数据锁（MDL）
MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。
元数据锁是server层的锁，主要用于隔离DML和DDL操作之间的干扰。每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁。MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥。
因此，在MySQL 5.5 版本中引入了MDL
- 当对一个表做增删改查操作的时候，加MDL读锁。读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
- 当对表做结构变更操作的时候，加MDL写锁。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
虽然MDL 锁是系统默认会加的，但是不能忽视。
你肯定知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出现问题。
- 如果这张小表是个热点表，在给表加上MDL写锁之后，会阻塞住其他线程读写操作，如果客户端有重试机制，也即是说超时后会再起一个新 session 请求，这个库的线程很快就会爆满。
- 如果给表加上MDL写锁之前，有个长事务一直在执行，此时这个MDL写锁会阻塞住后面对这张表的所有读写线程。
那么如何安全地给小表加字段？
- 要解决长事务，事务不提交，就会一直占着MDL锁
- 对于热点表，可以在 alter table 语句里面设置等待时间。如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃，之后再次尝试。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
网友评论