2025年6月8日

Mysql锁面试题

作者 codecafe

1. 全局锁

问题：什么是全局锁？它的应用场景是什么？

答案：
全局锁通过 FLUSH TABLES WITH READ LOCK (FTWRL) 锁定整个数据库，禁止所有写操作。主要用于全库逻辑备份。
原理：阻塞所有 DDL 和 DML 操作，确保备份数据一致性。
示例：

FLUSH TABLES WITH READ LOCK;  -- 加全局锁
-- 执行备份操作...
UNLOCK TABLES;               -- 释放锁

追问：为什么生产环境不推荐用 FTWRL 备份？

追问答案：
因为会导致服务完全不可写。推荐使用 mysqldump --single-transaction，利用 InnoDB 的 MVCC 实现非阻塞备份。
原理：通过事务隔离级别为 RR（Repeatable Read）实现一致性快照读。

2. 表级锁

问题：表锁有哪两种类型？区别是什么？

答案：
– 表共享读锁（LOCK TABLE ... READ）：允许多会话读，阻塞所有写操作。
– 表独占写锁（LOCK TABLE ... WRITE）：禁止其他会话读写。
示例：

LOCK TABLE users READ;  -- 加表读锁
SELECT * FROM users;    -- 允许
UPDATE users SET name='a' WHERE id=1; -- 报错（禁止写）

追问：什么场景下会触发隐式表锁？

追问答案：
当执行无索引条件的 UPDATE 或 DELETE 时，InnoDB 会锁全表（实际是锁所有行）。
原理：没有索引会导致全表扫描，MySQL 升级为行锁全表锁定（并非真正表锁，但效果相同）。

3. 行级锁（Record Lock）

问题：如何显式对某行加排他锁？

答案：
使用 SELECT ... FOR UPDATE 锁定单行：

BEGIN;
SELECT * FROM orders WHERE id=100 FOR UPDATE; -- 锁定 id=100 的行
UPDATE orders SET amount=200 WHERE id=100;
COMMIT;

原理：FOR UPDATE 对主键索引加 X 锁，阻塞其他事务修改该行。

追问：如果 WHERE 条件无索引会发生什么？

追问答案：
全表所有行被锁定！其他事务无法修改表中任何数据。
原理：InnoDB 需扫描所有行来定位数据，导致所有扫描过的行都被加锁。

4. 间隙锁（Gap Lock）

问题：什么是间隙锁？解决什么问题？

答案：
锁定索引记录之间的区间（如 id=10 和 id=20 之间的间隙），防止其他事务插入数据，解决幻读问题。
示例：

-- 事务 A (RR 隔离级别)
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- 对 (20,30) 区间加间隙锁
-- 事务 B 尝试插入
INSERT INTO users(age) VALUES(25); -- 阻塞！

原理：RR 隔离级别下自动启用，锁定区间而非具体记录。

追问：唯一索引是否使用间隙锁？

追问答案：
唯一索引的等值查询不使用间隙锁（如 WHERE id=15），但范围查询仍使用（如 WHERE id>10）。
原理：唯一索引可精确定位单条记录，无需间隙锁。

5. Next-Key Lock

问题：Next-Key Lock 由哪两部分组成？

答案：
Record Lock（记录锁） + Gap Lock（间隙锁），锁定记录及前开区间。
示例：
– 索引值：10, 20, 30
– WHERE id=20 FOR UPDATE 锁定范围：(10, 20]
原理：RR 隔离级别默认锁机制，解决幻读。

追问：如何减少 Next-Key Lock 的范围？

追问答案：
– 使用唯一索引
– 精确查询条件（避免范围查询）
– 使用 RC 隔离级别（但会引发幻读）
原理：唯一索引缩小间隙范围，精确查询避免锁扩大。

6. 意向锁

问题：意向锁的作用是什么？

答案：
快速判断表中是否有行锁，避免逐行检查锁状态。分为：
– IS（意向共享锁）：准备加行级 S 锁
– IX（意向排他锁）：准备加行级 X 锁
原理：表级锁，在加行锁前自动申请。

追问：意向锁之间是否互斥？

追问答案：
IS 和 IX 互相兼容，但：
– IS 与表级 S 锁兼容
– IX 与所有表级锁互斥
原理：意向锁仅表示“意向”，不阻塞同行级锁操作。

