开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL 锁篇 -> 正文阅读

[大数据]MySQL 锁篇

MySQL 有哪些锁

全局锁

定义：对整个数据库进行加锁，常用于数据库的备份

如果要使用全局锁，执行下面这条SQL

-- 关闭所有的表加上读锁
 flush tables with read lock

执行后，数据库的状态就处于只读的状态
此时就可以对数据库进行备份

如果要释放全局锁，执行下面这条SQL

 unlock tables

使用场景

mysql 数据备份的时候，只读状态防止数据被修改，导致数据不同步的情况

缺点

很明显，当数据库处于只读状态的情况下，对于业务来说，只能够进行读数据，不能更新新增数据，导致业务停滞。
在事务支持可重复读的隔离级别，备份数据库可以事先开启事务，创建事务快照 Read View，这样就可以异步在后台备份数据
MySQL 常用的备份工具是 Mysqldump，加上 -single-transaction就可以在备份前开启事务

表级锁

表级锁的种类：

表锁
元数据锁
意向锁
AUTO-INC 锁

表锁

首先了解如何去加一个表锁

-- 读锁
lock tables student read;
-- 写锁
lock tables student write;

解锁

-- 释放当前会话的所有锁
unlock tables

表级锁相对于全局锁来说粒度减小了，但事实上粒度还是过大，导致并发的阻塞时间可能会比较长，所以不推荐使用

元数据锁

metadata lock，简称 MDL，当对数据库表进行操作的时候，会自动给相应操作的表加上 MDL 锁：

对一张表进行CRUD，加 MDL 读锁
对一张表的结构进行变更，加 MDL 写锁

就相当于自动加上相应表读写锁，在执行语句的时候

但这个读锁和数据的写锁并不冲突

	-- 这句话之前就隐式的加上了 MDL 锁
	insert into student(id, name) value(1, "hh");

读写锁是互斥的，申请锁的时候形成一个等待队列，等待获取锁

MDL 何时释放锁？

在提交事务后，会主动释放锁

意向锁

存在于 InnoDB 引擎，为了获取锁的目的而服务

在对某些记录加上 共享锁 之前，需要在表级别加上一个 意向共享锁
在对某些记录加上独占锁之前，需要在表级别加上一个意向独占锁

当插入、修改、删除操作的时候，需要先对表加上意向锁独占，然后再加独占锁

普通的 select 是没有带锁的，利用 MVCC 实现一致性读，是无锁的
不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

-- 共享锁
select * from student lock in share mode;
-- 独占锁
select * from student for update;

意向锁的目的是什么？

意向锁不会与行级锁发生冲突！意向锁不会与行级锁发生冲突！意向锁不会与行级锁发生冲突！

意向锁是表锁，意向锁之间是互相兼容的
但是意向锁和其他表锁（独占锁和共享锁）之间是存在读读共存，读写互斥，写写互斥的

下面说明一个例子：（例子中加的都是表级锁）

A 事务对某数据加了独占锁（行级锁），那么该表就有一个意向独占锁
B 事务过来加一个共享锁（表级锁）的时候，通过意向独占锁就可以得知该表已经有数据加了独占锁，所以不能加锁
- 这里的优势体现在是某一行加了锁，如果没有意向锁的情况下，需要遍历整个表检查是否有锁，但是如果有意向锁的情况下，就可以直接通过意向锁进行判断

所以 意向锁 解决的是 行锁和表锁之间发生冲突的一个问题，目的是为了减少加锁的判断成本，有了意向锁之后，就不需要去每行遍历数据判断是否有行级锁，大大减少了时间成本

AUTO-INC锁

这个锁是主键自增的时候所使用的锁，一般来说，如果开启了主键自增，新增语句都不需要填写 ID

-- student表(id, name, age)
insert into student(name, age) values('hello', 10);

为了防止并发插入的时候，主键自增被打乱，所以需要在insert的时候，加一个相应的AUTO-INC锁，在语句执行完之后，释放锁。
但是这样很明显比较消耗性能，因为批量导入的时候，需要频繁的等待锁的释放

所以在 MySQL 5.1.22 版本开始，InnoDB 提供了轻量级的锁来实现自增

具体的表现为，在新增时加锁，赋值后，释放锁，不需要等待语句执行完成

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用AUTO-INC锁，还是轻量级锁

参数为 0：AUTO-INC 锁
参数为 1：默认值，两种锁混着用，如果能够确定插入记录的数量就采用轻量级锁，不确定时就使用 AUTO-INC 锁
参数为 2：轻量级锁

轻量级锁是性能最高的，但是当并发插入的时候，会导致自增的值不连续的情况发生，这样当主从同步的时候，binlog 中记录的是 sql 语句，在从库执行的 SQL 自增的值可能就会发生不同的情况，这样会导致主从数据不一致

表锁的使用时机

当数据列不加索引的时候，也就是查询条件where后不带有索引列，就会触发表锁

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 并不支持

