MySQL 锁机制详解

MySQL 锁机制详解

1. 表级锁（Table-Level Locks）

• 粒度：整张表。
• 使用场景：
  • DDL操作：如 ALTER TABLE、DROP TABLE 等结构变更。
  • 全表扫描：未使用索引的查询（如 SELECT * FROM table WHERE non_index_column=1）。
• 特点：
  • 锁冲突率高：同一时间只允许一个事务操作表。
  • 并发性能差：MyISAM 引擎默认使用表级锁。
  • 显式加锁语法：

    LOCK TABLES table_name READ;  -- 加共享锁（允许其他事务读，阻塞写）
    LOCK TABLES table_name WRITE; -- 加排他锁（阻塞其他事务读写）
    UNLOCK TABLES;               -- 释放锁
    

2. 行级锁（Row-Level Locks）

• 粒度：单行记录。
• 使用场景：
  • 高并发写操作：如 UPDATE、DELETE 带索引条件的语句。
  • 精确控制：仅锁定需要修改的行，减少锁冲突。
• 特点：
  • 锁冲突率低：InnoDB 引擎默认支持行级锁。
  • 依赖索引实现：若未使用索引，退化为表级锁。
  • 显式加锁语法：

    SELECT * FROM table WHERE id=1 FOR UPDATE;    -- 加排他锁（X锁）
    SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE; -- 加共享锁（S锁）
    

3. 共享锁（Shared Lock, S Lock）

• 粒度：行或表。
• 使用场景：
  • 读操作需阻塞写：如事务A读取数据时，事务B不能修改。
  • 显式加锁示例：

    BEGIN;
    SELECT * FROM orders WHERE user_id=100 LOCK IN SHARE MODE; -- 加S锁
    -- 其他事务可读，但无法修改 user_id=100 的行
    COMMIT;
    

• 特点：
  • 允许多事务并发读：不阻塞其他 S 锁。
  • 阻塞 X 锁：其他事务无法加 X 锁修改数据。

4. 排他锁（Exclusive Lock, X Lock）

• 粒度：行或表。
• 使用场景：
  • 写操作需独占数据：如事务A修改数据时，禁止其他事务读写。
  • 显式加锁示例：

    BEGIN;
    SELECT * FROM products WHERE id=5 FOR UPDATE; -- 加X锁
    UPDATE products SET stock=stock-1 WHERE id=5;  -- 其他事务无法读写此行
    COMMIT;
    

• 特点：
  • 完全独占：阻塞其他事务的 S 锁和 X 锁。
  • 自动加锁：DML 语句（如 UPDATE、DELETE）隐式加 X 锁。

5. 意向锁（Intention Locks）

• 粒度：表级锁。
• 使用场景：
  • 辅助快速判断表是否被锁定：避免逐行检查锁冲突。
• 类型：
  • 意向共享锁（IS）：事务打算在表中某些行加 S 锁。
  • 意向排他锁（IX）：事务打算在表中某些行加 X 锁。
• 兼容性：

  当前锁	IS	IX	S	X
  IS	✅	✅	✅	❌
  IX	✅	✅	❌	❌
  S	✅	❌	✅	❌
  X	❌	❌	❌	❌

6. 间隙锁（Gap Lock）

• 粒度：索引记录的间隙（区间）。
• 使用场景：
  • 防止幻读：在 RR 隔离级别下，锁定索引范围（如 WHERE id > 10）。
  • 示例：

    -- 表中存在 id=5, 10, 15 的记录
    BEGIN;
    SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE; -- 锁定 (10,15) 和 (15,20) 的间隙
    -- 其他事务无法插入 id=12 或 id=18 的新记录
    COMMIT;
    

• 特点：
  • 仅 RR 隔离级别生效：RC 级别无间隙锁。
  • 锁定“不存在”的数据：防止范围内插入新记录。

7. 临键锁（Next-Key Lock）

• 粒度：行锁 + 间隙锁（左开右闭区间）。
• 使用场景：
  • 默认 RR 隔离级别的锁机制：结合行锁和间隙锁避免幻读。
  • 示例：

    -- 表中存在 id=5, 10, 15 的记录
    BEGIN;
    SELECT * FROM users WHERE id > 10 FOR UPDATE; -- 锁定 (10,15], (15,+∞)
    -- 其他事务无法插入 id=12（落在 (10,15]）或 id=20（落在 (15,+∞)）
    COMMIT;
    

• 特点：
  • 锁区间示例：假设当前有记录 id=10，临键锁范围为 (-∞,10]。
  • 解决幻读：通过锁定可能插入新记录的区间。

---

锁机制对比总结

锁类型	粒度	加锁场景	阻塞操作	隔离级别依赖
表级锁	整张表	DDL、全表扫描	阻塞其他所有表操作	所有级别
行级锁（X/S）	单行记录	带索引的 DML 操作	阻塞同一行的写（S锁）或读写（X锁）	所有级别
间隙锁	索引间隙	RR 级别下的范围查询	阻塞区间内的插入	仅 RR
临键锁	行+间隙	RR 级别下的默认锁机制	阻塞区间内的插入和修改	仅 RR

---

实际应用示例

场景：防止超卖（行锁 + 临键锁）

-- 事务A（用户下单，购买商品id=100）
BEGIN;
SELECT stock FROM products WHERE id=100 FOR UPDATE; -- 加X锁（行锁）
IF stock > 0 THEN
    UPDATE products SET stock=stock-1 WHERE id=100;
COMMIT;
ELSE
    ROLLBACK;
END IF;

-- 事务B（同时尝试购买同一商品）
-- 会被阻塞，直到事务A提交或回滚。

死锁案例与解决

-- 事务1
BEGIN;
UPDATE accounts SET balance=balance-100 WHERE id=1; -- 锁住id=1
UPDATE accounts SET balance=balance+100 WHERE id=2; -- 尝试锁id=2

-- 事务2（同时运行）
BEGIN;
UPDATE accounts SET balance=balance-200 WHERE id=2; -- 锁住id=2
UPDATE accounts SET balance=balance+200 WHERE id=1; -- 尝试锁id=1

-- 死锁发生！InnoDB 自动回滚其中一个事务。

解决方案：按固定顺序访问资源（如先更新id较小的账户）。

---

总结

• 表级锁：简单但低并发，适合结构变更或全表操作。
• 行级锁：高并发场景首选，需结合索引使用。
• 意向锁：优化表锁冲突检测效率。
• 间隙锁/临键锁：RR 隔离级别下防止幻读的核心机制。

建议：

• 尽量使用索引缩小锁范围。
• 避免长事务，减少锁持有时间。
• 在 RR 级别下，注意间隙锁可能导致的锁竞争问题。