MySQL 锁机制

锁是数据库实现并发控制的核心机制。MySQL 使用多层次的锁策略来平衡并发性能和数据一致性。理解锁机制对于编写高效 SQL 和排查死锁问题至关重要。

1. 锁的分类

1.1 按粒度分类

锁类型	粒度	并发度	冲突概率	适用引擎
表级锁	表	低	高	MyISAM、Memory
行级锁	行	高	低	InnoDB
页级锁	页（数据页）	中	中	BDB

1.2 按功能分类

锁类型	说明
共享锁（S Lock）	读锁，允许其他事务读取，但不允许修改
排他锁（X Lock）	写锁，不允许其他事务读取或修改
意向共享锁（IS Lock）	表级锁，表示事务打算在表中的某些行上加 S 锁
意向排他锁（IX Lock）	表级锁，表示事务打算在表中的某些行上加 X 锁
自增锁（AUTO-INC Lock）	插入自增列时使用

1.3 锁兼容矩阵

	IS	IX	S	X
IS	兼容	兼容	兼容	冲突
IX	兼容	兼容	冲突	冲突
S	兼容	冲突	兼容	冲突
X	冲突	冲突	冲突	冲突

2. 表级锁

2.1 MyISAM 表锁

MyISAM 使用表级锁，读写互斥。

sql

-- 加读锁（共享锁）
LOCK TABLES users READ;
-- 其他会话可以读，不能写
UNLOCK TABLES;

-- 加写锁（排他锁）
LOCK TABLES users WRITE;
-- 其他会话不能读也不能写
UNLOCK TABLES;

MyISAM 锁调度：

写锁优先级高于读锁（即使读请求先到，写请求也会被优先处理）。
可通过 LOW_PRIORITY 降低写优先级，HIGH_PRIORITY 提高读优先级。

2.2 InnoDB 表级锁

InnoDB 虽然主要使用行级锁，但在某些情况下也会使用表级锁。

sql

-- 显式加表锁
LOCK TABLES users READ;
LOCK TABLES users WRITE;

-- 在执行 DDL 时自动加表锁（MySQL 5.5 及之前）
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- MySQL 5.6+ 支持 Online DDL，不锁表

3. InnoDB 行级锁

3.1 行锁实现原理

InnoDB 的行锁是通过索引实现的。只有通过索引检索数据时，InnoDB 才使用行级锁，否则退化为表级锁。

sql

-- 假设 id 是主键
UPDATE users SET age = 26 WHERE id = 1;
-- 只锁 id=1 这一行

-- 假设 name 没有索引
UPDATE users SET age = 26 WHERE name = 'Alice';
-- 退化为表锁，锁住所有行

3.2 共享锁（S Lock）与排他锁（X Lock）

共享锁（Shared Lock）：

允许持有锁的事务读取行。
多个事务可以同时持有同一行的 S 锁。
S 锁与 X 锁互斥。

排他锁（Exclusive Lock）：

允许持有锁的事务更新或删除行。
同一时刻只有一个事务能持有 X 锁。
X 锁与任何锁互斥。

sql

-- 加共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0.22+ 推荐写法
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- 加排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

3.3 Record Lock（记录锁）

记录锁是最基本的行锁，锁定的是索引记录，而不是数据行本身。

sql

-- 锁定 id=1 的索引记录
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 加 X 锁，其他事务不能修改 id=1 的行

3.4 Gap Lock（间隙锁）

间隙锁锁定的是索引记录之间的间隙，防止其他事务在这个间隙中插入数据，从而解决幻读问题。

间隙锁特点：

只在 REPEATABLE READ 隔离级别下生效。
锁定的是区间，而不是具体记录。
间隙锁之间不冲突（多个事务可以在同一间隙加锁）。

sql

-- 假设 id 有值：1, 5, 10, 15
-- 间隙区间：(-inf, 1), (1, 5), (5, 10), (10, 15), (15, +inf)

-- 锁定 id > 5 AND id < 10 的间隙
SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE;
-- 其他事务不能在 (5, 10) 之间插入 id=6,7,8,9

3.5 Next-Key Lock（临键锁）

临键锁是 Record Lock + Gap Lock 的组合，锁定一个左开右闭的区间。

临键锁规则：

