Oracle PL/SQL中MERGE语句如何跳过被锁定的行？

针对大表F41021的批量更新场景，Oracle原生MERGE语句没有直接跳过锁定行的SQL提示，但可以通过以下几种方案实现需求：

1. 临时设置DML锁超时并捕获异常 #

Oracle的DML_LOCK_TIMEOUT会话参数可控制DML操作等待行锁的时间（单位：秒），默认是无限等待。你可以在MERGE前临时将其设为极短时间，当遇到锁定行时，MERGE会抛出ORA-00054异常，捕获该异常后即可跳过当前批次中被锁定的行，继续处理其他数据。

DECLARE
    {Variables};
    l_original_timeout NUMBER;
BEGIN
    -- 保存原超时设置
    SELECT value INTO l_original_timeout FROM v$parameter WHERE name = 'dml_lock_timeout';
    -- 设置1秒超时
    EXECUTE IMMEDIATE 'ALTER SESSION SET dml_lock_timeout = 1';

    FOR T IN ({Subquery}) LOOP
        INSERT INTO Table_Temp
        SELECT {Extensive calculation} FROM {Many Tables};

        BEGIN
            MERGE INTO Table_1
            USING (SELECT * FROM Table_Temp WHERE {Conditions for Table_Temp})
            ON ({Pivoting Fields})
            WHEN MATCHED THEN UPDATE
            SET {Updatable Fields};
        EXCEPTION
            WHEN OTHERS THEN
                -- 仅捕获锁超时异常，其他异常按需处理
                IF SQLCODE = -54 THEN
                    DBMS_OUTPUT.PUT_LINE('批次' || T.xxx || '存在锁定行，已跳过');
                    -- 记录跳过的批次信息到日志表，方便后续处理
                    INSERT INTO update_log (batch_id, skip_reason) VALUES (T.xxx, '行锁超时');
                ELSE
                    -- 其他异常可选择抛出或记录
                    RAISE;
                END IF;
        END;

        -- 清空临时表，准备下一批
        DELETE FROM Table_Temp;
    END LOOP;

    -- 恢复原超时设置
    EXECUTE IMMEDIATE 'ALTER SESSION SET dml_lock_timeout = ' || l_original_timeout;
END;
/

2. 预筛选可锁定行再执行MERGE #

虽然你提到无法直接添加FOR UPDATE SKIP LOCKED，但可以先从目标表中筛选出未被锁定的行，再基于这些行执行MERGE：

• 从Table_Temp中获取要匹配的主键/关联字段
• 用SELECT ... FOR UPDATE SKIP LOCKED从Table_1中筛选出可锁定的行，将关联字段存入临时表
• MERGE时仅处理临时表中存在的行

DECLARE
    {Variables};
BEGIN
    FOR T IN ({Subquery}) LOOP
        INSERT INTO Table_Temp
        SELECT {Extensive calculation} FROM {Many Tables};

        -- 创建临时表存储可处理的关联字段（若未提前创建）
        CREATE GLOBAL TEMPORARY TABLE temp_processable_rows (
            {Pivoting Fields}
        ) ON COMMIT DELETE ROWS;

        -- 筛选Table_1中未被锁定的匹配行
        INSERT INTO temp_processable_rows
        SELECT t1.{Pivoting Fields}
        FROM Table_1 t1
        JOIN Table_Temp tt ON t1.{Pivoting Fields} = tt.{Pivoting Fields}
        WHERE {Conditions for Table_Temp}
        FOR UPDATE SKIP LOCKED;

        -- 仅MERGE可处理的行
        MERGE INTO Table_1
        USING (
            SELECT tt.* 
            FROM Table_Temp tt
            JOIN temp_processable_rows tpr ON tt.{Pivoting Fields} = tpr.{Pivoting Fields}
        ) src
        ON (Table_1.{Pivoting Fields} = src.{Pivoting Fields})
        WHEN MATCHED THEN UPDATE
        SET {Updatable Fields};

        -- 清空临时表
        DELETE FROM Table_Temp;
        DELETE FROM temp_processable_rows;
    END LOOP;
END;
/

3. 自治事务隔离锁异常 #

将MERGE逻辑封装到自治事务中，当某批次遇到行锁时，自治事务可以独立回滚该批次的操作，主事务不受影响，继续处理下一批次。注意：自治事务会独立提交，需确保业务逻辑允许这种拆分方式。

-- 定义自治事务过程
CREATE OR REPLACE PROCEDURE merge_batch(p_batch_data IN SYS_REFCURSOR) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_temp_data Table_Temp%ROWTYPE;
BEGIN
    -- 清空临时表（自治事务内独立）
    DELETE FROM Table_Temp;
    -- 插入当前批次数据
    LOOP
        FETCH p_batch_data INTO l_temp_data;
        EXIT WHEN p_batch_data%NOTFOUND;
        INSERT INTO Table_Temp VALUES l_temp_data;
    END LOOP;
    CLOSE p_batch_data;

    -- 执行MERGE
    MERGE INTO Table_1
    USING (SELECT * FROM Table_Temp WHERE {Conditions for Table_Temp})
    ON ({Pivoting Fields})
    WHEN MATCHED THEN UPDATE
    SET {Updatable Fields};

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('批次处理失败：' || SQLERRM);
        -- 记录失败批次信息
        INSERT INTO update_log (error_msg) VALUES (SQLERRM);
        COMMIT;
END;
/

-- 主PL/SQL块
DECLARE
    {Variables};
    l_batch_cursor SYS_REFCURSOR;
BEGIN
    FOR T IN ({Subquery}) LOOP
        -- 获取当前批次数据游标
        OPEN l_batch_cursor FOR
            SELECT {Extensive calculation} FROM {Many Tables} WHERE {Batch Condition};
        -- 调用自治事务过程处理批次
        merge_batch(l_batch_cursor);
    END LOOP;
END;
/

注意事项 #

• 批量大小要合理：过小会增加IO开销，过大容易碰到锁定行导致整批跳过，建议根据业务场景测试调整。
• 记录跳过/失败的行：务必将无法处理的数据记录到日志表，方便后续重试或人工介入。
• 性能测试：以上方案都需要结合实际数据量和锁竞争情况做性能测试，确保满足业务要求。

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