【MYSQL】InnoDB的锁机制详解

llzcxdb

已于 2023-12-06 14:34:43 修改

阅读量1.1k

点赞数 14

分类专栏： MYSQL 文章标签： mysql

于 2023-12-05 14:15:27 首次发布

本文链接：https://blog.csdn.net/m0_63011748/article/details/134762706

版权

MYSQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

预备知识

数据库并发安全场景中有读写、写写和读读，我们可以发现读读完全可以并行，无需阻塞，而普通锁机制是所有情况下都串行执行，并发量不高。读写锁就是为了解决这一问题的。

读写锁，MYSQL的共享锁是读锁，排他锁就是写锁。简单来说这种锁就是在没有写入线程和情况下可以让多个读线程并行执行从而提高并行度。也就是读写互斥，写写互斥，读读并行。

锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁

FTWRL

SQL如下：

flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

对数据的增删改操作，比如 insert、delete、update等语句；
对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：

unlock tables

有什么用呢？备份的时候就可以用到了，让其他写线程被阻塞。

表级锁

表锁

MYSQL支持表锁，这同时也是一个读写锁（支持共享锁和排他锁）

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

解锁

unlock tables

元数据锁

元数据锁（MDL）是为了在数据读写的时候表结构不会被修改，它是一种读写锁

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL是自动加的，这意味着事务执行期间，MDL 是一直持有的。

意向锁

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作某条记录时，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

扩展一下，普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。除了以下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

先说明：意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

清楚了基本的概念，那意向锁的目的是什么？

我们先看一种情况：

时间线	A事务	B事务
1	SELECT * FROM users WHERE id = 6 FOR UPDATE;
2		LOCK TABLES users READ;

首先A事务对id=6（假设id为主键）这一行数据加上了行锁，此时B 想要获取 users 表的表锁（读锁）：

因为读锁与写锁互斥，所以事务 B 在视图对 users 表加共享锁的时候，必须保证：

当前没有其他事务持有 users 表的排他锁。
当前没有其他事务持有 users 表中任意一行的排他锁。

第一条很容易满足，但是第二条就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。

那原理是什么呢？进一步往下看。

需要先知道：X（写锁）（S读锁）（IX意向写锁）（IS意向读锁）

这里再次强调，这里和S和X都是表锁，意向锁不会与行级的共享 / 排他锁互斥。

MYSQL官方文档中给出了各个锁之间的冲突和相容的关系。

MySQL ：： MySQL 8.2 参考手册：： 15.7.1 InnoDB 锁定

很容易看到意向锁之间（IX和IS之间）全部是相容的，都是相容怎么阻塞互斥呢？

意向锁和表的S、X锁是互斥的！也就是说阻塞是依靠下面的互斥关系实现的：

	IS	IX
S	兼容	互斥
X	互斥	互斥

现在假设有一张users表，id为PK，我们分情况讨论：

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

这里的读行记录我们涉及到当前读的2种情况（都是行锁）

S：SELECT * FROM users WHERE id = 6 SHARE MODE;

X：SELECT * FROM users WHERE id = 6 FOR UPDATE;

S和IX互斥

A事务:先获得users这张表的意向排他锁IX，然后获取id=6这一行记录的排他锁。

SELECT * FROM users WHERE id = 6 FOR UPDATE;

B事务：想获得users这张表的共享锁S，此时需要先获得users的IS（前面说过意向锁之间不会排斥所以可以直接获得），然后继续想要获得这张表的S锁，但是事务A已经获得了该表的IX，查表知S和IX互斥，这说明了当前有其他事务持有 users 表中任意一行的排他锁（不相容的，也就是读写场景），然后B事务就阻塞了。

LOCK TABLES users READ;

S和IS兼容

A事务:先获得users这张表的意向共享锁IS，然后获取id=6这一行记录的共享锁。

SELECT * FROM users WHERE id = 6 SHARE MODE;

B事务：想获得users这张表的共享锁，此时需要先获得users的IS（前面说过意向锁之间不会排斥所以可以直接获得），然后继续想要获得这张表的S锁，此时事务A已经获得了该表的IS，查表知S和IS相容，这说明其他事务持有行的共享锁并不会影响表的共享锁的持有（是相容的，也就是读读场景），所以B事务和A事务可以并行执行并不会被阻塞。

LOCK TABLES users READ;

X和IX互斥

A事务:先获得users这张表的意向排他锁IX，然后获取id=6这一行记录的排他锁。

SELECT * FROM users WHERE id = 6 FOR UPDATE;

B事务：想获得users这张表的排他锁X，此时需要先获得users的IX（前面说过意向锁之间不会排斥所以可以直接获得），然后继续想要获得这张表的X锁，但是事务A已经获得了该表的IX，查表知X和IX互斥，这说明了当前有其他事务持有 users 表中任意一行的排他锁（不相容的，也就是写写场景），然后B事务就阻塞了。

LOCK TABLES users WRITE;

X和IS互斥

A事务:先获得users这张表的意向排他锁IX，然后获取id=6这一行记录的排他锁。

SELECT * FROM users WHERE id = 6 FOR UPDATE;

