MySQL基础篇5 mysql的全局锁和表锁

数据库锁设计的初衷是处理并发问题;

当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构;

根据加锁的范围. MySQL里面的锁大致可以分为三类: 全局锁, 表锁, 行锁

全局锁

全局锁就是对整个数据库实例加锁;

加全局读锁命令: Flush tables with read lock (FTWRL) 当你需要让整个库处于只读状态的时候.

使用上述命令后, 之后其他线程的以下语句会被阻塞

• 数据更新语句(增删改)
• 数据定义语句(建表, 修改表结构等)'
• 更新类事物提交语句

全局所的典型使用场景是, 做全库的逻辑备份. 也就是把整库每个表都 select 出来存成文本

以前有种做法, 是通过FTWRL确保不会有其他线程对数据库做更新, 然后对整个库做备份. 备份过程中整个库完全处于只读状态

会有以下问题:

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

从上述看加全局锁不太行, 所以说为啥要加锁呢? 不加锁会发生什么?

假设:

有一个用户, 他购买了一门, 业务逻辑就要扣掉他的余额, 然后往已购里面加上该课程.

如果时间顺序上是先备份账户余额表(u_account), 然后用户购买, 然后备份用户已购表(u_course), 会怎么样呢

业务和备份状态图

可能会导致:

• 用户 A 的数据状态是“账户余额没扣，但是用户课程表里面已经多了一门课
• 如果备份表的顺序反过来，先备份用户课程表再备份账户余额表, 会导致钱没了, 课也没了.

也就是说，不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的.

其实有一个方法可以拿到一致性的视图. ----- 在可重复度的隔离级别下开启事务

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

所以说已经有了可充复读为什么还要FTWRL? 是因为可重复读虽然挺好, 但是前提是引擎需要支持这个隔离级别.

例如myisam. 如果备份过程中有更新就会破坏备份的一致性.

所以. single-transcation 方法只适用于所有的表使用事务引擎的库. 这也是innodb代替myisam的原因之一.

一个问题: 既然只要全库可读, 为啥不使用set global readonly = true 的方式呢?

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

表级锁

即使没有被全局锁住, 加字段也不是能一番风顺的, 因为你还会碰到接下来我们要介绍的表级锁.

mysql表级锁有两种: 一种是表锁, 一种是元数据锁.

表锁

语法是: lock tables ... read/write

与FTWRD 类似, 可以用unlock table 主动的释放锁. 也可以在客户端断开的时候释放锁.

lock table 语法除了会限制别的线程读写外, 也限定了本线程接下来的操作对象.

eg:

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

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

MDL(metadata lock)

MDL 不需要显式使用, 在访问一个表会自动加上.

作用是: 保证读写的正确性.

引入场景: 如果一个查询正在遍历一个表中的数据, 而执行期间另一个线程对这个表结构做了变更, 删了一列, 那么查询线程拿到的结果跟表结构对不上, 肯定是不可以的.

so . 在mysql 5.5 中引入了MDL, 当对一个表做增伤爱差操作的时候, 加MDL 读锁; 当对一个表做结构变更擦欧总的时候, 加MDL写锁.

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

看一个栗子:

假设表t是一个小表.

可以看到seesionA 先启动, 这时候会对表t加一个MDL读锁, 由于seesionB需要的也是MDL读锁, 所以可以正常执行.

之后seesionC会被blocked, 是因为sessionA 的MDL 读锁还没有被释放. 而session C 需要的是MDL写锁. 因此只能被阻塞.

if 只有seesion C 被阻塞还没有啥关系, 但是之后所有的表要在t表上申请MDL 读锁的请求也会被session C 阻塞.

前面提到. 所有对表的增删改查都需要先申请MDL读锁, 就都被锁住, 等于这个表完全不可读写了.

if 某个表上的查询语句频繁, 而且客户端有重试机制, 也就是说超时后会再起一个新session 再请求的话, 这个库的线程很快就会爆满.

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放.

所以说 应该如何安全的给小表加字段.

首先我们要解决长事务, 事务不提交, 就会一直占着MDL锁,

在mysql 的information_schema 库的innodb_trx表中, 可以查到当前执行中的事务.

如果你要做DDL变更的表刚好要有长事务在执行, 要考虑先暂停DDL. 或者kill这个长事务

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

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

小结

全局锁主要是用在逻辑备份过程中, 对于全部是innodb引擎的库, 建议使用sinle-transaction参数. 会更友好.

表锁一般是在数据库引擎不支持行锁的时候才会被用到, 如果你发现你的应用程序里lock tbales 这样的语句, 你需要追查一下, 比较可能的情况是:

• 要么是你的系统现在还再用myisam这类不支持事物的引擎, 那要安排升级换引擎
• 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决

MDL 会直到事务提交才释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

全局锁和表锁都是server层实现的

MDL是防止DDL和DML并发的冲突

补充:

上述例子. sessionC在被sessionA阻塞后, sessionC的DDL操作处理等待, 为什么sessionD 会被 C阻塞?

--- 因为mysqlserver端对于sessionC 与 D有一个队列, 来决定谁先执行.

DML DDL DCL的区别:

https://www.cnblogs.com/dato/p/7049343.html

• DML(data manipulation language) 数据库操纵语言. 表增删改查. (SELECT UPDATE INSERT DELETE)
• DDL(data definition language) 数据库定于语言. 创建表的时候用的一些sql (CREATE ALTER DROP)
• DCL(data control language) 数据库控住语言. 用来设置或者更改数据库用户或者角色权限的语句(grant deny revoke)

当使用了全局锁命令FTWRL后如何关闭呢?

ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

与解决表锁一样. 使用 unlock tables