MySQL 各种锁详解

理解 MySQL 锁机制是解决并发问题、避免死锁的基础。本文全面解析 MySQL 的各种锁类型及其应用场景。

MySQL 锁概述

锁的分类

MySQL 锁
├── 按粒度分
│   ├── 全局锁（Global Lock）
│   ├── 表级锁（Table Lock）
│   │   ├── 表锁（Table Lock）
│   │   ├── 意向锁（Intention Lock）
│   │   └── AUTO-INC 锁
│   └── 行级锁（Row Lock）
│       ├── 记录锁（Record Lock）
│       ├── 间隙锁（Gap Lock）
│       └── 临键锁（Next-Key Lock）
├── 按属性分
│   ├── 共享锁（S Lock）
│   └── 排他锁（X Lock）
└── 按算法分
    ├── 记录锁
    ├── 间隙锁
    └── 临键锁

事务隔离级别与锁

sql复制
-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 隔离级别与锁的关系
-- READ UNCOMMITTED: 几乎不加锁
-- READ COMMITTED: 只锁记录（Record Lock）
-- REPEATABLE READ: 锁记录 + 间隙（Next-Key Lock）默认
-- SERIALIZABLE: 所有读都加锁

全局锁（Global Lock）

FTWRL（Flush Tables With Read Lock）

sql复制
-- 加全局读锁
FLUSH TABLES WITH READ LOCK;

-- 释放锁
UNLOCK TABLES;

-- 特性：
-- 1. 整个数据库实例变成只读
-- 2. 所有表被锁定
-- 3. DDL、DML 都会被阻塞
-- 4. 用于全库备份、导出

全局锁应用场景

sql复制
-- 正确备份流程
-- 1. 加全局锁
FLUSH TABLES WITH READ LOCK;

-- 2. 执行备份（mysqldump）
-- mysqldump -uroot -p --single-transaction --master-data=2 db_name > backup.sql

-- 3. 释放锁
UNLOCK TABLES;

-- ⚠️ 注意：会导致主从延迟，对业务有影响

mysqldump 的替代方案

bash复制
# 使用 --single-transaction（InnoDB 推荐）
mysqldump -uroot -p \
    --single-transaction \
    --master-data=2 \
    --triggers \
    --routines \
    --events \
    db_name > backup.sql

# 原理：
# 1. 设置事务隔离级别为 REPEATABLE READ
# 2. 开启一致性快照读
# 3. 不加全局锁，对业务无影响
# 4. 只适用于 InnoDB 引擎

表级锁（Table Lock）

显式表锁

sql复制
-- 读锁（共享锁）
LOCK TABLES orders READ;

-- 写锁（排他锁）
LOCK TABLES orders WRITE;

-- 释放锁
UNLOCK TABLES;

-- 读锁特性：
-- - 其他线程可以读，不能写
-- - 当前线程可以读，不能写

-- 写锁特性：
-- - 其他线程读、写都被阻塞
-- - 当前线程可以读、写

MDL（元数据锁）

sql复制
-- MDL 是 MySQL 自动加的锁
-- 读事务获取 MDL 读锁
BEGIN;
SELECT * FROM orders WHERE id = 1;  -- 获取 MDL 读锁

-- 写事务需要 MDL 写锁（阻塞）
ALTER TABLE orders ADD COLUMN remark VARCHAR(500);  -- 等待 MDL 读锁释放

-- 读锁与读锁不互斥
-- 读锁与写锁互斥
-- 写锁与写锁互斥

MDL 锁问题排查

sql复制
-- 查看 MDL 锁状态
SELECT 
    t.processlist_id,
    t.thd_id,
    t.`NAME`,
    m.`LOCK_STATUS`,
    m.`LOCK_MODE`,
    m.`LOCK_TYPE`,
    m.`LOCK_TABLE`,
    m.`LOCK_INDEX`,
    m.`LOCK_DATA`
