跳到主要内容

MySQL 中的锁是怎么回事

前言：MySQL 锁机制知识体系​

说明：

  1. 乐观锁与悲观锁是两种并发控制思想，用于解决丢失更新问题
  2. InnoDB支持多种锁粒度，默认使用行锁，锁粒度最小，并发度最高
  3. 共享锁与排他锁 是InnoDB实现的两种标准行锁
  4. InnoDB锁算法：记录锁、间隙锁、Next-Key锁，用于解决幻读问题
  5. 意向锁支持多种粒度锁同时存在

全局锁相关面试题​

面试题1：什么是MySQL的全局锁？使用场景是什么？​

答案： 全局锁是对整个数据库实例加锁的机制。使用命令：

FLUSH TABLES WITH READ LOCK;

主要使用场景：

  • 全库逻辑备份
  • 需要让整个库处于只读状态的维护操作

影响：

  • 阻塞所有数据更新语句（增删改）
  • 阻塞数据定义语句（建表、修改表结构等）
  • 阻塞更新类事务的提交语句

面试题2：全库备份时不加全局锁会有什么问题？请举例说明​

答案： 不加全局锁会导致备份数据不一致。

问题说明： 假设备份过程中有并发修改：

  1. 备份开始时Alice年龄是25
  2. 备份过程中，业务将Alice年龄改为35，新增Charlie用户
  3. 最终备份可能包含修改前的Alice(25岁)和新增的Charlie
  4. 这样的备份数据在逻辑上是不一致的

面试题3：mysqldump的--single-transaction参数作用是什么？为什么不能完全替代全局锁？​

答案： --single-transaction参数的作用：

  • 利用MVCC机制，在备份开始时启动一个事务
  • 确保获得一致性视图，备份过程中数据可以正常更新

不能完全替代全局锁的原因：

  • 只适用于支持事务的存储引擎（如InnoDB）
  • MyISAM等不支持事务的引擎无法使用此方法
  • 对于混合存储引擎的库，仍需要使用FTWRL（FLUSH TABLES WITH READ LOCK）

表级锁相关面试题​

面试题4：MySQL表级锁有哪些类型？如何使用？​

答案： 表级锁包括表锁和元数据锁（MDL）。

表锁类型：

  1. 共享锁（READ）：允许并发读，阻塞写
  2. 排他锁（WRITE）：独占访问，阻塞所有其他操作
-- 获取共享锁
LOCK TABLES users READ;
SELECT * FROM users;
UNLOCK TABLES;

-- 获取排他锁
LOCK TABLES users WRITE;
UPDATE users SET age = 35 WHERE id = 1;
UNLOCK TABLES;

面试题5：什么是MDL锁？它是如何自动管理的？​

答案： MDL（Metadata Locking）是元数据锁，用于保护数据库对象的结构和定义。

自动管理机制：

  • 对表做增删改查时，自动加MDL读锁
  • 对表做结构变更时，自动加MDL写锁
  • 读锁之间不互斥，读写锁、写写锁互斥

面试题6：请描述MDL锁导致数据库连接爆满的场景，并画出时序图​

答案： 这是一个经典的生产环境问题。

问题分析：

  1. Session A执行长时间查询，持有MDL读锁
  2. Session C申请MDL写锁被阻塞
  3. 后续所有申请MDL读锁的操作都被C阻塞
  4. 客户端重试机制导致连接数爆满

面试题7：如何安全地给热点表添加字段？​

答案： 安全策略：

  1. 在线DDL工具：使用pt-online-schema-change等工具
  2. 分步执行：将操作拆分为多个小步骤
  3. 维护窗口：在业务低峰期执行
  4. 监控预警：实时监控锁等待情况

具体操作流程：

代码示例：

-- 使用pt-online-schema-change
pt-online-schema-change \
  --alter "ADD COLUMN new_field VARCHAR(50)" \
  --execute h=localhost,D=mydb,t=hot_table

-- 或者手动分步操作
CREATE TABLE hot_table_new LIKE hot_table;
ALTER TABLE hot_table_new ADD COLUMN new_field VARCHAR(50);
-- 分批迁移数据...
RENAME TABLE hot_table TO hot_table_old, hot_table_new TO hot_table;

行锁相关面试题​

面试题8：MySQL行锁的工作原理是什么？请举例说明​

答案： 行锁是对表中具体行记录的锁定，允许不同会话访问同一表的不同行。

面试题9：InnoDB的三种锁算法是什么？分别解决什么问题？​

答案： 三种锁算法：

  1. 记录锁（Record Lock）

    • 锁定单个行记录
    • 防止其他事务修改该行

  2. 间隙锁（Gap Lock）

    • 锁定索引记录之间的间隙
    • 防止其他事务在间隙中插入记录

  3. Next-Key Lock

    • 记录锁 + 间隙锁的组合
    • 锁定记录本身和前面的间隙

解决的问题：

  • 记录锁：防止脏读、不可重复读
  • 间隙锁：防止幻读
  • Next-Key锁：在可重复读隔离级别下彻底解决幻读

面试题10：请画图说明间隙锁如何防止幻读​

答案：

假设表中有索引值：10, 20, 30

防止幻读的过程：

锁粒度相关面试题​

面试题11：对比表级锁、行级锁、页面锁的优缺点​

答案：

锁粒度开销加锁速度死锁锁冲突概率并发度适用场景
表级锁小快不会最高最低全表操作、MyISAM
行级锁大慢会最低最高OLTP、InnoDB
页面锁中等中等会中等中等NDB引擎

详细对比：

面试题12：什么情况下InnoDB会使用表锁而不是行锁？​

答案： InnoDB使用表锁的情况：

  1. 未使用索引的查询
-- 没有合适索引，会升级为表锁
UPDATE users SET status = 1 WHERE name = 'Alice';
  1. 显式使用表锁语句
LOCK TABLES users WRITE;

  1. 事务中混合使用不同存储引擎

  2. 全表扫描的更新操作

UPDATE users SET created_time = NOW();

优化建议：

  • 确保WHERE条件使用索引
  • 避免全表更新操作
  • 合理设计索引策略

面试题13：在高并发场景下，如何选择合适的锁策略？​

答案： 选择策略：

具体建议：

  1. 读多写少场景：

    • 使用读未提交或读已提交隔离级别
    • 利用MVCC避免读锁
    • 考虑读写分离

  2. 写多读少场景：

    • 减小事务粒度
    • 使用批量操作
    • 优化索引减少锁范围

  3. 混合场景：

    • 使用行级锁
    • 避免长事务
    • 合理设计索引

Reference​