如何解决PL/SQL存储过程并行执行时的重复插入问题

你的问题是典型的检查-然后-操作竞态条件：两个线程同时执行检查时都判定记录不存在，随后同时尝试插入，触发主键冲突。以下是几种高效可行的解决方案，完全无需使用dbms_session.sleep()这类严重影响性能的方法：

方案1：使用MERGE语句（推荐） #

MERGE是Oracle原生的原子性DML语句，能在单个操作中完成“存在则更新，不存在则插入”的逻辑，数据库引擎会自动处理并发冲突，从根源上避免竞态问题。

修改后的存储过程示例：

PROCEDURE Insert_Aaa(
    rec_ IN Aaa_TAB%ROWTYPE)
IS
BEGIN
  MERGE INTO Aaa_TAB t
  -- 用传入参数构建源数据集
  USING (
    SELECT 
      rec_.a AS a, 
      rec_.b AS b, 
      rec_.c AS c,
      rec_.col1 AS col1, -- 替换为表中实际需要更新/插入的列
      rec_.col2 AS col2
    FROM DUAL
  ) s
  -- 主键匹配条件
  ON (t.a = s.a AND t.b = s.b AND t.c = s.c)
  -- 匹配时执行更新
  WHEN MATCHED THEN
    UPDATE SET 
      t.col1 = s.col1,
      t.col2 = s.col2
  -- 不匹配时执行插入
  WHEN NOT MATCHED THEN
    INSERT (a, b, c, col1, col2)
    VALUES (s.a, s.b, s.c, s.col1, s.col2);
END Insert_Aaa;

这种方式无需额外检查逻辑，所有并发控制由数据库内部处理，性能和可靠性最优。

方案2：通过锁定+异常捕获处理竞态 #

如果无法直接使用MERGE（比如原有Insert___和Update___过程包含复杂业务逻辑），可通过以下方式改造：

步骤1：修改检查函数，添加行级锁 #

在Check_Exist___中使用SELECT ... FOR UPDATE锁定匹配记录，避免其他线程同时修改：

FUNCTION Check_Exist___(
    p_a IN Aaa_TAB.a%TYPE, 
    p_b IN Aaa_TAB.b%TYPE, 
    p_c IN Aaa_TAB.c%TYPE
) RETURN BOOLEAN
IS
  v_dummy VARCHAR2(1);
BEGIN
  SELECT 'X' INTO v_dummy
  FROM Aaa_TAB
  WHERE a = p_a AND b = p_b AND c = p_c
  FOR UPDATE; -- 锁定找到的行，无匹配则不锁定
  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END Check_Exist___;

步骤2：捕获插入时的主键冲突异常 #

在Insert_Aaa中捕获DUP_VAL_ON_INDEX异常，当插入失败时直接执行更新：

PROCEDURE Insert_Aaa(
    rec_ IN Aaa_TAB%ROWTYPE)
IS
BEGIN
  IF NOT Check_Exist___(rec_.a, rec_.b, rec_.c) THEN
    BEGIN
      Insert___(rec_);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- 捕获主键冲突，说明另一个线程已完成插入，直接执行更新
        Update___(rec_);
    END;
  ELSE
    Update___(rec_);
  END IF;
END Insert_Aaa;

这种方式通过锁定和异常处理覆盖竞态场景，适合需要保留原有Insert___/Update___逻辑的情况。

为什么sleep方案不可行 #

你提到的dbms_session.sleep()完全不适合高并发场景：120个调用方会导致大量等待时间，不仅无法彻底避免竞态（随机sleep仍可能出现同时执行的情况），还会严重拖慢整体执行效率，直接排除即可。

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