FOR UPDATE与锁

MySQL 的 FOR UPDATE 语句在特定情况下会加 记录锁（Record Lock） 和 间隙锁（Gap Lock），具体取决于事务的隔离级别和查询条件。

---

1. FOR UPDATE 的作用

FOR UPDATE 是 MySQL 中用于锁定查询结果集的语句，通常用于确保在事务中读取的数据不会被其他事务修改。它的加锁行为如下：

• 记录锁（Record Lock）：锁定符合条件的实际存在的记录。
• 间隙锁（Gap Lock）：锁定符合条件的记录之间的间隙，防止其他事务插入新记录。

---

2. 加锁行为分析

FOR UPDATE 的加锁行为取决于以下因素：

1. 事务隔离级别

• READ COMMITTED：
  • 只加记录锁，不加间隙锁。
  • 这是因为 READ COMMITTED 隔离级别允许幻读（Phantom Read），不需要防止其他事务插入新记录。
• REPEATABLE READ（默认隔离级别）：
  • 加记录锁和间隙锁。
  • 这是为了防止幻读（Phantom Read），确保事务期间查询结果集的一致性。
• SERIALIZABLE：
  • 加记录锁和间隙锁，甚至可能加表级锁。
  • 这是最严格的隔离级别，完全防止幻读。

2. 查询条件

• 唯一索引查询（精确匹配）：

  • 只加记录锁，不加间隙锁。
  • 例如：

    SELECT * FROM users WHERE id = 1 FOR UPDATE;

    这里只会对 id = 1 的记录加记录锁。

• 非唯一索引查询或范围查询：

  • 加记录锁和间隙锁。
  • 例如：

    SELECT * FROM users WHERE age > 20 FOR UPDATE;

    这里会对 age > 20 的记录加记录锁，并对符合条件的记录之间的间隙加间隙锁。

---

3. 示例分析

示例 1：唯一索引查询

SELECT * FROM users WHERE id = 1 FOR UPDATE;

• 加锁类型：记录锁。
• 原因：id 是唯一索引，精确匹配只会锁定 id = 1 的记录。

示例 2：非唯一索引查询

SELECT * FROM users WHERE age = 20 FOR UPDATE;

• 加锁类型：记录锁 + 间隙锁。
• 原因：age 是非唯一索引，MySQL 会锁定 age = 20 的记录，并锁定这些记录之间的间隙，防止其他事务插入 age = 20 的新记录。

示例 3：范围查询

SELECT * FROM users WHERE age > 20 AND age < 30 FOR UPDATE;

• 加锁类型：记录锁 + 间隙锁。
• 原因：范围查询会锁定符合条件的记录，并锁定这些记录之间的间隙，防止其他事务插入 age 在 (20, 30) 范围内的新记录。

---

4. 查看加锁情况

可以通过以下方法查看 FOR UPDATE 的加锁情况：

方法 1：使用 SHOW ENGINE INNODB STATUS

执行 FOR UPDATE 查询后，运行：

SHOW ENGINE INNODB STATUS;

在输出中查找 TRANSACTIONS 部分，查看锁信息。

方法 2：使用 performance_schema

启用 performance_schema 后，可以通过以下查询查看锁信息：

SELECT * FROM performance_schema.data_locks;

5. 什么是死锁？

死锁是指两个或多个事务相互等待对方释放锁，导致所有事务都无法继续执行的状态。MySQL 会检测到死锁并自动回滚其中一个事务，以解除死锁。

---

6. FOR UPDATE 如何导致死锁？

FOR UPDATE 语句会对查询结果集中的记录加排他锁（X 锁）。如果多个事务以不同的顺序锁定记录，就可能发生死锁。

死锁的典型场景

假设有两个事务（T1 和 T2），操作如下：

1. T1 执行：

   SELECT * FROM users WHERE id = 1 FOR UPDATE;

   • T1 对 id = 1 的记录加排他锁。