7. 插入意向锁

问题：插入意向锁是什么？解决什么问题？

答案：
特殊的 Gap Lock，允许多事务在相同间隙插入非冲突数据，提高并发插入效率。
示例：

-- 事务 A
SELECT * FROM users WHERE age=25 FOR UPDATE; -- 对间隙加锁
-- 事务 B
INSERT INTO users(age) VALUES(26); -- 获取插入意向锁，成功！

原理：只要插入位置不同就不冲突。

追问：插入意向锁是否与 Gap Lock 互斥？

追问答案：
不互斥！插入意向锁只与其他插入意向锁冲突。
原理：插入意向锁是 Gap Lock 的子集，设计目标为优化插入性能。

8. 自增锁（AUTO-INC Lock）

问题：MySQL 8.0 如何优化自增锁？

答案：
– 8.0 前：表级锁，语句执行完才释放；
– 8.0 后：轻量级锁（原子操作），自增值分配后立即释放。
原理：通过 innodb_autoinc_lock_mode 参数控制：
– 0：传统表锁模式
– 1：默认（批量插入用表锁，单条用轻量锁）
– 2：全部轻量锁（可能 ID 不连续）

追问：为什么自增 ID 可能不连续？

追问答案：
– 事务回滚（自增值不回收）
– 批量插入预分配 ID（如插入 3 条，分配 ID 1,2,3，但事务只插入 1 条）
示例：

INSERT INTO users(name) VALUES ('a'),('b'); -- 预分配 ID=1,2
-- 若事务回滚，下次插入从 ID=3 开始

9. 死锁场景

问题：给出一个经典的死锁案例

答案：

-- 事务 A
BEGIN;
UPDATE accounts SET balance=100 WHERE id=1; -- 锁定 id=1
UPDATE accounts SET balance=200 WHERE id=2; -- 请求 id=2 的锁
-- 事务 B
BEGIN;
UPDATE accounts SET balance=300 WHERE id=2; -- 锁定 id=2
UPDATE accounts SET balance=400 WHERE id=1; -- 请求 id=1 的锁

原理：事务 A 持有 id=1 锁等待 id=2，事务 B 持有 id=2 锁等待 id=1，形成循环等待。

追问：InnoDB 如何检测死锁？

追问答案：
通过等待图（wait-for graph）检测循环依赖，自动回滚 undo 量小的事务。
原理：每 100ms 检测一次锁等待链，深度优先搜索环路。

10. 锁超时

问题：如何控制锁等待时间？

答案：
设置 innodb_lock_wait_timeout（默认 50 秒），超时自动回滚语句。
示例：

SET innodb_lock_wait_timeout=10; -- 设置 10 秒超时

追问：MySQL 8.0 新增哪两种避免等待的语法？

追问答案：
– SELECT ... FOR UPDATE NOWAIT：立即报错
– SELECT ... FOR UPDATE SKIP LOCKED：跳过锁定行
示例：

SELECT * FROM orders WHERE status='pending' 
FOR UPDATE SKIP LOCKED; -- 只返回未锁定的行

11. 隔离级别与锁

问题：RC 和 RR 隔离级别下锁的区别？

答案：

特性	RC（Read Committed）	RR（Repeatable Read）
幻读	可能发生	通过 Next-Key Lock 避免
锁范围	仅锁定现有记录	锁定记录 + 间隙
一致性	语句级一致性	事务级一致性

追问：如何验证 RR 级别解决了幻读？

追问答案：
通过当前读测试：

-- 事务 A
BEGIN;
SELECT * FROM users WHERE age>20 FOR UPDATE; -- 加 Next-Key Lock
-- 事务 B
INSERT INTO users(age) VALUES(25); -- 阻塞！无法插入
COMMIT;

原理：FOR UPDATE 触发当前读，间隙锁阻塞插入操作。

12. 半一致读（Semi-Consistent Read）

问题：什么是半一致读？

答案：
RC 隔离级别下，UPDATE 语句可能读到已提交的最新版本，但最终基于当前读版本修改。
原理：优化锁机制，减少锁冲突。

追问：半一致读如何避免死锁？

