为什么会有数据库锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

锁的分类

根据加锁的范围,MySQL 里面的锁大致可以分成

  • 全局锁
  • 表级锁
  • 行锁。

全局锁

顾名思义,全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。全局锁的典型使用场景是,做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
以前有一种做法,是通过 FTWRL 确保不会有其他线程对数据库做更新,然后对整个库做备份。
注意,在备份过程中整个库完全处于只读状态。但是让整库都只读,听上去就很危险:

  • 如果你在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆;
  • 如果你在从库上备份,那么备份期间从库不能执行主库同步过来的 binlog,会导致主从延迟

备份为什么要加锁

不加锁的话,备份系统备份的得到的库不是一个逻辑时间点,这个视图是逻辑不一致的。

什么时候需要使用 FTWRL

结论

在使用 MyISAM 这样不支持事务的引擎的时候,需要备份,可以加全局锁,使用(FTWRL 语句),但在使用类似 InnoDB 这样支持事务的引擎的时候,则有更好的办法,来支持备份的需求。

用事务的方法来处理备份,从而不加全局锁

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。

为什么不 set global readonly=true?

  1. 一是,在有些系统中,readonly 的值会被用来做其他逻辑,比如用来判断一个库是主库还是备库。因此,修改 global 变量的方式影响面更大,不建议使用。
  2. 二是,在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于不可写状态,风险较高。

表锁

MySQL 里面表级别的锁有两种:一种是表锁,一种是**元数据锁(meta data lock,MDL)**。

表锁

表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。

元数据锁(MDL)

MDL 不需要显式使用,在访问一个表的时候会被自动加上。
MDL 的作用是,保证读写的正确性。你可以想象一下,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。
当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

  1. 读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
  2. 读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

  1. 我们可以看到 session A 先启动,这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁,因此可以正常执行。
  2. 之后 session C 会被 blocked,是因为 session A 的 MDL 读锁还没有释放,而 session C 需要 MDL 写锁,因此只能被阻塞。如果只有 session C 自己被阻塞还没什么关系,但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。
  3. 前面我们说了,所有对表的增删改查操作都需要先申请 MDL 读锁,就都被锁住,等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新 session 再请求的话,这个库的线程很快就会爆满。
事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

如何安全的表加字段

  1. 首先我们要解决长事务,事务不提交,就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中,你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行,要考虑先暂停 DDL,或者 kill 掉这个长事务。
  2. 但考虑一下这个场景。如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?这时候 kill 可能未必管用,因为新的请求马上就来了。比较理想的机制是,在 alter table 语句里面设定等待时间,如果在这个指定的等待时间里面能够拿到 MDL 写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
  3. MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

总结

FTWRL 前有读写的话 ,FTWRL 都会等待 读写执行完毕后才执行
FTWRL 执行的时候要刷脏页的数据到磁盘,因为要保持数据的一致性 ,理解的执行 FTWRL 时候是 所有事务 都提交完毕的时候

mysqldump + -single-transaction 也是保证事务的一致性,但他只针对 有支持事务 引擎,比如 innodb
所以 还是强烈建议大家在创建实例,表时候需要 innodb 引擎 为好
全库只读 readonly = true 还有个情况在 slave 上 如果用户有超级权限的话 readonly 是失效的

表级别 锁 :一个直接就是表锁 lock table 建议不要使用, 影响太大,另个就是 MDL 元数据锁

MDL 是并发情况下维护数据的一致性,在表上有事务的时候,不可以对元数据经行写入操作,并且这个是在 server 层面实现的
当你做 dml 时候增加的 MDL 读锁, update table set id=Y where id=X; 并且由于隔离级别的原因 读锁之间不冲突

当你 DDL 时候 增加对表的写锁, 同时操作两个 alter table 操作 这个要出现等待情况。

但是 如果是 dml 与 ddl 之间的交互 就更容易出现不可读写情况,这个情况容易 session 爆满,session 是占用内存的,也会导致内存升高
MDL 释放的情况就是 事务提交.

主库上的一个小表做了一个 DDL, 同步给 slave ,由于这个时候有了先前的 single-transaction,所以 slave 就会出现 该表的 锁等待, 并且 slave 出现延迟

MDL 锁和表锁是两个不同的结构。

比如:
你要在 myisam 表上更新一行,那么会加 MDL 读锁和表的写锁;
然后同时另外一个线程要更新这个表上另外一行,也要加 MDL 读锁和表写锁。

第二个线程的 MDL 读锁是能成功加上  的,但是被表写锁堵住了。从语句现象上看,就是第二个线程要等第一个线程执行完成。
hexo 推送测试