基于查询结果的基础行级锁实现：避免并发数据库操作冲突

嘿，这个并发场景下的“先查后写”竞态问题我太熟了！你不想锁整张表，又要避免重复插入，其实有几个非常实用的方案，都是基于数据库的行级锁或者原子操作来实现的：

方案1：使用SELECT ... FOR UPDATE锁定行/间隙（支持间隙锁的数据库，如InnoDB） #

这个方法的核心是在查询阶段就锁定目标ID对应的行（如果存在）或者间隙（如果不存在），阻止其他事务插入相同ID的行，然后在同一个事务内完成插入操作。

Java代码示例： #

// 开启事务，必须在事务内执行锁操作
connection.setAutoCommit(false);
try {
    // 关键：用FOR UPDATE锁定目标行/间隙
    String lockQuery = "SELECT total FROM `order` WHERE id = ? FOR UPDATE";
    PreparedStatement lockStmt = connection.prepareStatement(lockQuery);
    lockStmt.setString(1, "15679");
    ResultSet rs = lockStmt.executeQuery();

    if (!rs.next()) {
        // 行不存在，执行插入
        String insertQuery = "INSERT INTO `order` (id, total) VALUES (?, ?)";
        PreparedStatement insertStmt = connection.prepareStatement(insertQuery);
        insertStmt.setString(1, "15679");
        insertStmt.setBigDecimal(2, new BigDecimal("100.00"));
        insertStmt.executeUpdate();
    }

    // 提交事务，释放锁
    connection.commit();
} catch (SQLException e) {
    // 异常时回滚事务
    connection.rollback();
    throw new RuntimeException("操作失败", e);
} finally {
    // 恢复自动提交模式
    connection.setAutoCommit(true);
}

原理说明： #

在InnoDB引擎下，SELECT ... FOR UPDATE会对查询到的行加排他锁；如果查询的行不存在，它会创建间隙锁，锁定该ID所在的区间，其他事务尝试插入相同ID的行时会被阻塞，直到当前事务提交或回滚释放锁。这样就从根源上避免了两个事务同时查询为空然后重复插入的情况。

方案2：使用原子化的INSERT ... SELECT语句 #

把查询和插入合并成一个SQL语句，数据库会自动保证这个操作的原子性，不需要手动管理锁。

SQL语句示例： #

INSERT INTO `order` (id, total)
SELECT '15679', '100.00'
WHERE NOT EXISTS (SELECT 1 FROM `order` WHERE id = '15679')

Java代码调用： #

String atomicQuery = "INSERT INTO `order` (id, total) SELECT ?, ? WHERE NOT EXISTS (SELECT 1 FROM `order` WHERE id = ?)";
PreparedStatement stmt = connection.prepareStatement(atomicQuery);
stmt.setString(1, "15679");
stmt.setBigDecimal(2, new BigDecimal("100.00"));
stmt.setString(3, "15679");
int affectedRows = stmt.executeUpdate();

if (affectedRows == 1) {
    System.out.println("插入成功");
} else {
    System.out.println("行已存在，无需插入");
}

原理说明： #

这个SQL是原子执行的，数据库内部会处理锁的逻辑，确保要么插入新行，要么因为行存在而不执行任何操作，完全不会出现竞态条件。这个方法更简洁，不需要手动管理事务，适合逻辑简单的场景。

方案3：使用INSERT ... ON DUPLICATE KEY UPDATE（MySQL专属） #

虽然你提到不想依赖唯一约束的异常，但这个语法是利用唯一约束（ID列需要加唯一索引）实现原子操作，不会抛出异常，而是根据行是否存在执行插入或更新（可以设置空更新）。

SQL语句示例： #

INSERT INTO `order` (id, total) VALUES ('15679', '100.00')
ON DUPLICATE KEY UPDATE total = total; -- 空更新，仅占位，不修改数据

原理说明： #

当ID列存在唯一索引时，这个语句会原子性地检查行是否存在：

• 如果不存在：执行插入操作
• 如果存在：执行后面的UPDATE语句（这里写total=total相当于什么都不做，你也可以根据需求写实际的更新逻辑）

这个方法性能极高，适合高并发场景，唯一的前提是ID列有唯一索引（其实即使不用这个方法，为了数据一致性也应该加唯一索引）。

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