MySQL 死锁及解决方案

🔥 MySQL 死锁及解决方案

在 MySQL 事务中，多个事务同时竞争相同资源（如表、行）时，可能会 相互等待锁释放，最终形成 死锁（Deadlock）。MySQL 需要检测和解决死锁，否则数据库可能会陷入死循环，影响系统可用性。

1️⃣ 什么是 MySQL 死锁？

死锁（Deadlock） 指两个或多个事务在持有某些资源的同时，又等待对方释放资源，导致事务无法继续执行的情况。

💡 死锁的本质：事务之间形成循环等待。

🔹 典型死锁示例

场景：两个事务交叉锁定同一批数据

-- 事务 A
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;  -- 锁住 id=1
UPDATE users SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2 的锁（被事务 B 占用）
-- 事务 B
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 2;  -- 锁住 id=2
UPDATE users SET balance = balance + 100 WHERE id = 1;  -- 等待 id=1 的锁（被事务 A 占用）

🔥 结果：

• 事务 A 持有 id=1 的锁，等待 id=2。
• 事务 B 持有 id=2 的锁，等待 id=1。
• 循环等待，产生死锁。

  ---

  2️⃣ MySQL 如何检测死锁？

  MySQL InnoDB 引擎可以自动检测死锁，并选择回滚某个事务来解除死锁。

  🔹 如何查看 MySQL 死锁？

  SHOW ENGINE INNODB STATUS;
  

  示例输出（简化版）：

  ------------------------
  LATEST DETECTED DEADLOCK
  ------------------------
  Transaction A: 
    WAITING for lock on row (users.id=2)
  Transaction B: 
    WAITING for lock on row (users.id=1)
  

  💡 MySQL 自动检测死锁，回滚 其中一个事务，另一个事务可以继续执行。

  ---

  3️⃣ 如何解决 MySQL 死锁？（最佳实践）

  ✅ 方法 1：保证事务访问顺序一致

  💡 规则：总是按照固定顺序访问资源，避免交叉锁定

  -- ✅ 事务 A 和 B 都按 `id` 递增顺序访问
  BEGIN;
  UPDATE users SET balance = balance - 100 WHERE id = 1;
  UPDATE users SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  

  📌 事务访问顺序相同，避免循环等待，减少死锁风险。

  ---

  ✅ 方法 2：使用短事务，减少锁持有时间

  💡 规则：事务越短，死锁概率越低

  -- ❌ 不推荐：事务时间过长，增加死锁概率
  BEGIN;
  UPDATE users SET balance = balance - 100 WHERE id = 1;
  -- 业务逻辑处理（等待 10 秒）
  SLEEP(10);
  UPDATE users SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  -- ✅ 推荐：立即提交，避免长时间持有锁
  BEGIN;
  UPDATE users SET balance = balance - 100 WHERE id = 1;
  UPDATE users SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  

  📌 事务执行越快，锁的持有时间越短，死锁概率越低。

  ---

  ✅ 方法 3：使用 SELECT ... FOR UPDATE 显式加锁

  💡 规则：手动锁定数据，避免隐式锁冲突

  -- ✅ 在更新前先显式加锁，避免死锁
  BEGIN;
  SELECT balance FROM users WHERE id = 1 FOR UPDATE;
  SELECT balance FROM users WHERE id = 2 FOR UPDATE;
  UPDATE users SET balance = balance - 100 WHERE id = 1;
  UPDATE users SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  

  📌 手动加锁可减少 MySQL 自动加锁导致的死锁问题。

  ---

  ✅ 方法 4：合理设置索引，避免行锁升级为表锁

  💡 规则：索引命中减少锁范围，避免锁冲突

  
  （图片来源网络，侵删）

  -- ❌ 没有索引，导致全表锁
  UPDATE users SET balance = balance - 100 WHERE name = 'Alice';
  -- ✅ 使用索引减少锁的范围
  UPDATE users SET balance = balance - 100 WHERE id = 1;
  

  📌 索引可以让 InnoDB 只锁定必要的行，减少锁冲突。

  ---

  ✅ 方法 5：使用 NOWAIT 或 SKIP LOCKED 避免等待

  💡 规则：遇到锁竞争时直接失败或跳过

  
  （图片来源网络，侵删）

  -- ✅ `NOWAIT` 避免长时间等待锁（适用于高并发场景）
  SELECT * FROM orders WHERE status = 'pending' FOR UPDATE NOWAIT;
  -- ✅ `SKIP LOCKED` 直接跳过被锁行（适用于队列消费）
  SELECT * FROM orders WHERE status = 'pending' FOR UPDATE SKIP LOCKED;
  

  📌 适用于队列型业务（如订单、任务调度）。

  ---

  4️⃣ MySQL 死锁处理流程

  📌 MySQL 处理死锁的机制：

  1. 发现死锁（InnoDB 死锁检测）。
  2. 回滚其中一个事务，让另一个事务继续执行。
  3. 事务重试机制：应用层检测死锁并重试事务。

  🔹 事务重试代码示例（Java）

  int retryCount = 3;
  while (retryCount > 0) {
      try {
          // 开启事务
          connection.setAutoCommit(false);
          
          // 业务逻辑
          statement.executeUpdate("UPDATE users SET balance = balance - 100 WHERE id = 1");
          statement.executeUpdate("UPDATE users SET balance = balance + 100 WHERE id = 2");
          
          // 提交事务
          connection.commit();
          break;
      } catch (SQLException e) {
          if (e.getMessage().contains("Deadlock found")) {
              retryCount--;
              System.out.println("死锁发生，重试中... 剩余次数: " + retryCount);
          } else {
              throw e; // 非死锁错误，抛出异常
          }
      }
  }
  

  📌 思路：

  • 检测 SQL 异常信息，如果发现是死锁，则自动重试。
  • 避免无限重试（如 3~5 次）。
  • 业务层面可使用乐观锁（版本号机制），避免竞争冲突。

    ---

    5️⃣ 总结

    死锁原因	解决方案
    交叉锁定资源	固定访问顺序（如按 ID 递增访问）
    事务执行过慢	减少事务时间，尽快提交
    隐式锁冲突	使用 SELECT ... FOR UPDATE 显式加锁
    锁范围过大	合理使用索引，减少锁的范围
    高并发竞争	使用 NOWAIT / SKIP LOCKED 处理锁等待
    死锁不可避免	应用层事务重试机制

    📌 一句话总结：死锁是 MySQL 并发访问的常见问题，优化事务逻辑、减少锁等待是关键。🚀