追问答案：
当 UPDATE 遇到被锁定的行时：
– 先读最新提交版本
– 若不符合 WHERE 条件则放弃锁请求
示例：

-- 事务 A
UPDATE t SET val=val+1 WHERE id=1; -- 持有锁
-- 事务 B
UPDATE t SET val=val+1 WHERE val>0; -- 若 id=1 的 val 已为 -1，则跳过锁等待

13. 外键锁机制

问题：外键关联操作如何加锁？

答案：
– 子表插入/更新：对父表对应行加 S 锁
– 父表更新/删除：对子表关联行加 S 锁
原理：保证参照完整性，S 锁阻塞父表删除但允许读。

追问：如何避免外键导致的死锁？

追问答案：
– 按固定顺序访问表（先父表后子表）
– 使用 ON UPDATE/DELETE CASCADE 减少操作
示例：

CREATE TABLE orders (
  id INT PRIMARY KEY
);
CREATE TABLE order_details (
  id INT PRIMARY KEY,
  order_id INT,
  FOREIGN KEY (order_id) REFERENCES orders(id) 
    ON DELETE CASCADE -- 级联删除避免额外锁
);

14. 覆盖索引与锁优化

问题：覆盖索引如何减少锁范围？

答案：
当查询只需索引列时，InnoDB 仅访问索引，避免锁主键数据行。
示例：

-- 表结构
CREATE TABLE users (
  id INT PRIMARY KEY,
  age INT,
  name VARCHAR(20),
  INDEX idx_age(age)
);
-- 事务 A
SELECT age FROM users WHERE age=25 FOR UPDATE; 
-- 仅锁定 idx_age 索引上的记录，不锁主键行

原理：所有数据从索引获取，无需回表。

追问：哪些场景会扩大锁范围？

追问答案：
– 无索引查询
– 索引失效（如函数操作）
– 范围查询（RR 级别）

15. 死锁日志分析

问题：如何查看死锁日志？

答案：

SHOW ENGINE INNODB STATUS; -- 查看 LATEST DETECTED DEADLOCK 部分

输出示例：

*** (1) TRANSACTION: 
UPDATE t SET val=1 WHERE id=1
*** (1) HOLDS THE LOCK(S):  -- 持有 id=1 的 X 锁
*** (2) TRANSACTION:
UPDATE t SET val=2 WHERE id=2
*** (2) HOLDS THE LOCK(S):  -- 持有 id=2 的 X 锁
*** (2) WAITING FOR THIS LOCK: -- 等待 id=1 的 X 锁

追问：如何永久记录死锁日志？

追问答案：
设置参数：

innodb_print_all_deadlocks=ON  -- 将死锁日志写入 error log

16. 悲观锁 vs 乐观锁

问题：如何用版本号实现乐观锁？

答案：
– 表增加 version 字段
– 更新时检查版本号：

UPDATE products 
SET stock=100, version=version+1 
WHERE id=1 AND version=5; -- 若版本号改变则更新失败

原理：应用层冲突检测，无数据库锁开销。

追问：乐观锁适用什么场景？

追问答案：
– 读多写少
– 冲突概率低
– 分布式环境

17. 锁分裂（Lock Escalation）

问题：InnoDB 是否支持锁升级？

答案：
不支持！与 SQL Server 不同，InnoDB 不会将行锁升级为表锁。
原理：始终保持行级锁定粒度，避免并发性能断崖下跌。

追问：为什么用户感觉“锁表”？

追问答案：
当无索引更新时，实际是锁定了所有行 + 间隙（效果类似表锁），并非真正的锁升级。

18. 隐式锁（Implicit Lock）

问题：什么是隐式锁？

答案：
新插入的记录未提交时，其他事务访问该行会自动为其加锁，避免脏读。
原理：InnoDB 通过事务 ID 实现，延迟加锁直到冲突发生。

追问：如何触发隐式锁转显式锁？

追问答案：
当其他事务尝试修改该行时：

-- 事务 A
INSERT INTO t(id) VALUES(1); -- 未提交
-- 事务 B
UPDATE t SET val=1 WHERE id=1; -- 触发隐式锁转显式 X 锁，事务 B 阻塞

19. 锁监控

问题：如何实时查看锁等待？

答案：
MySQL 8.0+：

SELECT * FROM sys.innodb_lock_waits;

