MySQL 锁机制

核心摘要：MySQL 的锁机制主要用于管理共享资源的并发访问。根据加锁粒度，主要分为 全局锁、表级锁 和 行级锁。

一、 全局锁 (Global Locks)

全局锁就是对整个数据库实例加锁。

1. 核心作用

让整个库处于 只读状态 (Read-Only)。

2. 语法演示

  • 加锁命令：

    1
    FLUSH TABLES WITH READ LOCK;

  • 解锁命令：

    1
    UNLOCK TABLES;

    (注意：如果客户端连接断开，锁也会自动释放)

3. 锁生效后的行为代码演示

假设我们执行了上面的加锁命令，此时不同类型的 SQL 语句表现如下：

1
2
3
4
5
6
7
8
9
-- 1. 查询语句 (DQL) -> ✅ 成功
SELECT * FROM user_info;

-- 2. 数据更新语句 (DML) -> ❌ 阻塞 (Blocked)
INSERT INTO user_info (name) VALUES ('Alice');
-- 状态：一直等待，直到全局锁释放或超时

-- 3. 表结构修改语句 (DDL) -> ❌ 阻塞 (Blocked)
ALTER TABLE user_info ADD COLUMN age INT;

二、 表级锁 (Table-level Locks)

MySQL 的表级锁主要包含两种：显式表锁 和 元数据锁 (MDL)。

1. 显式表锁 (Explicit Table Locks)

显式表锁分为 读锁 (Read Lock) 和 写锁 (Write Lock)。

(1) 表读锁 (Read Lock)

  • 语法：LOCK TABLES 表名 READ;
  • 特性：共享锁。当前线程和其他线程都只能读，不能写。
动作 当前持有锁的线程 (Session A) 其他线程 (Session B)
读 (SELECT) ✅ 可执行 ✅ 可执行
写 (UPDATE/INSERT) ❌ 报错 (Can’t update table ‘x’ while ‘x’ is locked) ⏸️ 阻塞等待 (Waiting for table metadata lock)

(2) 表写锁 (Write Lock)

  • 语法：LOCK TABLES 表名 WRITE;
  • 特性：排他锁。当前线程可以读写，其他线程既不能读也不能写。
动作 当前持有锁的线程 (Session A) 其他线程 (Session B)
读 (SELECT) ✅ 可执行 ⏸️ 阻塞等待
写 (UPDATE/INSERT) ✅ 可执行 ⏸️ 阻塞等待

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

  • 触发方式：系统自动添加，无需显式 SQL。
  • 目的：维护表结构一致性。

MDL 锁类型详解

类型 触发场景 兼容性
MDL 读锁 对表进行增删改查 (SELECT, INSERT, UPDATE…) 与其他 MDL 读锁 兼容与 MDL 写锁 互斥
MDL 写锁 对表结构进行修改 (ALTER TABLE, DROP TABLE…) 与 所有锁 (读/写) 都互斥

演示示例：

1
2
3
4
5
6
7
8
9
10
11
-- Session A (正在查询，未提交，持有 MDL 读锁)
BEGIN;
SELECT * FROM student; 

-- Session B (尝试修改表结构，需要申请 MDL 写锁)
ALTER TABLE student ADD COLUMN score INT;
-- 结果：⏸️ 阻塞 (Blocked)，因为 MDL 读写互斥

-- Session C (尝试查询)
SELECT * FROM student;
-- 结果：⏸️ 阻塞！(通常会被 Session B 的写锁请求阻塞在队列中)

三、 行级锁 (Row-level Locks)

前提：行锁由存储引擎实现，InnoDB 支持，MyISAM 不支持。

InnoDB 的行锁主要分为 共享锁 (S锁) 和 排他锁 (X锁)。

1. 共享锁 (Shared Lock / S锁)

  • 概念：又称读锁。允许一个事务去读一行，阻止其他事务获得该行的排他锁。

  • 适用场景：确保读取的数据在处理期间不被他人修改。

  • SQL 语法：

    1
    2
    3
    4
    -- 手动添加 S 锁
    SELECT * FROM student WHERE id = 1 LOCK IN SHARE MODE;
    -- 或者 MySQL 8.0+ 写法
    SELECT * FROM student WHERE id = 1 FOR SHARE;

2. 排他锁 (Exclusive Lock / X锁)

  • 概念：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务获取该行的 S 锁或 X 锁。

  • SQL 语法：

    1
    2
    3
    4
    5
    -- 1. 自动添加：对于 UPDATE, DELETE, INSERT 语句，InnoDB 会自动给涉及的数据行加 X 锁
    UPDATE student SET name = 'Tom' WHERE id = 1;

    -- 2. 手动添加：查询时顺便加 X 锁
    SELECT * FROM student WHERE id = 1 FOR UPDATE;

3. 行锁的兼容性矩阵

当前锁 \ 请求锁 S 锁 (共享/读) X 锁 (排他/写)
已持有 S 锁 ✅ 兼容 (大家都能读) ❌ 冲突 (别人不能改)
已持有 X 锁 ❌ 冲突 (别人不能读) ❌ 冲突 (别人不能改)

四、 意向锁 (Intention Locks)

这是一种表级锁，主要为了解决行锁和表锁的冲突问题。

1. 场景问题

假设事务 A 锁住了表中的某一行（加了行级 X 锁）。 此时事务 B 想要申请整个表的写锁 (LOCK TABLES ... WRITE)。 如果没有意向锁，事务 B 需要遍历表中的每一行，查看是否被锁住。这效率极低。

2. 解决方案

  • 意向共享锁 (IS)：事务打算给数据行加行级 S 锁前，必须先取得该表的 IS 锁。
  • 意向排他锁 (IX)：事务打算给数据行加行级 X 锁前，必须先取得该表的 IX 锁。

3. 效果

事务 B 申请表锁时，只需要看表上是否有 IX 锁。如果有，说明表里有人正在改数据，直接阻塞等待，不需要遍历。

五、 锁的算法

InnoDB 的行锁实际上是锁在索引上的。

  1. Record Lock：单个行记录上的锁。
    • SELECT * FROM t WHERE id = 1 FOR UPDATE; (id 是主键)
  2. Gap Lock (间隙锁)：锁定一个范围，但不包含记录本身。
    • 防止幻读 (Phantom Read)。
    • 例如锁住 (5, 10) 之间的空隙，防止插入 id=7 的记录。
  3. Next-Key Lock：Record Lock + Gap Lock。
    • 锁定一个范围，并且锁定记录本身。
    • InnoDB 默认加锁单位。