如何管理已提交事务与回滚？双存储过程事务处理方案咨询

1. 批次标记+事后回滚 #

核心思路是给Proc1写入的数据打上唯一批次标识，Proc2失败时直接删除该批次的数据，模拟回滚效果：

• 修改Proc1，新增一个批次ID参数，写入数据时同时把批次ID存入myTable（需要给表加个batch_id字段，类型可以是UUID或自增ID）。
• 外层执行逻辑示例：

-- 生成唯一批次ID
SET @batch_id = UUID();

-- 执行Proc1并验证结果
START TRANSACTION;
SET @proc1_success = CALL Proc1(@batch_id);
IF @proc1_success THEN
    COMMIT;
    -- 执行Proc2
    SET @proc2_success = CALL Proc2();
    IF NOT @proc2_success THEN
        -- 删除Proc1写入的批次数据，实现回滚
        START TRANSACTION;
        DELETE FROM myTable WHERE batch_id = @batch_id;
        COMMIT;
    END IF;
ELSE
    ROLLBACK;
END IF;

这种方式兼容性强，几乎所有关系型数据库都支持，缺点是需要修改表结构增加批次字段；如果Proc2还修改了该批次外的数据，这部分修改没法回滚（若有此场景，需把Proc2的所有修改也关联批次ID）。

2. 预提交事务（部分数据库支持） #

利用数据库的预提交事务特性（比如PostgreSQL的PREPARE TRANSACTION、SQL Server的分布式事务准备），让Proc1的事务处于"已准备提交"状态（数据已对外可见，但未最终提交），等Proc2执行成功再确认提交，失败则回滚：

• 以PostgreSQL为例，执行逻辑：

-- 启动事务并执行Proc1
BEGIN;
CALL Proc1();
-- 将事务标记为预提交状态，此时数据已可被其他会话读取
PREPARE TRANSACTION 'proc1_transaction';

-- 执行Proc2并处理结果
DO $$
DECLARE
    proc2_result BOOLEAN;
BEGIN
    proc2_result := CALL Proc2();
    IF proc2_result THEN
        -- 确认提交预准备的事务
        COMMIT PREPARED 'proc1_transaction';
    ELSE
        -- 回滚预准备的事务
        ROLLBACK PREPARED 'proc1_transaction';
    END IF;
END $$;

注意：MySQL不支持该特性，且预提交事务会占用数据库资源，必须确保及时处理，避免遗留未确认的事务。

3. 镜像表切换 #

维护一个正式表的镜像副本，Proc1先写入镜像表，验证成功后切换镜像表为正式表，Proc2失败则切回原表：

• 先创建镜像表myTable_mirror，结构和myTable完全一致。
• 执行逻辑示例：

-- 执行Proc1到镜像表
START TRANSACTION;
SET @proc1_success = CALL Proc1('myTable_mirror'); -- 修改Proc1支持指定目标表
IF @proc1_success THEN
    COMMIT;
    -- 原子性切换表：原表临时改名，镜像表转正
    RENAME TABLE myTable TO myTable_temp, myTable_mirror TO myTable;
    
    -- 执行Proc2
    SET @proc2_success = CALL Proc2();
    IF NOT @proc2_success THEN
        -- 切换回原表
        RENAME TABLE myTable TO myTable_mirror, myTable_temp TO myTable;
    END IF;
ELSE
    ROLLBACK;
END IF;

这种方式的优势是切换操作原子性强，数据切换瞬间完成；缺点是需要额外维护镜像表，数据量大时初始化镜像的成本较高。

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