MYSQL 锁 详解

MySQL 锁详解

锁是 MySQL 数据库并发控制的核心机制，它通过限制不同事务对同一数据的访问，确保数据的完整性和一致性。MySQL 提供了丰富的锁机制，以满足不同场景下的并发需求。

---

1. 锁的分类

MySQL 中的锁可以按照粒度和功能划分为以下几类：

1.1 按锁的粒度

1.1.1 全局锁

• 锁住整个数据库实例。

• 用途：通常用于备份或迁移。

• 实现：

  FLUSH TABLES WITH READ LOCK;
  UNLOCK TABLES;
  

• 特点：

  • 所有操作（包括写入和读操作）都会被阻塞。
  • 高级别锁，影响全局性能。

---

1.1.2 表级锁

• 锁住整张表。
• 种类：
  • 表锁（Table Lock）：所有线程对表的访问需要等待。
  • 元数据锁（MDL，Metadata Lock）：对表的元数据进行操作（如 ALTER TABLE）时自动加锁。
• 特点：
  • 粒度较粗，性能较差。
  • 适用于表操作（如表结构修改）。

---

1.1.3 行级锁

• 锁住数据表的特定行。
• 种类：
  • 共享锁（S 锁/读锁）：允许其他事务读取该行，但不能修改。
  • 排他锁（X 锁/写锁）：阻止其他事务读取或修改该行。
• 特点：
  • 粒度最细，性能较高。
  • 需要存储引擎支持（如 InnoDB 支持，MyISAM 不支持）。

---

1.2 按锁的功能

1.2.1 共享锁（S 锁）

• 特点：

  • 多个事务可以同时获取共享锁，用于只读操作。
  • 防止数据被其他事务修改。

• 使用：

  SELECT * FROM table_name LOCK IN SHARE MODE;
  

1.2.2 排他锁（X 锁）

• 特点：

  • 只有一个事务可以获取排他锁。
  • 防止数据被其他事务读取或修改。

• 使用：

  SELECT * FROM table_name FOR UPDATE;
  

---

1.3 按锁的实现方式

1.3.1 乐观锁

• 原理：

  • 假设冲突很少，通过版本号或时间戳检测冲突。

• 实现：

  UPDATE table_name
  SET value = 'new_value', version = version + 1
  WHERE id = 1 AND version = current_version;
  

1.3.2 悲观锁

• 原理：

  • 假设冲突频繁，通过加锁防止冲突。

• 实现：

  SELECT * FROM table_name WHERE id = 1 FOR UPDATE;
  

---

2. InnoDB 锁机制

InnoDB 是 MySQL 中常用的存储引擎，支持行级锁和多版本并发控制（MVCC）。

2.1 行锁

• 行锁是通过索引实现的。
• 如果没有使用索引，InnoDB 会退化为表锁。

---

2.2 意向锁

• 定义：在表级别标记事务的锁定意图，用于加速检测锁冲突。
• 类型：
  • 意向共享锁（IS）：事务计划在某些行加共享锁。
  • 意向排他锁（IX）：事务计划在某些行加排他锁。
• 锁兼容性表：

	IS	IX	S	X
IS	✔️	✔️	✔️	❌
IX	✔️	✔️	❌	❌
S	✔️	❌	✔️	❌
X	❌	❌	❌	❌

---

2.3 GAP 锁（间隙锁）

• 定义：锁定索引之间的空隙。

• 用途：

  • 防止幻读。
  • 确保范围查询的稳定性。

• 使用场景：

  SELECT * FROM table_name WHERE id > 10 FOR UPDATE;
  

---

2.4 Next-Key Lock

• 定义：行锁 + 间隙锁。

• 特点：

  • 锁住索引记录和索引之间的间隙。
  • 避免幻读。

• 使用场景：

  SELECT * FROM table_name WHERE id = 5 FOR UPDATE;
  

---

2.5 死锁

• 定义：两个或多个事务互相等待资源，导致无法继续。
• 解决：
  • 超时检测。
  • 按固定顺序加锁。
  • 避免长事务。

---

3. 锁的使用示例

3.1 表级锁

LOCK TABLES table_name READ;   -- 加共享锁
LOCK TABLES table_name WRITE; -- 加排他锁
UNLOCK TABLES;                -- 释放锁

---

3.2 行级锁

SELECT * FROM table_name WHERE id = 1 FOR UPDATE; -- 加排他锁
SELECT * FROM table_name WHERE id = 1 LOCK IN SHARE MODE; -- 加共享锁

---

3.3 GAP 锁

-- 锁住 id 大于 10 的所有行及其间隙
SELECT * FROM table_name WHERE id > 10 FOR UPDATE;

---

4. 锁相关问题

4.1 幻读

• 现象：
  • 一个事务在范围查询后，另一个事务插入了符合范围的数据，导致结果不一致。
• 解决：
  • 使用 Next-Key Lock。
  • 设置隔离级别为 SERIALIZABLE。

---

4.2 死锁

• 现象：

  • 多个事务互相等待锁，导致程序无法继续。

• 解决方案：

  1. 超时检测：事务等待超过一定时间后回滚。

     SET innodb_lock_wait_timeout = 10;
     

  2. 加锁顺序：确保事务以固定顺序获取锁。

---

5. 锁的性能问题

1. 锁冲突：
   • 解决：减少事务持有锁的时间，优化索引。
2. 锁升级：
   • 问题：行锁升级为表锁导致性能下降。
   • 解决：合理设计索引。

---

6. 常见锁命令

1. 查看当前事务：

   SELECT * FROM information_schema.innodb_trx;
   

2. 查看锁等待：

   SELECT * FROM information_schema.innodb_locks;
   

3. 查看锁的状态：

   SHOW ENGINE INNODB STATUS;
   

---

7. 总结

锁类型	特点	适用场景
全局锁	锁住整个数据库实例，影响所有操作。	数据库备份或迁移
表级锁	锁住整张表，粒度较粗。	表结构修改或整表操作
行级锁	锁住特定行，粒度最细，性能最高。	并发写入或查询场景
意向锁	标记锁定意图，用于加速检测冲突。	表和行同时加锁的场景
GAP 锁	锁住索引之间的间隙，防止幻读。	范围查询的事务一致性

MySQL 提供了丰富的锁机制，通过合理选择和优化，可以有效提升数据库的并发性能和数据一致性。