MySQL-07 锁机制详解

2026-03-17 约 4144 字预计阅读 9 分钟

MySQL 锁机制详解

1. 锁的分类

MySQL 锁体系：

按粒度：
  全局锁（FTWRL）
  表级锁
    ├── MDL（元数据锁）
    ├── 表锁（LOCK TABLES）
    └── 意向锁（IS/IX，InnoDB 内部）
  行级锁（InnoDB）
    ├── Record Lock（记录锁）
    ├── Gap Lock（间隙锁）
    └── Next-Key Lock（记录锁 + 间隙锁）

按兼容性：
  共享锁（S Lock，读锁）：多个事务可同时持有
  排他锁（X Lock，写锁）：与其他所有锁互斥

1.1 锁兼容矩阵

	读锁（S）	写锁（X）
读锁（S）	兼容 ✅	冲突 ❌
写锁（X）	冲突 ❌	冲突 ❌

2. 全局锁与表锁

2.1 全局锁（FTWRL）

-- 加全局读锁（Flush Tables With Read Lock）
FLUSH TABLES WITH READ LOCK;

-- 释放
UNLOCK TABLES;

用途：全库逻辑备份（mysqldump --single-transaction 对 InnoDB 用 RR 事务替代，不需要 FTWRL）。

影响：加锁期间，整个数据库只读，所有 DML 和 DDL 都被阻塞。

2.2 元数据锁（MDL）

MDL 是 MySQL 5.5+ 自动管理的，无需显式加锁。

执行 DML/SELECT 时，自动加 MDL 读锁
执行 DDL（ALTER TABLE 等）时，自动加 MDL 写锁

MDL 读锁互相兼容，MDL 写锁与读锁互斥。

经典问题：MDL 锁阻塞场景

会话 A：长查询（持有 MDL 读锁）
会话 B：ALTER TABLE（等待 MDL 写锁）
会话 C：普通查询（等待，因为 B 在等 A）
会话 D、E、F...：后续请求全部积压

-- 发现 MDL 等待
SELECT blocking_pid, blocking_query, waiting_query
FROM sys.schema_table_lock_waits;

-- 或者
SELECT * FROM performance_schema.metadata_locks;

处理方式：Kill 掉持有 MDL 读锁的长查询。

2.3 表锁

-- 加表级读锁（当前会话可读不可写，其他会话可读不可写）
LOCK TABLES users READ;

-- 加表级写锁（当前会话可读写，其他会话读写都阻塞）
LOCK TABLES users WRITE;

-- 释放
UNLOCK TABLES;

InnoDB 极少用表锁（只有 LOCK TABLES 显式加，或某些特殊 DDL）。

2.4 意向锁（InnoDB 内部）

意向锁是 InnoDB 内部自动维护的表级锁，用于快速判断表中是否有行级锁。

加行级 S 锁前，先加表级 IS（意向共享）锁
加行级 X 锁前，先加表级 IX（意向排他）锁

意向锁之间完全兼容，只与表级 S/X 锁冲突。

这样 LOCK TABLES users WRITE 时，只需检查意向锁，不用遍历所有行锁。

3. InnoDB 行级锁

InnoDB 行锁锁的是索引记录，而不是行本身。

MySQL 特有特性：如果查询没有走索引，InnoDB 会退化为锁整张表的所有行（因为没有索引，无法精确定位要锁的行，只能锁所有行）。

3.1 Record Lock（记录锁）

锁定某个索引记录，防止其他事务修改/删除。

-- 显式加 S 锁（共享读锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;  -- MySQL 5.7
SELECT * FROM users WHERE id = 1 FOR SHARE;            -- MySQL 8.0

-- 显式加 X 锁（排他写锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 跳过锁等待（MySQL 8.0）
SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED;  -- 跳过已锁定的行
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;       -- 立即失败（不等待）

3.2 隐式加锁