InnoDB 拥有的 MVCC，使得select操作不需要加锁，就可以读取，称之为 快照读
但 select 也是可以加锁读取的，默认不使用，称之为锁定读

-- 读锁
select * form student lock in share mode;
-- 写锁
select * from student for update;

当开启自动提交事务的时候，一条语句会自动开启关闭事务，那么锁定读就会自动释放锁
当没有开启自动提交事务时，需要显性的开启锁，在语句前后加上 begin 和 commit

行级锁主要有以下三类

Record Lock：记录锁，仅仅将某条记录锁上
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

Record Lock

Record Lock 被称为记录锁，锁住的是一条记录，有 X 锁（读锁）和 S 锁（写锁）的区别：
遵循规则 读读共享，读写互斥，写写互斥
执行SQL语句

bgein;
select * from student where id = 1 for update;

触发加锁
在这里插入图片描述

Gap Lock

Gap Lock 被称为间隙锁，只存在于可重复读隔离级别，作用是阻挡插入，目的是为了解决可重复读隔离级别下的幻读现象

要注意，间隙锁是空的间隙，不包含记录，也就是说，只有解决插入并发的问题
也就是说，间隙锁实际上和更新无关
- 会出现一种情况， id >= 1 and id <= 8的情况，依照下面的图加锁的情况解析
```
	记录锁：1，2，5，6
	间隙锁：(2, 5) 和 (7, 10)
```

假设事务A执行插入SQL

begin;
select id, name from student where id > 2 and id < 5;
insert into student(id, name) values(3, 'hwllo');
-- 当没有间隙锁的时候，这句话会出现幻读的情况
select id, name from student where id > 2 and id < 5;

这时候就会对(2,5)的间隙中加锁，如下图所示
在这里插入图片描述
当另一个事务B也执行插入语句

insert into student(id, name) value (4, 'hy');

就会由于间隙锁的存在，不能够成功插入，需要循环获取间隙锁

间隙锁阻挡插入

当事务 A 在插入的时候，事务 B 检测到了间隙锁，就会拒绝插入，避免幻读的情况发生

Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围 + 记录本身

该锁遵循前开后闭原则 (2, 5]

一般来说，执行唯一索引范围查询时会加上这样的锁（非快照读）

-- 锁定读
select * from student where id > 2 and id <= 5 for update;

在这里插入图片描述

插入意向锁

插入意向锁是一种特殊的间隙锁，，属于行级别锁，其目的是为了增加并发程度，减少等待的时间

插入意向锁之间并不冲突，即多条记录插入同一个间隙的时候，只要不是重复的记录，就不会发生阻塞等待的情况

用上面说到的间隙锁举例
在这里插入图片描述
当事务 A 已经对[2, 5]加上了间隙锁后，准备插入记录 id = 3，此时事务 B 插入记录 id = 4，判断到插入的位置，已经留有了间隙锁的情况，所以事务 B 会生成一个插入意向锁，那么事务 B 不需要等待事务 A 释放间隙锁，减少了阻塞等待的情况（因为实际上他们是不冲突的）

insert 语句在插入前，会在 gap 加上插入意向锁，如果是主键索引，则会进行 Duplicate Key 判断，如果存在相同 Key 且该 Key 被加了互斥锁，则还会加共享锁，然后等待（因为这个相同的 Key 之后有可能会回滚删除，这里非常容易死锁）。等到成功插入后，会在这条记录上加记录锁

记录并发插入会出现幻读么？

幻读的出现问题还是依赖于 快照读 解决了，不是当前事务id能够读取的，就不会被允许读取

行锁的使用时机

默认的情况下，InnoDB 优先时去加 Next-Key Lock，但根据优化规则，InnoDB 会对锁进行降级处理，比如降级为 Record Lock，或者是间隙锁

只使用唯一索引查询，进行等值查询
- 记录存在，Next-key lock 退化为 Record Lock
- 记录不存在，Next-key lock 退化为 Gap Lock
只是用唯一索引查询，但是检索条件是范围检索，或是不存在数据的时候，会产生 Next-Key Lock
- 情况和等值相同，处理等值是否存在的情况，转换为相应间隙锁
使用普通索引时，不论是何种查询，只要加锁，都会产生 Gap Lock +Next-key Lock
- 比如数据库中只存在 uid = 5 和 uid = 10 记录，查询 uid = 5 lock in share mode
- 产生Next-key Lock (1,5]，向前搜索直到结束
  - 这个要注意查询的参数，如果是 覆盖索引，那么就不会去锁唯一索引
  - 参数的意思就是如果只查索引本身(uid)，就不需要回主键树，也就不会锁唯一索引
- 产生 Gap Lock (6,10)，向后搜索直到搜索到10放弃，由于条件中只有等值5，所以不包括10
同时使用两种索引时，优先根据普通索引，再根据唯一索引，所以也会产生间隙锁
当没有索引，就会触发表锁