锁定当前记录 + 当前记录之前的间隙。
区间格式：(prev_key, current_key]。

sql

-- 假设 id 有值：1, 5, 10, 15
-- 临键锁区间：
-- (-inf, 1], (1, 5], (5, 10], (10, 15], (15, +inf)

-- 当 WHERE id = 10 时，临键锁为 (5, 10]
-- 当 WHERE id = 5 AND id < 10 时，锁定 (1, 5], (5, 10]

规则总结：

查询条件	等值查询（命中）	等值查询（未命中）	范围查询
主键/唯一索引	Record Lock	Gap Lock	Next-Key Lock
普通索引	Next-Key Lock + Gap Lock	Gap Lock	Next-Key Lock

3.6 插入意向锁（Insert Intention Lock）

插入意向锁是一种特殊的间隙锁，在 INSERT 操作时自动加锁。多个事务可以在同一间隙加插入意向锁，前提是插入的位置不冲突。

sql

-- 事务 A：INSERT INTO users (id) VALUES (6)
-- 事务 B：INSERT INTO users (id) VALUES (7)
-- 都在 (5, 10) 间隙内，但插入位置不同，不冲突

4. 意向锁（Intention Lock）

4.1 为什么需要意向锁

意向锁是表级锁，用于解决表锁和行锁之间的冲突检测问题。

场景：

事务 A 对某行加了 X 锁，需要对表加 IX 意向锁。
事务 B 想对整张表加 X 锁，先检查表上是否有意向锁。
如果表上有 IX 锁，说明有行被加了 X 锁，事务 B 无法加表级 X 锁。

没有意向锁时：需要遍历所有行检查是否有锁，效率极低。

4.2 意向锁规则

事务在给行加 S 锁之前，必须先获取表的 IS 锁。
事务在给行加 X 锁之前，必须先获取表的 IX 锁。
意向锁之间兼容（IS 和 IX 兼容），意向锁与表级锁之间可能冲突。

5.1 SELECT FOR UPDATE

sql

-- 对查询结果加排他锁（X Lock）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 支持 NOWAIT：如果锁被占用，立即报错而不是等待
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- 支持 SKIP LOCKED：跳过已锁定的行
SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED;

使用场景：

读取后即将修改，防止被其他事务修改。
实现乐观锁检查失败后的悲观锁回退。

sql

-- 对查询结果加共享锁（S Lock）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0.22+ 推荐
SELECT * FROM users WHERE id = 1 FOR SHARE;

使用场景：

读取后可能修改，但允许其他事务也读取。
防止其他事务修改数据，但自己不立即修改。

5.3 使用注意事项

必须在事务中使用：FOR UPDATE 和 FOR SHARE 必须在事务中（autocommit=0 或显式 BEGIN）。
尽快提交事务：锁持有时间越长，并发性能越差。
避免锁定范围过大：WHERE 条件尽量精确，使用索引。
NOWAIT 和 SKIP LOCKED：MySQL 8.0+ 支持，提高并发场景下的用户体验。

6. 死锁

6.1 死锁产生的原因

死锁是指两个或多个事务相互等待对方持有的锁，导致所有事务都无法继续执行。

经典死锁场景：

sql

-- 事务 A
BEGIN;
UPDATE users SET age = 26 WHERE id = 1;  -- 获取 id=1 的 X 锁
-- 事务 B
BEGIN;
UPDATE users SET age = 31 WHERE id = 2;  -- 获取 id=2 的 X 锁

-- 事务 A
UPDATE users SET age = 27 WHERE id = 2;  -- 等待 id=2 的 X 锁
-- 事务 B
UPDATE users SET age = 32 WHERE id = 1;  -- 等待 id=1 的 X 锁

-- 死锁！A 等 B，B 等 A

死锁产生的四个必要条件：

互斥：资源不能被共享。
持有并等待：事务持有锁的同时等待其他锁。
不可剥夺：已获得的锁不能被强制释放。
循环等待：事务之间形成循环等待链。

6.2 死锁检测

InnoDB 自动检测死锁，当发现死锁时，选择一个代价较小的事务回滚，释放锁。

sql

-- 查看死锁检测开关
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

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

-- MySQL 8.0+ 查看死锁日志
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

死锁检测的代价：