-- INSERT 自动加 X 锁（行插入意向锁 + 记录锁）
INSERT INTO users VALUES (...)

-- UPDATE 自动加 X 锁
UPDATE users SET name = 'Alice' WHERE id = 1;

-- DELETE 自动加 X 锁
DELETE FROM users WHERE id = 1;

4. 间隙锁与 Next-Key Lock

4.1 为什么需要间隙锁

在 REPEATABLE READ 隔离级别下，InnoDB 为了防止幻读，不仅锁定已存在的记录，还要锁定"间隙"（不存在的记录之间的范围）。

-- 假设 users 表中 id 为 1, 5, 10
-- 执行：
SELECT * FROM users WHERE id BETWEEN 3 AND 7 FOR UPDATE;

-- 不仅锁定了 id=5 这一行
-- 还锁定了 (1, 5) 和 (5, 10) 这两个间隙
-- 防止其他事务在这个范围内插入新行

4.2 Next-Key Lock = Record Lock + Gap Lock

索引值：  1    5    10    ∞

区间：(-∞,1] (1,5] (5,10] (10,+∞)

Next-Key Lock 的区间（左开右闭）：
  (-∞, 1]
  (1, 5]
  (5, 10]
  (10, +∞)

4.3 间隙锁的特殊性

间隙锁之间互相兼容（两个事务可以同时持有同一个间隙的间隙锁）
间隙锁与插入意向锁冲突（插入意向锁是 INSERT 操作加的）
READ COMMITTED 隔离级别下没有间隙锁

-- 演示间隙锁导致的插入阻塞

-- 事务 A（RR 隔离级别）
START TRANSACTION;
SELECT * FROM users WHERE id = 5 FOR UPDATE;
-- 加了 Next-Key Lock：(1, 5]，以及可能 (5, 10)

-- 事务 B（尝试插入 id=3）
INSERT INTO users (id, name) VALUES (3, 'Bob');
-- 阻塞！因为 id=3 落在 (1, 5) 间隙中

4.4 插入意向锁（Insert Intention Lock）

插入意向锁是一种特殊的间隙锁，表示"我要在这个间隙中的某个位置插入"。

多个插入意向锁互相兼容（不同位置的插入不互斥）。但插入意向锁与间隙锁冲突。

5. 锁的加锁规则

理解 InnoDB 在不同情况下加什么锁，是避免死锁的关键。

5.1 原则（MySQL 8.0，RR 隔离级别）

原则 1：加锁的基本单位是 Next-Key Lock
原则 2：只有访问到的对象才会加锁
原则 3：索引上的等值查询，给唯一索引加锁时，Next-Key Lock 退化为 Record Lock
原则 4：索引上的等值查询，向右遍历到不满足条件的值时，退化为 Gap Lock

5.2 案例分析

假设表结构：

CREATE TABLE t (
  id   INT PRIMARY KEY,
  c    INT,
  d    INT,
  INDEX idx_c (c)
);
-- 数据：(0,0,0), (5,5,5), (10,10,10), (15,15,15), (20,20,20), (25,25,25)

案例 1：主键等值查询，行存在

BEGIN;
SELECT * FROM t WHERE id = 5 FOR UPDATE;
-- 加锁：id=5 的 Record Lock（Next-Key Lock 退化，因为唯一索引等值）
-- 不影响其他行的插入/删除

案例 2：主键等值查询，行不存在

BEGIN;
SELECT * FROM t WHERE id = 7 FOR UPDATE;
-- 加锁：(5, 10) 的 Gap Lock（id=7 不存在，等值查询退化为 Gap Lock）
-- 阻止在 (5, 10) 间插入

案例 3：主键范围查询

BEGIN;
SELECT * FROM t WHERE id >= 10 AND id < 11 FOR UPDATE;
-- id=10 等值：Record Lock（原则3）
-- id=11 不存在，范围查询：(10, 15) Gap Lock（原则4）
-- 总加锁：id=10 Record Lock + (10, 15) Gap Lock

