Oracle存储过程遇ORA-00054错误：并行DML是否为诱因？

针对你遇到的存储过程偶尔触发ORA-00054的问题，结合你的代码逻辑，以下是具体分析和解决方向：

可能的根源分析 #

1. 并行DML与TRUNCATE的锁时序冲突：虽然TRUNCATE是DDL会隐式提交，但提前启用并行DML会话状态后，后续并行INSERT的调度进程可能在TRUNCATE的锁完全释放前尝试获取锁，引发会话内部的锁竞争。
2. TRUNCATE的元数据锁残留：TRUNCATE会修改表的元数据（如高水位线），若元数据锁未及时释放，后续并行INSERT尝试获取锁时就会触发冲突。
3. 并行INSERT的进程锁竞争：并行DML执行时，多个slave进程同时操作表，极端情况下可能出现进程间的锁竞争，或与TRUNCATE残留的锁产生冲突。

具体解决措施 #

• 调整操作顺序，先TRUNCATE再启用并行DML：
  将并行会话启用步骤移到TRUNCATE之后，避免提前开启并行状态导致的锁竞争。修改后的代码如下：

  BEGIN
     -- 先执行截断操作
     EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table';
  
     -- 截断完成后再启用并行DML
     EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
  
     -- 执行并行插入
     EXECUTE IMMEDIATE 'INSERT /*+ PARALLEL(my_table, 4) */ INTO my_table (column1, column2)
                         SELECT column1, column2 FROM another_table';
  
  END;
  

• 添加短暂等待确保锁释放：
  如果是元数据锁释放不及时的问题，可在TRUNCATE后添加短暂等待，确保锁完全释放后再执行INSERT：

  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table';
     -- 等待1秒，可根据实际情况调整时长
     DBMS_LOCK.SLEEP(1);
     EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
     EXECUTE IMMEDIATE 'INSERT /*+ PARALLEL(my_table, 4) */ INTO my_table (column1, column2)
                         SELECT column1, column2 FROM another_table';
  END;
  

  注意：使用DBMS_LOCK需要对应权限，可通过GRANT EXECUTE ON DBMS_LOCK TO your_user;授权。
• 降低并行度减少竞争：
  过高的并行度会增加进程锁竞争概率，尝试将并行度从4调整为2，观察错误是否还会出现。
• 临时禁用并行验证问题：
  去掉所有并行相关设置，执行存储过程，如果不再触发ORA-00054，说明问题确实与并行DML相关，再针对性调整：

  BEGIN
     EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table';
     EXECUTE IMMEDIATE 'INSERT INTO my_table (column1, column2)
                         SELECT column1, column2 FROM another_table';
  END;
  

额外排查建议 #

出现错误时，立即执行以下SQL查询my_table的锁情况，确认锁持有者：

SELECT s.sid, s.serial#, l.type, l.lmode, l.request, o.object_name
FROM v$lock l
JOIN v$session s ON l.sid = s.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE o.object_name = 'MY_TABLE';

这能帮你确认是否真的没有其他会话持有锁，或是当前会话内部的并行进程引发的锁冲突。

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