浅谈 MYSQL - 锁机制

MYSQL数据中台2025-5-29 10:00 阅读 26 评论 0 热度 1

背景

本文旨在帮助读者了解数据库锁的类型、特性以及数据库的加锁的原则，方便于各类复杂场景下的死锁问题的分析和解决。

锁类型

加锁颗粒度：

- 表锁


- 行锁

加锁机制：

- 悲观锁
- 乐观锁

锁兼容性

- 共享锁 S (lock in share model) lock in share mode 只锁覆盖索引
- 排他锁 X (select ... for update) for update 锁覆盖索引 + 主键索引

锁详细类型

记录锁
- 记录锁是最简单的行锁，锁住索引上的一行记录。对应的锁状态信息为 lock_mode X locks rec but not gap
间隙锁（Gap Lock）
- 为了解决幻读问题，InnoDB引入了间隙锁(Gap Lock)。为了解决幻读问题，InnoDB引入了间隙锁(Gap Lock)。间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据。它锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
临键锁 next-key lock
- 临键锁 = 行锁 + 间隙锁
- 临键锁加锁范围：
  - 非唯一索引的等值查询：
    值存在：加两把锁，命中的临键锁和等值后的间隙锁
    值不存在：只加命中的区间的临建锁
  - 非唯一索引的范围查询：只加临建锁
  - 唯一索引的等值查询：
    值存在：行锁
    值不存在：间隙锁
  - 唯一索引的范围查询：加临建锁，但是会退化成间隙锁/行锁
意向锁
- 什么是意向锁：
  - 意向锁是表级别锁
  - 意向共享锁：简称IS锁，当事务准备在某些记录上加S锁时，需要现在表级别加一个IS锁。
  - 意向排他锁：简称IX锁，当事务准备在某条记录上加上X锁时，需要现在表级别加一个IX锁。
- 为什么需要意向锁
  - 因为InnoDB是支持表锁和行锁共存的，如果一个事务A获取到某一行的排他锁，并未提交，这时候事务B请求获取同一个表的表共享锁。因为共享锁和排他锁是互斥的，因此事务B想对这个表加共享锁时，需要保证没有其他事务持有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDB的设计中提出了意向锁。
插入意向锁
metadata lock
主键锁

锁状态分析

加锁范围

非唯一索引的等值查询：
- 值存在：加两把锁，命中的临键锁和等值后的间隙锁
- 值不存在：只加命中的区间的临建锁
非唯一索引的范围查询：只加临建锁
唯一索引的等值查询：
- 值存在：行锁
- 值不存在：间隙锁
唯一索引的范围查询：加临建锁，但是会退化成间隙锁/行锁
开启锁状态输出 SET GLOBAL innodb_status_output=ON; -- 开启输出 SET GLOBAL innodb_status_output_locks=ON; -- 开启锁信息输出 SHOW ENGINE INNODB STATUS

RR 隔离级别加锁原则

原则1 ：加锁的基本单位都是next-key lock。next-key lock（临键锁）是前开后闭区间。
原则2 : 访问到的对象才会加锁
- lock in share mode 只锁覆盖索引
  - 例如：查询二级索引，使用到了索引覆盖，主键索引上不会加锁
- for update 锁覆盖索引 + 主键索引
优化1 ：
- 索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁（Record lock）
优化2 ：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁（Gap lock）。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。