MySQL 二轮学习笔记·进阶篇·(七) 锁

锁

并发访问数据库时，会出现脏读、不可重复读、幻读等问题（隔离级别相关），锁通过控制资源的访问权限，保证数据一致性。

锁的分类：

按锁定粒度：全局锁-表级锁-行级锁（粒度递减，并发能力递增）

按锁的功能：共享锁（S锁，读锁）、排他锁（X锁，写锁）

按锁的性质：乐观锁、悲观锁

1.全局锁

锁定所有表，禁止任何写操作（包括数据增删改、表结构修改）

加锁：flush tables with read block;

解锁：unlock tables; 或断开会话

注意与set global read_only = 1的区别，后者对超级用户无效，且无法阻止表结构的修改，前者ftwrl更严格。

核心应用：一致性数据备份

适用于非事务引擎（如MyISAM，无MVCC机制）的全库备份，锁定全库后，所有表处于只读状态，备份工具（如 mysqldump）可读取到某一时刻的 “静态数据快照”。对事务引擎（InnoDB）不推荐，InnoDB可以通过mysqldump --single-transaction利用MVCC实现热备份，无需锁全库，不影响业务写操作。

2.表级锁

分为表锁、元数据锁、意向锁

(1)表锁-Table Lock

最基础的表级锁，直接控制整张表的读写权限，InnoDB和MyISAM均支持（InnoDB更支持行级锁）。

分为两种类型：

表共享读锁（Table Read Lock, TRL）

加锁：lock tables 表名 read;

所有会话可读不可写

表排他写锁（Table Write Lock, TWL）

加锁：lock tables 表名 write;

当前会话可写可读，其他会话不可写不可读

（不可读指不可加读锁，实际可以读到快照数据）

(2)元数据锁-Metadata Lock, MDL

由数据库自动管理加解锁，用来保护表结构元数据，防止查询和改表并发冲突。

执行读操作时，自动加MDL读锁（多个读操作可以共享读锁，不会阻塞）

执行写操作时，等待所有的MDL读锁和写锁释放，添加MDL写锁

典型问题：长事务持有 MDL 读锁，导致 alter table 阻塞 —— 解决：优先 kill 长时间未提交的读事务，再执行表结构修改。

(3)意向锁

意向锁是行锁给表锁打的“招呼”，像一个预告牌，避免了表锁需要逐个检查行锁的低效行为。

意向共享锁（Intention Shared Lock，IS 锁）：加行级S锁前，自动加IS意向锁

意向排他锁（Intention Exclusive Lock，IX 锁）：加行级X锁前，自动加IX意向锁

意向锁测试：验证 “意向锁与表锁的互斥关系”

1. 会话 A 执行 select * from t where id=1 for update（为 t 表加 IX 锁，为 id=1 行加 X 锁）。
2. 会话 B 执行 lock tables t write（加表写锁）：会阻塞，因为 IX 锁与表写锁互斥。
3. 会话 A 提交事务（释放 IX 锁和行 X 锁）后，会话 B 的表写锁才能成功加锁。

3.行级锁

InnoDB特有的锁，锁定表中某一行或某几行数据，粒度最小，并发能力最强，是InnoDB支持高并发的核心基础（依赖聚簇索引实现）。

核心特点

1. 基于索引：仅当SQL语句使用索引条件过滤数据时，才会加行级锁，否则会全表扫描，退化为表级锁（IX锁+全表行锁，等同于表锁）
2. 与事务绑定：行级锁的释放时机为事务提交（commit）或回滚（rollback），而非SQL执行结束

分类

1. 行共享锁（Shared Lock，S锁）

   select ... lock in share mode;

   所有对话可读不可写

2. 行排他锁（Exclusive Lock，X锁）

   insert/update/delete自动加锁，或select ... for update;手动加锁

   当前对话可读可写，其他对话不可读不可写

   （不可读指不可加读锁，实际上可以读到数据，这是因为 InnoDB 默认的隔离级别（REPEATABLE-READ）下，普通查询基于多版本并发控制（MVCC），读取的是快照版本，不会被 X锁 阻塞）

(1)行锁-Record Lock

行级锁的基础类型，锁定具体的行记录（即索引对应的 “行数据”），仅锁定存在的行，不会锁定 “不存在的间隙”

应用场景：精准更新 / 查询某条存在的记录，例如 update t set name='a' where id=10;（id 是主键索引）。

案例：

1. 会话 A 执行 update t set name='a' where id=10;（未提交，为 id=10 行加 X 锁）。
2. 会话 B 执行 update t set name='b' where id=10;：阻塞，直到 A 提交事务释放 X 锁。
3. 会话 B 执行 update t set name='c' where id=11;（id=11 存在）：正常执行，仅锁定 id=11 行，与 id=10 行无关。

(2)间隙锁&临键锁-GapLock&Next-keyLock

为了解决幻读问题（InnoDB默认隔离级别可重复读，RR），防止其他事务在间隙中插入数据。

间隙锁（Gap Lock）

使用范围查询且查询条件命中 “间隙”（即范围内无实际行记录）时加锁，锁定 “两个索引记录之间的间隙”。

案例：表 t 的 id（主键）存在值：1、3、5。

会话 A 执行 select * from t where id between 2 and 4 for update;（范围查询，命中间隙 (1,3)、(3,5)）。

会话 A 会为间隙 (1,3)、(3,5) 加间隙锁。

会话 B 执行 insert into t(id) values(2); 或 insert into t(id) values(4);：均阻塞，因为插入的 id 在锁定的间隙中。

临键锁（Next-Key Lock）

“间隙锁 + 行锁” 的组合，锁定 “间隙 + 间隙右侧的行记录”，是 InnoDB 在 RR 隔离级别下默认的行级锁算法（除非使用唯一索引且查询条件为 “等值匹配”）

临键锁的锁定范围遵循 “左开右闭” 原则，例如表 t 的 id（主键）存在 1、3、5，临键锁范围为：(-∞,1]、(1,3]、(3,5]、(5,+∞)。

案例：

会话 A 执行 select * from t where id <=3 for update;（范围查询）。

会话 A 会加临键锁：(-∞,1]、(1,3]（锁定间隙 (-∞,1)、(1,3) + 行 1、3）。

会话 B 执行 insert into t(id) values(0);（间隙 (-∞,1)）或 insert into t(id) values(2);（间隙 (1,3)）：均阻塞；执行 update t set name='x' where id=3;：也阻塞（行 3 被临键锁包含）。

若查询条件为 “唯一索引（主键 / 唯一键）的等值匹配”，临键锁会退化为 “行锁”（无需锁定间隙），例如 select * from t where id=3 for update;（仅锁定 id=3 行，不锁定间隙）。