手动实现SEQUENCE/TRIGGER时，如何避免并发主键重复错误？

当前手动实现主键生成的逻辑在高并发场景下会出现主键冲突，核心原因是FIND LAST NO-LOCK无法防止多个会话同时获取相同的最后ID，且现有事务的重试逻辑未覆盖CREATE时的主键冲突错误。以下是两种可行的解决方式：

方案一：优化现有逻辑，增强并发控制与错误重试 #

修改代码，通过锁定最后一条记录减少冲突概率，并显式捕获主键冲突错误触发重试：

DO TRANSACTION ON ERROR UNDO, RETRY:

    DEF VAR iNextID AS INTEGER NO-UNDO.

    /* 用FOR UPDATE锁定最后一条记录，确保同一时间仅一个会话能获取并递增ID */
    FIND LAST mensagem FOR UPDATE NO-ERROR.

    IF AVAIL mensagem THEN
        iNextID = mensagem.mensagem_id + 1.
    ELSE
        iNextID = 1.

    /* 提前检查ID是否存在，降低CREATE阶段冲突概率 */
    IF CAN-FIND(mensagem WHERE mensagem.mensagem_id = iNextID NO-LOCK) THEN
        UNDO, RETRY.
    
    CREATE mensagem.

    ASSIGN 
        mensagem.mensagem_id = iNextID
        mensagem.field1 = value1
        mensagem.field2 = value2.

    /* 显式处理CREATE时的主键冲突，确保事务触发重试 */
    IF ERROR-STATUS:GET-NUM(MESSAGE) > 0 THEN DO:
        IF ERROR-STATUS:MESSAGE BEGINS "mensagem already exists" THEN
            UNDO, RETRY.
        ELSE
            UNDO, THROW.
    END.

END.

关键调整说明： #

• FIND LAST mensagem FOR UPDATE：锁定目标表的最后一条记录，避免多个会话同时拿到相同的最后ID，从根源减少并发冲突。
• 显式捕获主键冲突错误：默认的ON ERROR UNDO, RETRY不会自动重试所有错误，通过判断错误信息，对主键冲突场景强制触发重试，其他错误则抛出。

方案二：使用独立序列表管理主键（高并发场景更可靠） #

创建一个单独的序列表来存储当前最大ID，通过锁定该表的唯一记录来保证ID递增的原子性，彻底避免并发冲突：

1. 先创建序列表（无需停机，可直接创建） #

CREATE TABLE seq_mensagem (
    max_id INTEGER
).

2. 修改存储过程逻辑 #

DO TRANSACTION ON ERROR UNDO, RETRY:

    DEF VAR iNextID AS INTEGER NO-UNDO.

    /* 锁定序列表的唯一记录，保证ID递增的原子性 */
    FIND seq_mensagem FOR UPDATE NO-ERROR.
    IF NOT AVAIL seq_mensagem THEN DO:
        CREATE seq_mensagem.
        ASSIGN seq_mensagem.max_id = 0.
    END.

    iNextID = seq_mensagem.max_id + 1.
    ASSIGN seq_mensagem.max_id = iNextID.

    /* 额外检查目标表（避免序列表与目标表数据不一致的极端情况） */
    IF CAN-FIND(mensagem WHERE mensagem.mensagem_id = iNextID NO-LOCK) THEN
        UNDO, RETRY.
    
    CREATE mensagem.
    ASSIGN 
        mensagem.mensagem_id = iNextID
        mensagem.field1 = value1
        mensagem.field2 = value2.

END.

方案优势： #

• 序列表的锁定粒度极小，不会影响目标表的其他操作，性能更优。
• ID递增操作完全原子化，几乎不会出现并发冲突，适合高频率调用的场景。

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