输出字段：
– waiting_trx_id：等待事务 ID
– blocking_trx_id：阻塞事务 ID
– waiting_query：被阻塞的 SQL

追问：如何查看事务持有的锁？

追问答案：

SELECT * FROM performance_schema.data_locks; -- 8.0+

关键字段：
– LOCK_TYPE（TABLE/RECORD）
– LOCK_MODE（X/S, GAP/INSERT_INTENTION）
– LOCK_DATA：锁定记录的主键值

20. 锁与复制

问题：基于语句复制（SBR）如何避免主从不一致？

答案：
对非事务表（如 MyISAM）加锁：

LOCK TABLES non_innodb_table WRITE; -- 加写锁
... DML 操作 ...
UNLOCK TABLES;

原理：确保语句串行执行，但牺牲并发性。

追问：为什么推荐行级复制（RBR）？

追问答案：
– 避免锁表
– 更安全（直接复制行变更）
– 解决不确定函数问题（如 NOW()）

21. 在线 DDL 锁机制

问题：`ALGORITHM=INPLACE` 需要加锁吗？

答案：
需要！但仅需短暂排他锁（通常只在元数据变更阶段）。
原理：多数 DDL 分三个阶段：
– 准备阶段（可读写）
– DDL 执行阶段（需要排他锁，阻塞写操作）
– 提交阶段（可读写）

追问：如何实现真正的无锁 DDL？

追问答案：
使用 pt-online-schema-change 工具：
– 创建影子表
– 增量数据同步（INSERT...SELECT）
– 原子切换表名
原理：通过触发器实时同步数据，业务无感知。

22. 死锁预防策略

问题：列出 4 种死锁预防方法

答案：
– 事务保持短小
– 按固定顺序访问资源（如按 id 排序更新）
– 使用低隔离级别（如 RC）
– 使用 SELECT ... FOR UPDATE NOWAIT

追问：为什么按顺序访问能避免死锁？

追问答案：
打破“循环等待”条件：

事务 A：锁顺序 1→2  
事务 B：锁顺序 1→2  -- 即使并发，也不会出现 A 等 B 且 B 等 A

23. 无主键表锁机制

问题：无主键表如何加行锁？

答案：
InnoDB 会创建隐藏的 _rowid 作为聚簇索引，但全表扫描时仍会锁所有行。
原理：所有更新操作都需全表扫描定位记录。

追问：为什么生产环境禁止无主键表？

追问答案：
– 锁全表风险高
– 复制延迟（RBR 模式下修改效率低）
– 磁盘空间浪费（隐藏主键管理开销）

24. 锁与缓冲池

问题：缓冲池如何影响锁行为？

答案：
当数据页不在缓冲池时：
– 从磁盘加载页面
– 加载过程中阻塞其他事务访问该页
原理：物理 I/O 操作需要短暂互斥访问。

追问：如何减少此类阻塞？

追问答案：
– 增大 innodb_buffer_pool_size
– 预热缓冲池（重启后执行 SELECT * FROM table）
– 使用 SSD 降低 I/O 延迟

25. 锁升级陷阱

问题：什么行为会导致“伪锁升级”？

答案：
– 无索引 UPDATE：UPDATE users SET score=0 WHERE name LIKE '%test%'
– 范围查询加锁：SELECT * FROM log WHERE time>'2023-01-01' FOR UPDATE
原理：锁范围远大于预期，效果类似表锁。

追问：如何快速发现无索引更新？

追问答案：
监控慢查询日志：

slow_query_log=ON
log_queries_not_using_indexes=ON  -- 记录未用索引的查询

26. 锁与事务隔离级别

问题：SERIALIZABLE 级别如何加锁？

答案：
所有 SELECT 自动转为 SELECT ... LOCK IN SHARE MODE，相当于全库读锁。
原理：强制所有操作串行化，性能极差但一致性最强。

追问：生产环境为何禁用 SERIALIZABLE？

追问答案：
– 并发性能下降 10 倍以上
– 死锁概率剧增
– 实际业务通常用 RR + 应用层控制即可

27. 锁等待分析

问题：如何定位阻塞源？

答案：