案例 4：普通索引等值查询

BEGIN;
SELECT id FROM t WHERE c = 5 LOCK IN SHARE MODE;
-- 在 c 索引上：Next-Key Lock (0, 5]，然后向右扫描到 c=10 不满足，退化为 Gap Lock (5, 10)
-- 在主键索引上：id=5 的 Record Lock
-- 总加锁：c 索引 (0,5] + (5,10)，主键 id=5

5.3 特殊场景

-- LIMIT 减少加锁范围
-- 不带 LIMIT：锁住所有满足条件的行及间隙
DELETE FROM t WHERE c = 5;

-- 带 LIMIT 1：找到第一条就停止，减少间隙锁范围
DELETE FROM t WHERE c = 5 LIMIT 1;

6. 死锁

6.1 死锁的产生

-- 事务 A                    事务 B
BEGIN;                       BEGIN;
UPDATE t SET d=1 WHERE id=1; -- 获得 id=1 X Lock
                             UPDATE t SET d=2 WHERE id=2; -- 获得 id=2 X Lock
UPDATE t SET d=2 WHERE id=2; -- 等待 id=2 X Lock（B持有）
                             UPDATE t SET d=1 WHERE id=1; -- 等待 id=1 X Lock（A持有）
-- 死锁！

6.2 死锁检测与处理

InnoDB 有自动死锁检测（deadlock detection）：

自动检测等待图中的环
选择回滚代价小的事务（回滚 Undo Log 少的那个）
被回滚的事务收到 ERROR 1213: Deadlock found

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS\G
-- 找 LATEST DETECTED DEADLOCK 部分

-- 关闭自动检测（高并发下死锁检测本身有性能开销，可以关闭改用超时）
innodb_deadlock_detect = OFF
innodb_lock_wait_timeout = 5  -- 等待 5s 超时自动回滚

6.3 避免死锁的方法

方法 1：固定加锁顺序

// ❌ 错误：两个 goroutine 以不同顺序访问资源
// goroutine 1: lock(A) → lock(B)
// goroutine 2: lock(B) → lock(A)

// ✅ 正确：统一顺序（按 ID 升序）
func transfer(tx *sql.Tx, fromID, toID int64) error {
    // 总是先锁 ID 小的账户
    first, second := fromID, toID
    if fromID > toID {
        first, second = toID, fromID
    }
    // SELECT ... FOR UPDATE 按固定顺序
    tx.QueryRow("SELECT balance FROM accounts WHERE id = ? FOR UPDATE", first)
    tx.QueryRow("SELECT balance FROM accounts WHERE id = ? FOR UPDATE", second)
    // ...
}

方法 2：减小事务粒度

// ❌ 错误：在事务中做复杂业务逻辑，持锁时间长
tx.Begin()
result := complexBusinessLogic()  // 持锁期间执行复杂逻辑
tx.Exec("UPDATE ...")
tx.Commit()

// ✅ 正确：事务内只做必要的数据库操作
result := complexBusinessLogic()  // 事务外计算
tx.Begin()
tx.Exec("UPDATE ...", result)
tx.Commit()

方法 3：使用 SELECT … FOR UPDATE SKIP LOCKED 实现任务队列

-- 多个 worker 并发取任务，互不阻塞
START TRANSACTION;
SELECT id FROM tasks
WHERE status = 'pending'
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- 跳过被其他 worker 锁定的行

UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;

方法 4：一次性锁定所有需要的行

-- ❌ 分步加锁，容易死锁
SELECT ... FOR UPDATE WHERE id = 1;
-- 其他操作
SELECT ... FOR UPDATE WHERE id = 2;

-- ✅ 一次性锁定
SELECT ... FOR UPDATE WHERE id IN (1, 2) ORDER BY id;

6.4 Go 中处理死锁错误

