PostgreSQL中PREPARE TRANSACTION后能否通过事务ID跨会话操作未提交数据

你对PostgreSQL中PREPARE TRANSACTION的能力存在认知偏差，你示例里写的prepare之后跨会话追加操作、读取未提交修改的逻辑完全无法实现，这个功能的设计目标是服务分布式原子提交，不是用来跨会话续接事务操作的。

当你执行PREPARE TRANSACTION 'xxx'语句的瞬间，会发生这些事：

• 当前事务的所有操作会被持久化到磁盘，事务进入"预提交"状态，之后没有任何语法支持给这个事务追加新的SQL操作，不管是同会话还是其他会话都不行
• 原会话和该事务彻底解绑，哪怕你立刻杀掉原会话，这个预提交事务的状态也不会受影响
• 对预提交事务你仅能执行两个操作：COMMIT PREPARED 'xxx'完成提交，或者ROLLBACK PREPARED 'xxx'回滚事务，不存在第三种操作选项
• 事务在预提交状态下会一直持有它加过的所有行锁、表锁，且它的未提交修改对所有其他会话都不可见——你想在prepare之后、commit之前读取更新后的name值做外部联动是根本做不到的，其他会话访问被锁的行只会被阻塞，读不到未提交数据。

你的需求本质是实现跨多会话、跨外部系统调用的操作原子性，根据业务对一致性的要求选对应方案即可：

方案1：业务层状态机+柔性事务（90%以上场景选这个，尤其是流程长、包含外部调用的场景） #

不要用数据库层的长事务或者两阶段提交扛全流程，长事务持锁会直接打垮数据库并发能力：

• 先建一张全局事务状态表，字段包含你生成的全局事务ID、事务状态（进行中/待提交/已提交/已回滚）、关联业务参数、操作日志
• 每个阶段的数据库操作都用普通本地短事务执行，操作时给关联的业务数据加临时状态标记（比如给department记录加个temp_tx_id字段存当前关联的全局事务ID），操作完直接提交，不要把事务开着等外部调用
• 执行外部系统联动操作时，通过全局事务ID查询关联的临时业务数据即可，注意正常业务查询要过滤掉带未完成事务标记的临时数据，避免脏读
• 所有阶段操作（包括外部联动）全部成功后，在一个短事务里把所有关联临时数据的正式字段更新为目标值、去掉临时标记、把事务状态更新为已提交
• 任何一步失败，就执行对应补偿逻辑：反向回滚之前已经提交的数据库修改、调用外部系统的回滚接口、把事务状态标记为已回滚，清理临时数据。

方案2：标准两阶段提交（仅适合所有参与方都支持XA协议、强一致要求极高的短流程场景） #

如果你的外部系统支持XA两阶段提交、且全流程执行时间很短（毫秒级，没有人工操作、长耗时外部调用），可以用PREPARE TRANSACTION，但必须调整操作顺序，绝对不能prepare之后再加操作：

1. 先生成全局唯一的事务ID
2. 每个数据库操作、外部系统操作都在自己的会话/连接里独立开事务执行，执行过程中不要做prepare
3. 等所有参与节点（数据库分片、外部系统）都把自己负责的操作执行完成、返回"可以提交"的确认后，再依次对每个数据库连接执行PREPARE TRANSACTION '全局事务ID'，外部系统也对应进入prepare阶段
4. 只要有一个节点prepare失败，立刻对所有已经prepare的节点执行ROLLBACK PREPARED，触发全链路回滚
5. 所有节点全部prepare成功后，再统一对所有节点执行commit操作，完成全链路原子提交

对应简单代码示例：

-- 会话1：执行第一个操作，先不提交
BEGIN;
UPDATE department SET name='newDepartment' WHERE id =4;

-- 执行外部系统联动操作，确认外部操作执行成功可提交
-- 会话2：执行第二个操作，先不提交
BEGIN;
UPDATE related_table SET department_name='newDepartment' WHERE dept_id =4;

-- 所有操作都确认执行完成后，再依次prepare
-- 会话1执行
PREPARE TRANSACTION 'tx_001';
-- 会话2执行
PREPARE TRANSACTION 'tx_001';
-- 外部系统也确认prepare成功

-- 全部prepare成功后，任意会话执行提交即可
COMMIT PREPARED 'tx_001';

• PostgreSQL默认配置中max_prepared_transactions参数很多发行版设为0，也就是默认关闭两阶段提交功能，要用的话需要提前修改配置重启数据库
• 预提交事务如果长期不提交/回滚，会一直持锁、占用存储，甚至数据库重启后也不会自动消失，必须配置定时巡检清理机制，否则时间长了会导致数据库不可用
• 只要你的业务流程包含跨网络外部调用、人工交互等耗时超过秒级的步骤，绝对不要用数据库层事务（包括预提交事务）把流程包起来，锁等待会直接拖垮整个库的正常业务。

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