FROM performance_schema.`metadata_locks` m
JOIN performance_schema.`threads` t ON m.THREAD_ID = t.THREAD_ID
WHERE m.`LOCK_TYPE` = 'SHARED' OR m.`LOCK_TYPE` = 'EXCLUSIVE';

-- 查看长事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_started < DATE_SUB(NOW(), INTERVAL 1 HOUR);

意向锁（Intention Lock）

sql复制
-- 意向锁是表锁，由存储引擎自动维护
-- 加行锁前先加意向锁

-- 事务 A：SELECT ... FOR UPDATE（获取行排他锁）
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 自动获得：IX锁（意向排他锁）

-- 事务 B：LOCK TABLE orders WRITE
-- 检查 IX 锁存在，意识到表不能加写锁，阻塞等待

-- 意向锁的兼容性
--           IS    IX    S     X
-- IS        兼容  兼容  兼容  互斥
-- IX        兼容  兼容  互斥  互斥
-- S         兼容  互斥  兼容  互斥
-- X         互斥  互斥  互斥  互斥

AUTO-INC 锁

sql复制
-- 自增锁是表锁，插入数据时自动获取
INSERT INTO orders (user_id, amount) VALUES (1, 100);
INSERT INTO orders (user_id, amount) VALUES (1, 100);

-- 获取方式：
-- 1. traditional 模式：INSERT 执行前加表锁
-- 2. consecutive 模式（默认）：批量插入只加一次锁
-- 3. interleaved 模式：完全不锁，并发最高但可能主从不一致

SET GLOBAL innodb_autoinc_lock_mode = 2;  -- 推荐用于主从复制

行级锁（Row Lock）

记录锁（Record Lock）

sql复制
-- 记录锁锁定索引记录
-- 即使表没有索引，InnoDB 会用隐式主键锁定

-- 排他记录锁
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 锁定 id=1 这条记录

-- 其他事务尝试修改被锁定记录会被阻塞
-- UPDATE orders SET status = 2 WHERE id = 1;  -- 阻塞

-- 提交或回滚后释放
COMMIT;  -- 释放锁

记录锁兼容矩阵

间隙锁（Gap Lock）

sql复制
-- 间隙锁锁定索引记录之间的间隙
-- 范围查询（未匹配记录）时产生

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 假设 orders 表有 id: 1, 5, 10
SELECT * FROM orders WHERE id > 8 AND id < 12 FOR UPDATE;
-- 锁定区间 (10, +∞) 和 [5, 10] 之间的间隙
-- 其他事务无法在这些间隙插入记录

-- 插入 id = 11 的操作会被阻塞
INSERT INTO orders (id, user_id) VALUES (11, 1);  -- 阻塞等待间隙锁释放

临键锁（Next-Key Lock）

sql复制
-- 临键锁 = 记录锁 + 间隙锁
-- 是 REPEATABLE READ 隔离级别下的默认锁算法

-- 假设 orders 表 id: 1, 5, 10
SELECT * FROM orders WHERE id <= 10 FOR UPDATE;
-- 锁定：(1, 5], (5, 10], (10, +∞)

-- 临键锁的作用：
-- 1. 防止幻读（Phantom Read）
-- 2. 锁定范围内不允许插入新记录

-- 关闭临键锁
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 退化为只锁记录

临键锁锁定示例

表 orders 数据：
id: 1, 3, 5, 7, 10

执行: SELECT * FROM orders WHERE id < 5 FOR UPDATE;

临键锁锁定区间：
(-∞, 1]  -- 记录锁
(1, 3]   -- 临键锁
(3, 5]   -- 临键锁
(5, +∞)  -- 间隙锁

阻止的操作：
- 插入 id = 2, 4 会被阻塞
- 插入 id = 6 会被阻塞
- 更新 id = 3 会被阻塞

锁等待与死锁

查看锁等待

sql复制
-- 查看当前锁等待
SELECT 
    r.trx_id,
    r.trx_mysql_thread_id,
    r.trx_state,
    r.trx_started,
    r.trx_rows_locked,
    r.trx_query,
    l.lock_id,
    l.lock_mode,
    l.lock_type,
    l.lock_table