import "github.com/go-sql-driver/mysql"

func isDeadlock(err error) bool {
    if err == nil {
        return false
    }
    var mysqlErr *mysql.MySQLError
    if errors.As(err, &mysqlErr) {
        return mysqlErr.Number == 1213  // ER_LOCK_DEADLOCK
    }
    return false
}

func isLockTimeout(err error) bool {
    var mysqlErr *mysql.MySQLError
    if errors.As(err, &mysqlErr) {
        return mysqlErr.Number == 1205  // ER_LOCK_WAIT_TIMEOUT
    }
    return false
}

// 带重试的事务
func executeWithRetry(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error, maxRetries int) error {
    var err error
    for i := 0; i <= maxRetries; i++ {
        err = WithTransaction(ctx, db, fn)
        if err == nil {
            return nil
        }
        if isDeadlock(err) || isLockTimeout(err) {
            // 指数退避
            time.Sleep(time.Duration(1<<uint(i)) * 10 * time.Millisecond)
            continue
        }
        return err  // 非锁相关错误，直接返回
    }
    return fmt.Errorf("max retries exceeded: %w", err)
}

7. 锁监控与诊断

7.1 查看当前锁信息

-- 当前持有的锁（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks\G

-- 当前锁等待
SELECT * FROM performance_schema.data_lock_waits\G

-- sys 库简化视图
SELECT * FROM sys.innodb_lock_waits\G

-- 综合视图：谁在等谁
SELECT
  r.trx_id                AS waiting_trx_id,
  r.trx_mysql_thread_id   AS waiting_thread,
  r.trx_query             AS waiting_query,
  b.trx_id                AS blocking_trx_id,
  b.trx_mysql_thread_id   AS blocking_thread,
  b.trx_query             AS blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

7.2 InnoDB 状态中的锁信息

SHOW ENGINE INNODB STATUS\G

-- 关注以下部分：
-- TRANSACTIONS：当前活跃事务
-- LOCK WAIT：锁等待
-- LATEST DETECTED DEADLOCK：最近死锁信息

7.3 开启锁超时警告

-- 设置合理的等待超时
SET innodb_lock_wait_timeout = 10;  -- 秒，默认 50

-- performance_schema 开启锁统计
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%wait/lock%';

8. 乐观锁 vs 悲观锁

8.1 悲观锁

认为并发冲突一定会发生，先加锁再操作。

-- 悲观锁：SELECT ... FOR UPDATE
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 加 X 锁，其他事务等待
UPDATE orders SET status = 1 WHERE id = 1;
COMMIT;

适用场景：写多读少，冲突频繁，业务不能容忍重试。

8.2 乐观锁

认为并发冲突很少发生，不提前加锁，提交时验证数据没有被修改。

-- 方案 1：版本号
CREATE TABLE orders (
  id      INT PRIMARY KEY,
  amount  DECIMAL(10,2),
  version INT NOT NULL DEFAULT 0
);

-- 读取时获取版本号
SELECT id, amount, version FROM orders WHERE id = 1;

-- 更新时检查版本号（CAS）
UPDATE orders
SET amount = 200, version = version + 1
WHERE id = 1 AND version = 3;  -- version 是之前读到的值
-- 若 affected rows = 0，说明被其他事务修改了，需要重试或报错

-- 方案 2：时间戳
UPDATE products
SET stock = stock - 1, updated_at = NOW()
WHERE id = ? AND updated_at = ?  -- updated_at 是之前读到的值

适用场景：读多写少，冲突少，业务可以接受重试。

8.3 Go 实现乐观锁

type Order struct {
    ID      int64
    Amount  float64
    Version int
}

