全局锁和表锁

  • 全局锁--对整个数据库实例加锁
  • 表级锁 -- 表锁、元数据锁



数据库锁设计的初衷：处理并发问题。 作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁–对整个数据库实例加锁




  • 假设你现在要维护“极客时间”的购买系统，关注的是用户账户余额表和用户课程表。现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。
    如果没有加全局锁，备份期间，主库在扣用户的余额，这时，如果使用备份结果进行还原，用户会发觉课程表里多了一门课，而自己余额并没有扣。
  • 不加锁的话，备份系统备份的得到的库的视图逻辑是不一致的
  • 官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图
  • 可重复读隔离级别下开启一个事务可以保证一致性视图，为什么还需要 FTWRL 呢？
  1. 一致性读是好，但前提是引擎要支持这个隔离级别。
  2. MyISAM 不支持事务的引擎
  3. single-transaction 只适用于所有表使用事务引擎的库，这也是要求开发人员使用InnoDB替代MyISAM的原因之一
  • 既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
  1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。
  2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁 – 表锁、元数据锁


  • 语法：lock tables … read/write,可以使用 unlock tables 主动释放锁，或者客户端断开自动释放
  • lock tables 除了现状别的线程的读写外，也限定了本线程接下来的操作对象
    例如： 线程A 中执行 lock tables t1 read,t2 write;其他线程写t1、读写t2都会被阻塞；线程A执行unlock tabls前，只能执行读t1，读写t2，连写t1都不允许，也不能访问其他表。

  • MySQL 5.5 引入MDL,当对一个表做增删改查操作（DML）时，加MDL读锁；当对表做结构变动（DDL）时，加MDL写锁。
  • 给一个小表加个字段，导致整个库挂了?

Session A

Session B

Session C

Session D

begin;

----

—

—

select * from t limit 1;(加MDL读锁)

-----

—

—

----

select * from t limit 1; (加MDL读锁)

—

—

----

----

alter table t add f int;(blocked）

—

----

----

----

select * from t limit 1;(blocked）

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

  • 如何安全地给小表加字段？
  1. 首先解决长事务，事务不提交，MDL锁不释放。在information_schema库中innodb_trx表中查询当前执行中的事务；有的话暂停DDL，或者KILL这个长事务
  2. 若要变更的表是热点表，数据量不大，但请求频繁，不得不加字段； 在alter table 里设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
  • 备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个
    DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

//WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`; //自动加上MDL锁
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp; //释放 t1 的 MDL 锁
/* 时刻 4 */
/* other tables */
  1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
  2. 如果在“时刻 2”到达，则表结构被改过，Q5
    执行的时候，报 Table definition has changed, please retry
    transaction，现象：mysqldump 终止；
  3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的
    MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
  4. 从“时刻 4”开始，mysqldump 释放了 MDL
    读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。