多用户/IP并发提交时，如何生成无冲突的顺序NO编号？

问题背景 #

我的数据库表P_MAIN有ID（唯一标识）和NO（插入顺序编号）两列，多用户/IP同时提交数据时，现在用的生成NO的函数会出现重复值，原函数代码如下：

FUNCTION get_next_no
(
 P_TYPE        IN VARCHAR2
)
RETURN VARCHAR2 IS PRAGMA autonomous_transaction;

v_last_sequence     INT;
v_no                VARCHAR2(24 Byte);

BEGIN        
    SELECT  MAX(SUBSTR(no,17,8))
    INTO    v_last_sequence
    FROM    P_MAIN
    WHERE   TO_CHAR(p_date, 'YYYYMM')  = TO_CHAR(SYSDATE, 'YYYYMM')
    AND     TYPE = P_TYPE
    ;

    IF v_last_sequence IS NULL THEN
        v_last_sequence := 0;
    END IF;
    
    IF P_TYPE = 'CONV' THEN
        v_no := 'CERC-00-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || TO_CHAR(SYSDATE, 'MM') || '-' || LPAD(v_last_sequence + 1, 8, '0');
    ELSE 
        v_no := 'CERS-00-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || TO_CHAR(SYSDATE, 'MM') || '-' || LPAD(v_last_sequence + 1, 8, '0');
    END IF;
    

    RETURN v_no;
END get_next_no;

原函数问题出在哪 #

• 并发时，多个会话同时执行MAX(SUBSTR(no,17,8))会拿到同一个最大值，加1后生成的NO自然重复。
• 加的PRAGMA autonomous_transaction（自治事务）在这里没用，反而可能加剧并发冲突——因为自治事务独立于主事务，无法共享锁。

可行解决方案 #

方案1：序列+触发器（最推荐，高并发首选） #

这是Oracle原生支持的并发唯一编号方案，靠谱稳定，分两步操作：

1. 按类型创建序列（如需按月重置，可每月重建或用循环序列，8位编号最大支持到99999999，足够大部分场景使用）：

   -- 给CONV类型创建序列
   CREATE SEQUENCE seq_conv_monthly
     START WITH 1
     INCREMENT BY 1
     MAXVALUE 99999999
     MINVALUE 1
     NOCYCLE
     NOCACHE; -- 关闭缓存避免会话异常导致编号断层，高并发场景可按需开启缓存
   
   -- 给其他类型创建类似序列
   CREATE SEQUENCE seq_cers_monthly
     START WITH 1
     INCREMENT BY 1
     MAXVALUE 99999999
     MINVALUE 1
     NOCYCLE
     NOCACHE;
   

2. 编写触发器，插入数据时自动生成NO：

   CREATE OR REPLACE TRIGGER trg_p_main_gen_no
   BEFORE INSERT ON P_MAIN
   FOR EACH ROW
   DECLARE
     v_seq_num NUMBER;
     v_prefix VARCHAR2(20);
   BEGIN
     v_prefix := CASE :NEW.TYPE
                  WHEN 'CONV' THEN 'CERC-00-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || TO_CHAR(SYSDATE, 'MM') || '-'
                  ELSE 'CERS-00-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || TO_CHAR(SYSDATE, 'MM') || '-'
                END;
   
     v_seq_num := CASE :NEW.TYPE
                   WHEN 'CONV' THEN seq_conv_monthly.NEXTVAL
                   ELSE seq_cers_monthly.NEXTVAL
                 END;
   
     :NEW.NO := v_prefix || LPAD(v_seq_num, 8, '0');
   END;
   

   如需每月初重置序列，可添加定时任务（如DBMS_SCHEDULER），或在触发器中判断当前月份与序列起始月份，不一致则重置序列。

方案2：自治事务+表锁（适合并发量低的场景） #

修改原函数，查询最大值前先锁表，保证同一时间只有一个会话能获取编号：

FUNCTION get_next_no
(
 P_TYPE        IN VARCHAR2
)
RETURN VARCHAR2 IS PRAGMA autonomous_transaction;

v_last_sequence     INT;
v_no                VARCHAR2(24 Byte);