-- 查看被阻塞事务
SELECT * FROM sys.innodb_lock_waits;
-- 查看阻塞者 SQL
SELECT * FROM performance_schema.events_statements_current 
WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads 
                  WHERE PROCESSLIST_ID = [blocking_trx_id]);

追问：遇到锁等待 DBA 首先做什么？

追问答案：
– 确定是否死锁（自动回滚则无需处理）
– 评估事务重要性
– 选择性 Kill 阻塞事务：KILL [blocking_trx_id]

28. 锁与索引类型

问题：唯一索引 vs 普通索引的锁差异？

追问：为什么普通索引范围查询锁更多行？

追问答案：
因为需锁定索引区间内所有记录（包括不存在的间隙）。
示例：
– 索引值：10,20,30
– WHERE id>15 FOR UPDATE 锁定范围：(10,20], (20,30], (30,+∞)

29. 锁分裂与合并

问题：InnoDB 如何处理高并发锁竞争？

答案：
通过锁分裂（Lock Splitting）机制：
– 将单个锁拆分为多个粒度更小的锁
– 允许不同事务竞争不同子锁
原理：提高并发度，减少等待。

追问：锁分裂的副作用是什么？

追问答案：
内存消耗增加（每个锁约占用 64 字节），可能触发 innodb_buffer_pool_size 不足。

30. 锁系统内存管理

问题：如何控制锁内存占用？

答案：
设置参数：

innodb_buffer_pool_size=4G     # 缓冲池大小
innodb_lru_scan_depth=1024     # LRU 扫描深度
innodb_lock_wait_timeout=30    # 锁等待超时

原理：超时或事务结束自动释放锁内存。

追问：锁信息存储在何处？

追问答案：
– 内存：锁对象存储在缓冲池
– 磁盘：事务信息在 undo log
– 持久化：死锁信息写入 error log

31. 锁与分区表

问题：分区表如何加锁？

答案：
按分区加锁：

LOCK TABLES orders PARTITION (p1) WRITE; -- 只锁 p1 分区

原理：每个分区独立处理锁，提高并发。

追问：分区表死锁风险更高吗？

追问答案：
是！因为事务可能跨多个分区加锁，更容易形成交叉等待。

32. 锁与全文索引

问题：全文索引操作需要加锁吗？

答案：
需要！对 FTS_DOC_ID 列加锁：
– 插入时：X 锁
– 删除时：X 锁
原理：维护倒排索引的一致性。

追问：如何优化全文索引并发？

追问答案：
– 使用 innodb_ft_cache_size 缓存变更
– 定期 OPTIMIZE TABLE 合并索引
– 业务低峰期更新全文索引

33. 锁与空间索引

问题：空间数据（GIS）索引使用什么锁？

答案：
RR-Tree 索引使用 Predicate Lock（谓词锁），锁定查询范围的空间区域。
原理：解决“幻读”在空间数据中的特殊表现。

追问：为什么空间索引死锁更难排查？

追问答案：
锁定的空间范围可能重叠但不对称：

事务 A：锁定区域 X  
事务 B：锁定区域 Y  
若 X∩Y≠∅ 且 Y∩X≠∅，形成环路等待

34. 锁在集群中的传递

问题：Group Replication 如何处理锁？

答案：
通过 Certification 机制检测冲突：
– 事务在本地执行
– 将写集（Write-set）广播到集群
– 各节点检测写集冲突（等效锁检查）
原理：分布式事务锁，无需全局锁表。

追问：集群中出现锁冲突怎么办？

追问答案：
冲突事务本地回滚，由应用层重试。
错误日志：

Transaction '123' conflicts with existing transaction '456'

35. 未来锁优化方向

问题：MySQL 9.0 可能在锁机制做哪些改进？

答案：
– 完全无锁的 MVCC 实现（类似 PostgreSQL）
– 细粒度锁管理（字节级锁定）
– 机器学习预测死锁
原理：减少锁开销是数据库永恒的优化主题。

追问：当前最急需的锁改进是什么？

追问答案：
在线锁降级（Lock Downgrade）：

-- 理论语法
SELECT ... FOR UPDATE; 
-- 执行读操作后
DOWNGRADE TO SHARE MODE; -- 降级为 S 锁，允许其他事务读

原理：允许长事务安全释放不必要的排他锁。

关于作者

codecafe

php搬砖工程师