B事务：想获得users这张表的排他锁X，此时需要先获得users的IX（前面说过意向锁之间不会排斥所以可以直接获得），然后继续想要获得这张表的X锁，但是事务A已经获得了该表的IS，查表知X和IS互斥，这说明了当前有其他事务持有 users 表中任意一行的排他锁（不相容的，也就是读写场景），然后B事务就阻塞了。

到这里基本上就讲清楚了。

AUTO-INC锁

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

一个参数innodb_autoinc_lock_mode 默认1

该参数始于5.1.22
为0时，语句级，表示采用之前MySQL 5.0版本的策略，即语句执行结束后才释放锁；
为1时： 自适应，普通insert语句，自增锁在申请之后就马上释放；类似insert …select这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
为2时，轻量级，所有的申请自增主键的动作都是申请后就释放锁。

行级锁

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

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为当前读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满

行级锁的类型主要有三类：

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）

Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。这里在我另外一篇详解MVCC博客中有详细介绍这里就不展开了。

特殊说明：由于间隙锁是为了解决幻读问题，所以在读已提交（RC）事务隔离级别是显示禁用间隙锁的。

Next Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身，会锁住一段左开右闭的区间。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

深入RR隔离级别下的行锁规则

在读未提交和读已提交的隔离级别下是没有间隙锁的，这里主要讨论可重复读隔离级别。

快照读由于读的是历史版本，无需行锁。这里讨论的是当前读select ..for update

假如有以下数据（pk为主键 idx为普通索引 uk为唯一键）

mysql> select * from tb;
+----+----------+-----+--------+--------+
| pk | name     | idx |     uk | other  |
+----+--------- +-----+--------+--------+
| 10 | AAAA     |  10 |     10 | NULL   |
| 15 | BBBB     |  10 |     15 | NULL   |
| 20 | CCCC     |  20 |     20 | NULL   |
| 30 | DDDD     |  30 |     30 | NULL   |
| 40 | EEEE     |  40 |     40 | NULL   |
+----+----------+-----+--------+--------+

先解释以下概念（结合上表数据）：

行所在的间隙：pk = 11,那么它所在的间隙就是(10,15)，注意是开区间，pk = 50所在的间隙是（40，正无穷）

唯一索引的加锁规则

注：主键也是唯一索引

等值查询

若查询记录存在则将next-key锁退化成记录锁，只锁住查询的记录。
若查询记录不存在，则next-key锁退化为间隙锁，锁住记录所在空隙，此处空隙是指与所查询记录（如果存在的话）相邻的两条记录之间的空隙，这两条记录除了可以是数据库表中实际存在的，也可以是其他事务执行了插入sql插入成功但是未提交或回滚事务。第二种情况中，若其他事务拟插入的数据进行了提交，则间隙锁固定，若选择回滚，则间隙扩大至下一条记录之前。

范围查询

注：以下的范围内指截至是最后一条符合条件的记录后的间隙

>和<：若起始索引记录不存在，则锁住起始索引所在间隙及后续记录和间隙；若存在，则从起始索引记录（不包含）开始，锁住范围内的记录和间隙。
>=：若起始索引不存在，则锁住起始索引所在间隙以及后续记录和间隙，若存在，则从起始索引记录（包含）开始，锁住范围内所有的记录和间隙
<=：若起始索引不存在，则锁住起始索引所在间隙与后续的记录和间隙，若存在，则从起始索引记录（包含）开始，锁住范围内记录和间隙，并锁住起始索引右端的间隙。

举例：select * from tb where 15 <= uk <= 20 for update锁的是 [15,正无穷)

普通索引的加锁规则

等值查询

若查询记录存在，除了加上next-key锁（即锁住本记录和上一条记录之间的间隙）以外，还会对本记录到下一条记录之间的间隙加锁。

范围查询

与唯一索引的区别在于：

>=会将另一侧的间隙也一起锁住；
<会将锁住第一条不满足条件的记录

如事务A执行：select * from tb where idx >= 20 and idx < 30 for update;
其中idx为普通索引，按照上表，(10,20)之间会加上间隙锁，且40会被锁住，也即[20,40]锁住。

锁优化建议

尽可能让数据检索通过索引完成，避免 InnoDB 因为无法通过索引加行锁，而导致升级为表锁的情况。换句话说就是，多用行锁，少用表锁。
加索引的时候尽量准确，避免造成不必要的锁定影响其他查询。
尽量减少给予范围的数据检索(间隙锁)，避免因为间隙锁带来的影响，锁定了不该锁定的记录。
尽量控制事务的大小，减少锁定的资源量和锁定时间。
尽量使用较低级别的事务隔离，减少 MySQL 因为事务隔离带来的成本。

llzcxdb

关注

14
点赞
踩
23

收藏

觉得还不错? 一键收藏
0
评论
【MYSQL】InnoDB的锁机制详解

B事务：想获得users这张表的共享锁S，此时需要先获得users的IS（前面说过意向锁之间不会排斥所以可以直接获得），然后继续想要获得这张表的S锁，但是事务A已经获得了该表的IX，查表知S和IX互斥，B事务：想获得users这张表的共享锁，此时需要先获得users的IS（前面说过意向锁之间不会排斥所以可以直接获得），然后继续想要获得这张表的S锁，此时事务A已经获得了该表的IS，查表知S和IS相容，其中idx为普通索引，按照上表，(10,20)之间会加上间隙锁，且40会被锁住，也即[20,40]锁住。
复制链接

扫一扫

专栏目录