当大量事务同时等待时，死锁检测算法复杂度为 O(N^2)。
高并发下可能出现 CPU 飙升，可考虑关闭死锁检测 + 设置锁超时。

sql

-- 方案一：关闭死锁检测，使用锁超时
SET GLOBAL innodb_deadlock_detect = OFF;
SET GLOBAL innodb_lock_wait_timeout = 5;  -- 5 秒超时

-- 方案二：MySQL 8.0.18+ 使用死锁检测的批处理优化
SET GLOBAL innodb_deadlock_detect = ON;

6.3 死锁避免策略

固定加锁顺序：所有事务按相同顺序访问表/行，避免循环等待。
缩小事务范围：事务尽量小，减少锁持有时间。
使用索引：避免行锁升级为表锁，减少锁范围。
低隔离级别：READ COMMITTED 没有间隙锁，死锁概率更低。
批量操作分批：大批量 UPDATE/DELETE 分批执行。
重试机制：应用层捕获死锁异常，重试事务。

java

// Java 死锁重试示例
int retry = 3;
while (retry-- > 0) {
    try {
        // 执行数据库操作
        break;
    } catch (DeadlockLoserDataAccessException e) {
        if (retry == 0) throw e;
        Thread.sleep(100);  // 等待后重试
    }
}

6.4 锁等待超时

sql

-- 查看锁等待超时时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
-- 默认 50 秒

-- 设置锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 10;
SET SESSION innodb_lock_wait_timeout = 10;

6.5 锁信息查询

sql

-- MySQL 5.7 及之前
SELECT * FROM information_schema.INNODB_LOCKS;       -- 当前持有的锁
SELECT * FROM information_schema.INNODB_LOCK_WAITS;  -- 锁等待信息
SELECT * FROM information_schema.INNODB_TRX;         -- 事务信息

-- MySQL 8.0+
SELECT * FROM performance_schema.data_locks;           -- 锁信息
SELECT * FROM performance_schema.data_lock_waits;      -- 锁等待
SELECT * FROM information_schema.INNODB_TRX;           -- 事务信息

-- 查看锁等待的 SQL
SELECT 
    waiting_trx_id,
    waiting_pid,
    waiting_query,
    blocking_trx_id,
    blocking_pid,
    blocking_query
FROM sys.innodb_lock_waits;

7. 乐观锁与悲观锁

7.1 悲观锁（Pessimistic Lock）

悲观锁假定每次操作都会发生冲突，在操作数据之前先加锁。

实现方式：

SELECT ... FOR UPDATE（行级排他锁）
SELECT ... FOR SHARE（行级共享锁）

sql

BEGIN;
-- 加悲观锁
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 检查库存
-- 扣减库存
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

优点：数据一致性高，不会出现冲突。缺点：锁持有时间长，并发性能差，可能导致死锁。

7.2 乐观锁（Optimistic Lock）

乐观锁假定冲突很少发生，只在提交时检查是否有冲突。

实现方式一：版本号：

sql

-- 添加版本号字段
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;

-- 更新时检查版本号
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 0;

-- 如果 affected_rows = 0，说明版本号已变，需要重试

实现方式二：时间戳：

sql

UPDATE products 
SET stock = stock - 1, update_time = NOW() 
WHERE id = 1 AND update_time = '2024-01-01 12:00:00';

实现方式三：CAS（Compare and Swap）：

sql

-- 直接比较原值
UPDATE products 
SET stock = stock - 1 
WHERE id = 1 AND stock = 10;
-- 如果 affected_rows = 0，说明 stock 已被其他事务修改

优点：不锁定数据，并发性能高。缺点：冲突时需要重试，增加业务复杂度。

7.3 选择建议

场景	推荐	原因
冲突概率高	悲观锁	减少重试开销
冲突概率低	乐观锁	提高并发性能
读多写少	乐观锁	冲突概率低
写多读少	悲观锁	冲突概率高
事务内操作多	悲观锁	后续操作基于锁定的数据
操作简单	乐观锁	重试成本低

8. MVCC 与锁的关系

MVCC（多版本并发控制）和锁是 InnoDB 实现并发控制的两种机制，两者互补。

MVCC 的作用：

