如何使用SELECT FOR UPDATE针对唯一约束避免死锁？

问题根源 #

你遇到的问题本质是：当SELECT ... FOR UPDATE查询不到对应行时，Aurora/MySQL基于的InnoDB引擎不会对不存在的行加锁，导致并发会话都能执行该查询，随后同时插入同一唯一键的记录，触发死锁。即使在Serializable或Repeatable Read隔离级别下，缺少索引支持的话，间隙锁也无法生效。

关键修复步骤 #

1. 建立联合唯一索引 #

首先必须给alert表的server_id和alert_id字段添加联合唯一索引，这是让间隙锁生效的核心前提：

ALTER TABLE alert ADD UNIQUE INDEX idx_server_alert (server_id, alert_id);

有了这个索引后，InnoDB在执行SELECT * FROM alert WHERE server_id = 'x' AND alert_id = 1 FOR UPDATE时，即使目标行不存在，也会锁定该联合索引对应的间隙，阻止其他会话的同条件SELECT ... FOR UPDATE执行，从根源避免并发插入冲突。

2. 标准JPA兼容的UPSERT流程 #

在JPA中结合悲观写锁实现安全的UPSERT，流程如下：

• 开启手动提交事务
• 使用悲观写锁查询目标记录（两种方式任选）：
  复合主键查询：

  Alert alert = entityManager.find(Alert.class, 
      new AlertId("x", 1), // 自定义复合主键类，包含serverId和alertId
      LockModeType.PESSIMISTIC_WRITE);
  

  JPQL查询：

  Alert alert = entityManager.createQuery(
      "SELECT a FROM Alert a WHERE a.serverId = :serverId AND a.alertId = :alertId", Alert.class)
      .setParameter("serverId", "x")
      .setParameter("alertId", 1)
      .setLockMode(LockModeType.PESSIMISTIC_WRITE)
      .getSingleResult();
  

• 根据查询结果执行对应操作：

  try {
      Alert alert = // 上述查询代码
      alert.setData("{...}");
      entityManager.merge(alert);
  } catch (NoResultException e) {
      Alert newAlert = new Alert();
      newAlert.setId(123);
      newAlert.setServerId("x");
      newAlert.setAlertId(1);
      newAlert.setData("{...}");
      entityManager.persist(newAlert);
  }
  

• 提交事务

3. 轻量替代方案：先更新再插入（无锁处理并发） #

如果不想依赖悲观锁，也可以用“先更新，无影响则插入”的方式，完全符合JPA规范：

int updateCount = entityManager.createQuery(
    "UPDATE Alert a SET a.data = :data WHERE a.serverId = :serverId AND a.alertId = :alertId")
    .setParameter("data", "{...}")
    .setParameter("serverId", "x")
    .setParameter("alertId", 1)
    .executeUpdate();

if (updateCount == 0) {
    try {
        Alert newAlert = new Alert();
        newAlert.setId(123);
        newAlert.setServerId("x");
        newAlert.setAlertId(1);
        newAlert.setData("{...}");
        entityManager.persist(newAlert);
    } catch (PersistenceException e) {
        // 捕获并发插入导致的唯一键冲突，重试更新即可
        entityManager.createQuery(
            "UPDATE Alert a SET a.data = :data WHERE a.serverId = :serverId AND a.alertId = :alertId")
            .setParameter("data", "{...}")
            .setParameter("serverId", "x")
            .setParameter("alertId", 1)
            .executeUpdate();
    }
}

这种方式不需要显式加锁，通过捕获异常处理并发场景，实现成本更低。

为什么之前的操作未阻塞？ #

因为缺少server_id和alert_id的联合唯一索引，InnoDB执行SELECT ... FOR UPDATE时是全表扫描，查不到行的情况下不会加任何锁，所以第二个窗口的查询能正常执行，最终两个窗口同时插入触发死锁。添加联合唯一索引后，InnoDB会锁定对应的索引间隙，让并发的SELECT ... FOR UPDATE阻塞，从而避免死锁。

内容的提问来源于stack exchange，提问作者Archimedes Trajano