Snowflake存储过程跨事务范围修改报错，求解决方案

问题背景 #

基于Snowflake存储过程构建的数据摄入管道，需要处理JSON数据的新增列操作：通过对比INFORMATION_SCHEMA.COLUMNS视图与每个JSON payload识别新列，存储过程结构如下：

CREATE PROCEDURE OUTERMOST_STORED_PROC () 
...
AS 
...
$$
    BEGIN TRANSACTION ; 

    COPY INTO SF_TMP FROM S3_STG; 

    CALL another_inner_proc ();

    -- 包含COMMIT或ROLLBACK的错误处理逻辑

$$;


CREATE PROCEDURE another_inner_proc () 
...
AS 
...
$$
    
    -- 遍历SF_TMP中的每个JSON对象

    -- 如果检测到新列
    CALL PROC_THAT_UPDATES_SCHEMA (affected_table, new_columns, new_columns_dtypes);

    -- 否则执行从SF_TMP到FINAL_TABLE的INSERT/UPDATE逻辑

$$;


CREATE PROCEDURE PROC_THAT_UPDATES_SCHEMA (affected_table, new_columns, new_columns_dtypes)
...
AS 
...
$$
    
    -- 根据参数执行ALTER TABLE ADD COLUMN语句

$$;

调用PROC_THAT_UPDATES_SCHEMA时触发报错：

Modifying a transaction that has started at a different scope is not allowed.

根源是Snowflake中DDL语句会自动提交当前事务，导致外层启动的事务被破坏，引发事务范围冲突。

可行解决方法 #

方法1：将Schema更新移到外层事务之外 #

核心思路是先完成表结构的检查与更新，再启动事务处理数据操作，彻底隔离DDL和DML的事务范围：

调整后的外层存储过程示例：

CREATE PROCEDURE OUTERMOST_STORED_PROC () 
...
AS 
...
$$
    -- 第一步：先执行Schema检查与更新，这部分不包裹在事务内
    CALL another_inner_proc_check_schema();

    -- 第二步：启动事务处理数据同步
    BEGIN TRANSACTION ; 

    COPY INTO SF_TMP FROM S3_STG; 

    CALL another_inner_proc_process_data();

    -- 错误处理：根据结果COMMIT或ROLLBACK
    IF (SUCCESS) THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
$$;

拆分后的内部存储过程：

-- 仅负责Schema检查与更新的存储过程
CREATE PROCEDURE another_inner_proc_check_schema () 
...
AS 
...
$$
    -- 遍历样本JSON数据检测新列
    -- 检测到新列时调用PROC_THAT_UPDATES_SCHEMA
    CALL PROC_THAT_UPDATES_SCHEMA (affected_table, new_columns, new_columns_dtypes);
$$;

-- 仅负责数据INSERT/UPDATE的存储过程
CREATE PROCEDURE another_inner_proc_process_data () 
...
AS 
...
$$
    -- 执行从SF_TMP到FINAL_TABLE的INSERT/UPDATE逻辑
$$;

方法2：拆分事务与DDL的执行上下文（应急方案） #

如果无法快速拆分存储过程，可以在执行DDL前显式提交外层事务，之后重新启动事务处理后续操作，但这种方式会破坏原事务的原子性，仅适合对事务一致性要求较低的场景：

CREATE PROCEDURE another_inner_proc () 
...
AS 
...
$$
    -- 遍历JSON对象检测新列
    IF (NEW_COLUMNS_EXIST) THEN
        -- 显式提交当前外层事务
        COMMIT;
        -- 执行Schema更新
        CALL PROC_THAT_UPDATES_SCHEMA (affected_table, new_columns, new_columns_dtypes);
        -- 重新启动事务处理后续数据操作
        BEGIN TRANSACTION;
    END IF;

    -- 执行INSERT/UPDATE逻辑
$$;

方法3：利用Snowflake原生特性简化Schema管理 #

如果场景允许，可以使用Snowflake的动态表（Dynamic Tables）或流（Streams）+任务（Tasks）来自动处理Schema演变，避免手动编写DDL逻辑：

• 动态表开启SCHEMA_EVOLUTION后，可自动同步源数据的Schema变更
• 流可以捕获源表的Schema变更事件，配合存储过程批量处理新增列

关键原理说明 #

Snowflake中所有DDL语句（如ALTER TABLE ADD COLUMN）都会隐式提交当前活跃事务，因此不能在同一个事务中混合执行DDL和DML操作。必须将Schema变更操作与数据处理操作放在独立的事务上下文或无事务上下文中，才能避免事务范围冲突的报错。

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