实现非锁定一致性读（Consistent Non-Locking Read）。
读操作不加锁，通过 undo log 读取历史版本。
只在 READ COMMITTED 和 REPEATABLE READ 隔离级别下生效。

锁的作用：

写操作必须加锁（X 锁）。
SELECT ... FOR UPDATE / SELECT ... FOR SHARE 显式加锁。
外键检查、唯一约束检查自动加锁。

两者的关系：

普通 SELECT 走 MVCC，不加锁，读写不阻塞。
显式加锁的 SELECT（FOR UPDATE）走锁机制，阻塞其他写操作。
UPDATE/DELETE 写操作加 X 锁，并生成 undo log 供 MVCC 使用。

9. 自增锁（AUTO-INC Lock）

自增锁是一种特殊的表级锁，在插入自增列时使用。

9.1 自增锁模式

sql

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

模式	值	行为
传统模式	0	所有 INSERT 加表级自增锁，语句结束释放
连续模式	1	简单 INSERT 不加锁，批量 INSERT 加锁（默认）
交错模式	2	所有 INSERT 都不加锁，自增值可能不连续

9.2 模式选择

模式 0：兼容性最好，但并发最差。
模式 1（默认）：平衡并发和连续性。
模式 2：并发最高，但自增值可能不连续，且 binlog 必须为 row 格式。

10. 生产环境锁问题排查

10.1 锁等待排查流程

sql

-- 步骤 1：查看当前事务
SELECT * FROM information_schema.INNODB_TRX\G

-- 步骤 2：查看锁等待（MySQL 8.0+）
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- 步骤 3：查看阻塞源的连接
SHOW PROCESSLIST;

-- 步骤 4：杀死阻塞的连接
KILL <blocking_thread>;

10.2 预防措施

合理使用索引：避免行锁升级为表锁。
事务尽量短小：避免长事务持有锁。
避免在事务中执行耗时操作：如远程调用、文件 I/O。
监控锁等待：使用 sys.innodb_lock_waits 定期检查。
设置锁超时：innodb_lock_wait_timeout 不宜设置过大。

11. 总结

InnoDB 通过索引实现行级锁，无索引时退化为表锁。
行锁包括 Record Lock、Gap Lock、Next-Key Lock。
间隙锁和临键锁只在 REPEATABLE READ 隔离级别下生效，用于解决幻读。
死锁不可避免，InnoDB 自动检测，应用层需要重试机制。
乐观锁适合冲突少的场景，悲观锁适合冲突多的场景。
MVCC 实现读不加锁，锁机制保证写操作的正确性。
生产环境应监控锁等待，及时处理长事务。

MySQL 锁机制 ​

1. 锁的分类 ​

1.1 按粒度分类 ​

1.2 按功能分类 ​

1.3 锁兼容矩阵 ​

2. 表级锁 ​

2.1 MyISAM 表锁 ​

2.2 InnoDB 表级锁 ​

3. InnoDB 行级锁 ​

3.1 行锁实现原理 ​

3.2 共享锁（S Lock）与排他锁（X Lock） ​

3.3 Record Lock（记录锁） ​

3.4 Gap Lock（间隙锁） ​

3.5 Next-Key Lock（临键锁） ​

3.6 插入意向锁（Insert Intention Lock） ​

4. 意向锁（Intention Lock） ​

4.1 为什么需要意向锁 ​

4.2 意向锁规则 ​

5. SELECT FOR UPDATE 与 LOCK IN SHARE MODE ​

5.1 SELECT FOR UPDATE ​

5.2 LOCK IN SHARE MODE / FOR SHARE ​

5.3 使用注意事项 ​

6. 死锁 ​

6.1 死锁产生的原因 ​

6.2 死锁检测 ​

6.3 死锁避免策略 ​

6.4 锁等待超时 ​

6.5 锁信息查询 ​

7. 乐观锁与悲观锁 ​

7.1 悲观锁（Pessimistic Lock） ​

7.2 乐观锁（Optimistic Lock） ​

7.3 选择建议 ​

8. MVCC 与锁的关系 ​

9. 自增锁（AUTO-INC Lock） ​

9.1 自增锁模式 ​

9.2 模式选择 ​

10. 生产环境锁问题排查 ​

10.1 锁等待排查流程 ​

10.2 预防措施 ​

11. 总结 ​