BEGIN        
    -- 锁表，直到事务提交才释放，避免并发读取同一最大值
    LOCK TABLE P_MAIN IN EXCLUSIVE MODE;

    SELECT  NVL(MAX(SUBSTR(no,17,8)), 0)
    INTO    v_last_sequence
    FROM    P_MAIN
    WHERE   TO_CHAR(p_date, 'YYYYMM')  = TO_CHAR(SYSDATE, 'YYYYMM')
    AND     TYPE = P_TYPE
    ;
    
    IF P_TYPE = 'CONV' THEN
        v_no := 'CERC-00-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || TO_CHAR(SYSDATE, 'MM') || '-' || LPAD(v_last_sequence + 1, 8, '0');
    ELSE 
        v_no := 'CERS-00-' || TO_CHAR(SYSDATE, 'YYYY') || '-' || TO_CHAR(SYSDATE, 'MM') || '-' || LPAD(v_last_sequence + 1, 8, '0');
    END IF;

    COMMIT; -- 自治事务必须提交，释放锁
    RETURN v_no;
END get_next_no;

缺点：表锁会降低并发性能，多用户提交时后续请求需等待，仅适合并发量小的场景。

方案3：单独的序列管理表（灵活可控） #

创建一张表存储每种类型每个月的当前最大编号，用行级锁控制并发，比表锁性能更优：

1. 先创建序列管理表：

   CREATE TABLE seq_manager (
     type_code VARCHAR2(10) NOT NULL,
     year_month VARCHAR2(6) NOT NULL,
     current_seq NUMBER(8) NOT NULL,
     PRIMARY KEY (type_code, year_month) -- 保证每个类型每个月的记录唯一
   );
   

2. 修改生成函数：

   FUNCTION get_next_no
   (
    P_TYPE        IN VARCHAR2
   )
   RETURN VARCHAR2 IS 
   v_year_month VARCHAR2(6) := TO_CHAR(SYSDATE, 'YYYYMM');
   v_current_seq NUMBER;
   v_no          VARCHAR2(24 Byte);
   
   BEGIN        
       -- 锁定对应行，阻止其他会话修改
       SELECT current_seq
       INTO v_current_seq
       FROM seq_manager
       WHERE type_code = P_TYPE
         AND year_month = v_year_month
       FOR UPDATE;
   
       -- 更新序列值
       UPDATE seq_manager
       SET current_seq = current_seq + 1
       WHERE type_code = P_TYPE
         AND year_month = v_year_month;
   
       -- 生成NO
       v_no := CASE P_TYPE
                WHEN 'CONV' THEN 'CERC-00-' || SUBSTR(v_year_month,1,4) || '-' || SUBSTR(v_year_month,5,2) || '-' || LPAD(v_current_seq + 1, 8, '0')
                ELSE 'CERS-00-' || SUBSTR(v_year_month,1,4) || '-' || SUBSTR(v_year_month,5,2) || '-' || LPAD(v_current_seq + 1, 8, '0')
              END;
   
       COMMIT;
       RETURN v_no;
   EXCEPTION
       WHEN NO_DATA_FOUND THEN
           -- 首次插入该类型当月数据，初始化序列为1
           INSERT INTO seq_manager (type_code, year_month, current_seq)
           VALUES (P_TYPE, v_year_month, 1);
           COMMIT;
           v_no := CASE P_TYPE
                    WHEN 'CONV' THEN 'CERC-00-' || SUBSTR(v_year_month,1,4) || '-' || SUBSTR(v_year_month,5,2) || '-' || '00000001'
                    ELSE 'CERS-00-' || SUBSTR(v_year_month,1,4) || '-' || SUBSTR(v_year_month,5,2) || '-' || '00000001'
                  END;
           RETURN v_no;
   END get_next_no;
   

   优点：行级锁仅锁定对应类型当月的记录，不影响其他类型或月份的操作，性能优于表锁；且规则灵活，修改序列逻辑直接操作管理表即可。

总结 #

• 高并发场景优先选方案1，Oracle原生支持，性能最优、可靠性最高。
• 并发量小、不想维护序列的场景用方案2，但需接受锁表带来的性能损耗。
• 需要按月重置、多类型区分，或需灵活控制序列规则的场景用方案3，逻辑清晰、扩展性强。

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