Oracle触发器执行时违反唯一约束及性能问题求助

首先咱们直接戳破核心问题：你遇到的ORA-00001唯一约束冲突、触发器执行失败，还有那个慢到离谱且数值跳得飞快的max(nvl(col_2,0))+1查询，本质上都是同一个问题导致的——用全表扫描取最大值加1的方式生成唯一ID，在并发场景下完全不靠谱。

为什么会出这些问题？ #

• 并发冲突导致ORA-00001：当多个用户同时提交新生儿表单时，触发器里的select max(...)会同时读取到tab_2的同一个最大值，然后各自加1生成newmofid。结果就是多个会话拿着相同的ID往tab_2插数据，直接触发主键约束冲突，进而导致触发器执行失败（ORA-04088）。
• 查询慢+数值跳得快：max(col_2)需要全表扫描tab_2，数据量一大自然就慢；而数值跳得快，是因为很多并发请求都执行了max()+1的计算，哪怕这些请求最后因为约束冲突插入失败，计算出来的临时最大值已经被多次累加，所以看起来ID涨得特别快。

针对性解决方案 #

1. 最优方案：改用Oracle序列（Sequence）生成唯一ID #

序列是Oracle专门为生成唯一自增数值设计的对象，天生支持高并发，不会出现冲突，性能拉满。

• 第一步：创建序列（根据你当前的max值设置起始值）

  CREATE SEQUENCE seq_tab2_col2 
  START WITH 6030819798  -- 用你最后查到的max值作为起始点
  INCREMENT BY 1 
  NOCACHE;  -- 如果业务允许跳号（比如实例重启后），可以改成CACHE 100提升性能
  

• 第二步：修改触发器，替换掉原来的max查询
  把触发器里的：

  select max(nvl(col_2,0))+1 into newmofid from tab_2;
  

  换成：

  newmofid := seq_tab2_col2.NEXTVAL;
  

  这样不管多少用户同时提交，每个会话拿到的都是唯一的ID，彻底解决约束冲突，查询速度也会从几秒降到毫秒级。

2. 过渡方案：行级锁控制（不推荐，仅临时救急） #

如果因为业务限制暂时不能用序列，可以用一个控制表来存当前最大ID，通过行级锁避免并发冲突：

• 先创建一个控制表：

  CREATE TABLE tab2_id_control (current_max_id NUMBER PRIMARY KEY);
  -- 初始化当前最大值
  INSERT INTO tab2_id_control VALUES(6030819798);
  

• 修改触发器里的ID生成逻辑：

  DECLARE
    v_current_max NUMBER;
    -- 其他变量保持不变
  BEGIN
    -- 先锁定控制行，确保同一时间只有一个会话能修改
    SELECT current_max_id INTO v_current_max FROM tab2_id_control FOR UPDATE;
    v_current_max := v_current_max + 1;
    UPDATE tab2_id_control SET current_max_id = v_current_max;
    newmofid := v_current_max;
    
    -- 后面的查询和插入逻辑保持不变
  END;
  

  这个方法能解决冲突，但性能不如序列，而且会有锁等待，只能作为临时过渡方案。

3. 辅助优化：给col_2加索引（治标不治本） #

如果想先解决查询慢的问题（但还是会有冲突），可以给tab_2的col_2列建索引：

CREATE INDEX idx_tab2_col2 ON tab_2(col_2);

这样max(col_2)就不用全表扫描了，速度会快很多，但并发冲突的问题依然存在，所以只能配合上面的方案一起用。

触发器里的其他潜在坑 #

• 注意SELECT distinct col_1,col_2,to_char(col,'DD-MM-YYYY') INTO v_1,v_2,v_6 from table where tcid = :new.tcid;里的table是Oracle关键字，应该替换成实际的表名；另外如果这个查询可能返回多行或无数据，最好加异常处理，避免触发器因为这类错误中断。
• INSERT INTO tab_2 (all_columns) VALUES(variable_names);要确保列和变量的数量、类型完全对应，避免插入时出现类型不匹配的错误。

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