FROM information_schema.INNODB_TRX r
JOIN information_schema.INNODB_LOCKS l ON r.trx_id = l.lock_trx_id
WHERE r.trx_state = 'LOCK WAIT';

-- 查看事务等待关系
SELECT 
    waiting.trx_id AS waiting_trx_id,
    waiting.trx_mysql_thread_id AS waiting_thread,
    waiting.trx_query AS waiting_query,
    blocking.trx_id AS blocking_trx_id,
    blocking.trx_mysql_thread_id AS blocking_thread,
    blocking.trx_query AS blocking_query
FROM information_schema.INNODB_TRX waiting
JOIN information_schema.INNODB_TRX blocking ON blocking.trx_id != waiting.trx_id
WHERE waiting.trx_state = 'LOCK WAIT'
  AND blocking.trx_state = 'RUNNING';

查看死锁日志

sql复制
-- 查看死锁配置
SHOW VARIABLES LIKE 'innodb_print_deadlocks';  -- 默认 OFF

-- 开启死锁日志
SET GLOBAL innodb_print_deadlocks = ON;

-- 死锁发生时查看错误日志
-- MySQL 会在 error.log 中打印死锁信息

-- 最近一次死锁信息
SHOW ENGINE INNODB STATUS;

-- 输出示例：
-- ------------------------
-- LATEST DETECTED DEADLOCK
-- ------------------------
-- Transaction 1: has lock at ... and waiting
-- Transaction 2: has lock at ... and waited for
-- ... 详细锁信息

死锁示例与解决

sql复制
-- 死锁场景
-- 事务 A
BEGIN;
UPDATE orders SET status = 1 WHERE id = 1;  -- 锁定 id=1
UPDATE orders SET status = 1 WHERE id = 2;  -- 等待 id=2

-- 事务 B
BEGIN;
UPDATE orders SET status = 2 WHERE id = 2;  -- 锁定 id=2
UPDATE orders SET status = 2 WHERE id = 1;  -- 死锁！等待 id=1

-- 解决死锁：
-- 1. 等待 innodb_lock_wait_timeout（默认 50 秒）
-- 2. MySQL 自动检测死锁，回滚较小事务
-- 3. 业务层重试

避免死锁的方法

sql复制
-- 1. 按固定顺序访问资源
-- ❌ 错误：不同事务以不同顺序访问
UPDATE orders SET status = 1 WHERE id = 1;
UPDATE orders SET status = 1 WHERE id = 2;

-- ✅ 正确：所有事务都按 id 顺序访问
UPDATE orders SET status = 1 WHERE id = 1;
UPDATE orders SET status = 1 WHERE id = 2;

-- 2. 降低隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 3. 减小事务范围
-- ❌ 长事务
BEGIN;
SELECT * FROM orders WHERE id IN (1,2,3,4,5);  -- 锁定多行
-- ... 很多操作 ...
COMMIT;

-- ✅ 小事务
BEGIN;
UPDATE orders SET status = 1 WHERE id = 1;
COMMIT;

BEGIN;
UPDATE orders SET status = 1 WHERE id = 2;
COMMIT;

-- 4. 添加合适索引
-- 索引能减少锁定范围
-- 无索引会锁全表或大范围

锁与 MVCC 的关系

MVCC 实现原理

sql复制
-- InnoDB 每一行都有两个隐藏列：
-- 1. DB_TRX_ID: 最后修改的事务 ID
-- 2. DB_ROLL_PTR: 指向 undo log 的指针

-- 读取数据时：
-- - READ COMMITTED: 每次 SELECT 都生成新快照
-- - REPEATABLE READ: 事务开始时生成快照，整个事务用同一个快照

-- 示例：REPEATABLE READ 下的读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

-- 快照 1（事务开始时的数据版本）
SELECT * FROM orders WHERE id = 1;  -- status = 1

-- 其他事务修改了这条记录
-- UPDATE orders SET status = 2 WHERE id = 1;  -- 提交