func UpdateOrderOptimistic(db *sql.DB, order Order, newAmount float64) error {
    const maxRetries = 3
    for i := 0; i < maxRetries; i++ {
        result, err := db.Exec(
            "UPDATE orders SET amount = ?, version = version + 1 WHERE id = ? AND version = ?",
            newAmount, order.ID, order.Version,
        )
        if err != nil {
            return err
        }
        n, err := result.RowsAffected()
        if err != nil {
            return err
        }
        if n > 0 {
            return nil  // 成功
        }
        // 更新失败，重新读取最新版本
        err = db.QueryRow("SELECT id, amount, version FROM orders WHERE id = ?", order.ID).
            Scan(&order.ID, &order.Amount, &order.Version)
        if err != nil {
            return err
        }
        // 重试
        time.Sleep(time.Duration(i+1) * 10 * time.Millisecond)
    }
    return errors.New("update failed after max retries: concurrent modification")
}

9. Go 并发场景实战

9.1 秒杀/库存扣减

// 方案 1：悲观锁（SELECT FOR UPDATE）
func DeductStockPessimistic(ctx context.Context, db *sql.DB, productID int64, qty int) error {
    return WithTransaction(ctx, db, func(tx *sql.Tx) error {
        var stock int
        err := tx.QueryRowContext(ctx,
            "SELECT stock FROM products WHERE id = ? FOR UPDATE",
            productID,
        ).Scan(&stock)
        if err != nil {
            return err
        }
        if stock < qty {
            return errors.New("insufficient stock")
        }
        _, err = tx.ExecContext(ctx,
            "UPDATE products SET stock = stock - ? WHERE id = ?",
            qty, productID,
        )
        return err
    })
}

// 方案 2：直接 UPDATE 加条件（原子操作，无需事务加锁）
func DeductStockAtomic(ctx context.Context, db *sql.DB, productID int64, qty int) error {
    result, err := db.ExecContext(ctx,
        "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?",
        qty, productID, qty,
    )
    if err != nil {
        return err
    }
    n, err := result.RowsAffected()
    if err != nil {
        return err
    }
    if n == 0 {
        return errors.New("insufficient stock")
    }
    return nil
}

9.2 任务队列（SKIP LOCKED）

func FetchTask(ctx context.Context, db *sql.DB) (*Task, error) {
    var task Task
    err := WithTransaction(ctx, db, func(tx *sql.Tx) error {
        err := tx.QueryRowContext(ctx, `
            SELECT id, payload
            FROM tasks
            WHERE status = 'pending'
            ORDER BY created_at ASC
            LIMIT 1
            FOR UPDATE SKIP LOCKED
        `).Scan(&task.ID, &task.Payload)
        if err == sql.ErrNoRows {
            return nil
        }
        if err != nil {
            return err
        }
        _, err = tx.ExecContext(ctx,
            "UPDATE tasks SET status = 'processing', started_at = NOW() WHERE id = ?",
            task.ID,
        )
        return err
    })
    return &task, err
}

9.3 防止重复提交（幂等性）

// 利用唯一索引实现幂等插入
func CreateOrderIdempotent(ctx context.Context, db *sql.DB, reqID string, order Order) error {
    _, err := db.ExecContext(ctx, `
        INSERT IGNORE INTO orders (request_id, user_id, amount, status)
        VALUES (?, ?, ?, 'pending')
    `, reqID, order.UserID, order.Amount)
    // INSERT IGNORE：如果 request_id 已存在（唯一索引），静默忽略
    return err
}

小结

锁类型	适用场景	粒度	兼容性
Global Lock	全库备份	全库	与所有锁冲突
MDL Read	DML/查询	表（元数据）	互相兼容
MDL Write	DDL	表（元数据）	与所有冲突
Table Lock	MyISAM	整表	S/S 兼容
Record Lock	行等值操作	行	S/S 兼容
Gap Lock	防幻读（RR）	间隙	间隙间兼容
Next-Key Lock	RR 默认加锁单位	行+间隙	—

死锁预防：

固定加锁顺序
缩小事务粒度，减少持锁时间
一次性锁定所有需要的行
应用层重试机制

目录