什么是 MySQL 全局锁、表锁、行锁，Java 高级开发岗必问知识点
现在我要备份，期间有人买票。逻辑上：余额表减掉相应金额，已购票表加上一张票。备份就会出现两个问题：
先备份余额表，用户购买，再备份用户表。这是会怎样呢？方便理解，我画张图：
从上图，我们也大概知道发生了啥。我来捋一捋：
T1 时刻是备份前两个表的数据状态；T2 时刻开始备份，只备份了余额表；T3 时刻，由于没有加锁，用户买票；T4 时刻是买完票后的状态；T5 时刻备份到已购票表。
看最终的备份状态你发现没有？？？用户钱没少，票却多了一张（用户窃喜，程序员苦逼）。
以上就是不加锁的下场，它会导致数据前后不一致。这还是先备份余额表后备份已购票表的情况出现的问题。
如果，备份的顺序颠倒一下就会出现：用户钱少了，票却没增加（你指定被投诉，程序员还是苦逼）。
通过上面分析知道，不加锁的话。备份得到的库不是同一个逻辑时间点，才会造成这种后果。那怎么保证是同一逻辑时间点呢？
这时候就要引入上篇文章提到的一致性视图。
2.3 一致性视图备份
上篇说到在可重读隔离级别下开启一个事务，会创建一致性视图。
PS：不了解事务，看这里肯定一脸懵。建议看这篇：[《MySQL 事务与 MVCC 原理》](
)
你可能会问：狗狗你说得，我都知道。问题是怎么在备份的时候开启事务呢？
是这样，MySQL 自带的逻辑备份工具是 mysqldump 。它使用参数 -single-transaction 可以启动一个事务，从而确保拿到一致性视图。并且由于 MVCC 的支持，备份期间数据库仍可以写入。比如像这样：
// 详细参数见：cnblogs.com/markLogZhu/p/11398028.html
// 格式：mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump -uroot -p test -single-transaction > /backup/mysqldump/test.db
复制代码
这时好学的朋友可能会说：既然有了这功能，那不用 FTWRL 命令行不行呀？
答案是：可以的，前提是你的数据库引擎要支持可重复读隔离级别，比如：InnDB；如果是 MyLSAM，那么很抱歉，你还是得用 FTWRL，不然备份拿到的视图还是不一致。就会出现上面数据不一致的问题。
2.4 readonly = 1 的方式行么？
提到全库只读你可能想到这个命令：
mysql> set global read_only=1;
复制代码
能使用它来让全库只读么？不行或者说是不建议，主要原因有三点：
影响业务逻辑；set global read_only=1 可能会用于一些业务判断，比如：主从的判断，从库只读。
异常不释放状态；FTRWL 命令在异常发生时，会自动释放全局锁；而 set global read_only=1 在异常时，数据库会一直保持只读状态，这时候业务就完犊子了。
set global read_only=1 这个命令对超级管理员角色无效；备份期间，超管更新数据库还是会导致数据不一致问题。
03 表级锁
======
MySQL 有两种表级锁：表锁以及元数据锁（meta data lock，MDL）
3.1 表锁
表锁的语法是这样的：lock tables ... read/write，它是显式使用的，同样也是通过 unlock tables 主动释放锁；当然，客户算断开或者异常时也会释放。
mysql> lock tables student read,course read;
mysql> SELECT count(1) FROM student;
mysql> SELECT count(1) FROM course;
mysql> unlock tables;
复制代码
需要注意一点：lock tables 除了会限制别的线程读写以外，也限定了本线程接下来操作的对象。举个栗子：
线程 A 执行 lock tables student read,course write; 语句，其他线程读 student、读写 course 都会被阻塞。同时，线程 A 在执行 unlock tables 之后，也只能读 student、读写 course；不能访问其他表。整个表格更直观：
| ? | student 表 | course 表 | 其他表 |
| --- | --- | --- | --- |
| 线程 A | 读 | 读写 | 不允许 |
| 其他线程 | 阻塞 | 阻塞 | 随便 |
PS：在没有更细粒度的年代，表锁是最常用与处理并发的方式。但是对于 InnDB 来说，一般不使用 lock tables 控制并发，因为粒度太大了。
3.2 MDL 元数据锁
MDL 不需要我们记命令，它是隐式使用的，访问表会自动加上。它的主要作用是防止 DDL（改表结构） 和 DML（CRUD 表数据） 并发的冲突。
举个栗子，线程 A 遍历查询表数据，这期间线程 B 删了表的某一列，这时 A 拿到的数据就跟表结构对不上，MySQL 不允许这种事发生，所以在 5.5 版本引入了 MDL。
它的逻辑很简单，对表进行 CRUD 操作，加 MDL 读锁；对表结构下手时，加 MDL 写锁。因此：
读读不互斥，可以多线程对一张表增删改查。
读写互斥、写写互斥，保证对表结构下手时只能有一个线程操作，另一个进入阻塞。
3.2.1 加个字段就搞挂数据库？
我们知道 MDL 默认是系统加的，对表结构下手时（加字段、该字段、加索引等等），需要全表扫描。对大表操作时，你肯定会选月黑凤高，系统使用人数最少时进行，以免遭投诉。
但不只是大表，有时候对小表进行操作时，也会有这样的问题。比如下面的例子：4 个 session 对表进行操作。
PS：版本是 MySQL 5.7
前提：注意，我这里的事务是手动开启和提交的。而 MDL 锁是语句开始时申请，事务提交才释放。所以，如果是自动提交就不会出现下面的问题。
T1、T2 时刻 session A 事务启动，加个 MDL 读锁，然后执行 select 语句。注意：这时事务并没有提交；
T3 时刻 session B 也是读操作，可以共享 MDL 读锁，顺利执行；
T4 时刻 session C 不讲武德，对表执行 DDL （改表结构）操作，需要的是 MDL 写锁，所以被阻塞；
T5 时刻 session D 也是读操作，按道理说 session C 阻塞应该没影响。
但是 MySQL 有一个队列会根据时间先后决定哪个 Session 先执行。所以，不管是 D 还是之后的 session 都会被 C 阻塞。而恰巧 student 又是访问频率很高的表，如此这个库的线程数很快就打满了。
此时，数据库完全不能读写，甚至导致宕机，在用户界面看来就是没响应了。
3.2.2 安全地更改表
相信你都看出来了，出现上面问题是因为使用了长事务（一个事务包括 session A、B、C、D 的操作）。事务一直不提交，MDL 锁就会一直被占用。
所以，遇到这种情况就要在 MySQL 的 information_schema 表中先找出长事务对应的线程，把它 kill 掉。
// MySQL 长事务请看这篇：cnblogs.com/mysqljs/p/11552646.html
// 查询事务
select * from information_schema.INNODB_TRX;
复制代码
那你可能又问了。我的表就是热点表访问很高频，但我又不得不加个字段。那应该咋办呢？回想下多线程业务操作时，线程一直拿不到锁，我们是怎么处理的？
没错，就是加超时时间。比如在 alter 语句里面加个等待时间，超过了这时间还拿不到锁。也不要阻塞后面的业务查询语句，先放弃更改。之后再交由你司 DBA 重复这个过程，直到更改成功。加等待时间语句，像下面这样的：
// N 以秒为单位
ALTER TABLE tbl_name WAIT N add column ...
复制代码
04 行锁
=====
mysql 的行索是在引擎实现的，但并不是所有引擎都支持行锁，不支持行锁的引擎只能使用表锁。
行锁比较容易理解：行锁就是针对数据表中行记录的锁。比如：事务 A 先更新一行，同时事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。
4.1 两阶段提交
先举个栗子：事务 A 和 B 对 student 中的记录进行操作。
其中事务 A 先启动，在这个事务中更新两条数据；事务 B 后启动，更新 id = 1 的数据。由于 A 更新的也是 id = 1 的数据，所以事务 B 的 update 语句从事务 A 开始就会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。
在事务期间，事务 A 实际上持有 id = 1 和 id = 2 这两行的行锁。如果事务 B 更新的是 id = 2 的数据，那么它阻塞的时间就是从 A 更新 id = 2 这行开始（事务 A 更新 id = 1 时，它并没有阻塞），到事务 A 提交结束，比更新 id = 1 数据阻塞的时间要短。PS：理解这句话很重要。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。锁的添加与释放分到两个阶段进行，之间不允许交叉加锁和释放锁。
根据这个特性，对于高并发的行记录的操作语句就可以尽可能的安排到最后面，以减少锁等待的时间，提高并发性能。
举个栗子：广州长隆乐园卖票系统。卖出一张票的逻辑应该分三步：
1、扣除用户账户余额
2、增加长隆账户收入
3、插入一条交易记录
三个操作必须是要放在同一个事务当中，那应该怎么安排它们的执行顺序呢？做个分析：
用户余额表是个人的，并发度很低；
长隆账户表每个用户买票都要访问，并发度最高；
交易记录表是插入操作问题不大；
这时将事务步骤安排成 3、1、2 这样的顺序是最佳的。因为此时如果有别的用户买票，它的事务在顺序 1、2 并不会阻塞，而是到了顺序 3 更新长隆账户表才会引起阻塞。但它的阻塞时间是最短的。
4.2 死
锁
不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
举个行锁死锁的例子：两个事物相互等待对方持有的锁。
操作开始，事务 A 持有 id = 1 的行锁，事务 B 持有 id = 2 的行锁；事务 A 想更新 id = 2 行数据，不料事务 B 已持有，事务 A 只能等待事务 B 释放 id = 2 的行锁；同理，事务 B 想更新 id = 1 行数据，不料事务 A 已持有，事务 B 只能等事务 A 释放 id = 1 的行锁。
还未添加个人签名 2021.03.18 加入
还未添加个人简介
评论