-- 再次读取，还是看到 status = 1（快照读）
SELECT * FROM orders WHERE id = 1;  -- status = 1（没变）

COMMIT;  -- 提交后快照失效

快照读 vs 当前读

sql复制
-- 快照读（普通 SELECT）：不加锁，利用 MVCC
SELECT * FROM orders WHERE id = 1;  -- 读取快照

-- 当前读（加锁读）：获取最新数据，加锁保护
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 锁定记录
UPDATE orders SET status = 2 WHERE id = 1;      -- 当前读
DELETE FROM orders WHERE id = 1;                -- 当前读

-- INSERT：也是当前读，获取自增锁
INSERT INTO orders (user_id) VALUES (1);  -- AUTO-INC 锁

-- REPLACE：当前读
REPLACE INTO orders (id, user_id) VALUES (1, 1);  -- 锁定 + 删除 + 插入

常见面试问题

Q1: 行锁是通过锁索引实现的？

是的，InnoDB 的行锁是基于索引的：

sql复制
-- 有索引，锁索引记录
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 只锁 id=1

-- 无索引或索引失效，锁全表
SELECT * FROM orders WHERE user_id = 1 FOR UPDATE;  -- user_id 无索引，锁全表

-- 建议：确保 WHERE 条件有合适索引
ALTER TABLE orders ADD INDEX idx_user_id(user_id);

Q2: SELECT 语句会加锁吗？

取决于隔离级别和查询类型：

sql复制
-- 普通 SELECT（快照读）：不加锁
SELECT * FROM orders WHERE id = 1;  -- 无锁

-- 加锁 SELECT（当前读）：加锁
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- X 锁
SELECT * FROM orders WHERE id = 1 LOCK IN SHARE MODE;  -- S 锁

-- 在 SERIALIZABLE 级别：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM orders WHERE id = 1;  -- 自动加 S 锁

Q3: update 语句的锁流程？

sql复制
UPDATE orders SET status = 2 WHERE id = 1;

-- 执行过程：
-- 1. 先读取 id=1 的记录（当前读，加 X 锁）
-- 2. 检查更新条件是否满足
-- 3. 更新记录（X 锁持有）
-- 4. 事务提交/回滚时释放锁

-- 如果 id=1 已经被其他事务锁定：
UPDATE orders SET status = 2 WHERE id = 1;  -- 阻塞等待

Q4: 如何排查锁等待超时？

sql复制
-- 1. 查看超时配置
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';  -- 默认 50 秒

-- 2. 查看当前锁等待
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'LOCK WAIT';

-- 3. 查看阻塞者
SELECT 
    blocking.trx_id AS blocking_trx_id,
    blocking.trx_mysql_thread_id AS blocking_thread_id,
    blocking.trx_query AS blocking_query,
    blocked.trx_id AS blocked_trx_id,
    blocked.trx_mysql_thread_id AS blocked_thread_id,
    blocked.trx_query AS blocked_query
FROM information_schema.INNODB_TRX blocked
JOIN information_schema.INNODB_TRX blocking ON blocking.trx_id = blocked.trx_blocked_by_trx;

-- 4. kill 阻塞的线程
KILL <blocking_thread_id>;

Q5: RC 级别下如何避免幻读？

sql复制
-- READ COMMITTED 下没有间隙锁
-- 需要业务层面或应用层面处理

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

-- 使用锁定读避免幻读
SELECT * FROM orders WHERE id BETWEEN 1 AND 100 FOR UPDATE;

-- 或者使用应用层乐观锁
-- 添加版本号字段
ALTER TABLE orders ADD COLUMN version INT DEFAULT 0;

-- 更新时检查版本
UPDATE orders 
SET status = 2, version = version + 1 
WHERE id = 1 AND version = 5;

总结

理解锁机制的关键：

1. 索引决定锁范围 - 索引失效会锁全表
2. 隔离级别决定锁算法 - RC 没有间隙锁
3. 小事务减少锁冲突 - 事务越小，持有锁时间越短
4. 统一访问顺序避免死锁 - 按固定顺序访问资源