面试:详细介绍MySQL锁类型、锁机制

原创于 2025-08-04 20:28:22 发布 · 651 阅读

28 ·

CC 4.0 BY-SA版权

文章标签：

#面试 #mysql

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
MySQL 的锁机制是数据库并发控制的核心，尤其 InnoDB 引擎的锁设计极为精细。以下从锁类型、机制、适用场景三方面深度解析，并结合通俗案例说明：

一、MySQL 锁分类全景图

二、核心锁类型详解

1. 全局锁（Global Lock）

机制：FLUSH TABLES WITH READ LOCK 锁住整个数据库实例
特性：
- 所有表只读，DDL/DML 阻塞
- 典型场景：逻辑备份（替代方案：mysqldump --single-transaction）
案例：

银行年终结算日，暂停所有存取款业务（全局只读），财务进行全库账务备份。

2. 表级锁（Table Lock）

模式：
- 表共享读锁（S）：LOCK TABLES t READ
- 表独占写锁（X）：LOCK TABLES t WRITE
机制：MyISAM 默认锁，InnoDB 也支持（但非首选）
问题：并发性差，易阻塞
案例：

图书馆闭馆盘点（表写锁），禁止借阅；开馆后允许多人同时查阅同一本书（表读锁）。

3. 行级锁（InnoDB 核心）

(1) 共享锁（S Lock）

机制：SELECT ... LOCK IN SHARE MODE
特性：允许其他事务读，但阻塞写
适用场景：确保读取期间数据不被修改

-- 事务1
SELECT balance FROM accounts WHERE id=1 LOCK IN SHARE MODE; -- 加S锁
-- 事务2
UPDATE accounts SET balance=0 WHERE id=1; -- 阻塞直到事务1提交！

(2) 排他锁（X Lock）

机制：SELECT ... FOR UPDATE 或 DML 语句自动加锁
特性：禁止其他事务任何读写
适用场景：更新关键数据（如账户扣款）

-- 事务1
SELECT * FROM orders WHERE id=100 FOR UPDATE; -- 加X锁
-- 事务2
SELECT * FROM orders WHERE id=100 LOCK IN SHARE MODE; -- 阻塞！

(3) 意向锁（Intention Lock）

作用：快速判断表中是否有行锁（避免遍历每行）
类型：
- 意向共享锁（IS）：事务准备加行级 S 锁
- 意向排他锁（IX）：事务准备加行级 X 锁
兼容性：
X IX S IS
X ❌ ❌ ❌ ❌
IX ❌ ✔️ ❌ ✔️
S ❌ ❌ ✔️ ✔️
IS ❌ ✔️ ✔️ ✔️

(4) 记录锁（Record Lock）

机制：锁住索引记录（即使表无索引，也会隐式创建聚簇索引锁）
场景：精确匹配单行

-- 锁住id=5的索引项
UPDATE users SET name='Bob' WHERE id=5;

(5) 间隙锁（Gap Lock）

机制：锁住索引记录之间的区间（开区间）
场景：防止幻读（Phantom Read）

-- 锁住(20,30)区间，阻止插入id=25的记录
SELECT * FROM products WHERE price BETWEEN 20 AND 30 FOR UPDATE;

(6) 临键锁（Next-Key Lock）

机制：记录锁 + 间隙锁（左开右闭区间）
场景：RR隔离级别默认锁，解决幻读

-- 锁住(15,20]区间（假设已有记录id=20）
SELECT * FROM orders WHERE order_id > 15 FOR UPDATE;

(7) 插入意向锁（Insert Intention Lock）

机制：特殊的间隙锁，表示准备插入
特性：多个事务可在同一间隙插入不同位置（不互斥）

-- 事务1 准备在id=10~20之间插入15（不阻塞事务2插入18）
INSERT INTO logs (id, msg) VALUES (15, 'test');

三、锁机制底层原理

1. 锁兼容性矩阵

请求锁类型 \ 已存在锁	X	IX	S	IS
X	❌	❌	❌	❌
IX	❌	✔️	❌	✔️
S	❌	❌	✔️	✔️
IS	❌	✔️	✔️	✔️

2. 锁升级流程

3. 死锁产生与解决

典型死锁场景：

-- 事务1
UPDATE accounts SET balance=100 WHERE id=1; -- 持有id=1的X锁
UPDATE accounts SET balance=200 WHERE id=2; -- 请求id=2的X锁

-- 事务2（同时执行）
UPDATE accounts SET balance=300 WHERE id=2; -- 持有id=2的X锁
UPDATE accounts SET balance=400 WHERE id=1; -- 请求id=1的X锁

解决方案：
- 超时机制：innodb_lock_wait_timeout=50（默认50秒）
- 死锁检测：innodb_deadlock_detect=ON（主动回滚代价小的事务）

四、通俗场景举例

案例1：会议室预定系统（行锁 + 间隙锁）

场景：预定10:00-11:00的会议室A

-- 检查间隙：锁住[10:00, 11:00] 防止其他人插入冲突时段
SELECT * FROM bookings 
WHERE room='A' AND end_time > '10:00' AND start_time < '11:00'
FOR UPDATE;

案例2：商品秒杀（排他锁 + 乐观锁）

场景：100件商品，避免超卖

-- 排他锁确保原子性
START TRANSACTION;
SELECT stock FROM products WHERE id=1001 FOR UPDATE; 
UPDATE products SET stock=stock-1 WHERE id=1001;
COMMIT;

案例3：用户注册防重复（唯一索引 + 记录锁）

场景：防止并发注册相同用户名

-- 唯一索引username上的记录锁
INSERT IGNORE INTO users (username) VALUES ('alice');

五、关键监控命令

查看锁状态：

SHOW ENGINE INNODB STATUS;  -- 关注TRANSACTIONS和LOCK WAIT
SELECT * FROM information_schema.INNODB_LOCKS; 
SELECT * FROM information_schema.INNODB_LOCK_WAITS;