2. T2 执行：

   SELECT * FROM users WHERE id = 2 FOR UPDATE;

   • T2 对 id = 2 的记录加排他锁。

3. T1 尝试执行：

   SELECT * FROM users WHERE id = 2 FOR UPDATE;

   • T1 需要等待 T2 释放 id = 2 的锁。

4. T2 尝试执行：

   SELECT * FROM users WHERE id = 1 FOR UPDATE;

   • T2 需要等待 T1 释放 id = 1 的锁。

此时，T1 和 T2 相互等待对方释放锁，导致死锁。

---

7. 死锁的根本原因

死锁的发生需要满足以下四个条件（死锁的四个必要条件）：

1. 互斥条件：资源（如记录）一次只能被一个事务占用。
2. 占有并等待：事务已经占有一些资源，同时请求其他被占用的资源。
3. 不可抢占：事务占用的资源不能被其他事务强行抢占。
4. 循环等待：多个事务形成等待环路。

FOR UPDATE 语句会加排他锁，因此可能满足上述条件，导致死锁。

---

8. 如何避免死锁？

虽然死锁无法完全避免，但可以通过以下方法减少死锁的概率：

1. 统一加锁顺序

• 确保所有事务以相同的顺序加锁。
• 例如，如果事务需要锁定 id = 1 和 id = 2 的记录，可以约定先锁定 id 较小的记录。

2. 减少事务粒度

• 尽量缩小事务的范围，减少加锁的数量和时间。
• 例如，避免在事务中锁定大量记录。

3. 使用索引

• 确保查询条件使用索引，减少锁冲突的范围。
• 例如，在 id 列上创建索引。

4. 设置超时时间

• 通过设置锁等待超时时间（innodb_lock_wait_timeout），避免事务长时间等待。

  SET innodb_lock_wait_timeout = 50;  -- 设置锁等待超时时间为 50 秒

5. 使用乐观锁

• 在业务逻辑中使用乐观锁（如版本号机制），避免频繁加锁。

---

9. 死锁的检测与处理

MySQL 的 InnoDB 存储引擎会自动检测死锁，并选择一个事务进行回滚（通常是影响较小的事务）。可以通过以下方法查看死锁信息：

1. 查看死锁日志

• 启用死锁日志记录：

  SET GLOBAL innodb_print_all_deadlocks = 1;

• 查看错误日志（SHOW ENGINE INNODB STATUS）中的死锁信息。

2. 使用 SHOW ENGINE INNODB STATUS

• 执行以下命令查看死锁信息：

  SHOW ENGINE INNODB STATUS;

• 在输出中查找 LATEST DETECTED DEADLOCK 部分。

---

10. 示例分析

假设有两个事务（T1 和 T2），操作如下：

1. T1 执行：

   SELECT * FROM users WHERE id = 1 FOR UPDATE;

   • T1 锁定 id = 1。

2. T2 执行：

   SELECT * FROM users WHERE id = 2 FOR UPDATE;

   • T2 锁定 id = 2。

3. T1 执行：

   SELECT * FROM users WHERE id = 2 FOR UPDATE;

   • T1 等待 T2 释放 id = 2。

4. T2 执行：

   SELECT * FROM users WHERE id = 1 FOR UPDATE;

   • T2 等待 T1 释放 id = 1。

此时，MySQL 检测到死锁，并回滚其中一个事务（如 T2），解除死锁。

---

11. 总结

• 死锁风险：FOR UPDATE 可能导致死锁，尤其是在多个事务以不同顺序锁定记录时。

• 性能影响：间隙锁会增加锁冲突的概率，影响并发性能。

• 锁升级：如果锁定的记录过多，MySQL 可能会将锁升级为表级锁。

• FOR UPDATE 语句可能会导致死锁，尤其是在多个事务以不同顺序锁定记录时。

• 死锁的根本原因是多个事务相互等待对方释放锁。

• 避免死锁的方法包括统一加锁顺序、减少事务粒度、使用索引、设置超时时间等。

• MySQL 会自动检测死锁，并回滚其中一个事务。